On 10/07/10 03:54, Kevin Grittner wrote:
Mark Kirkwoodmark.kirkw...@catalyst.net.nz wrote:
Purely out of interest, since the old repo is still there, I had a
quick look at measuring the overhead, using 8.4's pgbench to run
two custom scripts: one consisting of a single 'SELECT 1', the
Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote:
Purely out of interest, since the old repo is still there, I had a
quick look at measuring the overhead, using 8.4's pgbench to run
two custom scripts: one consisting of a single 'SELECT 1', the
other having 100 'SELECT 1' - the latter being
Simon, Mark,
Actually only 1 lock check per query, but certainly extra processing and
data structures to maintain the pool information... so, yes certainly
much more suitable for DW (AFAIK we never attempted to measure the
additional overhead for non DW workload).
I recall testing it when the
On 09/07/10 05:10, Josh Berkus wrote:
Simon, Mark,
Actually only 1 lock check per query, but certainly extra processing and
data structures to maintain the pool information... so, yes certainly
much more suitable for DW (AFAIK we never attempted to measure the
additional overhead for non DW
On 09/07/10 12:58, Mark Kirkwood wrote:
On 09/07/10 05:10, Josh Berkus wrote:
Simon, Mark,
Actually only 1 lock check per query, but certainly extra processing
and
data structures to maintain the pool information... so, yes certainly
much more suitable for DW (AFAIK we never attempted to
On Thu, Jul 8, 2010 at 10:21 PM, Mark Kirkwood
mark.kirkw...@catalyst.net.nz wrote:
Purely out of interest, since the old repo is still there, I had a quick
look at measuring the overhead, using 8.4's pgbench to run two custom
scripts: one consisting of a single 'SELECT 1', the other having 100
On 09/07/10 14:26, Robert Haas wrote:
On Thu, Jul 8, 2010 at 10:21 PM, Mark Kirkwood
mark.kirkw...@catalyst.net.nz wrote:
Purely out of interest, since the old repo is still there, I had a quick
look at measuring the overhead, using 8.4's pgbench to run two custom
scripts: one consisting
On Thu, Jul 8, 2010 at 11:00 PM, Mark Kirkwood
mark.kirkw...@catalyst.net.nz wrote:
On 09/07/10 14:26, Robert Haas wrote:
On Thu, Jul 8, 2010 at 10:21 PM, Mark Kirkwood
mark.kirkw...@catalyst.net.nz wrote:
Purely out of interest, since the old repo is still there, I had a quick
look at
On 09/07/10 15:57, Robert Haas wrote:
Hmm. Well, those numbers seem awfully high, for what you're doing,
then. An admission control mechanism that's just letting everything
in shouldn't knock 5% off performance (let alone 30%).
Yeah it does, on the other hand both Josh and I were trying
On Fri, Jul 9, 2010 at 12:03 AM, Mark Kirkwood
mark.kirkw...@catalyst.net.nz wrote:
On 09/07/10 15:57, Robert Haas wrote:
Hmm. Well, those numbers seem awfully high, for what you're doing,
then. An admission control mechanism that's just letting everything
in shouldn't knock 5% off
On Fri, 2010-06-25 at 13:10 -0700, Josh Berkus wrote:
The problem with centralized resource control
We should talk about the problem of lack of centralized resource control
as well, to balance.
Another well observed problem is that work_mem is user settable, so many
programs acting together
On 29/06/10 05:36, Josh Berkus wrote:
Having tinkered with it, I'll tell you that (2) is actually a very
hard problem, so any solution we implement should delay as long as
possible in implementing (2). In the case of Greenplum, what Mark did
originally IIRC was to check against the global
On Sat, Jun 26, 2010 at 01:19:57PM -0400, Robert Haas wrote:
I'm not sure. What does seem clear is that it's fundamentally at odds
with the admission control approach Kevin is advocating. When you
start to run short on a resource (perhaps memory), you have to decide
between (a) waiting for
Ross J. Reedstrom reeds...@rice.edu writes:
Hmm, I'm suddenly struck by the idea of having a max_cost parameter,
that refuses to run (or delays?) queries that have too high a cost.
That's been suggested before, and shot down on the grounds that the
planner's cost estimates are not trustworthy
While this does have the advantage of being relatively simple to
implement, I think it would be a bitch to tune...
Precisely. So, there's a number of issues to solve here:
1) We'd need to add accouting for total memory usage to explain plans
(worth doing on its own, really, even without
Josh Berkus j...@agliodbs.com wrote:
We can go back to Kevin's originally proposed simple feature:
just allowing the DBA to limit the number of concurrently
executing queries by role and overall.
Well, that's more sophisticated than what I proposed, but it's an
interesting twist on it.
On 2010-06-25 22:44, Robert Haas wrote:
On Fri, Jun 25, 2010 at 3:52 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Heck, I think an even *more* trivial admission control policy which
limits the number of active database transactions released to
execution might solve a lot of
Jesper Krogh jes...@krogh.cc wrote:
Sorry if I'm asking silly questions, but how does transactions and
connection pooler's interact?
That depends a great deal on the pooler and its configuration, as
well as your client architecture. Our shop gathers up the
information needed for our
On 2010-06-28 21:24, Kevin Grittner wrote:
Jesper Kroghjes...@krogh.cc wrote:
Sorry if I'm asking silly questions, but how does transactions and
connection pooler's interact?
That depends a great deal on the pooler and its configuration, as
well as your client architecture. Our
Jesper Krogh jes...@krogh.cc wrote:
I have not hit any issues with the work_mem being too high, but
I'm absolutely sure that I could flood the system if they happened
to be working at the same time.
OK, now that I understand your workload, I agree that a connection
pool at the transaction
On 29/06/10 04:48, Tom Lane wrote:
Ross J. Reedstromreeds...@rice.edu writes:
Hmm, I'm suddenly struck by the idea of having a max_cost parameter,
that refuses to run (or delays?) queries that have too high a cost.
That's been suggested before, and shot down on the grounds that the
On Fri, Jun 25, 2010 at 03:15:59PM -0400, Robert Haas wrote:
A
refinement might be to try to consider an inferior plan that uses less
memory when the system is tight on memory, rather than waiting. But
you'd have to be careful about that, because waiting might be better
(it's worth waiting
On Sat, Jun 26, 2010 at 11:03 AM, Martijn van Oosterhout
klep...@svana.org wrote:
On Fri, Jun 25, 2010 at 03:15:59PM -0400, Robert Haas wrote:
A
refinement might be to try to consider an inferior plan that uses less
memory when the system is tight on memory, rather than waiting. But
you'd
On Sat, Jun 26, 2010 at 11:37:16AM -0400, Robert Haas wrote:
On Sat, Jun 26, 2010 at 11:03 AM, Martijn van Oosterhout
(It doesn't help in situations where you can't accurately predict
memory usage, like hash tables.)
Not sure what you mean by this part. We already predict how much
memory
On Sat, Jun 26, 2010 at 11:59 AM, Martijn van Oosterhout
klep...@svana.org wrote:
On Sat, Jun 26, 2010 at 11:37:16AM -0400, Robert Haas wrote:
On Sat, Jun 26, 2010 at 11:03 AM, Martijn van Oosterhout
(It doesn't help in situations where you can't accurately predict
memory usage, like hash
Recent discussions involving the possible benefits of a connection
pool for certain users has reminded me of a brief discussion at The
Royal Oak last month, where I said I would post a reference a
concept which might alleviate the need for external connection
pools. For those interested, check
On Fri, Jun 25, 2010 at 1:33 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Recent discussions involving the possible benefits of a connection
pool for certain users has reminded me of a brief discussion at The
Royal Oak last month, where I said I would post a reference a
concept which
Robert Haas robertmh...@gmail.com wrote:
Kevin Grittner kevin.gritt...@wicourts.gov wrote:
check out section 2.4 of this
A really trivial admission control system might let you set a
system-wide limit on work_mem.
Heck, I think an even *more* trivial admission control policy which
limits
On 6/25/10 12:15 PM, Robert Haas wrote:
I think a good admission control system for memory would be huge for
us. There are innumerable threads on pgsql-performance where we tell
people to set work_mem to a tiny value (like 4MB or 16MB) because any
higher value risks driving the machine into
Josh Berkus j...@agliodbs.com wrote:
Greenplum did this several years ago with the Bizgres project
However, it [was not] compatible with OLTP workloads.
the poor man's admission control is a waste of time because it
doesn't actually help performance. We're basically facing doing
the
On Fri, Jun 25, 2010 at 4:10 PM, Josh Berkus j...@agliodbs.com wrote:
On 6/25/10 12:15 PM, Robert Haas wrote:
I think a good admission control system for memory would be huge for
us. There are innumerable threads on pgsql-performance where we tell
people to set work_mem to a tiny value (like
On Fri, Jun 25, 2010 at 3:52 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Heck, I think an even *more* trivial admission control policy which
limits the number of active database transactions released to
execution might solve a lot of problems.
That wouldn't have any benefit over what
Robert Haas robertmh...@gmail.com wrote:
Kevin Grittner kevin.gritt...@wicourts.gov wrote:
Heck, I think an even *more* trivial admission control policy
which limits the number of active database transactions released
to execution might solve a lot of problems.
That wouldn't have any
This paper has a brief but interesting discussion of Admission
Control in section 2.4:
Architecture of a Database System. (Joseph M. Hellerstein, Michael
Stonebraker and James Hamilton). Foundations and Trends in Databases
1(2).
http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf
They
Hi,
Le 28 déc. 2009 à 21:33, Kevin Grittner a écrit :
We often see posts from people who have more active connections than
is efficient.
How would your proposal better solve the problem than using pgbouncer?
mad proposal time
I'd be in favor of considering how to get pgbouncer into -core, and
On Monday 28 December 2009 22:39:06 Dimitri Fontaine wrote:
Hi,
Le 28 déc. 2009 à 21:33, Kevin Grittner a écrit :
We often see posts from people who have more active connections than
is efficient.
How would your proposal better solve the problem than using pgbouncer?
mad proposal
Le 28 déc. 2009 à 22:46, Andres Freund a écrit :
mad proposal time
I'd be in favor of considering how to get pgbouncer into -core, and now
that we have Hot Standby maybe implement a mode in which as soon as a
real XID is needed, or maybe upon receiving start transaction read write
command,
Dimitri Fontaine dfonta...@hi-media.com wrote:
Le 28 déc. 2009 à 21:33, Kevin Grittner a écrit :
We often see posts from people who have more active connections
than is efficient.
How would your proposal better solve the problem than using
pgbouncer?
With my current knowledge of pgbouncer
Le 28 déc. 2009 à 22:59, Kevin Grittner a écrit :
With my current knowledge of pgbouncer I can't answer that
definitively; but *if* pgbouncer, when configured for transaction
pooling, can queue new transaction requests until a connection is
free, then the differences would be:
It does that,
Dimitri Fontaine dfonta...@hi-media.com wrote:
That's why there's both transaction and session pooling. The
benefit of session pooling is to avoid forking backends, reusing
them instead, and you still get the pooling control.
So the application would need to open and close a pgbouncer
Le 28 déc. 2009 à 23:35, Kevin Grittner a écrit :
So the application would need to open and close a pgbouncer
connection for each database transaction in order to share the
backend properly?
No, in session pooling you get the same backend connection for the entire
pgbouncer connection, it's a
Dimitri Fontaine dfonta...@hi-media.com wrote:
Le 28 déc. 2009 à 22:59, Kevin Grittner a écrit :
(3) With the ACP, the statements would be parsed and optimized
before queuing, so they would be ready to execute as soon as a
connection was freed.
There's a pgfoundry project called
Le 28 déc. 2009 à 23:56, Kevin Grittner a écrit :
http://preprepare.projects.postgresql.org/README.html
I just reviewed the documentation for preprepare -- I can see a use
case for that, but I really don't think it has a huge overlap with
my point. The parsing and planning mentioned in my
Dimitri Fontaine dfonta...@hi-media.com wrote:
No, in session pooling you get the same backend connection for the
entire pgbouncer connection, it's a 1-1 mapping.
Right -- so it doesn't allow more logical connections than that with
a limit to how many are active at any one time, *unless* the
On Mon, Dec 28, 2009 at 3:33 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
They describe a two-tier approach, where the first tier is already
effectively implemented in PostgreSQL with the max_connections and
superuser_reserved_connections GUCs. The second tier is implemented
to run
Robert Haas robertmh...@gmail.com wrote:
It seems like it might be helpful, before tackling what you're
talking
about here, to have some better tools for controlling resource
utilization. Right now, the tools we have a pretty crude. You
can't
even nice/ionice a certain backend without
Robert Haas robertmh...@gmail.com wrote:
Kevin Grittner kevin.gritt...@wicourts.gov wrote:
The second tier is implemented to run after a plan is chosen, and
may postpone execution of a query (or reduce the resources it is
allowed) if starting it at that time might overload available
47 matches
Mail list logo
