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 <br...@momjian.us> 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 **** <para> 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 (<literal>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 ---- <para> 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 (<literal>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 <command>VACUUM</command>, <command>CREATE INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults ! to 16 megabytes (<literal>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 <command>VACUUM</command>, <command>CREATE INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults ! to 64 megabytes (<literal>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.sample *************** *** 124,131 **** # per transaction slot, plus lock space (see max_locks_per_transaction). # It is not advisable to set max_prepared_transactions nonzero unless you # actively intend to use prepared transactions. ! #work_mem = 1MB # min 64kB ! #maintenance_work_mem = 16MB # min 1MB #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem #max_stack_depth = 2MB # min 100kB #dynamic_shared_memory_type = posix # the default is the first option --- 124,131 ---- # per transaction slot, plus lock space (see max_locks_per_transaction). # It is not advisable to set max_prepared_transactions nonzero unless you # actively intend to use prepared transactions. ! #work_mem = 4MB # min 64kB ! #maintenance_work_mem = 64MB # min 1MB #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem #max_stack_depth = 2MB # min 100kB #dynamic_shared_memory_type = posix # the default is the first option
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers