Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-03-14 Thread Jeff Janes
On Mon, Mar 10, 2014 at 3:16 PM, Kevin Grittner kgri...@ymail.com wrote: Andres Freund and...@2ndquadrant.com wrote: On 2014-02-16 21:26:47 -0500, Robert Haas wrote: I don't really know about cpu_tuple_cost. Kevin's often advocated raising it, but I haven't heard anyone else advocate

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-03-10 Thread Kevin Grittner
Andres Freund and...@2ndquadrant.com wrote: On 2014-02-16 21:26:47 -0500, Robert Haas wrote: I don't really know about cpu_tuple_cost.  Kevin's often advocated raising it, but I haven't heard anyone else advocate for that. I think we need data points from more people to know whether or not

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-03-10 Thread Josh Berkus
On 03/10/2014 03:16 PM, Kevin Grittner wrote: I only have anecdotal evidence, though. I have seen it help dozens of times, and have yet to see it hurt. That said, most people on this list are probably capable of engineering a benchmark which will show whichever result they would prefer. I

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-27 Thread Craig Ringer
On 02/18/2014 12:19 AM, Andres Freund wrote: On 2014-02-16 21:26:47 -0500, Robert Haas wrote: I don't think anyone objected to increasing the defaults for work_mem and maintenance_work_mem by 4x, and a number of people were in favor, so I think we should go ahead and do that. If you'd like to

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-25 Thread Robert Haas
On Mon, Feb 24, 2014 at 1:05 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Feb 17, 2014 at 11:14:33AM -0500, Bruce Momjian wrote: On Sun, Feb 16, 2014 at 09:26:47PM -0500, Robert Haas wrote: So, would anyone like me to create patches for any of these items before we hit 9.4 beta? We

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-24 Thread Bruce Momjian
On Mon, Feb 17, 2014 at 11:14:33AM -0500, Bruce Momjian wrote: On Sun, Feb 16, 2014 at 09:26:47PM -0500, Robert Haas wrote: So, would anyone like me to create patches for any of these items before we hit 9.4 beta? We have added autovacuum_work_mem, and increasing work_mem and

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Tom Lane
Gavin Flower gavinflo...@archidevsys.co.nz writes: On 17/02/14 15:26, Robert Haas wrote: I don't really know about cpu_tuple_cost. Kevin's often advocated raising it, but I haven't heard anyone else advocate for that. I think we need data points from more people to know whether or not

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Bruce Momjian
On Sun, Feb 16, 2014 at 09:26:47PM -0500, Robert Haas wrote: So, would anyone like me to create patches for any of these items before we hit 9.4 beta? We have added autovacuum_work_mem, and increasing work_mem and maintenance_work_mem by 4x is a simple operation. Not sure about the

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Andres Freund
On 2014-02-16 21:26:47 -0500, Robert Haas wrote: I don't think anyone objected to increasing the defaults for work_mem and maintenance_work_mem by 4x, and a number of people were in favor, so I think we should go ahead and do that. If you'd like to do the honors, by all means! Actually, I

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote: On 2014-02-16 21:26:47 -0500, Robert Haas wrote: I don't think anyone objected to increasing the defaults for work_mem and maintenance_work_mem by 4x, and a number of people were in favor, so I think we should go ahead and do that. If you'd

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Andres Freund
On 2014-02-17 11:31:56 -0500, Stephen Frost wrote: * Andres Freund (and...@2ndquadrant.com) wrote: On 2014-02-16 21:26:47 -0500, Robert Haas wrote: I don't think anyone objected to increasing the defaults for work_mem and maintenance_work_mem by 4x, and a number of people were in favor,

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Robert Haas
On Mon, Feb 17, 2014 at 11:19 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-02-16 21:26:47 -0500, Robert Haas wrote: I don't think anyone objected to increasing the defaults for work_mem and maintenance_work_mem by 4x, and a number of people were in favor, so I think we should go

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Andres Freund
On 2014-02-17 12:23:58 -0500, Robert Haas wrote: On Mon, Feb 17, 2014 at 11:19 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-02-16 21:26:47 -0500, Robert Haas wrote: I don't think anyone objected to increasing the defaults for work_mem and maintenance_work_mem by 4x, and a

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2014-02-17 12:23:58 -0500, Robert Haas wrote: I think you may be out-voted. I realize that, but I didn't want to let the I don't think anyone objected stand :) FWIW, I think we need to be pretty gradual about this sort of thing, because

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Robert Haas
On Mon, Feb 17, 2014 at 11:33 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-02-17 11:31:56 -0500, Stephen Frost wrote: * Andres Freund (and...@2ndquadrant.com) wrote: On 2014-02-16 21:26:47 -0500, Robert Haas wrote: I don't think anyone objected to increasing the defaults for

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Andres Freund
On 2014-02-17 13:33:17 -0500, Robert Haas wrote: On Mon, Feb 17, 2014 at 11:33 AM, Andres Freund and...@2ndquadrant.com wrote: And I still disagree with this- even in those cases. Those same untuned servers are running dirt-simple queries 90% of the time and they won't use any more

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Bruce Momjian
On Mon, Feb 17, 2014 at 07:39:47PM +0100, Andres Freund wrote: On 2014-02-17 13:33:17 -0500, Robert Haas wrote: On Mon, Feb 17, 2014 at 11:33 AM, Andres Freund and...@2ndquadrant.com wrote: And I still disagree with this- even in those cases. Those same untuned servers are running

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Gavin Flower
On 18/02/14 03:48, Tom Lane wrote: Gavin Flower gavinflo...@archidevsys.co.nz writes: On 17/02/14 15:26, Robert Haas wrote: I don't really know about cpu_tuple_cost. Kevin's often advocated raising it, but I haven't heard anyone else advocate for that. I think we need data points from more

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Peter Geoghegan
On Mon, Feb 17, 2014 at 8:31 AM, Stephen Frost sfr...@snowman.net wrote: Actually, I object to increasing work_mem by default. In my experience most of the untuned servers are backing some kind of web application and often run with far too many connections. Increasing work_mem for those is

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Jeff Janes
On Sun, Feb 16, 2014 at 6:26 PM, Robert Haas robertmh...@gmail.com wrote: The current bgwriter_lru_maxpages value limits the background writer to a maximum of 4MB/s. If one imagines shared_buffers = 8GB, that starts to seem rather low, but I don't have a good feeling for what a better

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-16 Thread Robert Haas
On Thu, Feb 13, 2014 at 3:34 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Oct 11, 2013 at 03:39:51PM -0700, Kevin Grittner wrote: Josh Berkus j...@agliodbs.com wrote: On 10/11/2013 01:11 PM, Bruce Momjian wrote: In summary, I think we need to: * decide on new defaults for work_mem

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-16 Thread Josh Berkus
On 02/16/2014 09:26 PM, Robert Haas wrote: I don't really know about cpu_tuple_cost. Kevin's often advocated raising it, but I haven't heard anyone else advocate for that. I think we need data points from more people to know whether or not that's a good idea in general. In 10 years of

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-16 Thread Gavin Flower
On 17/02/14 15:26, Robert Haas wrote: On Thu, Feb 13, 2014 at 3:34 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Oct 11, 2013 at 03:39:51PM -0700, Kevin Grittner wrote: Josh Berkus j...@agliodbs.com wrote: On 10/11/2013 01:11 PM, Bruce Momjian wrote: In summary, I think we need to: *

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-13 Thread Bruce Momjian
On Fri, Oct 11, 2013 at 03:39:51PM -0700, Kevin Grittner wrote: Josh Berkus j...@agliodbs.com wrote: On 10/11/2013 01:11 PM, Bruce Momjian wrote: In summary, I think we need to: *  decide on new defaults for work_mem and maintenance_work_mem *  add an initdb flag to allow

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-18 Thread Josh Berkus
All, So, I did an informal survey last night a SFPUG, among about 30 PostgreSQL DBAs and developers. While hardly a scientific sample, it's a data point on what we're looking at for servers. Out of the 30, 6 had one or more production instances of PostgreSQL running on machines or VMs with less

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Robert Haas
On Wed, Oct 16, 2013 at 5:14 PM, Josh Berkus j...@agliodbs.com wrote: On 10/16/2013 01:25 PM, Andrew Dunstan wrote: Andres has just been politely pointing out to me that my knowledge of memory allocators is a little out of date (i.e. by a decade or two), and that this memory is not in fact

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: I still think my previous proposal of increasing the defaults for work_mem and maintenance_work_mem by 4X would serve many more people well than it would serve poorly.  I haven't heard anyone disagree with that notion.  Does anyone disagree?  Should we

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Joshua D. Drake
On 10/17/2013 08:55 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I still think my previous proposal of increasing the defaults for work_mem and maintenance_work_mem by 4X would serve many more people well than it would serve poorly. I haven't heard anyone disagree with

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Josh Berkus
JD, A lot. A whole lot, more than what most people have in production with more than that. You are forgetting a very large segment of the population who run... VMs. Actually, even a mini AWS instance has 1GB of RAM. And nobody who uses a micro is going to expect it to perform well under

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Robert Haas
On Thu, Oct 17, 2013 at 12:03 PM, Joshua D. Drake j...@commandprompt.com wrote: On 10/17/2013 08:55 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I still think my previous proposal of increasing the defaults for work_mem and maintenance_work_mem by 4X would serve many

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Joshua D. Drake
On 10/17/2013 09:49 AM, Robert Haas wrote: A lot. A whole lot, more than what most people have in production with more than that. You are forgetting a very large segment of the population who run... VMs. That's true, but are you actually arguing for keeping work_mem at 1MB? Even on a VM

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Jeff Janes
On Thu, Oct 17, 2013 at 9:03 AM, Joshua D. Drake j...@commandprompt.comwrote: On 10/17/2013 08:55 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I still think my previous proposal of increasing the defaults for work_mem and maintenance_work_mem by 4X would serve many

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Joshua D. Drake
On 10/17/2013 10:33 AM, Jeff Janes wrote: A lot. A whole lot, more than what most people have in production with more than that. You are forgetting a very large segment of the population who run... VMs. Why don't we just have 3 default config files: 2GB memory 4GB

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Merlin Moncure
On Thu, Oct 17, 2013 at 7:22 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Oct 16, 2013 at 5:14 PM, Josh Berkus j...@agliodbs.com wrote: On 10/16/2013 01:25 PM, Andrew Dunstan wrote: Andres has just been politely pointing out to me that my knowledge of memory allocators is a little out

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread MauMau
From: Andres Freund and...@2ndquadrant.com I've seen several sites shutting down because of forgotten prepared transactions causing bloat and anti-wraparound shutdowns. From: Magnus Hagander mag...@hagander.net I would say *using* an external transaction manager *is* the irregular thing. The

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread Andrew Dunstan
On 10/09/2013 11:06 AM, Andrew Dunstan 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. Andres has just been politely pointing out to me that my knowledge of memory allocators is a little out

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread Bruce Momjian
On Wed, Oct 16, 2013 at 04:25:37PM -0400, Andrew Dunstan wrote: On 10/09/2013 11:06 AM, Andrew Dunstan 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. Andres has just been politely

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread Claudio Freire
On Wed, Oct 16, 2013 at 5:30 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Oct 16, 2013 at 04:25:37PM -0400, Andrew Dunstan wrote: On 10/09/2013 11:06 AM, Andrew Dunstan wrote: The assumption that each connection won't use lots of work_mem is also false, I think, especially in these

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread Josh Berkus
On 10/16/2013 01:25 PM, Andrew Dunstan wrote: Andres has just been politely pointing out to me that my knowledge of memory allocators is a little out of date (i.e. by a decade or two), and that this memory is not in fact likely to be held for a long time, at least on most modern systems. That

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread MauMau
From: Magnus Hagander mag...@hagander.net On Oct 12, 2013 2:13 AM, MauMau maumau...@gmail.com wrote: I'm not sure if many use XA features, but I saw the questions and answer a few times, IIRC. In the trouble situation, PostgreSQL outputs an intuitive message like increase

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Andres Freund
On 2013-10-15 21:41:18 +0900, MauMau wrote: Likewise, non-zero max_prepared_transactons would improve the impression of PostgreSQL (for limited number of users, though), and it wouldn't do any harm. I've seen several sites shutting down because of forgotten prepared transactions causing bloat

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread MauMau
From: Dimitri Fontaine dimi...@2ndquadrant.fr The reason why that parameter default has changed from 5 to 0 is that some people would mistakenly use a prepared transaction without a transaction manager. Few only people are actually using a transaction manager that it's better to have them have

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 2:47 PM, MauMau maumau...@gmail.com wrote: From: Dimitri Fontaine dimi...@2ndquadrant.fr The reason why that parameter default has changed from 5 to 0 is that some people would mistakenly use a prepared transaction without a transaction manager. Few only people are

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Josh Berkus
On 10/15/2013 05:52 AM, Magnus Hagander wrote: But the argument about being friendly for new users should definitely have us change wal_level and max_wal_senders. +1 for having replication supported out-of-the-box aside from pg_hba.conf. To put it another way: users are more likely to care

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Andres Freund
On 2013-10-15 10:19:06 -0700, Josh Berkus wrote: On 10/15/2013 05:52 AM, Magnus Hagander wrote: But the argument about being friendly for new users should definitely have us change wal_level and max_wal_senders. +1 for having replication supported out-of-the-box aside from pg_hba.conf.

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 10:19:06 -0700, Josh Berkus wrote: On 10/15/2013 05:52 AM, Magnus Hagander wrote: But the argument about being friendly for new users should definitely have us change wal_level and max_wal_senders.

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Andres Freund
On 2013-10-15 19:29:50 +0200, Magnus Hagander wrote: On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 10:19:06 -0700, Josh Berkus wrote: On 10/15/2013 05:52 AM, Magnus Hagander wrote: But the argument about being friendly for new users should

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 7:32 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 19:29:50 +0200, Magnus Hagander wrote: On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 10:19:06 -0700, Josh Berkus wrote: On 10/15/2013 05:52 AM, Magnus

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-14 Thread Robert Haas
On Sat, Oct 12, 2013 at 3:07 AM, Magnus Hagander mag...@hagander.net wrote: On Oct 11, 2013 10:23 PM, Josh Berkus j...@agliodbs.com wrote: On 10/11/2013 01:11 PM, Bruce Momjian wrote: In summary, I think we need to: * decide on new defaults for work_mem and maintenance_work_mem * add

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-14 Thread Jim Nasby
On 10/14/13 8:18 AM, Robert Haas wrote: On Sat, Oct 12, 2013 at 3:07 AM, Magnus Hagander mag...@hagander.net wrote: On Oct 11, 2013 10:23 PM, Josh Berkus j...@agliodbs.com wrote: On 10/11/2013 01:11 PM, Bruce Momjian wrote: In summary, I think we need to: * decide on new defaults for

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-13 Thread Dimitri Fontaine
MauMau maumau...@gmail.com writes: I understand this problem occurs only when the user configured the application server to use distributed transactions, the application server crashed between prepare and commit/rollback, and the user doesn't recover the application server. So only improper

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-13 Thread Dimitri Fontaine
Magnus Hagander mag...@hagander.net writes: Frankly, I think we'd help 1000 times more users of we enabled a few wal writers by default and jumped the wal level. Mainly so they could run one off base backup. That's used by orders of magnitude more users than XA. +1, or += default

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-13 Thread Andres Freund
On 2013-10-12 09:04:55 +0200, Magnus Hagander wrote: Frankly, I think we'd help 1000 times more users of we enabled a few wal writers by default and jumped the wal level. Mainly so they could run one off base backup. That's used by orders of magnitude more users than XA. Yes, I've thought

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-12 Thread Magnus Hagander
On Oct 12, 2013 2:13 AM, MauMau maumau...@gmail.com wrote: From: Bruce Momjian br...@momjian.us On Thu, Oct 10, 2013 at 11:01:52PM +0900, MauMau wrote: Although this is not directly related to memory, could you set max_prepared_transactions = max_connections at initdb time? People must

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-12 Thread Magnus Hagander
On Oct 11, 2013 10:23 PM, Josh Berkus j...@agliodbs.com wrote: On 10/11/2013 01:11 PM, Bruce Momjian wrote: In summary, I think we need to: * decide on new defaults for work_mem and maintenance_work_mem * add an initdb flag to allow users/packagers to set shared_bufffers? * add an

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread Magnus Hagander
On Thu, Oct 10, 2013 at 9: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

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 10:20:36PM -0700, Josh Berkus wrote: 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

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread Josh Berkus
On 10/11/2013 01:11 PM, Bruce Momjian wrote: In summary, I think we need to: * decide on new defaults for work_mem and maintenance_work_mem * add an initdb flag to allow users/packagers to set shared_bufffers? * add an autovacuum_work_mem setting? * change the default for temp_buffers?

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread Kevin Grittner
Josh Berkus j...@agliodbs.com wrote: On 10/11/2013 01:11 PM, Bruce Momjian wrote: In summary, I think we need to: *  decide on new defaults for work_mem and maintenance_work_mem *  add an initdb flag to allow users/packagers to set shared_bufffers? *  add an autovacuum_work_mem setting? * 

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread MauMau
From: Bruce Momjian br...@momjian.us On Thu, Oct 10, 2013 at 11:01:52PM +0900, MauMau wrote: Although this is not directly related to memory, could you set max_prepared_transactions = max_connections at initdb time? People must feel frustrated when they can't run applications on a Java or .NET

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread MauMau
From: Dimitri Fontaine dimi...@2ndquadrant.fr MauMau maumau...@gmail.com writes: Although this is not directly related to memory, could you set max_prepared_transactions = max_connections at initdb time? People must You really need to have a transaction manager around when issuing prepared

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Wed, Oct 9, 2013 at 11:35 PM, 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

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
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 variable based on that and then autotune off that, just have *initdb* do

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Peter Geoghegan (p...@heroku.com) wrote: 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

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* 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 variable based on that and then autotune off that, just have *initdb* do these calculations you're

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread MauMau
From: Bruce Momjian br...@momjian.us I will work on auto-tuning temp_buffers next. Any other suggestions? wal_buffers is already auto-tuned. Great work. I'm looking forward to becoming able to fully utilize system resources right after initdb. Although this is not directly related to

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: I actually had the thought that it might be something we'd integrate *into* initdb.  So you'd do initdb --system-memory 8GB or something like that and it would do the rest.  That'd be slick, at least IMHO. How would you handle the case that the machine

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread MauMau
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 variable based on that and then

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 11:01:52PM +0900, MauMau wrote: From: Bruce Momjian br...@momjian.us I will work on auto-tuning temp_buffers next. Any other suggestions? wal_buffers is already auto-tuned. Great work. I'm looking forward to becoming able to fully utilize system resources right

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Dimitri Fontaine
MauMau maumau...@gmail.com writes: Although this is not directly related to memory, could you set max_prepared_transactions = max_connections at initdb time? People must You really need to have a transaction manager around when issuing prepared transaction as failing to commit/rollback them

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 07:24:26AM -0700, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I actually had the thought that it might be something we'd integrate *into* initdb.  So you'd do initdb --system-memory 8GB or something like that and it would do the rest.  That'd be

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 07:24:26AM -0700, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I actually had the thought that it might be something we'd integrate *into* initdb.  So you'd do initdb --system-memory 8GB or something

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Wed, Oct 9, 2013 at 09:34:16PM -0400, Robert Haas wrote: But your auto-tuned value can easily be too low or too high, too. Consider someone with a system that has 64GB of RAM. EnterpriseDB has had customers who have found that with, say, a 40GB database, it's best to set shared_buffers

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 07:24:26AM -0700, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I actually had the thought that it might be something we'd integrate

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote: For this case, I think the suggestion made by MauMau would be better- tell the user (in the postgresql.conf comments) a command they can run with different memory settings to see what

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 11:45:41AM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote: For this case, I think the suggestion made by MauMau would be better- tell the user (in the postgresql.conf comments) a

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: Well, I like the idea of initdb calling the tool, though the tool then would need to be in C probably as we can't require python for initdb. The tool would not address Robert's issue of someone increasing shared_buffers on their own. I'm really not

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: Well, I like the idea of initdb calling the tool, though the tool then would need to be in C probably as we can't require python for initdb. The tool would not address Robert's issue of

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Andrew Dunstan
On 10/10/2013 12:28 PM, Bruce Momjian 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. Without considering any wider question here, let me just note this: Anything that can be done in this area

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 12:39:04PM -0400, Andrew Dunstan wrote: On 10/10/2013 12:28 PM, Bruce Momjian 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. Without considering any wider question

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Andrew Dunstan
On 10/10/2013 12:45 PM, Bruce Momjian wrote: On Thu, Oct 10, 2013 at 12:39:04PM -0400, Andrew Dunstan wrote: On 10/10/2013 12:28 PM, Bruce Momjian 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.

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 12:59:39PM -0400, Andrew Dunstan wrote: On 10/10/2013 12:45 PM, Bruce Momjian wrote: On Thu, Oct 10, 2013 at 12:39:04PM -0400, Andrew Dunstan wrote: On 10/10/2013 12:28 PM, Bruce Momjian wrote: How do we handle the Python dependency, or is this all to be done in some

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
Because 'maintenance' operations were rarer, so we figured we could use more memory in those cases. Once we brought Autovacuum into core, though, we should have changed that. However, I agree with Magnus that the simple course is to have an autovacuum_worker_memory setting which overrides

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
On 10/09/2013 02:15 PM, Bruce Momjian wrote: and for shared_buffers of 2GB: test= show shared_buffers; shared_buffers 2GB (1 row) test= SHOW work_mem; work_mem -- 6010kB (1 row) Huh?

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 10:20:02AM -0700, Josh Berkus wrote: On 10/09/2013 02:15 PM, Bruce Momjian wrote: and for shared_buffers of 2GB: test= show shared_buffers; shared_buffers 2GB (1 row) test= SHOW work_mem; work_mem

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
All, We can't reasonably require user input at initdb time, because most users don't run initdb by hand -- their installer does it for them. So any tuning which initdb does needs to be fully automated. So, the question is: can we reasonably determine, at initdb time, how much RAM the system

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote: I'm really not impressed with this argument. Either the user is going to go and modify the config file, in which case I would hope that they'd at least glance around at what

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Jeff Janes
On Wed, Oct 9, 2013 at 8:06 AM, Andrew Dunstan and...@dunslane.net 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

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
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 6MB gives us 600MB, and if each backend uses 3x work_mem, that gives

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
It also doesn't address my point that, if we are worst-case-scenario default-setting, we're going to end up with defaults which aren't materially different from the current defaults. In which case, why even bother with this whole exercise? Oh, and let me reiterate: the way to optimize

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: Well, I like the idea of initdb calling the tool, though the tool then would need to be in C probably as we can't

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 1:37 PM, Josh Berkus j...@agliodbs.com wrote: So, the question is: can we reasonably determine, at initdb time, how much RAM the system has? As long as you are willing to write platform-dependent code, yes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Geoghegan
On Thu, Oct 10, 2013 at 11:41 AM, Robert Haas robertmh...@gmail.com wrote: I don't see why it can't be done in C. The server is written in C, and so is initdb. So no matter where we do this, it's gonna be in C. Where does Python enter into it? I mentioned that pgtune was written in Python,

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Geoghegan
On Thu, Oct 10, 2013 at 11:43 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 10, 2013 at 1:37 PM, Josh Berkus j...@agliodbs.com wrote: So, the question is: can we reasonably determine, at initdb time, how much RAM the system has? As long as you are willing to write

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Eisentraut
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. I think this is a promising idea. available_mem could even be set

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
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 want a mode that rewrites the configuration file, we could have: tunedb

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Magnus Hagander
On Thu, Oct 10, 2013 at 8:41 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: Well, I like the idea of initdb calling

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
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 want a mode that rewrites the configuration file, we could have: tunedb --available-memory=32GB --rewrite-config-file=$PATH ...but that

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Geoghegan
On Wed, Oct 9, 2013 at 10:21 PM, Magnus Hagander mag...@hagander.net wrote: Well, the Postgres defaults won't really change, because the default vacuum_work_mem will be -1, which will have vacuum defer to maintenance_work_mem. Under this scheme, vacuum only *prefers* to get bound working

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Eisentraut
On 10/10/13 11:45 AM, Bruce Momjian wrote: I think the big win for a tool would be to query the user about how they are going to be using Postgres, and that can then spit out values the user can add to postgresql.conf, or to a config file that is included at the end of postgresql.conf. I

  1   2   >