On Thu, Oct 10, 2013 at 8:46 PM, Robert Haas robertmh...@gmail.com wrote:
On Thu, Oct 10, 2013 at 2:45 PM, Josh Berkus j...@agliodbs.com wrote:
On 10/10/2013 11:41 AM, Robert Haas wrote:
tunedb --available-memory=32GB
...and it will print out a set of proposed configuration settings. If
we
On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote:
How do we handle the Python dependency, or is this all to be done in
some other language? I certainly am not ready to take on that job.
I should think it possible to reimplement it in C. It was considerably
useful to
On Thu, Oct 10, 2013 at 3:41 PM, Christopher Browne cbbro...@gmail.com wrote:
On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote:
How do we handle the Python dependency, or is this all to be done in
some other language? I certainly am not ready to take on that job.
I
More generally, Josh has made repeated comments that various proposed
value/formulas for work_mem are too low, but obviously the people who
suggested them didn't think so. So I'm a bit concerned that we don't
all agree on what the end goal of this activity looks like.
The counter-proposal
On Thu, Oct 10, 2013 at 11:14:27AM -0700, Jeff Janes wrote:
The assumption that each connection won't use lots of work_mem is also
false, I think, especially in these days of connection poolers.
I don't follow that. Why would using a connection pooler change the multiples
of
On Thu, Oct 10, 2013 at 11:18:28AM -0700, Josh Berkus wrote:
Bruce,
That's way low, and frankly it's not worth bothering with this if all
we're going to get is an incremental increase. In that case, let's just
set the default to 4MB like Robert suggested.
Uh, well, 100 backends at
I don't follow that. Why would using a connection pooler change the
multiples
of work_mem that a connection would use?
I assume that a connection pooler would keep processes running longer,
so even if they were not all using work_mem, they would have that memory
mapped into the process,
On Thu, Oct 10, 2013 at 02:44:12PM -0400, Peter Eisentraut wrote:
On 10/10/13 11:31 AM, Bruce Momjian wrote:
Let me walk through the idea of adding an available_mem setting, that
Josh suggested, and which I think addresses Robert's concern about
larger shared_buffers and Windows servers.
On Thu, Oct 10, 2013 at 03:40:17PM -0700, Josh Berkus wrote:
I don't follow that. Why would using a connection pooler change the
multiples
of work_mem that a connection would use?
I assume that a connection pooler would keep processes running longer,
so even if they were not all
On Thu, Oct 10, 2013 at 03:27:17PM -0700, Josh Berkus wrote:
More generally, Josh has made repeated comments that various proposed
value/formulas for work_mem are too low, but obviously the people who
suggested them didn't think so. So I'm a bit concerned that we don't
all agree on what
On 10/10/13 9:44 AM, MauMau wrote:
From: Robert Haas robertmh...@gmail.com
On Thu, Oct 10, 2013 at 1:23 AM, Magnus Hagander mag...@hagander.net wrote:
I think it would be even simpler, and more reliable, to start with the
parameter to initdb - I like that. But instead of having it set a new
On Thu, Oct 10, 2013 at 6:36 PM, Bruce Momjian br...@momjian.us wrote:
Patch attached.
ISTM that we have broad consensus that doing this at initdb time is
more desirable than doing it in the server on the fly. Not everyone
agrees with that (you don't, for instance) but there were many, many
On Thu, Oct 10, 2013 at 6:27 PM, Josh Berkus j...@agliodbs.com wrote:
More generally, Josh has made repeated comments that various proposed
value/formulas for work_mem are too low, but obviously the people who
suggested them didn't think so. So I'm a bit concerned that we don't
all agree on
Robert,
The counter-proposal to auto-tuning is just to raise the default for
work_mem to 4MB or 8MB. Given that Bruce's current formula sets it at
6MB for a server with 8GB RAM, I don't really see the benefit of going
to a whole lot of code and formulas in order to end up at a figure only
Josh Berkus suggested here that work_mem and maintenance_work_mem could
be auto-tuned like effective_cache_size:
http://www.postgresql.org/message-id/50eccf93.3060...@agliodbs.com
The attached patch implements this, closely matching the default values
for the default shared_buffers
On 2013-10-09 10:30:46 -0400, Bruce Momjian wrote:
Josh Berkus suggested here that work_mem and maintenance_work_mem could
be auto-tuned like effective_cache_size:
http://www.postgresql.org/message-id/50eccf93.3060...@agliodbs.com
The attached patch implements this, closely matching
On Wed, Oct 9, 2013 at 04:32:44PM +0200, Andres Freund wrote:
On 2013-10-09 10:30:46 -0400, Bruce Momjian wrote:
Josh Berkus suggested here that work_mem and maintenance_work_mem could
be auto-tuned like effective_cache_size:
2013/10/9 Bruce Momjian br...@momjian.us
On Wed, Oct 9, 2013 at 04:32:44PM +0200, Andres Freund wrote:
On 2013-10-09 10:30:46 -0400, Bruce Momjian wrote:
Josh Berkus suggested here that work_mem and maintenance_work_mem could
be auto-tuned like effective_cache_size:
On Wed, Oct 9, 2013 at 04:38:01PM +0200, Andres Freund wrote:
On 2013-10-09 10:35:28 -0400, Bruce Momjian wrote:
On Wed, Oct 9, 2013 at 04:32:44PM +0200, Andres Freund wrote:
On 2013-10-09 10:30:46 -0400, Bruce Momjian wrote:
Josh Berkus suggested here that work_mem and
On Wed, Oct 9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote:
Effectively, if every session uses one full work_mem, you end up with
total work_mem usage equal to shared_buffers.
We can try a different algorithm to scale up work_mem, but it seems wise
to auto-scale it up to
On 2013-10-09 10:35:28 -0400, Bruce Momjian wrote:
On Wed, Oct 9, 2013 at 04:32:44PM +0200, Andres Freund wrote:
On 2013-10-09 10:30:46 -0400, Bruce Momjian wrote:
Josh Berkus suggested here that work_mem and maintenance_work_mem could
be auto-tuned like effective_cache_size:
On Wed, Oct 9, 2013 at 10:45:52AM -0400, Bruce Momjian wrote:
On Wed, Oct 9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote:
Effectively, if every session uses one full work_mem, you end up with
total work_mem usage equal to shared_buffers.
We can try a different algorithm
2013/10/9 Bruce Momjian br...@momjian.us
On Wed, Oct 9, 2013 at 10:45:52AM -0400, Bruce Momjian wrote:
On Wed, Oct 9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote:
Effectively, if every session uses one full work_mem, you end up
with
total work_mem usage equal to
On 10/09/2013 10:45 AM, Bruce Momjian wrote:
On Wed, Oct 9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote:
Effectively, if every session uses one full work_mem, you end up with
total work_mem usage equal to shared_buffers.
We can try a different algorithm to scale up work_mem,
On Wed, Oct 9, 2013 at 05:01:24PM +0200, Pavel Stehule wrote:
FYI, this auto-tuning is not for us, who understand the parameters and
how they interact, but for the 90% of our users who would benefit from
better defaults. It is true that there might now be cases where you
2013/10/9 Bruce Momjian br...@momjian.us
On Wed, Oct 9, 2013 at 05:01:24PM +0200, Pavel Stehule wrote:
FYI, this auto-tuning is not for us, who understand the parameters
and
how they interact, but for the 90% of our users who would benefit
from
better defaults. It is true
On Wed, Oct 9, 2013 at 11:06:07AM -0400, Andrew Dunstan wrote:
On 10/09/2013 10:45 AM, Bruce Momjian wrote:
On Wed, Oct 9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote:
Effectively, if every session uses one full work_mem, you end up with
total work_mem usage equal to
On Wed, Oct 9, 2013 at 06:20:13PM +0200, Pavel Stehule wrote:
On Wed, Oct 9, 2013 at 05:01:24PM +0200, Pavel Stehule wrote:
FYI, this auto-tuning is not for us, who understand the parameters
and
how they interact, but for the 90% of our users who would benefit
* Pavel Stehule (pavel.steh...@gmail.com) wrote:
2013/10/9 Bruce Momjian br...@momjian.us
I went with shared_buffers because unlike the others, it is a fixed
allocation quantity, while the other are much more variable and harder
to set. I figured we could keep our 25% estimate of
* Bruce Momjian (br...@momjian.us) wrote:
I think we should try to hit the existing defaults, which would mean we
would use this computation:
For my 2c, I was hoping this would improve things for our users by
raising the tiny 1M default work_mem, so I don't agree that we should
simply be coming
On 10/09/2013 09:30 AM, Stephen Frost wrote:
I went with shared_buffers because unlike the others, it is a fixed
allocation quantity, while the other are much more variable and harder
to set. I figured we could keep our 25% estimate of shared_buffers and
everything else would fall in
On Wed, Oct 09, 2013 at 12:30:22PM -0400, Stephen Frost wrote:
* Pavel Stehule (pavel.steh...@gmail.com) wrote:
2013/10/9 Bruce Momjian br...@momjian.us
I went with shared_buffers because unlike the others, it is a fixed
allocation quantity, while the other are much more variable and
On Wed, Oct 9, 2013 at 12:25:49PM -0400, Bruce Momjian wrote:
I'm not saying don't do it, but I think we need to be quite
conservative about it. A reasonable default might be (shared_buffers
/ (n * max_connections)) FSVO n, but I'm not sure what n should be.
Instinct says something like
On 10/09/2013 07:58 AM, Bruce Momjian wrote:
But it still is an independent parameter. I am just changing the default.
maintenance_work_mem can depend on work_mem ~ work_mem * 1 * max_connection / 4
That is kind of hard to do because we would have to figure out if the
old
On Wed, Oct 9, 2013 at 12:41:53PM -0400, Stephen Frost wrote:
* Bruce Momjian (br...@momjian.us) wrote:
I think we should try to hit the existing defaults, which would mean we
would use this computation:
For my 2c, I was hoping this would improve things for our users by
raising the tiny
On 10/09/2013 10:07 AM, Bruce Momjian wrote:
We can have the discussion here or in another thread. I am thinking the
right fix is to allocate larger shared_buffers, especially now that we
don't require a larger System V shared memory segement. Basically, for
128MB of shared buffers, I
On Wed, Oct 9, 2013 at 10:30 AM, Bruce Momjian br...@momjian.us wrote:
Josh Berkus suggested here that work_mem and maintenance_work_mem could
be auto-tuned like effective_cache_size:
http://www.postgresql.org/message-id/50eccf93.3060...@agliodbs.com
I think that this is unlikely to
On Wed, Oct 9, 2013 at 01:34:21PM -0400, Robert Haas wrote:
And quite frankly I don't think I really believe the auto-tuning
formula has much chance of being right in the first place. It's
generally true that you're going to need to increase work_mem if you
have more memory and decrease it
On Wed, Oct 9, 2013 at 1:44 PM, Bruce Momjian br...@momjian.us wrote:
On Wed, Oct 9, 2013 at 01:34:21PM -0400, Robert Haas wrote:
And quite frankly I don't think I really believe the auto-tuning
formula has much chance of being right in the first place. It's
generally true that you're going
On Wed, Oct 9, 2013 at 01:49:23PM -0400, Robert Haas wrote:
Having really bad defaults so everyone knows they are bad really isn't
user-friendly because the only people who know they are really bad are
the people who are tuning them already. Again, we need to think of the
typical user,
On Wed, Oct 9, 2013 at 2:28 PM, Bruce Momjian br...@momjian.us wrote:
On Wed, Oct 9, 2013 at 01:49:23PM -0400, Robert Haas wrote:
Having really bad defaults so everyone knows they are bad really isn't
user-friendly because the only people who know they are really bad are
the people who are
On 2013-10-09 10:02:12 -0700, Joshua D. Drake wrote:
On 10/09/2013 07:58 AM, Bruce Momjian wrote:
But it still is an independent parameter. I am just changing the default.
maintenance_work_mem can depend on work_mem ~ work_mem * 1 *
max_connection / 4
That is kind of hard to do
On Wed, Oct 9, 2013 at 02:34:19PM -0400, Robert Haas wrote:
On Wed, Oct 9, 2013 at 2:28 PM, Bruce Momjian br...@momjian.us wrote:
On Wed, Oct 9, 2013 at 01:49:23PM -0400, Robert Haas wrote:
Having really bad defaults so everyone knows they are bad really isn't
user-friendly because the
On Wed, Oct 9, 2013 at 7:30 AM, Bruce Momjian br...@momjian.us wrote:
Josh Berkus suggested here that work_mem and maintenance_work_mem could
be auto-tuned like effective_cache_size:
Have you ever thought that the role of maintenance_work_mem was a bit
muddled? It recently occurred to me that
On 10/09/2013 01:37 PM, Peter Geoghegan wrote:
If Heroku could increase maintenace_work_mem without having it affect
the amount of memory used by autovacuum workers, I'm fairly confident
that our setting would be higher. Sure, you can just increase it as
you need to, but you have to know about
On Wed, Oct 9, 2013 at 09:52:03AM -0700, Josh Berkus wrote:
On 10/09/2013 09:30 AM, Stephen Frost wrote:
I went with shared_buffers because unlike the others, it is a fixed
allocation quantity, while the other are much more variable and harder
to set. I figured we could keep our 25%
On Wed, Oct 9, 2013 at 02:11:47PM -0700, Josh Berkus wrote:
On 10/09/2013 01:37 PM, Peter Geoghegan wrote:
If Heroku could increase maintenace_work_mem without having it affect
the amount of memory used by autovacuum workers, I'm fairly confident
that our setting would be higher. Sure, you
On Wed, Oct 9, 2013 at 2:15 PM, Bruce Momjian br...@momjian.us wrote:
I did like Josh's idea about using autovacuum_max_workers for
maintenance_work_mem, though I used the shared_buffers/4 calculation.
I don't like that idea myself, because I wouldn't like to link
maintenance_work_mem to
On Wed, Oct 9, 2013 at 03:04:24PM -0700, Peter Geoghegan wrote:
On Wed, Oct 9, 2013 at 2:15 PM, Bruce Momjian br...@momjian.us wrote:
I did like Josh's idea about using autovacuum_max_workers for
maintenance_work_mem, though I used the shared_buffers/4 calculation.
I don't like that idea
On Wed, Oct 9, 2013 at 3:31 PM, Bruce Momjian br...@momjian.us wrote:
Splitting out vacuum_work_mem from maintenance_work_mem is a separate
issue. I assume they were combined because the memory used for vacuum
index scans is similar to creating an index.
Is it similar? Doesn't
On Wed, Oct 9, 2013 at 03:57:14PM -0700, Peter Geoghegan wrote:
On Wed, Oct 9, 2013 at 3:31 PM, Bruce Momjian br...@momjian.us wrote:
Splitting out vacuum_work_mem from maintenance_work_mem is a separate
issue. I assume they were combined because the memory used for vacuum
index scans is
On Wed, Oct 9, 2013 at 4:40 PM, Bruce Momjian br...@momjian.us wrote:
Well, if a person does not use vacuum_work_mem, then the cost to that
person is low. If they do, the benefits could be immense. At the
Heroku office, I've had people wonder why creating an index took what
seemed like way too
On Wed, Oct 9, 2013 at 4:10 PM, Bruce Momjian br...@momjian.us wrote:
I disagree. I think we can get a forumla that is certainly better than
a fixed value. I think the examples I have shown do have better value
than a default fixed value. I am open to whatever forumula people think
is best,
On Wed, Oct 9, 2013 at 08:55:33PM -0400, Robert Haas wrote:
On Wed, Oct 9, 2013 at 4:10 PM, Bruce Momjian br...@momjian.us wrote:
I disagree. I think we can get a forumla that is certainly better than
a fixed value. I think the examples I have shown do have better value
than a default
On Wed, Oct 9, 2013 at 9:11 PM, Bruce Momjian br...@momjian.us wrote:
On Wed, Oct 9, 2013 at 08:55:33PM -0400, Robert Haas wrote:
On Wed, Oct 9, 2013 at 4:10 PM, Bruce Momjian br...@momjian.us wrote:
I disagree. I think we can get a forumla that is certainly better than
a fixed value. I
* Bruce Momjian (br...@momjian.us) wrote:
For servers that are not dedicated, a fixed value can easily be too
large, and for a larger server, the value can easily be too small. Not
sure how you can argue that a fixed value could be better.
There is definitely something to be said for
On Wed, Oct 9, 2013 at 7:11 PM, Stephen Frost sfr...@snowman.net wrote:
There is definitely something to be said for simplicity and just up'ing
the default would have a more dramatic impact with a setting like
work_mem than it would with shared_buffers, imv.
Simplicity for us or for our users?
On Wed, Oct 9, 2013 at 09:34:16PM -0400, Robert Haas wrote:
On a more pedestrian note, when I try this patch with shared_buffers =
8GB, the postmaster won't start. It dies with:
FATAL: -20203 is outside the valid range for parameter work_mem (-1
.. 2147483647)
Fixed with the attached
On Wed, Oct 9, 2013 at 07:33:46PM -0700, Peter Geoghegan wrote:
I hear a lot of complaints about the first 15 minutes experience of
Postgres. It's easy to scoff at this kind of thing, but I think we
could do a lot better there, and at no real cost - the major blocker
to doing something like
On Wed, Oct 9, 2013 at 09:34:16PM -0400, Robert Haas wrote:
On Wed, Oct 9, 2013 at 9:11 PM, Bruce Momjian br...@momjian.us wrote:
On Wed, Oct 9, 2013 at 08:55:33PM -0400, Robert Haas wrote:
On Wed, Oct 9, 2013 at 4:10 PM, Bruce Momjian br...@momjian.us wrote:
I disagree. I think we can
On Wed, Oct 9, 2013 at 8:02 PM, Bruce Momjian br...@momjian.us wrote:
I think the simplest solution would be to have a parameter to initdb
which specifies how much memory you want to use, and set a new variable
available_mem from that, and have things auto-tune based on that value
in the
On Wed, Oct 9, 2013 at 8:13 PM, Bruce Momjian br...@momjian.us wrote:
My option is better, not perfect --- I don't know how many times I can
say something again and again. Fortunately there are enough people who
understand that on the lists.
+1 from me on the sentiment: the perfect cannot be
On Wed, Oct 9, 2013 at 08:15:44PM -0700, Peter Geoghegan wrote:
On Wed, Oct 9, 2013 at 8:02 PM, Bruce Momjian br...@momjian.us wrote:
I think the simplest solution would be to have a parameter to initdb
which specifies how much memory you want to use, and set a new variable
available_mem
On Wed, Oct 9, 2013 at 8:20 PM, Bruce Momjian br...@momjian.us wrote:
I am not sure that having that external to the backend really makes
sense because I am concerned people will not use it. We can certainly
add it to change our defaults, of course. Also consider many installs
are automated.
On Thu, Oct 10, 2013 at 2:04 AM, Peter Geoghegan p...@heroku.com wrote:
On Wed, Oct 9, 2013 at 4:40 PM, Bruce Momjian br...@momjian.us wrote:
Well, if a person does not use vacuum_work_mem, then the cost to that
person is low. If they do, the benefits could be immense. At the
Heroku office,
On Thu, Oct 10, 2013 at 5:02 AM, Bruce Momjian br...@momjian.us wrote:
On Wed, Oct 9, 2013 at 07:33:46PM -0700, Peter Geoghegan wrote:
I hear a lot of complaints about the first 15 minutes experience of
Postgres. It's easy to scoff at this kind of thing, but I think we
could do a lot better
On Thu, Oct 10, 2013 at 5:35 AM, Peter Geoghegan p...@heroku.com wrote:
On Wed, Oct 9, 2013 at 8:20 PM, Bruce Momjian br...@momjian.us wrote:
I am not sure that having that external to the backend really makes
sense because I am concerned people will not use it. We can certainly
add it to
101 - 167 of 167 matches
Mail list logo
