Re: Hope for a new PostgreSQL era?

http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/

Some of the points mentioned:
- MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL
in some ways. (Database extensibility if nothing else.)
- Neither EnterpriseDB (which now calls itself “The enterprise
PostgreSQL
company”) nor the PostgreSQL community leadership have covered
themselves
with stewardship glory.
- PostgreSQL advancement is not dead.

Comments?

On Wed, Dec 7, 2011 at 7:52 PM, Rodrigo E. De León Plicet
<rdeleonp(at)gmail(dot)com> wrote:
> http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/
>
> Some of the points mentioned:
> - MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL
>  in some ways.  (Database extensibility if nothing else.)
> - Neither EnterpriseDB (which now calls itself “The enterprise
> PostgreSQL
>  company”) nor the PostgreSQL community leadership have covered
> themselves
>  with stewardship glory.
> - PostgreSQL advancement is not dead.

I'd like to see the author's thoughts filled out on these points.
they seem rather vague and overly simplistic, and I wonder what
specific points he might have to make rather than this vague "hand
wavy" list he has so far.

On Wed, Dec 7, 2011 at 8:08 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
> I'd like to see the author's thoughts filled out on these points.
> they seem rather vague and overly simplistic, and  I wonder what
> specific points he might have to make rather than this vague "hand
> wavy" list he has so far.
>
>
Additionally I am not entirely sure what he means by the last point.
If you look at the work that NTT along with EDB has put into
Postgres-XC, for example, it looks to me like the Postgres ecosystem
is growing by leaps and bounds and we are approaching an era where
Oracle is no longer ahead in any significant use case.

The thing I am personally worried about is the ability of one company
to dominate the framing of PostgreSQL service offerings. For example
while in the US it hasn't caught on, a lot of people at MYGOSSCON
accepted EnterpriseDB's framing of the official PostgreSQL release as
the "community edition." If you have a single vendor which dominates
the dialogue that's a bad thing. To be clear this isn't a criticism
of EDB. I greatly appreciate the substantial effort they have put
into building Pg awareness here in SE Asia. However, it is a caution
about the recommendation that we need a corporate steward. I argue
corporate stewardship would be a strong net negative because it would
be first and foremost a way to crowd everyone else out. We have
stewardship. It's the core committee, and it's the best kind of
stewardship we can have.

Here's a useful post that I was forwarded by another LSMB developer.
http://openlife.cc/blogs/2010/november/how-grow-your-open-source-project-10x-and-revenues-5x

Additionally, I would suggest that PostgreSQL has a lot of users
because we have a great---and open---community. I think a new
PostgreSQL era is coming but I don't think it will happen the way that
blog poster implies. There is a tremendous need for Pg skills in SE
Asia right now, and I expect this to continue to grow exponentially.

PostgreSQL advancement also by my view is also not merely "not dead"
but in fact accelerating.

Best Wishes,
Chris Travers

On Dec 8, 2011 1:27 PM, "Chris Travers" <chris(dot)travers(at)gmail(dot)com> wrote:
>
> Additionally I am not entirely sure what he means by the last point.
> If you look at the work that NTT along with EDB has put into
> Postgres-XC, for example, it looks to me like the Postgres ecosystem
> is growing by leaps and bounds and we are approaching an era where
> Oracle is no longer ahead in any significant use case.

While Pg is impressively capable now, I don't agree that Oracle (if DB2,
MS-SQL etc) isn't ahead for any significant use case. Not on a purely
technical basis anyway - once cost is considered there may be a stronger
argument.

Multi-tenant hosting is a weak pint for Pg for quite a few reasons, done of
which appear below. It's not the only role Pg isn't a great fit for, but
probably one of the more obvious.

Areas in which Pg seems significantly less capable include:

- multi-tenant hosting and row level security

- admission control, queuing and resource limiting to optimally load a
machine. Some limited level is possible with external pooling, but only by
limiting concurrent workers.

- performance monitoring and diagnostics. It's way harder to find out
what's causing load on a busy Pg server or report on frequent/expensive
queries etc. Tooling is limited and fairly primitive. It's find, but
nowhere near as powerful and easy as some if the other DBs.

- prioritisation of queries or users. It's hard to say "prefer this query
over this one, give it more resources" or "user A's work always preempts
user B's" in Pg.

- transparent failover and recovery back to the original master.

- shared-storage clustering. Dunno if anyone still cares about this one
though.

On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:

> Areas in which Pg seems significantly less capable include:

Please can you explain the features Oracle has in these area, I'm not
clear. Thanks.

> - admission control, queuing and resource limiting to optimally load a
> machine. Some limited level is possible with external pooling, but only by
> limiting concurrent workers.

> - prioritisation of queries or users. It's hard to say "prefer this query
> over this one, give it more resources" or "user A's work always preempts
> user B's" in Pg.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 8 Prosinec 2011, 12:24, Craig Ringer wrote:
> - admission control, queuing and resource limiting to optimally load a
> machine. Some limited level is possible with external pooling, but only by
> limiting concurrent workers.

The first thing I'd like to see is "user profiles" - being able to set
things like work_mem, synchronous_commit, etc. on per-user basis
separately.

> - performance monitoring and diagnostics. It's way harder to find out
> what's causing load on a busy Pg server or report on frequent/expensive
> queries etc. Tooling is limited and fairly primitive. It's find, but
> nowhere near as powerful and easy as some if the other DBs.

