Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Mon, Mar 10, 2014 at 3:16 PM, Kevin Grittner wrote: > Andres Freund 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 that's a good idea in general. > > > > FWIW It's a good idea in my experience. > > This is more about the balance among the various cpu_* costs than > the balance between cpu_* costs and the *_page costs. I usually > need to adjust the page costs, too; and given how heavily cached > many machines are, I'm usually moving them down. But if you think > about the work involved in moving to a new tuple, do you really > think it's only twice the cost of moving to a new index entry on an > index scan? Or only four times as expensive as executing an > average operator function? If the next tuple is already hinted and not compressed or toasted, I would completely believe that. In fact, unless the operator is integer or dp, I would say it is less than 2 times as expensive. If it is a text operator and the collation is not "C" or "POSIX", then moving to the next tuple is likely less expensive than a single operator evaluation. If your tuples are updated nearly as often as queried, the hint resolution could be a big cost. But in that case, probably the contention would be a bigger issue than the pure CPU cost. I don't know how compression and toast would affect the times. Are your tables heavily toasted? If top down measurements and bottom up measurements aren't giving the same results, then what is going on? We know and document how caching needs to be baked into the page costs parameters. What unknown thing is throwing off the cpu costs? > In my experience setting cpu_tuple_cost > higher tends to better model costs, and prevent CPU-sucking scans > of large numbers of rows. > > 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 would prefer to > hear about other data points based on field experience with > production systems. I haven't offered the trivial patch because > when I've raised the point before, there didn't seem to be anyone > else who had the same experience. It's good to hear that Andres > has seen this, too. > > FWIW, even though I'm repeating something I've mentioned before, > whenever raising this setting did help, 0.03 was high enough to see > the benefit. Several times I have also tried 0.05 just to test > whether I was wandering near a tipping point for a bad choice from > this. I have never had 0.05 produce plans noticeably better or > worse than 0.03. > Have you ever tried lowering the other two cpu cost parameters instead? That would be the more definitive indication that the benefit is not coming just by moving the io costs closer to the cpu costs Cheers, Jeff
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 would prefer to > hear about other data points based on field experience with > production systems. I haven't offered the trivial patch because > when I've raised the point before, there didn't seem to be anyone > else who had the same experience. It's good to hear that Andres > has seen this, too. The problem with cpu_tuple_cost is that it's used in several places by the planner and makes it hard to model what the effect of any change would be. If we had a good general benchmark which actually gave the query planner a workout, we could come up with some reasonable default settings, but right now we can't. Back in 2004-2006 era, when CPU speeds had leapfrogged ahead of disk speeds (which were largely unchanged from 2000), I was routinely *lowering* cpu_tuple_cost (and cpu_index_tuple_cost) to get better plans. This was baked into early versions of Greenplum for that reason. So I'm not saying that we shouldn't change the default for cpu_tuple_cost. I'm saying that we currently don't have enough information on *when* and *how much* to change it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
Andres Freund 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 that's a good idea in general. > > FWIW It's a good idea in my experience. This is more about the balance among the various cpu_* costs than the balance between cpu_* costs and the *_page costs. I usually need to adjust the page costs, too; and given how heavily cached many machines are, I'm usually moving them down. But if you think about the work involved in moving to a new tuple, do you really think it's only twice the cost of moving to a new index entry on an index scan? Or only four times as expensive as executing an average operator function? In my experience setting cpu_tuple_cost higher tends to better model costs, and prevent CPU-sucking scans of large numbers of rows. 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 would prefer to hear about other data points based on field experience with production systems. I haven't offered the trivial patch because when I've raised the point before, there didn't seem to be anyone else who had the same experience. It's good to hear that Andres has seen this, too. FWIW, even though I'm repeating something I've mentioned before, whenever raising this setting did help, 0.03 was high enough to see the benefit. Several times I have also tried 0.05 just to test whether I was wandering near a tipping point for a bad choice from this. I have never had 0.05 produce plans noticeably better or worse than 0.03. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 do the >> honors, by all means! > > 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 dangerous. Good point. Especially with pagination involved. Those OFFSET 4 LIMIT 100 queries can be a killer. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Mon, Feb 24, 2014 at 1:05 PM, Bruce Momjian 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 have added autovacuum_work_mem, and increasing >> > > work_mem and maintenance_work_mem by 4x is a simple operation. Not sure >> > > about the others. Or do we just keep this all for 9.5? >> > >> > 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! >> >> OK, patch attached. > > Patch applied. Thanks! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 maintenance_work_mem by 4x is a simple operation. Not sure > > > about the others. Or do we just keep this all for 9.5? > > > > 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! > > OK, patch attached. Patch applied. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Sun, Feb 16, 2014 at 6:26 PM, Robert Haas 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 value would be. > I don't quite understand the point of bgwriter_lru_maxpages in the first place. What is it supposed to protect us from? I wonder if that isn't an artefact from when the checkpointer was the same process as the background writer, to prevent the background writer functionality from starving the checkpointer functionality. Cheers, Jeff
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Mon, Feb 17, 2014 at 8:31 AM, Stephen Frost 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 dangerous. > > 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 memory from this, while the 10% of the queries which are > more complicated will greatly improve. +1 -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On 18/02/14 03:48, Tom Lane wrote: Gavin Flower 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 that's a good idea in general. Processors have been getting faster, relative to spinning rust, over the years. So it puzzles me why anybody would want to raise the cpu_tuple_cost! The case where this is sensible is where your database mostly fits in RAM, so that the cost of touching the underlying spinning rust isn't so relevant. The default cost settings are certainly not very good for such scenarios. regards, tom lane Thanks. That is obvious... once you pointed it out! Cheers, Gavin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 > > 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 memory from this, while the 10% of the queries which are > > >> more complicated will greatly improve. > > > > > > Uh. Paging. > > > > What about it? > > It's often the source of a good portion of the queries and load in web > applications. Multiple joins and more than one row... I have several > time seen stats changes or bad to-be-sorted columns cause large amounts > of memory to be used. Perhaps we should have said there was "general agreement" to increase work_mem and maintenence_work_mem by 4x, not that there was 100% agreement. It would be nice to have 100% agreement, but if we _require_ that then defaults would probably never be changed. > Anyway, I've stated my opinion that I do not think it's a good idea to > raise that particular default (while agreeing with all the others) and I > know I am in the minority, so I don't think we need to argue this out... OK, good. If you did feel there was need for more discussion, we would need to push this change to PG 9.5. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On 2014-02-17 13:33:17 -0500, Robert Haas wrote: > On Mon, Feb 17, 2014 at 11:33 AM, Andres Freund > 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 memory from this, while the 10% of the queries which are > >> more complicated will greatly improve. > > > > Uh. Paging. > > What about it? It's often the source of a good portion of the queries and load in web applications. Multiple joins and more than one row... I have several time seen stats changes or bad to-be-sorted columns cause large amounts of memory to be used. Anyway, I've stated my opinion that I do not think it's a good idea to raise that particular default (while agreeing with all the others) and I know I am in the minority, so I don't think we need to argue this out... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Mon, Feb 17, 2014 at 11:33 AM, Andres Freund 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 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 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 dangerous. >> >> 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 memory from this, while the 10% of the queries which are >> more complicated will greatly improve. > > Uh. Paging. What about it? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
Andres Freund 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 push-back from the field is the only way to know if we've gone too far for average users. I'm OK with raising work_mem 4X in one go, but I'd complain if it were 10X, or if we were also raising other resource consumption limits in the same release. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On 2014-02-17 12:23:58 -0500, Robert Haas wrote: > On Mon, Feb 17, 2014 at 11: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 do the > >> honors, by all means! > > > > 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 dangerous. > > 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 :) > With the proposed defaults, a user with one sort or hash in every > session, each of which uses the entirety of work_mem, is on the hook > for 400MB. If you're trying to handle 100 connections on a machine > that does not have 400MB of working memory available, you are probably > in for a bad time of it. Sure, if that's all they do it's fine. But often enough queries aren't that simple. Lots of the ORMs commonly used for web applications tend to create lots of JOINs to gather all the data and also use sorting for paging. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Mon, Feb 17, 2014 at 11: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 do the >> honors, by all means! > > 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 dangerous. I think you may be out-voted. An awful lot of people have voiced support for the idea of raising this value, and there is no rule that our default should be the smallest value that anyone will ever find useful. We do tend to err on the side of conservatism and aim for a relatively low-end machine, and I agree with that policy, but there is such a thing as going overboard. With the proposed defaults, a user with one sort or hash in every session, each of which uses the entirety of work_mem, is on the hook for 400MB. If you're trying to handle 100 connections on a machine that does not have 400MB of working memory available, you are probably in for a bad time of it. Now, if you're saying that people raise max_connections to say 1000 *and do nothing else* perhaps that makes the argument more plausible. But I don't think it makes it very much more plausible. Even a high-end system is likely to deliver terrible performance if the user has 1000 simultaneously-active connections; one with only a few GB of memory is going to be crushed like a bug. I'll note that in 9.3, we quadrupled the default size of shared_buffers when we got out from under the POSIX shared memory limits and AFAIK we've had zero complaints about that. It is entirely possible, even likely, that there is a machine out there somewhere for which the old value of 32MB is preferable, and those people can configure a smaller value. But that's not typical. And neither do I believe that the typical PostgreSQL user wants a 2MB sort to spill to disk. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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, > > > so I think we should go ahead and do that. If you'd like to do the > > > honors, by all means! > > > > 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 dangerous. > > 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 memory from this, while the 10% of the queries which are > more complicated will greatly improve. Uh. Paging. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
* 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 like to do the > > honors, by all means! > > 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 dangerous. 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 memory from this, while the 10% of the queries which are more complicated will greatly improve. > > 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. > > FWIW It's a good idea in my experience. I'm in favor of this also but I'm also in the camp of "gee, more data would be nice". Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 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 dangerous. > 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. FWIW It's a good idea in my experience. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 others. Or do we just keep this all for 9.5? > > 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! OK, patch attached. > 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 value would be. > > The current vacuum cost delay settings limit autovacuum to about > 2.6MB/s. I am inclined to think we need a rather large bump there, > like 10x, but maybe it would be more prudent to do a smaller bump, > like say 4x, to avoid changing the default behavior too dramatically > between releases. IOW, I guess I'm proposing raising > vacuum_cost_limit from 200 to 800. > > 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. Robert, can you take the lead on these remaining possible changes? We don't have time for any controversial changes but things everyone can agree on, like work_mem, should be implemented for 9.4. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml new file mode 100644 index e12778b..47bdebf *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** include 'filename' *** 1213,1219 Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value ! defaults to one megabyte (1MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary --- 1213,1219 Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value ! defaults to four megabytes (4MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary *** include 'filename' *** 1239,1245 Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults ! to 16 megabytes (16MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger --- 1239,1245 Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults ! to 64 megabytes (64MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c new file mode 100644 index 86afde1..aa5a875 *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *** static struct config_int ConfigureNamesI *** 1773,1779 GUC_UNIT_KB }, &work_mem, ! 1024, 64, MAX_KILOBYTES, NULL, NULL, NULL }, --- 1773,1779 GUC_UNIT_KB }, &work_mem, ! 4096, 64, MAX_KILOBYTES, NULL, NULL, NULL }, *** static struct config_int ConfigureNamesI *** 1784,1790 GUC_UNIT_KB }, &maintenance_work_mem, ! 16384, 1024, MAX_KILOBYTES, NULL, NULL, NULL }, --- 1784,1790 GUC_UNIT_KB }, &maintenance_work_mem, ! 65536, 1024, MAX_KILOBYTES, NULL, NULL, NULL }, diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample new file mode 100644 index 480c9e9..07341e7 *** a/src/backend/utils/misc/postgresql.conf.sample --- b/src/backend/utils/misc/postgresql.conf.
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
Gavin Flower 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 >> that's a good idea in general. > Processors have been getting faster, relative to spinning rust, over the > years. So it puzzles me why anybody would want to raise the > cpu_tuple_cost! The case where this is sensible is where your database mostly fits in RAM, so that the cost of touching the underlying spinning rust isn't so relevant. The default cost settings are certainly not very good for such scenarios. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On 17/02/14 15:26, Robert Haas wrote: On Thu, Feb 13, 2014 at 3:34 PM, Bruce Momjian wrote: On Fri, Oct 11, 2013 at 03:39:51PM -0700, Kevin Grittner wrote: Josh Berkus 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? * change the default for temp_buffers? If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit could also use a bump; those thresholds were set for servers with < 1GB of RAM. +1 on those. Also, I have often had to bump cpu_tuple_cost into the 0.03 to 0.05 range to get a good plan. In general, this makes the exact settings of *_page_cost less fussy, and I have hit situations where I was completely unable to get a good plan to emerge without bumping cpu_tuple_cost relative to the other cpu costs. I know that it's possible to engineer a workload that shows any particular cost adjustment to make things worse, but in real-life production environments I have never seen an increase in this range make plan choice worse. 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 others. Or do we just keep this all for 9.5? 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! 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 value would be. The current vacuum cost delay settings limit autovacuum to about 2.6MB/s. I am inclined to think we need a rather large bump there, like 10x, but maybe it would be more prudent to do a smaller bump, like say 4x, to avoid changing the default behavior too dramatically between releases. IOW, I guess I'm proposing raising vacuum_cost_limit from 200 to 800. 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. Processors have been getting faster, relative to spinning rust, over the years. So it puzzles me why anybody would want to raise the cpu_tuple_cost! Possibly, the various costs should change if the database is on SSD's? Of course, I have the implicit assumption that cost factors like 'cpu_tuple_cost' have more than just a vague relation to the semantics implied by their naming! It would be good, if can we get some clarity on what these various cost factors are actually meant to do and how they relate to each other. Cheers, Gavin Cheers, Gavin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 tuning PostgreSQL professionally, I still don't have a mathematical model for the interaction of the various *_cost parameters with the speeds of CPU, RAM and IO. If someone else has one, please post it so that we can make some intelligent decisions on defaults. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Feb 13, 2014 at 3:34 PM, Bruce Momjian wrote: > On Fri, Oct 11, 2013 at 03:39:51PM -0700, Kevin Grittner wrote: >> Josh Berkus 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? >> >> * change the default for temp_buffers? >> > >> > If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit >> > could also use a bump; those thresholds were set for servers with < 1GB >> > of RAM. >> >> +1 on those. >> >> Also, I have often had to bump cpu_tuple_cost into the 0.03 to 0.05 >> range to get a good plan. In general, this makes the exact >> settings of *_page_cost less fussy, and I have hit situations where >> I was completely unable to get a good plan to emerge without >> bumping cpu_tuple_cost relative to the other cpu costs. I know that >> it's possible to engineer a workload that shows any particular cost >> adjustment to make things worse, but in real-life production >> environments I have never seen an increase in this range make plan >> choice worse. > > 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 others. Or do we just keep this all for 9.5? 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! 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 value would be. The current vacuum cost delay settings limit autovacuum to about 2.6MB/s. I am inclined to think we need a rather large bump there, like 10x, but maybe it would be more prudent to do a smaller bump, like say 4x, to avoid changing the default behavior too dramatically between releases. IOW, I guess I'm proposing raising vacuum_cost_limit from 200 to 800. 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Fri, Oct 11, 2013 at 03:39:51PM -0700, Kevin Grittner wrote: > Josh Berkus 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? > >> * change the default for temp_buffers? > > > > If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit > > could also use a bump; those thresholds were set for servers with < 1GB > > of RAM. > > +1 on those. > > Also, I have often had to bump cpu_tuple_cost into the 0.03 to 0.05 > range to get a good plan. In general, this makes the exact > settings of *_page_cost less fussy, and I have hit situations where > I was completely unable to get a good plan to emerge without > bumping cpu_tuple_cost relative to the other cpu costs. I know that > it's possible to engineer a workload that shows any particular cost > adjustment to make things worse, but in real-life production > environments I have never seen an increase in this range make plan > choice worse. 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 others. Or do we just keep this all for 9.5? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 than 1GB of RAM. Out of those 5 had already edited their PostgreSQL.conf extensively. Perhaps more importantly, for four out of the 6, the low-memory Postgres instance(s) was an older version (8.2 to 9.0) which they did not expect to upgrade. Also, note that a couple of the 6 were consultants, so they were speaking for dozens of customer servers. As a second data point, Christophe and I did a quick survey of the database of server information on our clients, which include a bunch of cloud-hosted web companies. We found two PostgreSQL VMs which did not have 1GB or more RAM, out of a few hundred. Now, obviously, there's some significant sample bias in the above, but I think it gives support to the assertion that we shouldn't really be worrying about PostgresQL running well out-of-the-box on machines with < 1GB of RAM. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 17, 2013 at 7:22 AM, Robert Haas wrote: > On Wed, Oct 16, 2013 at 5:14 PM, Josh Berkus 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 likely to be held for a long time, at >>> least on most modern systems. That undermines completely my reasoning >>> above. >> >> Except that Opensolaris and FreeBSD still have the old memory allocation >> behavior, as do older Linux kernels, many of which will remain in >> production for years. I have no idea what Windows' memory management >> behavior is. >> >> So this is a case of needing to know considerably more than the >> available RAM to determine a good setting. > > I agree, but 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 do it? One source of hesitation for me is that I have a hunch that the stock assumptions that go into shared_buffers will probably change, possibly by a lot. For a lot of (I think very good-) reasons current policy is to set s_b to max 2GB. After we nail some of the outstanding contention issues and make other optimizations I expect that the optimal setting may be 50% of ram or higher. Point being: I like the idea of an initdb time setting that takes in the estimated amount of RAM that is going to be dedicated to the database. From there, we then estimate best settings for the various configurations (perhaps taking in extra hypothetical parameters to help that job along). So the anchor should not be s_b, but user specified. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 memory 8GB memory But what would go in each of those files? Once we agree on what would be in them, why not just have a continuous knob that does that same thing? Because we should set defaults, not optimized parameters. Workloads vary and we can reasonably say this is what we want BY DEFAULT for something but we can not reasonably say, "this is what will suit your needs". Once you get above 8GB of memory you are dealing with workloads that vary widely and will almost always need some kind of indvidual attention. However, 8GB and below, we can set reasonable defaults that allow a user to likely but possibly not worry about changing the conf. Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 17, 2013 at 9:03 AM, Joshua D. Drake wrote: > > On 10/17/2013 08:55 AM, Kevin Grittner wrote: > >> >> Robert Haas 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 do >>> it? >>> >> >> I think that it makes sense to do that. Those are still reasonable >> defaults for a machine with 2GB of RAM, maybe even with less. >> We're talking about putting this only in a release that will come >> out in 2014. How many machines used for a database server that new >> will have less than that? >> > > 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 memory > 8GB memory > But what would go in each of those files? Once we agree on what would be in them, why not just have a continuous knob that does that same thing? Would your suggestion for the 2GB file have work_mem be at least 4 MB? Cheers, Jeff
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 with only 1GB of RAM, work_mem=4MB is not going to cause any problems unless you're also trying to service a large number of simultaneous connections. And if you're doing that, you probably need to rethink something anyway. No. I am arguing for the multiple config file option. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 17, 2013 at 12:03 PM, Joshua D. Drake wrote: > On 10/17/2013 08:55 AM, Kevin Grittner wrote: >> Robert Haas 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 do >>> it? >> >> >> I think that it makes sense to do that. Those are still reasonable >> defaults for a machine with 2GB of RAM, maybe even with less. >> We're talking about putting this only in a release that will come >> out in 2014. How many machines used for a database server that new >> will have less than that? > > 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 with only 1GB of RAM, work_mem=4MB is not going to cause any problems unless you're also trying to service a large number of simultaneous connections. And if you're doing that, you probably need to rethink something anyway. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 load. I think it's completely reasonable to tell people running on < 1GB of ram to tune PostgreSQL "down". 4MB work_mem / 64MB maint_work_mem still works fine at 1GB. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On 10/17/2013 08:55 AM, Kevin Grittner wrote: Robert Haas 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 do it? I think that it makes sense to do that. Those are still reasonable defaults for a machine with 2GB of RAM, maybe even with less. We're talking about putting this only in a release that will come out in 2014. How many machines used for a database server that new will have less than that? 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 memory 8GB memory Have initdb detect how much memory is available on the machine in TOTAL and pick the most appropriate. Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
Robert Haas 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 do > it? I think that it makes sense to do that. Those are still reasonable defaults for a machine with 2GB of RAM, maybe even with less. We're talking about putting this only in a release that will come out in 2014. How many machines used for a database server that new will have less than that? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Wed, Oct 16, 2013 at 5:14 PM, Josh Berkus 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 likely to be held for a long time, at >> least on most modern systems. That undermines completely my reasoning >> above. > > Except that Opensolaris and FreeBSD still have the old memory allocation > behavior, as do older Linux kernels, many of which will remain in > production for years. I have no idea what Windows' memory management > behavior is. > > So this is a case of needing to know considerably more than the > available RAM to determine a good setting. I agree, but 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 do it? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 undermines completely my reasoning > above. Except that Opensolaris and FreeBSD still have the old memory allocation behavior, as do older Linux kernels, many of which will remain in production for years. I have no idea what Windows' memory management behavior is. So this is a case of needing to know considerably more than the available RAM to determine a good setting. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Wed, Oct 16, 2013 at 5:30 PM, Bruce Momjian 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 days of connection >> >poolers. >> > >> > >> >> >> 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 undermines >> completely my reasoning above. >> >> Given that, it probably makes sense for us to be rather more liberal >> in setting work_mem that I was suggesting. > > Ah, yes, this came up last year (MMAP_THRESHOLD): > > http://www.postgresql.org/message-id/20120730161416.gb10...@momjian.us Beware of depending on that threshold. It varies wildly among platforms. I've seen implementations with the threshold well above 64MB. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 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 undermines > completely my reasoning above. > > Given that, it probably makes sense for us to be rather more liberal > in setting work_mem that I was suggesting. Ah, yes, this came up last year (MMAP_THRESHOLD): http://www.postgresql.org/message-id/20120730161416.gb10...@momjian.us -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 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 undermines completely my reasoning above. Given that, it probably makes sense for us to be rather more liberal in setting work_mem that I was suggesting. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
From: "Andres Freund" I've seen several sites shutting down because of forgotten prepared transactions causing bloat and anti-wraparound shutdowns. From: "Magnus Hagander" I would say *using* an external transaction manager *is* the irregular thing. The current default *is* friendly for normal users, for example see the comments from Andres about what happens if you make a mistake. So I definitely agree with your sentiment that we should be more friendly for normal users - but in this case we are. If I look through all the customers I've worked with, only a handful have actually used a transaction manager. And of those, at least half of them were using it even though they didn't need it, because they didn't know what it was. I understand that you mean by *irregular* that there are few people who use distributed transactions. I guess so too: there are not many users who require distributed transactions in the real world. I meant by *irregular* that almost all users should use distributed transactions through an external transaction manager incorporated in Java EE application servers or MSDTC. The distributed transaction features like XA and Java Transaction API (JTA) are established. They are not irregular for those who need them; they were developed and exist for a long time, because they were/are needed. I don't think the default value of zero for max_prepared_transactions is friendly for normal and not-normal users. Normal users, who properly use external transaction manager, won't be caught by the trouble Andres mentioned, because the external transaction manager soon resolves prepared (in-doubt) transactions. Not-normal users, who uses PREPARE TRANSACTION statement or XAResource.prepare() directly from their applications without using external transaction manager or without need based on proper understanding, won't escape from Andres's concern. They will see the following message and follow it blindly to make their applications succeed. ERROR: prepared transactions are disabled HINT: Set max_prepared_transactions to a nonzero value. So, the current default of zero is not only unfriendly for normal users but also non-helpful for those who make mistakes. Regards MauMau -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Tue, Oct 15, 2013 at 7:32 PM, Andres Freund wrote: > On 2013-10-15 19:29:50 +0200, Magnus Hagander wrote: >> On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund >> 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. >> >> >> >> +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 about replication >> >> than they are about IO overhead on a non-replicated server. And for the >> >> users who care about IO overhead, they are more likely to much about in >> >> pg.conf *anyway* in order to set a slew of performance-tuning settings. >> > >> > But it will hurt people restoring backups using pg_restore -j. I think >> > people might be rather dissapointed if that slows down by a factor of >> > three. >> > >> > I think we really need to get to the point where we increase the wal >> > level ondemand... >> >> Yeha, there are really two things. >> >> If we can increase wal_level on demand, that would solve one of them. >> Turning that into a SIGHUP parameter would be great. I have no idea >> how hard it would be. In theory, couldn't we let it be sighup and then >> just have do_pg_start_backup() block until all backends have >> acknowledged that they are on the new WAL level somehow? (Yes, I >> realize this might be a big simplification, but I'm allowed to hope, >> no?) > > Depends on what you want to support. For basebackups, that should be > doable with some pullups. > It's unfortunately more complex than that for streaming rep - we really > need persistent standby registration there. Otherwise the wal_level will > fall back to minimal when the standby disconnects which will obviously > break the standby. I was actually thinking the easier step might not be to do it dynamically as the standby registers - just allow it to be a SIGHUP parameter. So you'd still change it in postgresql.conf, but it would be ok with a reload rather than a restart. Yes, fully dynamic would be better, so if we could combined those two, that would make us "require nothing for pg_basebackup, and just a reload for replication slaves". The point being that we wouldn't need a *restart* at any point - and that alond would be a big improvement. >> The other problem is max_wal_senders. I think that's a much smaller >> problem - setting that one to 5 or so by default shouldn't have a big >> impact. But without the wal_level changes, it would also be mostly >> pointless... > > Well, you currently cannot even set it when the wal_level isn't set > appropriately, but that that should be easy enough to change. Yes, it would be a trivial change to allow that parametre to be set and then just give an error if you try to initiate streaming in that case. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On 2013-10-15 19:29:50 +0200, Magnus Hagander wrote: > On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund 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. > >> > >> +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 about replication > >> than they are about IO overhead on a non-replicated server. And for the > >> users who care about IO overhead, they are more likely to much about in > >> pg.conf *anyway* in order to set a slew of performance-tuning settings. > > > > But it will hurt people restoring backups using pg_restore -j. I think > > people might be rather dissapointed if that slows down by a factor of > > three. > > > > I think we really need to get to the point where we increase the wal > > level ondemand... > > Yeha, there are really two things. > > If we can increase wal_level on demand, that would solve one of them. > Turning that into a SIGHUP parameter would be great. I have no idea > how hard it would be. In theory, couldn't we let it be sighup and then > just have do_pg_start_backup() block until all backends have > acknowledged that they are on the new WAL level somehow? (Yes, I > realize this might be a big simplification, but I'm allowed to hope, > no?) Depends on what you want to support. For basebackups, that should be doable with some pullups. It's unfortunately more complex than that for streaming rep - we really need persistent standby registration there. Otherwise the wal_level will fall back to minimal when the standby disconnects which will obviously break the standby. > The other problem is max_wal_senders. I think that's a much smaller > problem - setting that one to 5 or so by default shouldn't have a big > impact. But without the wal_level changes, it would also be mostly > pointless... Well, you currently cannot even set it when the wal_level isn't set appropriately, but that that should be easy enough to change. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund 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. >> >> +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 about replication >> than they are about IO overhead on a non-replicated server. And for the >> users who care about IO overhead, they are more likely to much about in >> pg.conf *anyway* in order to set a slew of performance-tuning settings. > > But it will hurt people restoring backups using pg_restore -j. I think > people might be rather dissapointed if that slows down by a factor of > three. > > I think we really need to get to the point where we increase the wal > level ondemand... Yeha, there are really two things. If we can increase wal_level on demand, that would solve one of them. Turning that into a SIGHUP parameter would be great. I have no idea how hard it would be. In theory, couldn't we let it be sighup and then just have do_pg_start_backup() block until all backends have acknowledged that they are on the new WAL level somehow? (Yes, I realize this might be a big simplification, but I'm allowed to hope, no?) The other problem is max_wal_senders. I think that's a much smaller problem - setting that one to 5 or so by default shouldn't have a big impact. But without the wal_level changes, it would also be mostly pointless... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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. > > To put it another way: users are more likely to care about replication > than they are about IO overhead on a non-replicated server. And for the > users who care about IO overhead, they are more likely to much about in > pg.conf *anyway* in order to set a slew of performance-tuning settings. But it will hurt people restoring backups using pg_restore -j. I think people might be rather dissapointed if that slows down by a factor of three. I think we really need to get to the point where we increase the wal level ondemand... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 about replication than they are about IO overhead on a non-replicated server. And for the users who care about IO overhead, they are more likely to much about in pg.conf *anyway* in order to set a slew of performance-tuning settings. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Tue, Oct 15, 2013 at 2:47 PM, MauMau wrote: > From: "Dimitri Fontaine" > >> 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 to set PostgreSQL. > > > I guess this problem is not unique to PostgreSQL. I think PostgreSQL can be > more friendly for normal users (who use external transaction manager), and > does not need to be too conservative because of people who do irregular > things. I would say *using* an external transaction manager *is* the irregular thing. The current default *is* friendly for normal users, for example see the comments from Andres about what happens if you make a mistake. So I definitely agree with your sentiment that we should be more friendly for normal users - but in this case we are. If I look through all the customers I've worked with, only a handful have actually used a transaction manager. And of those, at least half of them were using it even though they didn't need it, because they didn't know what it was. But the argument about being friendly for new users should definitely have us change wal_level and max_wal_senders. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
From: "Dimitri Fontaine" 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 to set PostgreSQL. I guess this problem is not unique to PostgreSQL. I think PostgreSQL can be more friendly for normal users (who use external transaction manager), and does not need to be too conservative because of people who do irregular things. Regards MauMau -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 and anti-wraparound shutdowns. A big, big -1 for changing that default. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
From: "Magnus Hagander" On Oct 12, 2013 2:13 AM, "MauMau" 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 max_prepared_transactions", so many users might possibly have been able to change the setting and solve the problem themselves without asking for help, feeling stress like "Why do I have to set this?" For example, max_prepared_transactions is called "hideous creature" in the following page: https://community.jboss.org/wiki/InstallPostgreSQLOnFedora?_sscc=t Anybody who follows that page is screwed anyway. I notice they recommend running regular VACUUM FULL across the whole database, so it's obvious they know nothing about postgresql. There's nothing we can do about what people write on random pages around the Internet. Regular VACUUM FULL is certainly overkill. Apart from that, having to set max_prepared_transactions seems to make PostgreSQL difficult for people with that level of knowledge, doesn't it? I wonder if there are other major DBMSs which require marameter configuration and server restart to use distributed transactions. According to the below page, the amount of memory consumed for this is "(770 + 270 * max_locks_per_transaction) * max_prepared_transactions". With the default setting of maxconnections=100 and max_locks_per_transaction=64, this is only 180KB. So the overhead is negligible. You are assuming memory is the only overhead. I don't think it is. Having a quick look at the source code, just setting max_prepared_transactions to non-zero seems to produce almost no processing overhead. If the goal is to make PostgreSQL more friendly and run smoothly without frustration from the start and not perfect tuning, I think max_prepared_transactions=max_connections is an easy and good item. If the goal is limited to auto-tuning memory sizes, this improvement can be treated separately. 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. Agreed. The default of non-zero max_wal_senders and wal_level > 'archive' would be beneficial for more users. 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. Regards MauMau -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On 10/14/13 8:18 AM, Robert Haas wrote: On Sat, Oct 12, 2013 at 3:07 AM, Magnus Hagander wrote: On Oct 11, 2013 10:23 PM, "Josh Berkus" 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? * change the default for temp_buffers? If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit could also use a bump; those thresholds were set for servers with < 1GB of RAM Uh, those are there to limit io and not memory, right? More memory isn't the reason to increase them, more io is. For people deploying on modern server hardware then yes it's often low, but for all those deploying in virtualized environments with io performance reminding you of the 1990ies, I'm not so sure it is... bgwriter_lru_maxpages is clearly related to the size of shared_buffers, although confusingly it is expressed as a number of buffers, while shared_buffers is expressed as a quantity of memory. I think we might have done better to call the GUC bgwriter_lru_maxpercent and make it a percentage of shared buffers. Also, more memory generally means more filesystem cache which means you can do more vacuum work per round. FWIW, on our 512G servers... cnuapp_p...@postgres11.obr=# select name, setting from pg_settings where name ~ 'vacuum_cost'; name | setting --+- autovacuum_vacuum_cost_delay | 10 autovacuum_vacuum_cost_limit | -1 vacuum_cost_delay| 10 vacuum_cost_limit| 2000 vacuum_cost_page_dirty | 10 vacuum_cost_page_hit | 1 vacuum_cost_page_miss| 10 (7 rows) The page_hit cost is intentionally the same as the page_dirty limit because writes to the SAN are generally far cheaper than reads that actually hit spindles. Of course with the amount of FS cache we have (512G-8G shared buffers at most) reads are often very likely to hit the FS cache, but tuning of these settings while watching IO stats has shown these settings to be minimally disruptive. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Sat, Oct 12, 2013 at 3:07 AM, Magnus Hagander wrote: > On Oct 11, 2013 10:23 PM, "Josh Berkus" 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? >> > * change the default for temp_buffers? >> >> If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit >> could also use a bump; those thresholds were set for servers with < 1GB >> of RAM > > Uh, those are there to limit io and not memory, right? More memory isn't the > reason to increase them, more io is. For people deploying on modern server > hardware then yes it's often low, but for all those deploying in virtualized > environments with io performance reminding you of the 1990ies, I'm not so > sure it is... bgwriter_lru_maxpages is clearly related to the size of shared_buffers, although confusingly it is expressed as a number of buffers, while shared_buffers is expressed as a quantity of memory. I think we might have done better to call the GUC bgwriter_lru_maxpercent and make it a percentage of shared buffers. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 about that several times as well. I think it might actually not be too hard to allow increasing the WAL level dynamically similar to what we do with full_page_writes. If we then would allow starting wal senders with a lower wal_level we would get there without breaking older installations which rely on TRUNCATE && COPY optimizations et al. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
Magnus Hagander 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 max_wal_senders actually ;-) My vote would be to have default to at least 3, so that you can run both a pg_basebackup -Xs (stream) and a standby or a pg_receivexlog in parallel. Maybe 5 is an even better default. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
"MauMau" 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 operation produces the problem. 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 to set PostgreSQL. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Oct 11, 2013 10:23 PM, "Josh Berkus" 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? > > * change the default for temp_buffers? > > If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit > could also use a bump; those thresholds were set for servers with < 1GB > of RAM Uh, those are there to limit io and not memory, right? More memory isn't the reason to increase them, more io is. For people deploying on modern server hardware then yes it's often low, but for all those deploying in virtualized environments with io performance reminding you of the 1990ies, I'm not so sure it is... /Magnus
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Oct 12, 2013 2:13 AM, "MauMau" wrote: > > From: "Bruce Momjian" >> >> 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 application server and notice that they have to set >>> max_prepared_transactions and restart PostgreSQL. This is far from >>> friendly. >> >> >> I think the problem is that many users don't need prepared transactions >> and therefore don't want the overhead. Is that still accurate? > > > 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 max_prepared_transactions", so many users might possibly have been able to change the setting and solve the problem themselves without asking for help, feeling stress like "Why do I have to set this?" For example, max_prepared_transactions is called "hideous creature" in the following page: > > https://community.jboss.org/wiki/InstallPostgreSQLOnFedora?_sscc=t Anybody who follows that page is screwed anyway. I notice they recommend running regular VACUUM FULL across the whole database, so it's obvious they know nothing about postgresql. There's nothing we can do about what people write on random pages around the Internet. > According to the below page, the amount of memory consumed for this is "(770 + 270 * max_locks_per_transaction) * max_prepared_transactions". With the default setting of maxconnections=100 and max_locks_per_transaction=64, this is only 180KB. So the overhead is negligible. You are assuming memory is the only overhead. I don't think it is. > If the goal is to make PostgreSQL more friendly and run smoothly without frustration from the start and not perfect tuning, I think max_prepared_transactions=max_connections is an easy and good item. If the goal is limited to auto-tuning memory sizes, this improvement can be treated separately. 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. /Magnus
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
From: "Dimitri Fontaine" "MauMau" 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 will prevent VACUUM and quickly lead you to an interesting situation. 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 operation produces the problem. Just setting max_prepared_transactions to non-zero doesn't cause the problem. Is this correct? Regards MauMau -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
From: "Bruce Momjian" 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 application server and notice that they have to set max_prepared_transactions and restart PostgreSQL. This is far from friendly. I think the problem is that many users don't need prepared transactions and therefore don't want the overhead. Is that still accurate? 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 max_prepared_transactions", so many users might possibly have been able to change the setting and solve the problem themselves without asking for help, feeling stress like "Why do I have to set this?" For example, max_prepared_transactions is called "hideous creature" in the following page: https://community.jboss.org/wiki/InstallPostgreSQLOnFedora?_sscc=t According to the below page, the amount of memory consumed for this is "(770 + 270 * max_locks_per_transaction) * max_prepared_transactions". With the default setting of maxconnections=100 and max_locks_per_transaction=64, this is only 180KB. So the overhead is negligible. http://www.postgresql.org/docs/9.2/static/kernel-resources.html If the goal is to make PostgreSQL more friendly and run smoothly without frustration from the start and not perfect tuning, I think max_prepared_transactions=max_connections is an easy and good item. If the goal is limited to auto-tuning memory sizes, this improvement can be treated separately. Regards MauMau -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
Josh Berkus 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? >> * change the default for temp_buffers? > > If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit > could also use a bump; those thresholds were set for servers with < 1GB > of RAM. +1 on those. Also, I have often had to bump cpu_tuple_cost into the 0.03 to 0.05 range to get a good plan. In general, this makes the exact settings of *_page_cost less fussy, and I have hit situations where I was completely unable to get a good plan to emerge without bumping cpu_tuple_cost relative to the other cpu costs. I know that it's possible to engineer a workload that shows any particular cost adjustment to make things worse, but in real-life production environments I have never seen an increase in this range make plan choice worse. Regarding the settings which have been the center of attention for most of this thread, I have had very good luck with starting work_mem at machine RAM * 0.25 / max_connections. I get the impression that Josh regards that as too low. My guess is that he deals more with data warehouse reporting systems than I do, where larger settings are both more beneficial and less likely to cause memory exhaustion than the typical systems I've worked with. That is the big problem with auto-configuration -- it depends so much on the workload. In the long run, an admission control policy and/or adaptive configuration based on the observed workload seems like what we *really* need. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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? If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit could also use a bump; those thresholds were set for servers with < 1GB of RAM. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 whole lot of code and formulas in order to end up at a figure only > >> incrementally different from a new static default. > > > > Agreed. But what do you think the value SHOULD be on such a system? > > That's the problem: It Depends. > > One thing in particular which is an issue with calculating against > max_connections is that users who don't need 100 connections seldom > *reduce* max_connections. So that developer laptop which only needs 3 > connections is still going to have a max_connections of 100, just like > the DW server where m_c should probably be 30. > > > I guess the point I'm making here is that raising the default value is > > not mutually exclusive with auto-tuning. We could quadruple the > > current defaults for work_mem and maintenance_work_mem and be better > > off right now, today. Then, we could improve things further in the > > future if and when we agree on an approach to auto-tuning. And people > > who don't use the auto-tuning will still have a better default. > > Seems fine to me. I think we are nearing a conclusion on these issues, and I thank everyone for the vigorous discussion. When Josh showed disappointment at the small increases in work_mem and maintenance_work_mem from autotuning, I realized the complexity of autotuning just wasn't warranted here. Andrew's concern about the risks of having a work_mem too high was also sobering. Effective_cache_size has neither of these issues, and hence was logical for auto-tuning. I know Robert originally suggested just improving the work_mem default --- I now agree with him, and am sorry it took me so long to realize he was right. One other problem with auto-tuning is that it really relies not only on allocated_memory, but also on max_connections and autovacuum_max_workers, which are going to be rather arbitrary and hard for a user to set good enough to help auto-tuning. Josh might be right that auto-tuning of work_mem has to be more dynamic, perhaps based on the number of _active_ backends or number of backends who have allocate or are currently using work_mem. Our new dynamic shared memory allocation routines might help here in allocationg memory that can be easily purged from the process address space. I am now seeing a pattern that per-backend allocations really need run-time tuning, rather than being based on fixed GUC values. 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? I will try to think some more about work_mem dynamic/runtime tuning and return to it later. I know Kevin has also thought about it. I am also interesting in working on a server-side function that will make configuration suggestions or use ALTER SYSTEM to set values. I could do it in PL/pgSQL, but PL/Perl would allow me to run operating system commands to probe for OS information. The function could look at statistics and pg_buffercache output, and would be run during a typical workload. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 9:41 PM, Christopher Browne wrote: > On Thu, Oct 10, 2013 at 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. > > I should think it possible to reimplement it in C. It was considerably > useful to start by implementing in Python, as that evades various sorts > of efforts needed in C (e.g. - memory allocation, picking a hash table > implementation), and allows someone to hack on it without needing to > run through a recompile every time something is touched. I think in the context of this problem, reimplementing that from python to C is the easiest part. Actually figuring out what the tool should *do* and how it should do it is the hard part. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 >> incrementally different from a new static default. > > Agreed. But what do you think the value SHOULD be on such a system? That's the problem: It Depends. One thing in particular which is an issue with calculating against max_connections is that users who don't need 100 connections seldom *reduce* max_connections. So that developer laptop which only needs 3 connections is still going to have a max_connections of 100, just like the DW server where m_c should probably be 30. > I guess the point I'm making here is that raising the default value is > not mutually exclusive with auto-tuning. We could quadruple the > current defaults for work_mem and maintenance_work_mem and be better > off right now, today. Then, we could improve things further in the > future if and when we agree on an approach to auto-tuning. And people > who don't use the auto-tuning will still have a better default. Seems fine to me. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 6:27 PM, 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 the end goal of this activity looks like. > > 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 > incrementally different from a new static default. Agreed. But what do you think the value SHOULD be on such a system? I suggest that it's pretty reasonable to assume that even a developer's personal machine will likely have 8GB or so by the time PostgreSQL comes out, so tuning work_mem on that basis is not unreasonable. Plus, even if it has less, a developer probably won't have 100 connections. I guess the point I'm making here is that raising the default value is not mutually exclusive with auto-tuning. We could quadruple the current defaults for work_mem and maintenance_work_mem and be better off right now, today. Then, we could improve things further in the future if and when we agree on an approach to auto-tuning. And people who don't use the auto-tuning will still have a better default. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 6:36 PM, Bruce Momjian 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 votes in favor of that option. Judging by the commit I just pushed to do initdb-time selection of the dynamic shared memory implementation to use, this is probably not hard to code. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On 10/10/13 9:44 AM, MauMau wrote:
From: "Robert Haas"
On Thu, Oct 10, 2013 at 1:23 AM, Magnus Hagander 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 suggesting, and write new defaults to the
files (preferably with a comment).
That way if the user *later* comes in and say changes shared_buffers,
we don't dynamically resize the work_mem into a value that might cause
his machine to die from swapping which would definitely violate the
principle of least surprise..
+1 for all of that. I completely agree.
I vote for this idea completely, too. It's nice to be able to specify usable RAM with
something like "initdb --system-memory 8GB", because it provides flexibility
for memory allocation --- use the whole machine for one PostgreSQL instance, or run
multiple instances on one machine with 50% of RAM for instance-A and 25% of RAM for
instance B and C, etc. But what is the default value of --system-memory? I would like
it to be the whole RAM.
I hope something like pgtune will be incorporated into the core, absorbing the
ideas in:
- pgtune
- https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
- the book "PostgreSQL 9.0 High Performance" by Greg Smith
Then initdb calls the tool. Of course, DBAs can use the tool later. Like pgtune, the tool would
be nice if it and initdb can accept "--system-type" or "--workload" with
arguments {OLTP | DW | mixed}.
+1 on all of the above. If putting one-time magic in initdb works maybe then we
can look at runtime or even completely dynamic magic.
FWIW, I would be careful about allowing the tool to go completely crazy if
--system-memory is set really high, including for things like work_mem.
Frequently if you've got a lot of memory you're going to want a serious chunk
of it used by the filesystem/kernel cache, and not to just vanish into a random
sort (esp since last I knew there were diminishing returns on sort work_mem...)
Of course, I'm in a world of 512G servers with 8GB shared buffers so...
--
Jim C. Nasby, Data Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 the end goal of this activity looks like. > > 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 > incrementally different from a new static default. Well, the plan was going to auto-tune shared_buffers and effective_cache_size too. We could fall back to our existing code where effective_cache_size autotunes on shared_buffers, and we just up work_mem's default, tell people to set shared_buffers properly, and call it a day. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 using work_mem, they would have that memory > > mapped into the process, and perhaps swapped out. > > Yes, and then this is when it *really* matters what OS you're running, > and what release. FreeBSD and Solaris++ don't overallocate RAM, so > those long-running connections pin a lot of RAM eventually. And for > Linux, it's a question of how aggressive the OOM killer is, which kinda > depends on distro/version/sysadmin settings. > > When I configure pgbouncer for Illumos users, I specifically have it > rotate out old connections once an hour for this reason. Just as a point of education, this is a good idea why you want to allocate swap even if you expect your workload to fit in memory. Pushing unused memory to swap is a good use of swap. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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.
>
> I think this is a promising idea. available_mem could even be set
> automatically by packages. And power users could just set available_mem
> = -1 to turn off all the magic.
Yes, I was thinking about that. Imagine we have an initdb parameter
for available memory --- packagers could do something like:
initdb -M $(awk '{print $2 * 1024; exit}' /proc/meminfo)
to pass in the available memory of the server, or to use 90% of RAM,
use:
initdb -M $(awk '{printf "%.0f\n", $2 * 1024 * 0.9; exit}'
/proc/meminfo)
This allows us to externalize all the OS-specific information and allow
the packagers to supply it. The packagers could even ask the user if
they wish to control the percentage.
FYI, I hope people are OK with me replying a lot in this thread --- I do
think this is going to take a lot of discussion, but I think the
end-result will be worth it.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
>> 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, and perhaps swapped out. Yes, and then this is when it *really* matters what OS you're running, and what release. FreeBSD and Solaris++ don't overallocate RAM, so those long-running connections pin a lot of RAM eventually. And for Linux, it's a question of how aggressive the OOM killer is, which kinda depends on distro/version/sysadmin settings. When I configure pgbouncer for Illumos users, I specifically have it rotate out old connections once an hour for this reason. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 6MB gives us 600MB, and if each backend uses > > 3x work_mem, that gives us 1.8GB for total work_mem. This was based on > > Andrew's concerns about possible over-commit of work_mem. I can of > > course adjust that. > > That's worst-case-scenario planning -- the 3X work-mem per backend was: > a) Solaris and > b) data warehousing > > In a normal OLTP application each backend averages something like 0.25 * > work_mem, since many queries use no work_mem at all. > > 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? OK, here is an updated patch that is less conservative. FYI, this thread has gone on for 80 messages, and I assume it will take many more until we are done: test=> SHOW shared_buffers; shared_buffers 128MB (1 row) test=> SHOW work_mem; work_mem -- 2621kB (1 row) test=> SHOW maintenance_work_mem; maintenance_work_mem -- 10922kB (1 row) --- test=> SHOW shared_buffers; shared_buffers 2GB (1 row) test=> SHOW work_mem; work_mem -- 41943kB (1 row) test=> SHOW maintenance_work_mem; maintenance_work_mem -- 174762kB (1 row) --- test=> SHOW shared_buffers; shared_buffers 8GB (1 row) test=> SHOW work_mem; work_mem -- 167772kB (1 row) test=> SHOW maintenance_work_mem; maintenance_work_mem -- 699050kB (1 row) Patch attached. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml new file mode 100644 index e8e8e6f..2f00c74 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** include 'filename' *** 1121,1127 Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value ! defaults to one megabyte (1MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary --- 1121,1128 Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value ! defaults to 2 * shared_buffers / ! max_connections. Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary *** include 'filename' *** 1147,1153 Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults ! to 16 megabytes (16MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger --- 1148,1155 Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults ! to shared_buffers / 4 / ! autovacuum_max_workers. Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c new file mode 100644 index 33efb3c..68af1dc *** a/sr
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 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, and perhaps swapped out. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
> 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 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 incrementally different from a new static default. The core issue here is that there aren't good "generic" values for these settings for all users -- that's why we have the settings in the first place. Following a formula isn't going to change that. If we're serious about autotuning, then we should look at: a) admissions control for non-shared resources (e.g. work_mem) b) auto-feedback tuning loops (ala Heikki's checkpoint_segments and the bgwriter). We could certainly create an autofeedback tuning loop for work_mem. Just watch the database, record the amount of data spilled to disk for work (pg_stat_sorts), and record the total RAM pinned by backends. *Then* apply a formula and maybe bump up work_mem a bit depending on what comes out of it. And keep monitoring and keep readjusting. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 3:41 PM, Christopher Browne wrote: > On Thu, Oct 10, 2013 at 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. > > I should think it possible to reimplement it in C. It was considerably > useful to start by implementing in Python, as that evades various sorts > of efforts needed in C (e.g. - memory allocation, picking a hash table > implementation), and allows someone to hack on it without needing to > run through a recompile every time something is touched. Also, the last time I saw that tool, it output recommendations for work_mem that I would never, ever recommend to anyone on a production server - they were VERY high. 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 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. I should think it possible to reimplement it in C. It was considerably useful to start by implementing in Python, as that evades various sorts of efforts needed in C (e.g. - memory allocation, picking a hash table implementation), and allows someone to hack on it without needing to run through a recompile every time something is touched. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 8:46 PM, Robert Haas wrote: > On Thu, Oct 10, 2013 at 2:45 PM, Josh Berkus 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 --available-memory=32GB --rewrite-config-file=$PATH >>> >>> ...but that might be overkill, at least for version 1. >> >> Given that we are talking currently about ALTER SYSTEM SET *and* >> configuration directories, we should not be rewriting any existing >> config file. We should be adding an auto-generated one, or using ALTER >> SYSTEM SET. >> >> In fact, why don't we just do this though ALTER SYSTEM SET? add a >> plpgsql function called pg_tune(). > > That's another way to do it, for sure. It does require the ability to > log in to the database. I imagine that could be less convenient in > some scripting environments. I think that would also make it much harder to automate for packagers. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 think such a tool would actually make the initial experience worse for many people. Quick, how are you going to use your PostgreSQL server: - OLTP - web - mixed Uh, all of the above? This sort of thing can quickly turn the "first 15 minutes" into the first 2 hours, as users are forced to analyze the different settings, have second thoughts, wonder about how to change them back, etc. The fewer decisions people have to make initially, the better. The initdb phase already has too many required decisions that can cause regret later (e.g., locale, encoding, checksums). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Wed, Oct 9, 2013 at 10:21 PM, Magnus Hagander 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 memory size from vacuum_work_mem. If you don't like >> vacuum_work_mem, you can just ignore it. > While unrelated to the main topic of this thread, I think this is very > important as well. I often have to advice people to remember to cap > their maintenance_work_mem because of autovacuum, and to remember to > re-tune maintenance_wokr_mem when they change the number of autovacuum > workers. I'll code that up at some point, then. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 might be overkill, at least for version 1. Given that we are talking currently about ALTER SYSTEM SET *and* configuration directories, we should not be rewriting any existing config file. We should be adding an auto-generated one, or using ALTER SYSTEM SET. In fact, why don't we just do this though ALTER SYSTEM SET? add a plpgsql function called pg_tune(). -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 8:41 PM, Robert Haas wrote: > On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian 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 require python for initdb. >>> > The tool would not address Robert's issue of someone increasing >>> > shared_buffers on their own. >>> >>> 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 they should change, or they're going to >>> move off PG because it's not performing well enough for them- which is >>> really what I'm trying to avoid happening during the first 15m. >> >> Well, they aren't going around and looking at other parameters now or we >> would not feel a need to auto-tune many of our defaults. >> >> 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 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? > > What I might propose is that we have add a new binary tunedb, maybe > compiled out of the src/bin/initdb.c directory. So you can say: > > initdb --available-memory=32GB > > ...and it will initialize the cluster with appropriate settings. Or > you can say: > > 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 might be overkill, at least for version 1. I like this. And I agree that the edit-in-place might be overkill. But then, if/when we get the ability to programatically modify the config files, that's probably not a very complicated thing to add once the rest is done. >> One nice thing about a tool is that you can see your auto-tuned defaults >> right away, while doing this in the backend, you have to start the >> server to see the defaults. I am not even sure how I could allow users >> to preview their defaults for different available_mem settings. > > Yep, agreed. And agreed that not being able to preview settings is a problem. I'd even say it would be a *big* problem. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 2:45 PM, Josh Berkus 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 --available-memory=32GB --rewrite-config-file=$PATH >> >> ...but that might be overkill, at least for version 1. > > Given that we are talking currently about ALTER SYSTEM SET *and* > configuration directories, we should not be rewriting any existing > config file. We should be adding an auto-generated one, or using ALTER > SYSTEM SET. > > In fact, why don't we just do this though ALTER SYSTEM SET? add a > plpgsql function called pg_tune(). That's another way to do it, for sure. It does require the ability to log in to the database. I imagine that could be less convenient in some scripting environments. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 automatically by packages. And power users could just set available_mem = -1 to turn off all the magic. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 11:43 AM, Robert Haas wrote: > On Thu, Oct 10, 2013 at 1:37 PM, Josh Berkus 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. That's why trying to give the responsibility to a packager is compelling. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 11:41 AM, Robert Haas 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, but as you say that's wholly incidental. An equivalent C program would only be slightly more verbose. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 1:37 PM, Josh Berkus 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 Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian 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 require python for initdb. >> > The tool would not address Robert's issue of someone increasing >> > shared_buffers on their own. >> >> 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 they should change, or they're going to >> move off PG because it's not performing well enough for them- which is >> really what I'm trying to avoid happening during the first 15m. > > Well, they aren't going around and looking at other parameters now or we > would not feel a need to auto-tune many of our defaults. > > 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 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? What I might propose is that we have add a new binary tunedb, maybe compiled out of the src/bin/initdb.c directory. So you can say: initdb --available-memory=32GB ...and it will initialize the cluster with appropriate settings. Or you can say: 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 might be overkill, at least for version 1. > One nice thing about a tool is that you can see your auto-tuned defaults > right away, while doing this in the backend, you have to start the > server to see the defaults. I am not even sure how I could allow users > to preview their defaults for different available_mem settings. Yep, agreed. And agreed that not being able to preview settings is a problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
> 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 work_mem is through an admission control mechanism. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 us 1.8GB for total work_mem. This was based on > Andrew's concerns about possible over-commit of work_mem. I can of > course adjust that. That's worst-case-scenario planning -- the 3X work-mem per backend was: a) Solaris and b) data warehousing In a normal OLTP application each backend averages something like 0.25 * work_mem, since many queries use no work_mem at all. 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? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
On Wed, Oct 9, 2013 at 8:06 AM, 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 shared_buffers. >>> >>> We can try a different algorithm to scale up work_mem, but it seems >>> wise >>> to auto-scale it up to some extent based on shared_buffers. >>> >>> >>> In my experience a optimal value of work_mem depends on data and load, >>> so I >>> prefer a work_mem as independent parameter. >>> >> But it still is an independent parameter. I am just changing the default. >> >> > The danger with work_mem especially is that setting it too high can lead > to crashing postgres or your system at some stage down the track, so > autotuning it is kinda dangerous, much more dangerous than autotuning > shared buffers. > Is this common to see? I ask because in my experience, having 100 connections all decide to do large sorts simultaneously is going to make the server fall over, regardless of whether it tries to do them in memory (OOM) or whether it does them with tape sorts (stuck spin locks, usually). > > 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 work_mem that a connection would use? Cheers, Jeff
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 they should change, or they're going to > > move off PG because it's not performing well enough for them- which is > > really what I'm trying to avoid happening during the first 15m. > > Well, they aren't going around and looking at other parameters now or we > would not feel a need to auto-tune many of our defaults. I think you're confusing things here. There's a huge difference between "didn't configure anything and got our defaults" and "went and changed only one thing in postgresql.conf". For one thing, we have a ton of the former. Perhaps there are some of the latter as well, but I would argue it's a pretty small group. > 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 agree that we can't add a Python (or really, perl) dependency, but I don't think there's anything terribly complicated in what pgtune is doing that couldn't be pretty easily done in C.. > One nice thing about a tool is that you can see your auto-tuned defaults > right away, while doing this in the backend, you have to start the > server to see the defaults. I am not even sure how I could allow users > to preview their defaults for different available_mem settings. Agreed. > > Actually, it *is* good, as Magnus pointed out. Changing a completely > > unrelated parameter shouldn't make all of your plans suddenly change. > > This is mollified, but only a bit, if you have a GUC that's explicitly > > "this changes other GUCs", but I'd much rather have a tool that can do a > > better job to begin with and which helps the user understand what > > parameters are available to change and why there's more than one. > > Well, the big question is how many users are going to use the tool, as > we are not setting this up for experts, but for novices. The goal would be to have the distros and/or initdb use it for the initial configuration.. Perhaps by using debconf or similar to ask the user, perhaps by just running it and letting it do whatever it wants. > I think one big risk is someone changing shared_buffers and not having > an accurate available_memory. That might lead to some very inaccurate > defaults. Also, what happens if available_memory is not supplied at > all? Do we auto-tune just from shared_buffers, or not autotune at all, > and then what are the defaults? We could certainly throw an error if > shared_buffers > available_memory. We might just ship with > available_memory defaulting to 256MB and auto-tune everything from > there. These questions are less of an issue if we simply don't have this "available_memory" GUC (which strikes me as just adding more confusion for users anyway, not less). If no '--available-memory' (or whatever) option is passed to initdb then we should have it assume some default, yes, but my view on what that is depends on what the specific results are. It sounds like --avail-memory=256MB would end up setting things to about what we have now for defaults, which is alright for shared_buffers, imv, but not for a default work_mem (1MB is *really* small...). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 has? I also think this is where the much-debated ALTER SYSTEM SET suddenly becomes valuable. With it, it's reasonable to run a "tune-up" tool on the client side. I do think it's reasonable to tell a user: "Just installed PostgreSQL? Run this command to tune your system:" Mind you, the tuneup tool I'm working on makes use of Python, configuration directory, and Jinga2, so it's not even relevant to the preceeding. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 > > -- > > 6010kB > > (1 row) > > Huh? Only 6MB work_mem for 8GB RAM? How'd you get that? > 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 us 1.8GB for total work_mem. This was based on Andrew's concerns about possible over-commit of work_mem. I can of course adjust that. Consider 8GB of shared memory is 21MB. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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? Only 6MB work_mem for 8GB RAM? How'd you get that? 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. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
> 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 maint_work_mem if set. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 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 in Python should be doable in > >>Perl fairly simply. I don't think we should be adding any Python > >>dependencies. For good or ill Perl has been used for pretty much all > >>our complex scripting (pgindent, MSVC build system etc.) > >Yes, but this is a run-time requirement, not build-time, and we have not > >used Perl in that regard. > > > > > Nor Python. If we want to avoid added dependencies, we would need to use C. Yeah. :-( My crazy idea would be, because setting setting available_mem without a restart would not be supported, to allow the backend to output suggestions, e.g.: test=> SHOW available_mem = '24GB'; Auto-tuning values: shared_buffers = 6GB work_mem = 10MB ... ERROR: parameter "available_mem" cannot be changed without restarting the server -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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. Without considering any wider question here, let me just note this: Anything that can be done in this area in Python should be doable in Perl fairly simply. I don't think we should be adding any Python dependencies. For good or ill Perl has been used for pretty much all our complex scripting (pgindent, MSVC build system etc.) Yes, but this is a run-time requirement, not build-time, and we have not used Perl in that regard. Nor Python. If we want to avoid added dependencies, we would need to use C. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 here, let me just note this: > > Anything that can be done in this area in Python should be doable in > Perl fairly simply. I don't think we should be adding any Python > dependencies. For good or ill Perl has been used for pretty much all > our complex scripting (pgindent, MSVC build system etc.) Yes, but this is a run-time requirement, not build-time, and we have not used Perl in that regard. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 in Python should be doable in Perl fairly simply. I don't think we should be adding any Python dependencies. For good or ill Perl has been used for pretty much all our complex scripting (pgindent, MSVC build system etc.) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 someone increasing > > shared_buffers on their own. > > 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 they should change, or they're going to > move off PG because it's not performing well enough for them- which is > really what I'm trying to avoid happening during the first 15m. Well, they aren't going around and looking at other parameters now or we would not feel a need to auto-tune many of our defaults. 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. One nice thing about a tool is that you can see your auto-tuned defaults right away, while doing this in the backend, you have to start the server to see the defaults. I am not even sure how I could allow users to preview their defaults for different available_mem settings. > > In fact, the other constants would still > > be hard-coded in from initdb, which isn't good. > > Actually, it *is* good, as Magnus pointed out. Changing a completely > unrelated parameter shouldn't make all of your plans suddenly change. > This is mollified, but only a bit, if you have a GUC that's explicitly > "this changes other GUCs", but I'd much rather have a tool that can do a > better job to begin with and which helps the user understand what > parameters are available to change and why there's more than one. Well, the big question is how many users are going to use the tool, as we are not setting this up for experts, but for novices. I think one big risk is someone changing shared_buffers and not having an accurate available_memory. That might lead to some very inaccurate defaults. Also, what happens if available_memory is not supplied at all? Do we auto-tune just from shared_buffers, or not autotune at all, and then what are the defaults? We could certainly throw an error if shared_buffers > available_memory. We might just ship with available_memory defaulting to 256MB and auto-tune everything from there. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
* 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 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 they should change, or they're going to move off PG because it's not performing well enough for them- which is really what I'm trying to avoid happening during the first 15m. > In fact, the other constants would still > be hard-coded in from initdb, which isn't good. Actually, it *is* good, as Magnus pointed out. Changing a completely unrelated parameter shouldn't make all of your plans suddenly change. This is mollified, but only a bit, if you have a GUC that's explicitly "this changes other GUCs", but I'd much rather have a tool that can do a better job to begin with and which helps the user understand what parameters are available to change and why there's more than one. > 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. Agreed. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
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 command they can run > > > with different memory settings to see what the auto-tuning would do. > > > Perhaps even have a way to enable use of those new variables, but I > > > don't really care for the idea of making a GUC that isn't anything > > > except a control for defaults of *other* GUCs. > > > > Well, you then have two places you are doing the tuning --- one in > > initdb, and another in the tool, and you can have cases where they are > > not consistent. You could have a mode where initdb re-writes > > postgresql.conf, but that has all sorts of oddities about changing a > > config file. > > Not necessairly.. Have initdb call the tool to get the values to use > when first writing out the config file (or make the logic into a library > that initdb and the tool both use, or just both #include the same .h; > it's not like it's going to be terribly complicated), and then the tool > would be responsible for later changes to the postgresql.conf file, or > we just tell the user how to make the changes recommended by the tool. > I would *not* have initdb doing that, that's not its job. > > If the user is expected to be modifying the postgresql.conf file in this > scenario anyway, I hardly see that having one-parameter-to-rule-them-all > is actually better than having 3 or 4. If we're trying to get away from > the user modifying postgresql.conf, then we're going to need a tool to > do that (or use ALTER SYSTEM WHATEVER). For my part, I'm really much > more interested in the "first 15 minutes", as was mentioned elsewhere, > than how to help users who have been using PG for a year and then > discover they need to tune it a bit. 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. In fact, the other constants would still be hard-coded in from initdb, which isn't good. 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. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