True. Greg Smith actually mentioned this as one of the frequently asked
features in his post about two weeks ago
(http://blog.2ndquadrant.com/en/2011/11/global-trends-in-deploying-pos.html).
I've started to build my own tool and got it somehow working for my needs,
and there are other tools available, but none of them is really a complete
solution. Would be nice to form a dev group that would work on this.

> - prioritisation of queries or users. It's hard to say "prefer this query
> over this one, give it more resources" or "user A's work always preempts
> user B's" in Pg.

I wonder if the prioritisation could be done using nice - each backend is
a separate process, so why not to do 'nice(10)' for low priority processes
or something like that.

On 12/08/2011 08:53 PM, Tomas Vondra wrote:
> On 8 Prosinec 2011, 12:24, Craig Ringer wrote:
>> - admission control, queuing and resource limiting to optimally load a
>> machine. Some limited level is possible with external pooling, but only by
>> limiting concurrent workers.
>> o d
> The first thing I'd like to see is "user profiles"z- being able to set
> things like work_mem, synchronous_commit, etc. on per-user basis
> separately.
You can.

ALTER USER username SET work_mem = '100MB';

It's not a hard cap - the user can raise/lower it however they like. The
initial value can be set globally, per-user, per-database, or globally.

> I wonder if the prioritisation could be done using nice - each backend
> is a separate process, so why not to do 'nice(10)' for low priority
> processes or something like that.

Yes, to a limited degree you can prioritise queries using nice and
ionice, but it's awkward because:

- All queries run as `postgres' so you can't do per-user limiting very
easily

- The postmaster doesn't have a way to set the nice level and ionice
level when it forks a backend, nor does the backend have any way to do
it later. You can use your own user-defined C functions for this, though.

- Most importantly, even if you nice and ionice using C functions or
manually with the cmdline utilities, you can't affect the bgwriter, nor
can you affect how much data a low-priority query pushes out of cache.

--
Craig Ringer

On Thu, Dec 08, 2011 at 01:53:45PM +0100, Tomas Vondra wrote:
> I wonder if the prioritisation could be done using nice - each backend is
> a separate process, so why not to do 'nice(10)' for low priority processes
> or something like that.

This won't work because if you are holding a lock on something someone
else needs, your low nice score is going to cause them problems. It
could make things worse rather than better. (This suggestion comes up
a lot, by the way, so there's been a lot of discussion of it
historically.)

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca

2011/12/08 21:53, Tomas Vondra wrote:
>> - performance monitoring and diagnostics. It's way harder to find out
>> what's causing load on a busy Pg server or report on frequent/expensive
>> queries etc. Tooling is limited and fairly primitive. It's find, but
>> nowhere near as powerful and easy as some if the other DBs.
>
> True. Greg Smith actually mentioned this as one of the frequently asked
> features in his post about two weeks ago
> (http://blog.2ndquadrant.com/en/2011/11/global-trends-in-deploying-pos.html).
> I've started to build my own tool and got it somehow working for my needs,
> and there are other tools available, but none of them is really a complete
> solution. Would be nice to form a dev group that would work on this.

Seems a good point. I'm trying to build "a complete solution". :)

Anyway, one of the reasons of such difficulties to build "a complete solution"
is based on necessity of the support from the *entire* core code. Without the
core support, a complete solution would never be built. Obtaining LWLock
statistics or write I/O operations is actually pretty tough work for
"non-experienced" PostgreSQL DBA, like me. :)

For examples, I've been working on investigating PostgreSQL LWLock behaviors
precisely for a few weeks, and it could not be obtained within PostgreSQL
itself, therefore, I picked up SystemTap. However, SystemTap could not be
used in a production system, because it often kills the target processes. :(
How can I observe LWLocks in the production system?

There are several tools to monitor system behaviors around operating systems,
but it is far from understanding PostgreSQL behavior. And DBAs coming from
other RDBMSes, in particular proprietary RDBMSes, need it, because they've
already been using such facilities (or tools) in their RDBMSes.
That's the reason why we need more facilities to observe inside PostgreSQL.

In addition, one more reason of the difficulties is that experienced
PostgreSQL DBAs (or hackers) do not need such facilities in general,
because they can imagine how PostgreSQL works in such particular situation.

I still think we can implement (or enhance) for those facilities if we
focus on it, but I sometimes feel it's like "a chicken and egg situation".

Regards,
--
NAGAYASU Satoshi <satoshi(dot)nagayasu(at)gmail(dot)com>

On Wed, Dec 7, 2011 at 8:52 PM, Rodrigo E. De León Plicet
<rdeleonp(at)gmail(dot)com> wrote:
> http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/
>
> Some of the points mentioned:
> - MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL
>  in some ways.  (Database extensibility if nothing else.)

There is simply no comparing mysql's backend programming features with
those of postgres. Postgres is a development platform in a box, mysql
is not.

merlin

Le Thu, 8 Dec 2011 12:27:22 +0000,
Simon Riggs <simon(at)2ndQuadrant(dot)com> a écrit :

> On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
> wrote:
>
> > Areas in which Pg seems significantly less capable include:
>
> Please can you explain the features Oracle has in these area, I'm not
> clear. Thanks.

Maybe I can answer from my own Oracle experience. I hope it will be what
Craig had in mind :)

>
>
> > - admission control, queuing and resource limiting to optimally
> > load a machine. Some limited level is possible with external
> > pooling, but only by limiting concurrent workers.

Oracle has natively two ways of handling inbound connections:
- Dedicated, which is very similar to the PostgreSQL way of accepting
connections: accept(), fork() and so on
- Shared, which is based on processes listening and handling the
connections (called dispatchers) and processes doing the real work
(called workers, obviously). All of this works internally with
some sort of queuing and storing results in shared memory (I don't
remember the details of it)

The advantage of this second architecture being of course that you
can't have more than N workers hitting your database simultaneously. So
it's easier to keep the load on the server to a reasonable value.

>
> > - prioritisation of queries or users. It's hard to say "prefer this
> > query over this one, give it more resources" or "user A's work
> > always preempts user B's" in Pg.
>
It's called the resource manager in Oracle. You define 'resource plans',
'consumer groups', etc… and you get some sort of QoS for your queries.
It's mostly about CPU resource allocation if I remember correctly (I
never used it, except during training :) )

Being able of changing the backend's nice level may do something
similar I guess. I don't think Oracle's resource manager solves
the priority inversion due to locking in the database, but I'm not sure
of it.

On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com> wrote:
> Le Thu, 8 Dec 2011 12:27:22 +0000,
> Simon Riggs <simon(at)2ndQuadrant(dot)com> a écrit :
>
>> On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
>> wrote:
>>
>> > Areas in which Pg seems significantly less capable include:
>>
>> Please can you explain the features Oracle has in these area, I'm not
>> clear. Thanks.
>
> Maybe I can answer from my own Oracle experience. I hope it will be what
> Craig had in mind :)
>
>>
>>
>> > - admission control, queuing and resource limiting to optimally
>> > load a machine. Some limited level is possible with external
>> > pooling, but only by limiting concurrent workers.
>
> Oracle has natively two ways of handling inbound connections:
> - Dedicated, which is very similar to the PostgreSQL way of accepting
>  connections: accept(), fork() and so on
> - Shared, which is based on processes listening and handling the
>  connections (called dispatchers) and processes doing the real work
>  (called workers, obviously). All of this works internally with
>  some sort of queuing and storing results in shared memory (I don't
>  remember the details of it)
>
> The advantage of this second architecture being of course that you
> can't have more than N workers hitting your database simultaneously. So
> it's easier to keep the load on the server to a reasonable value.

you have a couple of very good options to achieve the same in postgres
-- pgbouncer, pgpool.

merlin

On Thu, Dec 08, 2011 at 09:29:28AM -0600, Merlin Moncure wrote:
>
> you have a couple of very good options to achieve the same in postgres
> -- pgbouncer, pgpool.

One of the central issues that Postgres has in the "enterprise" land
is exactly this sort of answer: "you have good options" but they're
"not part of the core release".

Now, we all know that this is a stupid and wrong way of thinking about
it. But one cannot complain about being held to those sorts of
enterprisey standards when one is having an enterprisey discussion.
The original analysis (on the blog) seemed to be primarily aimed at
exactly that sort of discussion, and I suspect that this is the kind
of thing that was meant by the "community leadership" not having
covered itself in stewardship glory. One of the "stewardship" tests,
from a business-analysis point of view, is whether you're going to be
able to find a reliable supply of experienced admins at all levels for
your systems.

Having a bunch of different, indifferently-documented projects that
are all doing similar but slightly different things is, to someone
looking from that point of view, a liability and not a strength. I
happen to disagree, but it always seemed to me that something the
Postgres community did poorly (and I count myself in that number,
though less now than in the past) was understanding the hardships of
the integrator and coming up with reasonably simple answers for those
kinds of questions. It is not unreasonable to say that there are no
simple answers here; but as unhappy as it makes me, those reasonably
simple answers are necessary for some classes of users.

And let's face it: companies like Oracle (and products like MySQL) are
in a position to treat those sorts of answers as part of the cost of
doing business, because they have revenue associated with their
licenses so they can pay for coming up with those answers that way.
In Postgres-land, everyone needs to charge money for those answers
(i.e. be consultants), because that's the only real place to make a
living. Alternatively, you can put together those answers as part of
your own package; but in that case, it's not "the core PostgreSQL
product", but something else.

In this respect, the decision of the core team a number of years ago
to say, "We're going to have 'integrated' replication that does x, y,
and z," was the right one, despite the fact that it undermined the
momentum of other interesting projects (and ones better suited to some
environments). Sometimes, it's better to cut off options.

Best,

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca

Le Thu, 8 Dec 2011 09:29:28 -0600,
Merlin Moncure <mmoncure(at)gmail(dot)com> a écrit :

> On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com>
> wrote:
> > Le Thu, 8 Dec 2011 12:27:22 +0000,
> > Simon Riggs <simon(at)2ndQuadrant(dot)com> a écrit :
> >
> >> On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer
> >> <ringerc(at)ringerc(dot)id(dot)au> wrote:
> >>
> >> > Areas in which Pg seems significantly less capable include:
> >>
> >> Please can you explain the features Oracle has in these area, I'm
> >> not clear. Thanks.
> >
> > Maybe I can answer from my own Oracle experience. I hope it will be
> > what Craig had in mind :)
> >
> >>
> >>
> >> > - admission control, queuing and resource limiting to optimally
> >> > load a machine. Some limited level is possible with external
> >> > pooling, but only by limiting concurrent workers.
> >
> > Oracle has natively two ways of handling inbound connections:
> > - Dedicated, which is very similar to the PostgreSQL way of
> > accepting connections: accept(), fork() and so on
> > - Shared, which is based on processes listening and handling the
> >  connections (called dispatchers) and processes doing the real work
> >  (called workers, obviously). All of this works internally with
> >  some sort of queuing and storing results in shared memory (I don't
> >  remember the details of it)
> >
> > The advantage of this second architecture being of course that you
> > can't have more than N workers hitting your database
> > simultaneously. So it's easier to keep the load on the server to a
> > reasonable value.
>
> you have a couple of very good options to achieve the same in postgres
> -- pgbouncer, pgpool.
>

I wish it was the same (I use and like both pgbouncer and pgpool too,
and they do a good job, I'm not arguing on that). But unfortunately it
isn't: you still have the notion of session for each connected client
in Oracle when using the shared servers model.

It means you keep your session variables, your prepared statements,
your running transaction, etc… in each individual session while having
the multiplexing equivalent of a 'statement level' from pgbouncer.

On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com>
> > wrote:
>
> I wish it was the same (I use and like both pgbouncer and pgpool too,
> and they do a good job, I'm not arguing on that). But unfortunately it
> isn't: you still have the notion of session for each connected client
> in Oracle when using the shared servers model.
>
> It means you keep your session variables, your prepared statements,
> your running transaction, etc… in each individual session while having
> the multiplexing equivalent of a 'statement level' from pgbouncer.

In Oracle - can the pool share connections between DB users and/or databases on the instance? If the answer is yes to either, that is a fair bit better than what we can achieve today.

Brad.

On 8 Prosinec 2011, 14:17, Craig Ringer wrote:
> You can.
>
> ALTER USER username SET work_mem = '100MB';
>
> It's not a hard cap - the user can raise/lower it however they like. The
> initial value can be set globally, per-user, per-database, or globally.

Oh, shame on me! I wasn't aware of this. Too bad it's not possible to
restrict this (changing certain config values). A simple 'before SET' hook
might do the trick.

>> I wonder if the prioritisation could be done using nice - each backend
>> is a separate process, so why not to do 'nice(10)' for low priority
>> processes or something like that.
>
> Yes, to a limited degree you can prioritise queries using nice and
> ionice, but it's awkward because:
>
> - All queries run as `postgres' so you can't do per-user limiting very
> easily
>
> - The postmaster doesn't have a way to set the nice level and ionice
> level when it forks a backend, nor does the backend have any way to do
> it later. You can use your own user-defined C functions for this, though.

Yes, that's what I meant.

> - Most importantly, even if you nice and ionice using C functions or
> manually with the cmdline utilities, you can't affect the bgwriter, nor
> can you affect how much data a low-priority query pushes out of cache.

IMHO bgwriter may be reasonably tuned by bgwriter_* GUC variables. The
user backends are probably more interesting here.

Tomas

On 8 Prosinec 2011, 16:11, Marc Cousin wrote:
>> > - admission control, queuing and resource limiting to optimally
>> > load a machine. Some limited level is possible with external
>> > pooling, but only by limiting concurrent workers.
>
> Oracle has natively two ways of handling inbound connections:
> - Dedicated, which is very similar to the PostgreSQL way of accepting
> connections: accept(), fork() and so on
> - Shared, which is based on processes listening and handling the
> connections (called dispatchers) and processes doing the real work
> (called workers, obviously). All of this works internally with
> some sort of queuing and storing results in shared memory (I don't
> remember the details of it)
>
> The advantage of this second architecture being of course that you
> can't have more than N workers hitting your database simultaneously. So
> it's easier to keep the load on the server to a reasonable value.

Which is exactly what pgbouncer and other connection pools are for ...

>> > - prioritisation of queries or users. It's hard to say "prefer this
>> > query over this one, give it more resources" or "user A's work
>> > always preempts user B's" in Pg.
>>
> It's called the resource manager in Oracle. You define 'resource plans',
> 'consumer groups', etc… and you get some sort of QoS for your queries.
> It's mostly about CPU resource allocation if I remember correctly (I
> never used it, except during training :) )

And it's damn difficult to get it working properly ... the simpler the
better here.

Tomas

On Thu, Dec 8, 2011 at 10:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com> wrote:
> Le Thu, 8 Dec 2011 09:29:28 -0600,
> Merlin Moncure <mmoncure(at)gmail(dot)com> a écrit :
>
>> On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com>
>> wrote:
>> > Le Thu, 8 Dec 2011 12:27:22 +0000,
>> > Simon Riggs <simon(at)2ndQuadrant(dot)com> a écrit :
>> >
>> >> On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer
>> >> <ringerc(at)ringerc(dot)id(dot)au> wrote:
>> >>
>> >> > Areas in which Pg seems significantly less capable include:
>> >>
>> >> Please can you explain the features Oracle has in these area, I'm
>> >> not clear. Thanks.
>> >
>> > Maybe I can answer from my own Oracle experience. I hope it will be
>> > what Craig had in mind :)
>> >
>> >>
>> >>
>> >> > - admission control, queuing and resource limiting to optimally
>> >> > load a machine. Some limited level is possible with external
>> >> > pooling, but only by limiting concurrent workers.
>> >
>> > Oracle has natively two ways of handling inbound connections:
>> > - Dedicated, which is very similar to the PostgreSQL way of
>> > accepting connections: accept(), fork() and so on
>> > - Shared, which is based on processes listening and handling the
>> >  connections (called dispatchers) and processes doing the real work
>> >  (called workers, obviously). All of this works internally with
>> >  some sort of queuing and storing results in shared memory (I don't
>> >  remember the details of it)
>> >
>> > The advantage of this second architecture being of course that you
>> > can't have more than N workers hitting your database
>> > simultaneously. So it's easier to keep the load on the server to a
>> > reasonable value.
>>
>> you have a couple of very good options to achieve the same in postgres
>> -- pgbouncer, pgpool.
>>
>
> I wish it was the same (I use and like both pgbouncer and pgpool too,
> and they do a good job, I'm not arguing on that). But unfortunately it
> isn't: you still have the notion of session for each connected client
> in Oracle when using the shared servers model.
>
> It means you keep your session variables, your prepared statements,
> your running transaction, etc… in each individual session while having
> the multiplexing equivalent of a 'statement level' from pgbouncer.

yeah -- maybe we could use a server side feature that could allow you
to save a session state and load it up later to make life easier for
connection pooled applications. however, it's not really that much
work to organize most of the things you'd use for this in an
application managed session instead of database managed one.

regarding the "enterprises won't use community supplied postgresql add
ons" point, this completely true in many cases. I do think pgbouncer
should be seriously considered for advancement as a core feature. That
said, this should be done on its own merits, not to satisfy the
capricious whims of enterprises.

merlin

Le Thu, 8 Dec 2011 16:27:56 +0000,
"Nicholson, Brad (Toronto, ON, CA)" <bnicholson(at)hp(dot)com> a écrit :

> On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com>
> > > wrote:
> >
> > I wish it was the same (I use and like both pgbouncer and pgpool
> > too, and they do a good job, I'm not arguing on that). But
> > unfortunately it isn't: you still have the notion of session for
> > each connected client in Oracle when using the shared servers model.
> >
> > It means you keep your session variables, your prepared statements,
> > your running transaction, etc… in each individual session while
> > having the multiplexing equivalent of a 'statement level' from
> > pgbouncer.
>
> In Oracle - can the pool share connections between DB users and/or
> databases on the instance? If the answer is yes to either, that is a
> fair bit better than what we can achieve today.

Between users yes. But there is only one DB per instance in Oracle :)

On 8 Prosinec 2011, 17:11, Marc Cousin wrote:
> Le Thu, 8 Dec 2011 09:29:28 -0600,
>> >> > - admission control, queuing and resource limiting to optimally
>> >> > load a machine. Some limited level is possible with external
>> >> > pooling, but only by limiting concurrent workers.
>> >
>> > Oracle has natively two ways of handling inbound connections:
>> > - Dedicated, which is very similar to the PostgreSQL way of
>> > accepting connections: accept(), fork() and so on
>> > - Shared, which is based on processes listening and handling the
>> >  connections (called dispatchers) and processes doing the real work
>> >  (called workers, obviously). All of this works internally with
>> >  some sort of queuing and storing results in shared memory (I don't
>> >  remember the details of it)
>> >
>> > The advantage of this second architecture being of course that you
>> > can't have more than N workers hitting your database
>> > simultaneously. So it's easier to keep the load on the server to a
>> > reasonable value.
>>
>> you have a couple of very good options to achieve the same in postgres
>> -- pgbouncer, pgpool.
>>
>
> I wish it was the same (I use and like both pgbouncer and pgpool too,
> and they do a good job, I'm not arguing on that). But unfortunately it
> isn't: you still have the notion of session for each connected client
> in Oracle when using the shared servers model.

True, it is not exactly the same, it's similar. I don't think PostgreSQL
will ever provide 'shared backends' the way Oracle does - it would require
significant code change. Most of the benefits can be achieved by using a
connection pool without the added complexity.

> It means you keep your session variables, your prepared statements,
> your running transaction, etc… in each individual session while having
> the multiplexing equivalent of a 'statement level' from pgbouncer.

Yes. But if you expect that PostgreSQL will mimic Oracle architecture,
then you're mistaken. It's simply a different solution with different
architecture, and that means you may need to use different application
design sometimes.

Tomas

Le Thu, 8 Dec 2011 17:54:20 +0100,
"Tomas Vondra" <tv(at)fuzzy(dot)cz> a écrit :

> On 8 Prosinec 2011, 16:11, Marc Cousin wrote:
> >> > - admission control, queuing and resource limiting to optimally
> >> > load a machine. Some limited level is possible with external
> >> > pooling, but only by limiting concurrent workers.
> >
> > Oracle has natively two ways of handling inbound connections:
> > - Dedicated, which is very similar to the PostgreSQL way of
> > accepting connections: accept(), fork() and so on
> > - Shared, which is based on processes listening and handling the
> > connections (called dispatchers) and processes doing the real work
> > (called workers, obviously). All of this works internally with
> > some sort of queuing and storing results in shared memory (I don't
> > remember the details of it)
> >
> > The advantage of this second architecture being of course that you
> > can't have more than N workers hitting your database
> > simultaneously. So it's easier to keep the load on the server to a
> > reasonable value.
>
> Which is exactly what pgbouncer and other connection pools are for ...
Yep. But with some limitations (not that important, but they exist) as
detailed in another message.

I like the pgbouncer approach as it is much simpler, but it has the
limitation that the real sessions aren't in the database anymore, so
context is lost, etc…

>
> >> > - prioritisation of queries or users. It's hard to say "prefer
> >> > this query over this one, give it more resources" or "user A's
> >> > work always preempts user B's" in Pg.
> >>
> > It's called the resource manager in Oracle. You define 'resource
> > plans', 'consumer groups', etc… and you get some sort of QoS for
> > your queries. It's mostly about CPU resource allocation if I
> > remember correctly (I never used it, except during training :) )
>
> And it's damn difficult to get it working properly ... the simpler the
> better here.

Yep, it's very hard and ugly to use. It's by the way the reason I used
it only in training, not in production situations (in production, when
it doesn't work, you have to debug the damn thing, not just throw it
away :) )

On 8 Prosinec 2011, 17:27, Nicholson, Brad (Toronto, ON, CA) wrote:
> On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com>
>> > wrote:
>>
>> I wish it was the same (I use and like both pgbouncer and pgpool too,
>> and they do a good job, I'm not arguing on that). But unfortunately it
>> isn't: you still have the notion of session for each connected client
>> in Oracle when using the shared servers model.
>>
>> It means you keep your session variables, your prepared statements,
>> your running transaction, etc… in each individual session while having
>> the multiplexing equivalent of a 'statement level' from pgbouncer.
>
> In Oracle - can the pool share connections between DB users and/or
> databases on the instance? If the answer is yes to either, that is a fair
> bit better than what we can achieve today.

Yes, each session has a UGA (User Global Area) memory, and this iss placed
either in SGA (Shared Global Area) in case of "dedicated server" or PGA
(Process Global Are) in case of "shared server." If you use shared server,
then each request might be handled by a different backend process.

PostgreSQL uses dedicated architecture which means simpler code base,
configuration etc. If you need something like a "shared server" then you
can use a connection pool, but you have to handle the session state on
your own (in the application).

Tomas

On 8 Prosinec 2011, 18:00, Marc Cousin wrote:
> Le Thu, 8 Dec 2011 16:27:56 +0000,
> "Nicholson, Brad (Toronto, ON, CA)" <bnicholson(at)hp(dot)com> a écrit :
>
>> On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com>
>> > > wrote:
>> >
>> > I wish it was the same (I use and like both pgbouncer and pgpool
>> > too, and they do a good job, I'm not arguing on that). But
>> > unfortunately it isn't: you still have the notion of session for
>> > each connected client in Oracle when using the shared servers model.
>> >
>> > It means you keep your session variables, your prepared statements,
>> > your running transaction, etc… in each individual session while
>> > having the multiplexing equivalent of a 'statement level' from
>> > pgbouncer.
>>
>> In Oracle - can the pool share connections between DB users and/or
>> databases on the instance? If the answer is yes to either, that is a
>> fair bit better than what we can achieve today.
>
> Between users yes. But there is only one DB per instance in Oracle :)

Because Oracle uses schemas instead of databases. One schema = one user =
one database.

Tomas

Le Thu, 8 Dec 2011 18:02:51 +0100,
"Tomas Vondra" <tv(at)fuzzy(dot)cz> a écrit :

> On 8 Prosinec 2011, 17:11, Marc Cousin wrote:
> > Le Thu, 8 Dec 2011 09:29:28 -0600,
> >> >> > - admission control, queuing and resource limiting to
> >> >> > optimally load a machine. Some limited level is possible with
> >> >> > external pooling, but only by limiting concurrent workers.
> >> >
> >> > Oracle has natively two ways of handling inbound connections:
> >> > - Dedicated, which is very similar to the PostgreSQL way of
> >> > accepting connections: accept(), fork() and so on
> >> > - Shared, which is based on processes listening and handling the
> >> >  connections (called dispatchers) and processes doing the real
> >> > work (called workers, obviously). All of this works internally
> >> > with some sort of queuing and storing results in shared memory
> >> > (I don't remember the details of it)
> >> >
> >> > The advantage of this second architecture being of course that
> >> > you can't have more than N workers hitting your database
> >> > simultaneously. So it's easier to keep the load on the server to
> >> > a reasonable value.
> >>
> >> you have a couple of very good options to achieve the same in
> >> postgres -- pgbouncer, pgpool.
> >>
> >
> > I wish it was the same (I use and like both pgbouncer and pgpool
> > too, and they do a good job, I'm not arguing on that). But
> > unfortunately it isn't: you still have the notion of session for
> > each connected client in Oracle when using the shared servers model.
>
> True, it is not exactly the same, it's similar. I don't think
> PostgreSQL will ever provide 'shared backends' the way Oracle does -
> it would require significant code change. Most of the benefits can be
> achieved by using a connection pool without the added complexity.

I didn't ask for it. It's just not the same, which was what I was
answering to.

>
> > It means you keep your session variables, your prepared statements,
> > your running transaction, etc… in each individual session while
> > having the multiplexing equivalent of a 'statement level' from
> > pgbouncer.
>
> Yes. But if you expect that PostgreSQL will mimic Oracle architecture,
> then you're mistaken. It's simply a different solution with different
> architecture, and that means you may need to use different application
> design sometimes.

I just don't understand what you're getting all heated up for. I don't
want PostgreSQL to mimic Oracle, and I'm only answering to Simon's
question at the begining.

I'm just saying that there are differences between the pgbouncer approach
and the shared server approach, and benefits to the later (and to the
former too by the way, mainly simplicity so less bugs, as shared servers
architecture suffered from a lot of bugs).

Le Thu, 8 Dec 2011 10:56:39 -0600,
Merlin Moncure <mmoncure(at)gmail(dot)com> a écrit :

> On Thu, Dec 8, 2011 at 10:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com>
> wrote:
> > Le Thu, 8 Dec 2011 09:29:28 -0600,
> > Merlin Moncure <mmoncure(at)gmail(dot)com> a écrit :
> >
> >> On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin <cousinmarc(at)gmail(dot)com>
> >> wrote:
> >> > Le Thu, 8 Dec 2011 12:27:22 +0000,
> >> > Simon Riggs <simon(at)2ndQuadrant(dot)com> a écrit :
> >> >
> >> >> On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer
> >> >> <ringerc(at)ringerc(dot)id(dot)au> wrote:
> >> >>
> >> >> > Areas in which Pg seems significantly less capable include:
> >> >>
> >> >> Please can you explain the features Oracle has in these area,
> >> >> I'm not clear. Thanks.
> >> >
> >> > Maybe I can answer from my own Oracle experience. I hope it will
> >> > be what Craig had in mind :)
> >> >
> >> >>
> >> >>
> >> >> > - admission control, queuing and resource limiting to
> >> >> > optimally load a machine. Some limited level is possible with
> >> >> > external pooling, but only by limiting concurrent workers.
> >> >
> >> > Oracle has natively two ways of handling inbound connections:
> >> > - Dedicated, which is very similar to the PostgreSQL way of
> >> > accepting connections: accept(), fork() and so on
> >> > - Shared, which is based on processes listening and handling the
> >> >  connections (called dispatchers) and processes doing the real
> >> > work (called workers, obviously). All of this works internally
> >> > with some sort of queuing and storing results in shared memory
> >> > (I don't remember the details of it)
> >> >
> >> > The advantage of this second architecture being of course that
> >> > you can't have more than N workers hitting your database
> >> > simultaneously. So it's easier to keep the load on the server to
> >> > a reasonable value.
> >>
> >> you have a couple of very good options to achieve the same in
> >> postgres -- pgbouncer, pgpool.
> >>
> >
> > I wish it was the same (I use and like both pgbouncer and pgpool
> > too, and they do a good job, I'm not arguing on that). But
> > unfortunately it isn't: you still have the notion of session for
> > each connected client in Oracle when using the shared servers model.
> >
> > It means you keep your session variables, your prepared statements,
> > your running transaction, etc… in each individual session while
> > having the multiplexing equivalent of a 'statement level' from
> > pgbouncer.
>
> yeah -- maybe we could use a server side feature that could allow you
> to save a session state and load it up later to make life easier for
> connection pooled applications. however, it's not really that much
> work to organize most of the things you'd use for this in an
> application managed session instead of database managed one.

For us who can change our application code, of course. But some people
can't.

>
> regarding the "enterprises won't use community supplied postgresql add
> ons" point, this completely true in many cases. I do think pgbouncer
> should be seriously considered for advancement as a core feature. That
> said, this should be done on its own merits, not to satisfy the
> capricious whims of enterprises.

Sure. I'm not advocating this. Neither am I advocating using Oracle by
the way. I'm just as pro-postgresql as anyone else. I just wanted to
weigh the pros and cons of Oracle's way of doing things versus
pgbouncer. And the shared server approach has its merits.

On 8 Prosinec 2011, 17:56, Merlin Moncure wrote:
> regarding the "enterprises won't use community supplied postgresql add
> ons" point, this completely true in many cases. I do think pgbouncer
> should be seriously considered for advancement as a core feature. That
> said, this should be done on its own merits, not to satisfy the
> capricious whims of enterprises.

I don't think so. In my experience, the enterprises are not using
PostgreSQL (or other OS software in genera) because they think there's no
guarantee or support available. If there's a third party (might be a local
consulting company) providing acceptable guarantees and support for
PostgreSQL, it may as well provide guarantees for pgbouncer and the
enterprise customer is fine.

They simply want a package with guarantees, it does not matter whether
it's in core or not.

Tomas

On 8 Prosinec 2011, 18:14, Marc Cousin wrote:
>> > It means you keep your session variables, your prepared statements,
>> > your running transaction, etc… in each individual session while
>> > having the multiplexing equivalent of a 'statement level' from
>> > pgbouncer.
>>
>> Yes. But if you expect that PostgreSQL will mimic Oracle architecture,
>> then you're mistaken. It's simply a different solution with different
>> architecture, and that means you may need to use different application
>> design sometimes.
>
> I just don't understand what you're getting all heated up for. I don't
> want PostgreSQL to mimic Oracle, and I'm only answering to Simon's
> question at the begining.

Sorry, I was not arguing with you nor heated up. It's rather a translation
mistake - in my native language we often use 'you' when we actually mean
'someone.' So it should be something like "If someone expects ... he's
mistaken," etc.

Tomas

On Thursday 08 December 2011 18:34:35 Tomas Vondra wrote:
> On 8 Prosinec 2011, 18:14, Marc Cousin wrote:
> >> > It means you keep your session variables, your prepared
> >> > statements,
> >> > your running transaction, etc… in each individual session while
> >> > having the multiplexing equivalent of a 'statement level' from
> >> > pgbouncer.
> >>
> >> Yes. But if you expect that PostgreSQL will mimic Oracle architecture,
> >> then you're mistaken. It's simply a different solution with different
> >> architecture, and that means you may need to use different application
> >> design sometimes.
> >
> > I just don't understand what you're getting all heated up for. I don't
> > want PostgreSQL to mimic Oracle, and I'm only answering to Simon's
> > question at the begining.
>
> Sorry, I was not arguing with you nor heated up. It's rather a translation
> mistake - in my native language we often use 'you' when we actually mean
> 'someone.' So it should be something like "If someone expects ... he's
> mistaken," etc.

Ok, no problem, that just felt weird :)

On 8 Prosinec 2011, 14:20, Andrew Sullivan wrote:
> On Thu, Dec 08, 2011 at 01:53:45PM +0100, Tomas Vondra wrote:
>> I wonder if the prioritisation could be done using nice - each backend
>> is
>> a separate process, so why not to do 'nice(10)' for low priority
>> processes
>> or something like that.
>
> This won't work because if you are holding a lock on something someone
> else needs, your low nice score is going to cause them problems. It
> could make things worse rather than better. (This suggestion comes up
> a lot, by the way, so there's been a lot of discussion of it
> historically.)

I'm aware of that, but there are cases when this may actually work.

For example we do have an OLTP system, but we need to build exports to
other systems regularly. The export may need to read a lot of data, but I
don't want to annoy the people who are using the system. So I could lower
the priority for the backend generating the report.

Yes, there are cases where this "priority inversion" makes it unusable.

Tomas

On 12/08/2011 03:24 AM, Craig Ringer wrote:

> - shared-storage clustering. Dunno if anyone still cares about this one
> though.

This one seems to be moving into the legacy category over the next 3-5
years.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

On 12/08/11 10:14 AM, Joshua D. Drake wrote:
>
>> - shared-storage clustering. Dunno if anyone still cares about this one
>> though.
>
> This one seems to be moving into the legacy category over the next 3-5
> years.

um, I believe this is referring to Oracle RAC clustering, not HA
active/standby. I seriously doubt Oracle is dropping RAC.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On 12/08/2011 10:54 AM, John R Pierce wrote:
>
> On 12/08/11 10:14 AM, Joshua D. Drake wrote:
>>
>>> - shared-storage clustering. Dunno if anyone still cares about this one
>>> though.
>>
>> This one seems to be moving into the legacy category over the next 3-5
>> years.
>
> um, I believe this is referring to Oracle RAC clustering, not HA
> active/standby. I seriously doubt Oracle is dropping RAC.

I meant worrying about it for Pg.

JD

>
>
>

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

On 12/08/11 11:16 AM, Joshua D. Drake wrote:
>>
>> um, I believe this is referring to Oracle RAC clustering, not HA
>> active/standby. I seriously doubt Oracle is dropping RAC.
>
> I meant worrying about it for Pg.

the odds of Postgres developing something as complex and intricate as
RAC are probably between zilch and none. RAC was for many years
completely unusable, and even now, its complicated, fragile, and expensive.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On 12/08/2011 11:24 AM, John R Pierce wrote:
>
> On 12/08/11 11:16 AM, Joshua D. Drake wrote:
>>>
>>> um, I believe this is referring to Oracle RAC clustering, not HA
>>> active/standby. I seriously doubt Oracle is dropping RAC.
>>
>> I meant worrying about it for Pg.
>
> the odds of Postgres developing something as complex and intricate as
> RAC are probably between zilch and none. RAC was for many years
> completely unusable, and even now, its complicated, fragile, and expensive.
>

Exactly.

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

On Thu, Dec 8, 2011 at 3:24 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
>
> On Dec 8, 2011 1:27 PM, "Chris Travers" <chris(dot)travers(at)gmail(dot)com> wrote:
>>
>> Additionally I am not entirely sure what he means by the last point.
>> If you look at the work that NTT along with EDB has put into
>> Postgres-XC, for example, it looks to me like the Postgres ecosystem
>> is growing by leaps and bounds and we are approaching an era where
>> Oracle is no longer ahead in any significant use case.
>
> While Pg is impressively capable now, I don't agree that Oracle (if DB2,
> MS-SQL etc) isn't ahead for any significant use case. Not on a purely
> technical basis anyway - once cost is considered there may be a stronger
> argument.

I said "approaching an era" for a reason. We aren't there yet, but we
are fast approaching it. Major areas I didn't think PostgreSQL would
ever directly compete in are now within arms reach.

Also when I say use case, I am talking like:

"I have a 2TB database and need to be able to run aggregates across
20M row tables as part of my transactional system."

What I see you mentioning are tools missing which in some cases show
use cases we aren't so good at (high security databases where
row-level security needs to be enforced, or accounting systems for
holding companies or the like), but given now that the above use case
is now within reach, I have to think the others will be soon as well.

Best Wishes,
Chris Travers

On Thu, 08 Dec 2011 11:24:12 -0800, John R Pierce <pierce(at)hogranch(dot)com>
wrote:
> On 12/08/11 11:16 AM, Joshua D. Drake wrote:
>>>
>>> um, I believe this is referring to Oracle RAC clustering, not HA
>>> active/standby. I seriously doubt Oracle is dropping RAC.
>>
>> I meant worrying about it for Pg.
>
> the odds of Postgres developing something as complex and intricate as
> RAC are probably between zilch and none. RAC was for many years
> completely unusable, and even now, its complicated, fragile, and
expensive.

Happily, the complications and fragility are now utilised by Oracle to
help sell ExaData units, on the basis that if you give Oracle even more
money, they'll sell you a RAC that actually works!

On Thu, Dec 8, 2011 at 6:54 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> There is simply no comparing mysql's backend programming features with
> those of postgres.  Postgres is a development platform in a box, mysql
> is not.
>
This and there are annoyances I have run into with MySQL, such as
deadlocks.... on a system with only one running transaction.... I
traced this problem eventually to multi-row inserts and concluded it
was a race condition in threaded processing. As I say, any db can and
should allow conflicting transactions to deadlock. It takes special
talent to allow transactions to deadlock against *themselves*......

Interestingly I have customers who run both MySQL and PostgreSQL and
have had similar issues with transactions deadlocking against
themselves on MySQL, so I know I am not unique here.

MySQL, I am sorry to say, is not catching up to PostgreSQL at least
from where I stand except for the few very specific workloads that its
query cache are designed to work with.

Best Wishes,
Chris Travers

On Thu, Dec 8, 2011 at 4:09 PM, Rodger Donaldson
<rodgerd(at)diaspora(dot)gen(dot)nz> wrote:
> On Thu, 08 Dec 2011 11:24:12 -0800, John R Pierce <pierce(at)hogranch(dot)com>
> wrote:
>> On 12/08/11 11:16 AM, Joshua D. Drake wrote:
>>>>
>>>> um, I believe this is referring to Oracle RAC clustering, not HA
>>>> active/standby. I seriously doubt Oracle is dropping RAC.
>>>
>>> I meant worrying about it for Pg.
>>
>> the odds of Postgres developing something as complex and intricate as
>> RAC are probably between zilch and none.  RAC was for many years
>> completely unusable, and even now, its complicated, fragile, and
> expensive.
>
> Happily, the complications and fragility are now utilised by Oracle to
> help sell ExaData units, on the basis that if you give Oracle even more
> money, they'll sell you a RAC that actually works!

Looking at the general design of Postgres-XC compared to RAC, which
workloads would the latter excel at as a matter of design that the
former would not? Granted Postgres-XC is still pre-1.0 (latest
release iirc is 0.9.6) and it doesn't yet support everything it needs
to, but it looks very promising in this area, and it is open source.

Best Wishes,
Chris Travers

On Thu, 8 Dec 2011 16:34:49 -0800, Chris Travers <chris(dot)travers(at)gmail(dot)com>
wrote:
> On Thu, Dec 8, 2011 at 4:09 PM, Rodger Donaldson
> <rodgerd(at)diaspora(dot)gen(dot)nz> wrote:
>> On Thu, 08 Dec 2011 11:24:12 -0800, John R Pierce <pierce(at)hogranch(dot)com>
>> wrote:
>>> On 12/08/11 11:16 AM, Joshua D. Drake wrote:
>>>>>
>>>>> um, I believe this is referring to Oracle RAC clustering, not HA
>>>>> active/standby. I seriously doubt Oracle is dropping RAC.
>>>>
>>>> I meant worrying about it for Pg.
>>>
>>> the odds of Postgres developing something as complex and intricate as
>>> RAC are probably between zilch and none.  RAC was for many years
>>> completely unusable, and even now, its complicated, fragile, and
>> expensive.
>>
>> Happily, the complications and fragility are now utilised by Oracle to
>> help sell ExaData units, on the basis that if you give Oracle even more
>> money, they'll sell you a RAC that actually works!
>
> Looking at the general design of Postgres-XC compared to RAC, which
> workloads would the latter excel at as a matter of design that the
> former would not?

Not having touched -XC I can only theorycraft, but if I've understood the
shared-nothing model correctly, I'd expect it to hammer RAC in high-write
workloads, based on my experience of contention in RAC servers - a common
condition is that since the SGA is (effectively) shared across the RAC
interconnect, you're effectively limiting your peak write performance to
the throughput and latency of your ethernet link (which is, I assume, why
the ExaData uses Infiniband). We've had to do things like set up seperate
connection pools which write to one node at a time (with failover, of
course) for high-INSERT tables to avoid the RAC becoming painfully slow.

On the other hand I don't see anything that would suggest -XC will work as
seamlessly for failover as the RAC does. We went through a period where
our applications (and DBAs!) failed to notice kernel-panic induced reboots
of RAC members at least once every couple of weeks, with absolutely no
customer impact.

Anyway, my comment was more of a dig at the selling pitch for ExaData: I
have had it pitched with a straight face that I should want to buy one
because RAC is so hard to configure and maintain.

> Granted Postgres-XC is still pre-1.0 (latest
> release iirc is 0.9.6) and it doesn't yet support everything it needs
> to, but it looks very promising in this area, and it is open source.

It looks really interesting. Thanks for the pointer.

On 12/08/2011 08:27 PM, Simon Riggs wrote:
> On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer<ringerc(at)ringerc(dot)id(dot)au> wrote:
>
>> Areas in which Pg seems significantly less capable include:
> Please can you explain the features Oracle has in these area, I'm not
> clear. Thanks.
>
Marc has, as I was hoping, done so much better than I could. Most of
what I know is 2nd hand from Oracle users - I'm not one myself.

It's interesting to see the view that the resource manager for query and
user prioritisation is hard to use in practice. That's not something I'd
heard before, but I can't say I'm entirely surprised given how
complicated problems around lock management and priority inversion are
to get right even in a system where there *aren't* free-form dynamic
user-defined queries running.

--
Craig Ringer

On 12/09/2011 01:02 AM, Tomas Vondra wrote:
> On 8 Prosinec 2011, 17:11, Marc Cousin wrote:
>> Le Thu, 8 Dec 2011 09:29:28 -0600,
>>>>>> - admission control, queuing and resource limiting to optimally
>>>>>> load a machine. Some limited level is possible with external
>>>>>> pooling, but only by limiting concurrent workers.
>>>> Oracle has natively two ways of handling inbound connections:
>>>> - Dedicated, which is very similar to the PostgreSQL way of
>>>> accepting connections: accept(), fork() and so on
>>>> - Shared, which is based on processes listening and handling the
>>>> connections (called dispatchers) and processes doing the real work
>>>> (called workers, obviously). All of this works internally with
>>>> some sort of queuing and storing results in shared memory (I don't
>>>> remember the details of it)
>>>>
>>>> The advantage of this second architecture being of course that you
>>>> can't have more than N workers hitting your database
>>>> simultaneously. So it's easier to keep the load on the server to a
>>>> reasonable value.
>>> you have a couple of very good options to achieve the same in postgres
>>> -- pgbouncer, pgpool.
>>>
>> I wish it was the same (I use and like both pgbouncer and pgpool too,
>> and they do a good job, I'm not arguing on that). But unfortunately it
>> isn't: you still have the notion of session for each connected client
>> in Oracle when using the shared servers model.
> True, it is not exactly the same, it's similar. I don't think PostgreSQL
> will ever provide 'shared backends' the way Oracle does - it would require
> significant code change. Most of the benefits can be achieved by using a
> connection pool without the added complexity.
>
Yep - a connection pool that could save and restore session state,
separating "executor/backend" from "connection/session", would produce
much the same result with a lot less complexity. It's one of the reasons
I'd love to see in-core pooling, because I don't see how an out-of-core
solution can maintain session state like advisory locks, HOLD cursors, etc.

--
Craig Ringer

On Thu, Dec 8, 2011 at 6:47 PM, Rodger Donaldson
<rodgerd(at)diaspora(dot)gen(dot)nz> wrote:
> On Thu, 8 Dec 2011 16:34:49 -0800, Chris Travers <chris(dot)travers(at)gmail(dot)com>
> wrote:
>> On Thu, Dec 8, 2011 at 4:09 PM, Rodger Donaldson
>> <rodgerd(at)diaspora(dot)gen(dot)nz> wrote:
>>> On Thu, 08 Dec 2011 11:24:12 -0800, John R Pierce <pierce(at)hogranch(dot)com>
>>> wrote:
>>>> On 12/08/11 11:16 AM, Joshua D. Drake wrote:
>>>>>>
>>>>>> um, I believe this is referring to Oracle RAC clustering, not HA
>>>>>> active/standby. I seriously doubt Oracle is dropping RAC.
>>>>>
>>>>> I meant worrying about it for Pg.
>>>>
>>>> the odds of Postgres developing something as complex and intricate as
>>>> RAC are probably between zilch and none.  RAC was for many years
>>>> completely unusable, and even now, its complicated, fragile, and
>>> expensive.
>>>
>>> Happily, the complications and fragility are now utilised by Oracle to
>>> help sell ExaData units, on the basis that if you give Oracle even more
>>> money, they'll sell you a RAC that actually works!
>>
>> Looking at the general design of Postgres-XC compared to RAC, which
>> workloads would the latter excel at as a matter of design that the
>> former would not?
>
> Not having touched -XC I can only theorycraft, but if I've understood the
> shared-nothing model correctly, I'd expect it to hammer RAC in high-write
> workloads, based on my experience of contention in RAC servers - a common
> condition is that since the SGA is (effectively) shared across the RAC
> interconnect, you're effectively limiting your peak write performance to
> the throughput and latency of your ethernet link (which is, I assume, why
> the ExaData uses Infiniband).  We've had to do things like set up seperate
> connection pools which write to one node at a time (with failover, of
> course) for high-INSERT tables to avoid the RAC becoming painfully slow.

I think you have to define "shared-nothing" in this context. Like a
shared-storage architecture, this has two tiers, namely a tier that
orchestrates queries and a storage tier for lack of a better word. In
other words, XC is attempting to be like Teradata more than like RAC.

In fact one of the key issues here is that Postgres-XC is supposed to
be write-scalable.

>
> On the other hand I don't see anything that would suggest -XC will work as
> seamlessly for failover as the RAC does.  We went through a period where
> our applications (and DBAs!) failed to notice kernel-panic induced reboots
> of RAC members at least once every couple of weeks, with absolutely no
> customer impact.

Looking to me like it would be quite possible to set it up so that it
would be seemless in the event of node reboots at least on the
coordinator tier. On the storage tier, not sure the extent to which
this would be possible but it might depend on Postgresql replication
options and how well supported they are by XC.

>
> Anyway, my comment was more of a dig at the selling pitch for ExaData: I
> have had it pitched with a straight face that I should want to buy one
> because RAC is so hard to configure and maintain.

In which case it's good that Pg isnt gunning for that market, right?

>
>> Granted Postgres-XC is still pre-1.0 (latest
>> release iirc is 0.9.6) and it doesn't yet support everything it needs
>> to, but  it looks very promising in this area, and it is open source.
>
> It looks really interesting.  Thanks for the pointer.

YW :-)

Chris Travers

Le Fri, 09 Dec 2011 11:11:12 +0800,
Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> a écrit :

> On 12/08/2011 08:27 PM, Simon Riggs wrote:
> > On Thu, Dec 8, 2011 at 11:24 AM, Craig
> > Ringer<ringerc(at)ringerc(dot)id(dot)au> wrote:
> >
> >> Areas in which Pg seems significantly less capable include:
> > Please can you explain the features Oracle has in these area, I'm
> > not clear. Thanks.
> >
> Marc has, as I was hoping, done so much better than I could. Most of
> what I know is 2nd hand from Oracle users - I'm not one myself.
>
> It's interesting to see the view that the resource manager for query
> and user prioritisation is hard to use in practice. That's not
> something I'd heard before, but I can't say I'm entirely surprised
> given how complicated problems around lock management and priority
> inversion are to get right even in a system where there *aren't*
> free-form dynamic user-defined queries running.

The complexity, at least for me, came from the user interface (at
least a dozen of stored procedures with a complex syntax) to set up and
monitor the resource manager.

I don't think it manages the priority inversion problems, just CPU
priorities. I asked the Oracle trainer, who wasn't sure either :)

On Thu, Dec 8, 2011 at 3:11 PM, Marc Cousin <cousinmarc(at)gmail(dot)com> wrote:
> Le Thu, 8 Dec 2011 12:27:22 +0000,
> Simon Riggs <simon(at)2ndQuadrant(dot)com> a écrit :
>
>> On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
>> wrote:
>>
>> > Areas in which Pg seems significantly less capable include:
>>
>> Please can you explain the features Oracle has in these area, I'm not
>> clear. Thanks.
>
> Maybe I can answer from my own Oracle experience. I hope it will be what
> Craig had in mind :)
>
>>
>>
>> > - admission control, queuing and resource limiting to optimally
>> > load a machine. Some limited level is possible with external
>> > pooling, but only by limiting concurrent workers.
>
> Oracle has natively two ways of handling inbound connections:
> - Dedicated, which is very similar to the PostgreSQL way of accepting
>  connections: accept(), fork() and so on
> - Shared, which is based on processes listening and handling the
>  connections (called dispatchers) and processes doing the real work
>  (called workers, obviously). All of this works internally with
>  some sort of queuing and storing results in shared memory (I don't
>  remember the details of it)

> The advantage of this second architecture being of course that you
> can't have more than N workers hitting your database simultaneously. So
> it's easier to keep the load on the server to a reasonable value.
>
>>
>> > - prioritisation of queries or users. It's hard to say "prefer this
>> > query over this one, give it more resources" or "user A's work
>> > always preempts user B's" in Pg.
>>
> It's called the resource manager in Oracle. You define 'resource plans',
> 'consumer groups', etc… and you get some sort of QoS for your queries.
> It's mostly about CPU resource allocation if I remember correctly (I
> never used it, except during training :) )
>
> Being able of changing the backend's nice level may do something
> similar I guess. I don't think Oracle's resource manager solves
> the priority inversion due to locking in the database, but I'm not sure
> of it.

Thanks, sounds interesting.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Thu, Dec 8, 2011 at 2:54 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Wed, Dec 7, 2011 at 8:52 PM, Rodrigo E. De León Plicet
> <rdeleonp(at)gmail(dot)com> wrote:
>> http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/
>>
>> Some of the points mentioned:
>> - MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL
>>  in some ways.  (Database extensibility if nothing else.)
>
> There is simply no comparing mysql's backend programming features with
> those of postgres.  Postgres is a development platform in a box, mysql
> is not.

A key point, I think, but not just as a comparison against other RDBMS.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Am 08.12.2011 19:54, schrieb John R Pierce:
> On 12/08/11 10:14 AM, Joshua D. Drake wrote:
>>
>>> - shared-storage clustering. Dunno if anyone still cares about this one
>>> though.
>>
>> This one seems to be moving into the legacy category over the next 3-5
>> years.
>
> um, I believe this is referring to Oracle RAC clustering, not HA
> active/standby. I seriously doubt Oracle is dropping RAC.

Oracle Exadata reqires RAC. Therefore I also don't think, they will drop
this ;-)

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

On 12/08/2011 09:48 AM, Satoshi Nagayasu wrote:
> For examples, I've been working on investigating PostgreSQL LWLock
> behaviors
> precisely for a few weeks, and it could not be obtained within PostgreSQL
> itself, therefore, I picked up SystemTap. However, SystemTap could not be
> used in a production system, because it often kills the target
> processes. :(
> How can I observe LWLocks in the production system?

I decided about a year ago that further work on using SystemTap was a
black hole: time goes in, nothing really usable on any production
server seems to come out. It can be useful for collecting data in a
developer context. But the sort of problems people are more interested
in all involve "why is the production server doing this?", and as you've
also discovered the only reasonable answer so far doesn't involve
SystemTap; it involves DTrace and either Solaris or FreeBSD (or Mac OS,
for smaller server hardware deployments). Since those platforms are
problematic to run database servers on in many cases, that doesn't help
very much.

I'm planning to put that instrumentation into the database directly,
which is what people with Oracle background are asking for. There are
two underlying low-level problems to solve before even starting that:

-How can the overhead of collecting the timing data be kept down? It's
really high in some places. This is being worked out right now on
pgsql-hackers, see "Timing overhead and Linux clock sources"

-How do you log the potentially large amount of data collected without
killing server performance? Initial discussions also happening right
now, see "logging in high performance systems".

I feel this will increasingly be the top blocker for performance
sensitive deployments in the coming year, people used to having these
tools in Oracle cannot imagine how they would operate without them. One
of my big pictures goals is have this available as a compile-time option
starting in PostgreSQL 9.3 in 2013, piggybacked off the existing DTrace
support. And the earlier the better--since many migrations have a long
lead time, just knowing it's coming in the next version would be good
enough for some people who are blocked right now to start working on theirs.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

On 12/10/2011 09:54 AM, Greg Smith wrote:
> I'm planning to put that instrumentation into the database directly,
> which is what people with Oracle background are asking for.

FWIW, even for folks like me who've come from a general OSS DB
background with a smattering of old Sybase and other primitive stuff,
it's still a concern. I know enough about Linux's guts, I/O and memory
behaviour to be able to track down many issues but it takes *time*, time
I can't spend on any of the numerous other things I have to also be
doing. Right now Pg performance fine-tuning is more of an expert skill
(as you know!) and for those with a less strong background in Linux/UNIX
systems and tuning it's a bit of a barrier.

Thankfully my workload is so light I don't need to care; manual EXPLAIN
ANALYSE and auto_explain along with vmstat/iotop is enough for me.

> -How can the overhead of collecting the timing data be kept down?
> It's really high in some places. This is being worked out right now
> on pgsql-hackers, see "Timing overhead and Linux clock sources"

One thing I think would be interesting for this would be to identify
slow queries (without doing detailed plan timing) and flag them for more
detailed timing if they're run again within <x> time. I suspect this
would only be practical with parameterised prepared statements where the
query string remained the same, but that'd still be interesting -
essentially automatically upgrading the log level for problem queries
from slow query logging to auto_explain with explain analyse.

The main issue would be exempting queries that're expected to take
longer than the slow query threshold, like reporting queries, where you
wouldn't want to pay that overhead. That should be handled by forgetting
about slow queries that aren't run again too soon, so they get flagged
for EXPLAIN ANALYZE next run but forgotten about before they're next run.

I don't actually need this myself, it's just something I've been
thinking about as a way to reduce the admin load of identifying and
tuning problem queries.

> I feel this will increasingly be the top blocker for performance
> sensitive deployments in the coming year, people used to having these
> tools in Oracle cannot imagine how they would operate without them.

Yep, especially since there's nothing in Pg to manage user/query
priorities for I/O or CPU, so the ability to actively manage performance
problems from the outside is particularly crucial. You'll always want to
do that of course, and it's probably _better_ than relying on work
priorities, especially since it sounds from recent comments like even on
Oracle those priority features aren't what you'd call friendly.

Personally I'd choose good performance monitoring over user/query
priorities any day. With good perf monitoring I can script from the
outside I have a lot more control, can send alerts, etc etc.

--
Craig Ringer

On Sat, Dec 10, 2011 at 7:28 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
> The main issue would be exempting queries that're expected to take longer
> than the slow query threshold, like reporting queries, where you wouldn't
> want to pay that overhead.

One trick you can use for this is to assign the reporting application
a different user and then alter user yada yada to turn off logging of
slow queries etc for that user. Since it's often useful to increase
work_mem and / or change random_page_cost and so on for a reporting
user, it's pretty common to do this anyway.

On 12/10/2011 09:28 PM, Craig Ringer wrote:
> One thing I think would be interesting for this would be to identify
> slow queries (without doing detailed plan timing) and flag them for
> more detailed timing if they're run again within <x> time. I suspect
> this would only be practical with parameterised prepared statements
> where the query string remained the same, but that'd still be interesting

There are actually two patches sitting in the current PostgreSQL
CommitFest that allow normalizing query strings in a way that they could
be handled like this even if not prepared, as part of
pg_stat_statements. What you're asking for is basically a hybrid of
that and auto_explain, with something smarter deciding when the explain
is triggered. Interesting idea, I hadn't thought of that heuristic
before. It won't be hard to do if the query normalization stuff commits.

> Personally I'd choose good performance monitoring over user/query
> priorities any day. With good perf monitoring I can script from the
> outside I have a lot more control, can send alerts, etc etc.

Luckily for you it's hard to do it in any other order. When I think
about how we'd have to validate whether query prioritization code was
operating as expected or not, I imagine some extra monitoring tools
really need to get built first. Might as well expose those for people
like yourself too, once they're built for that purpose.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

2011/12/10 10:54, Greg Smith wrote:
> On 12/08/2011 09:48 AM, Satoshi Nagayasu wrote:
>> For examples, I've been working on investigating PostgreSQL LWLock behaviors
>> precisely for a few weeks, and it could not be obtained within PostgreSQL
>> itself, therefore, I picked up SystemTap. However, SystemTap could not be
>> used in a production system, because it often kills the target processes. :(
>> How can I observe LWLocks in the production system?
>
> I decided about a year ago that further work on using SystemTap was a black hole: time goes in, nothing really usable on any production server seems to come out. It can be useful for collecting data in a developer context. But the sort of problems people are more interested in all involve "why is the production server doing this?", and as you've also discovered the only reasonable answer so far doesn't involve SystemTap; it involves DTrace and either Solaris or FreeBSD (or Mac OS, for smaller server hardware deployments). Since those platforms are problematic to run database servers on in many cases, that doesn't help very much.

Absolutely. SystemTap would be useful if I'm able to reproduce the situation
outside the production system. However, in most cases, it would be actually
difficult.

> I'm planning to put that instrumentation into the database directly, which is what people with Oracle background are asking for. There are two underlying low-level problems to solve before even starting that:
>
> -How can the overhead of collecting the timing data be kept down? It's really high in some places. This is being worked out right now on pgsql-hackers, see "Timing overhead and Linux clock sources"
>
> -How do you log the potentially large amount of data collected without killing server performance? Initial discussions also happening right now, see "logging in high performance systems".
>
> I feel this will increasingly be the top blocker for performance sensitive deployments in the coming year, people used to having these tools in Oracle cannot imagine how they would operate without them. One of my big pictures goals is have this available as a compile-time option starting in PostgreSQL 9.3 in 2013, piggybacked off the existing DTrace support. And the earlier the better--since many migrations have a long lead time, just knowing it's coming in the next version would be good enough for some people who are blocked right now to start working on theirs.

I'm glad to hear that. I'm very interested in focusing on it,
and will follow the threads. Thanks.

--
NAGAYASU Satoshi <satoshi(dot)nagayasu(at)gmail(dot)com>

Sorry for delay,

>>I wonder if the prioritisation could be done
>>using nice - each backend is a separate
>>process, so why not to do 'nice(10)' for low
>>priority processes or something like that.
>
>Yes, to a limited degree you can prioritise
>queries using nice and ionice, but it's awkward because:
>
>- All queries run as `postgres' so you can't do per-user limiting very easily
>
>- The postmaster doesn't have a way to set the
>nice level and ionice level when it forks a
>backend, nor does the backend have any way to do
>it later. You can use your own user-defined C functions for this, though.

Postmaster hasn't a way to modify the system nice
or ionice, but it can send signals to child
process. Sending a user signal to sleep
completely the child (i/o and cpu) or before any
i/o call can do the trick. Perhaps it's time to
add a query scheduler or a more complete/complex one in postgres.

>--
>Craig Ringer

----------------------------------------------
Si la vida te da la espalda, ¡tocale el culo!

Hello,
> Absolutely. SystemTap would be useful if I'm able to reproduce the
situation
> outside the production system. However, in most cases, it would be
actually
> difficult.
At the db level, Oracle provides "Database replay" feature. that lets you
replay the production server events in the development/test environment.
http://docs.oracle.com/cd/B28359_01/server.111/e12253/dbr_intro.htm
Won't something like this be useful in PostgreSQL? It will let us mimic
the production environment load and analyze it better.
Regards,
Jayadevan

DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

Hi,

On 12 December 2011 15:39, Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com> wrote:
> At the db level, Oracle provides "Database replay" feature. that lets you
> replay the production server events in the development/test environment.
> http://docs.oracle.com/cd/B28359_01/server.111/e12253/dbr_intro.htm
> Won't something like this be useful in PostgreSQL? It will let us mimic the
> production environment load and analyze it better.

There is project called pgreplay (http://pgreplay.projects.postgresql.org/):
pgreplay reads a PostgreSQL log file (not a WAL file), extracts the
SQL statements and executes them in the same order and relative time
against a PostgreSQL database cluster. The idea is to replay a
real-world database workload as exactly as possible.

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)

On 12/11/2011 11:39 PM, Jayadevan M wrote:
> At the db level, Oracle provides "Database replay" feature. that lets
> you replay the production server events in the development/test
> environment.
> http://docs.oracle.com/cd/B28359_01/server.111/e12253/dbr_intro.htm
> Won't something like this be useful in PostgreSQL? It will let us
> mimic the production environment load and analyze it better.

There are several projects aiming at this goal in various ways:
http://wiki.postgresql.org/wiki/Statement_Playback

Some of the features currently under development right now will make
this sort of thing easier to build into the core database. For example,
the recent "Command Triggers" feature submission will make it easier to
catch DDL changes as well as queries for this sort of thing.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

On 12/09/2011 08:54 PM, Greg Smith wrote:
> I decided about a year ago that further work on using Systemtap was a
> black hole: time goes in, nothing really usable on any production
> server seems to come out.

My off-list e-mail this weekend has, quite rightly, pointed out that
this cheap shot is unfair bordering on libel toward the hard working
Systemtap developers. I'd like to publicly apologize for that and
clarify my frustrated statement here (I'd *really* like this sort of
tool available more)

The main problem I've had with Systemtap is its reputation; I don't
actually have any real, informed gripes about its current state. But
the sort of customers I have are very risk-adverse. PostgreSQL does a
good job attracting that sort of user. I'm sure we have a
disproportionate number of them relative to your average open-source
program. Accordingly, unless a piece of software is very trusted, it's
hard for me to convince anyone to use it. (See "why Greg hates the
disclaimers around the PostgreSQL contrib modules") That makes it hard
for me to give Systemtap a serious spin on most of the production
servers I see my hardest problems on. That's the reason behind the
statement I made badly here--regardless of how much I know about it, I
can't seem to get Systemtap deployed in the places I spent the most time
working at.

True or false, the "Systemtap is more likely to crash your kernel than
DTrace" meme is out there. I think some of that is an unexpectedly bad
side-effect of its open-source development. DTrace had the luxury of
being hidden from the world at large until it was well formed. Whereas
a lot of people saw Systemtap in a really early state, formed opinions
several years ago, and the oldest of those are some of the highest
ranking pages when you search for information. I just searched again
today, and there's mounds of stuff from 2006 and 2007 that surely
doesn't reflect the current state of things coming back from that.
Systemtap didn't get a 1.0 release until September 2009.

As someone who has spent a lot of time at the wrong end of the
"PostgreSQL is slower than MySQL" meme, I shouldn't have just thrown
this sort of criticism out there without explaining the basis for my
statement. I hope this clears up what I meant. Ultimately I think we
need both more tools like Systemtap and DTrace, as well as more
instrumentation inside PostgreSQL, to cover all of the things people
would like visibility into.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

On Sat, Dec 10, 2011 at 9:28 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:

>
> One thing I think would be interesting for this would be to identify slow
> queries (without doing detailed plan timing) and flag them for more
> detailed timing if they're run again within <x> time. I suspect this would
> only be practical with parameterised prepared statements where the query
> string remained the same, but that'd still be interesting - essentially
> automatically upgrading the log level for problem queries from slow query
> logging to auto_explain with explain analyse.

I'll suggest a different take. How about adding a feature where the system
flags queries that are taking longer than the optimizer expects? The
optimizer must be coming up with some kind of cost number that it uses to
rank query plans. If Postgres is using significantly (1) more than the
expected cost when executing the query, then that's a sign that something
is wrong (statistics wrong, cost ratios out of whack, etc).

I could see a future where Postgres could either alert a DBA to the issue,
or try to take corrective action on it's own (queue up a table or index for
a statistics update). Maybe the next time a table is probed, let Postgres
collect the distribution statistics as a side effect of the query. I'm not
aware of any database engine that does that today.

I wish I had the programming chops to take a swing at this....

(1) the definition of "significantly" is left as an exercise for the
configuration file :)

--
e-Mail is the equivalent of a postcard written in pencil. This message may
not have been sent by me, or intended for you. It may have been read or
even modified while in transit. e-Mail disclaimers have the same force in
law as a note passed in study hall. If your corporate attorney says that
you need an disclaimer in your signature, you need a new corporate attorney.

Hello,
>
> Some of the features currently under development right now will make
> this sort of thing easier to build into the core database. For
> example, the recent "Command Triggers" feature submission will make
> it easier to catch DDL changes as well as queries for this sort of
thing.
Thank you for all the responses. I am a long-time Oracle guy who loves
trying PostgreSQL because it is looks like a great database. I try it out
on my pc, and have no production experience with PostgrSQL. We recently
tried moving our product (using Oracle) with more than 100 GB data to
PostgreSQL and conducted performance testing. It did an outstanding job!
But I miss all those user-friendly trouble-shooting utilities (like
Automatic Workload Repository,Active Session History etc etc) in
PostgreSQL. Yes - some of them are there, but one has to search,download,
configure etc. I hope many of these features will become part of the
'core' soon.
Regards,
Jayadevan

DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

On 12/12/2011 10:33 PM, Jayadevan M wrote:
> But I miss all those user-friendly trouble-shooting utilities (like
> Automatic Workload Repository,Active Session History etc etc) in
> PostgreSQL. Yes - some of them are there, but one has to
> search,download, configure etc. I hope many of these features will
> become part of the 'core' soon.

That's unlikely, simply because the definition of "core" in PostgreSQL
doesn't quite include the full experience of user tools like this. For
example, you might see the core collect the data needed for something
that acts like a AWR baseline. But you're unlikely to ever get the sort
of easy baseline management+graph management tools that Oracle's
Enterprise Manager layers on top of them in core. There's no place to
put a GUI/web tool like that there, and I wouldn't expect that to ever
change. You might see it provided as a feature to the pgAdmin tool
though, or as a separate web application.

The fact that there are multiple pieces of software involved doesn't
have to make this hard. Ultimately the problem you're identifying is a
packaging one. Something doesn't have to be in the PostgreSQL core to
be packaged nicely so that you can easily install and use it. It's
probably easy for you to get pgAdmin installed and working for example,
and that's not a part of core. There's just been a lot more work put
into packaging it than most tools have gotten so far.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us