From 391c3811a2b7f4cd666e1b4f35534046a862abbb Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 3 Feb 2004 17:34:04 +0000 Subject: Rename SortMem and VacuumMem to work_mem and maintenance_work_mem. Make btree index creation and initial validation of foreign-key constraints use maintenance_work_mem rather than work_mem as their memory limit. Add some code to guc.c to allow these variables to be referenced by their old names in SHOW and SET commands, for backwards compatibility. --- doc/src/sgml/backup.sgml | 6 +-- doc/src/sgml/installation.sgml | 4 +- doc/src/sgml/perform.sgml | 14 ++++--- doc/src/sgml/plpgsql.sgml | 4 +- doc/src/sgml/ref/postgres-ref.sgml | 17 ++++---- doc/src/sgml/ref/postmaster.sgml | 8 ++-- doc/src/sgml/runtime.sgml | 84 ++++++++++++++++++++------------------ 7 files changed, 70 insertions(+), 67 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index 864fb37f982..c235cf9e4f5 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -1,5 +1,5 @@ Backup and Restore @@ -156,8 +156,8 @@ pg_dump -h host1 dbname | psql -h h Restore performance can be improved by increasing the - configuration parameter sort_mem (see ). + configuration parameter maintenance_work_mem + (see ). diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml index 368d82e3cbf..6cfe75aeae0 100644 --- a/doc/src/sgml/installation.sgml +++ b/doc/src/sgml/installation.sgml @@ -1,4 +1,4 @@ - + <![%standalone-include[<productname>PostgreSQL</>]]> @@ -1399,7 +1399,7 @@ kill `cat /usr/local/pgsql/data/postmaster.pid` not designed for optimum performance. To achieve optimum performance, several server parameters must be adjusted, the two most common being <varname>shared_buffers</varname> and - <varname> sort_mem</varname> mentioned in the documentation. + <varname>work_mem</varname>. Other parameters mentioned in the documentation also affect performance. </para> diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index 923612c290c..c7d947eaf7a 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.40 2004/01/11 05:46:58 neilc Exp $ +$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.41 2004/02/03 17:34:02 tgl Exp $ --> <chapter id="performance-tips"> @@ -684,16 +684,18 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; </para> </sect2> - <sect2 id="populate-sort-mem"> - <title>Increase <varname>sort_mem</varname> + + Increase <varname>maintenance_work_mem</varname> - Temporarily increasing the sort_mem + Temporarily increasing the maintenance_work_mem configuration variable when restoring large amounts of data can lead to improved performance. This is because when a B-tree index is created from scratch, the existing content of the table needs - to be sorted. Allowing the merge sort to use more buffer pages - means that fewer merge passes will be required. + to be sorted. Allowing the merge sort to use more memory + means that fewer merge passes will be required. A larger setting for + maintenance_work_mem may also speed up validation + of foreign-key constraints. diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 0e7f7ad5d0a..7cce45b75e0 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -1354,7 +1354,7 @@ SELECT * FROM some_func(); allow users to define set-returning functions that do not have this limitation. Currently, the point at which data begins being written to disk is controlled by the - sort_mem configuration variable. Administrators + work_mem configuration variable. Administrators who have sufficient memory to store larger result sets in memory should consider increasing this parameter. diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml index 3ae6003120b..cfb16a78686 100644 --- a/doc/src/sgml/ref/postgres-ref.sgml +++ b/doc/src/sgml/ref/postgres-ref.sgml @@ -1,5 +1,5 @@ @@ -41,7 +41,7 @@ PostgreSQL documentation -s -tpaplex - -S sort-mem + -S work-mem -W seconds --name=value database @@ -64,7 +64,7 @@ PostgreSQL documentation -s -tpaplex - -S sort-mem + -S work-mem -v protocol -W seconds --name=value @@ -197,16 +197,13 @@ PostgreSQL documentation - sort-mem + work-mem Specifies the amount of memory to be used by internal sorts and hashes - before resorting to temporary disk files. The value is specified in - kilobytes, and defaults to 1024. Note that for a complex query, - several sorts and/or hashes might be running in parallel, and each one - will be allowed to use as much as - sort-mem kilobytes - before it starts to put data into temporary files. + before resorting to temporary disk files. See the description of the + work_mem configuration parameter in . diff --git a/doc/src/sgml/ref/postmaster.sgml b/doc/src/sgml/ref/postmaster.sgml index 6623b247de7..d829a7e2d11 100644 --- a/doc/src/sgml/ref/postmaster.sgml +++ b/doc/src/sgml/ref/postmaster.sgml @@ -1,5 +1,5 @@ @@ -541,10 +541,10 @@ PostgreSQL documentation Named run-time parameters can be set in either of these styles: -$ postmaster -c sort_mem=1234 -$ postmaster --sort-mem=1234 +$ postmaster -c work_mem=1234 +$ postmaster --work-mem=1234 - Either form overrides whatever setting might exist for sort_mem + Either form overrides whatever setting might exist for work_mem in postgresql.conf. Notice that underscores in parameter names can be written as either underscore or dash on the command line. diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index a38e6c01598..71d7f0621c8 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ @@ -850,37 +850,41 @@ SET ENABLE_SEQSCAN TO OFF; - sort_mem (integer) + work_mem (integer) - Specifies the amount of memory to be used by internal sort operations and - hash tables before switching to temporary disk files. The value is + Specifies the amount of memory to be used by internal sort operations + and hash tables before switching to temporary disk files. The value is specified in kilobytes, and defaults to 1024 kilobytes (1 MB). Note that for a complex query, several sort or hash operations might be running in parallel; each one will be allowed to use as much memory as this value specifies before it starts to put data into temporary - files. Also, several running sessions could be doing - sort operations simultaneously. So the total memory used could be many - times the value of sort_mem. Sort operations are used - by ORDER BY, merge joins, and CREATE INDEX. + files. Also, several running sessions could be doing such operations + concurrently. So the total memory used could be many + times the value of work_mem; it is necessary to + keep this fact in mind when choosing the value. Sort operations are + used for ORDER BY, DISTINCT, and + merge joins. Hash tables are used in hash joins, hash-based aggregation, and - hash-based processing of IN subqueries. Because - CREATE INDEX is used when restoring a database, - increasing sort_mem before doing a large - restore operation can improve performance. + hash-based processing of IN subqueries. - vacuum_mem (integer) + maintenance_work_mem (integer) - Specifies the maximum amount of memory to be used by - VACUUM to keep track of to-be-reclaimed - rows. The value is specified in kilobytes, and defaults to - 8192 kB. Larger settings may improve the speed of - vacuuming large tables that have many deleted rows. + Specifies the maximum amount of memory to be used in maintenance + operations, such as VACUUM, CREATE + INDEX, and ALTER TABLE ADD FOREIGN KEY. + The value is specified in kilobytes, and defaults to 16384 kilobytes + (16 MB). Since only one of these operations can be executed at + a time by a database session, and an installation normally doesn't + have very many of them happening concurrently, it's safe to set this + value significantly larger than work_mem. Larger + settings may improve performance for vacuuming and for restoring + database dumps. @@ -1412,25 +1416,25 @@ SET ENABLE_SEQSCAN TO OFF; Various tuning parameters for the genetic query optimization algorithm. The recommended one to modify is - geqo_effort, which can range from 1 to 10 with - a default of 5. Larger values increase the time spent in planning - but make it more likely that a good plan will be found. - geqo_effort doesn't actually do anything directly, - it is just used to compute the default values for the other - parameters. If you prefer, you can set the other parameters by hand - instead. - The pool size is the number of individuals in the genetic population. - It must be at least two, and useful values are typically 100 to 1000. - If it is set to zero (the default setting) then a suitable default - is chosen based on geqo_effort and the number of - tables in the query. - Generations specifies the number of iterations of the algorithm. - It must be at least one, and useful values are in the same range - as the pool size. - If it is set to zero (the default setting) then a suitable default - is chosen based on the pool size. - The run time of the algorithm is roughly proportional to the sum of - pool size and generations. + geqo_effort, which can range from 1 to 10 with + a default of 5. Larger values increase the time spent in planning + but make it more likely that a good plan will be found. + geqo_effort doesn't actually do anything directly, + it is just used to compute the default values for the other + parameters. If you prefer, you can set the other parameters by hand + instead. + The pool size is the number of individuals in the genetic population. + It must be at least two, and useful values are typically 100 to 1000. + If it is set to zero (the default setting) then a suitable default + is chosen based on geqo_effort and the number of + tables in the query. + Generations specifies the number of iterations of the algorithm. + It must be at least one, and useful values are in the same range + as the pool size. + If it is set to zero (the default setting) then a suitable default + is chosen based on the pool size. + The run time of the algorithm is roughly proportional to the sum of + pool size and generations. The selection bias is the selective pressure within the population. Values can be from 1.50 to 2.00; the latter is the default. @@ -2840,7 +2844,7 @@ $ postmaster -o '-S 1024 -s' - sort_mem = x + work_mem = x @@ -3230,7 +3234,7 @@ kernel.shmmax = 134217728 In OS X 10.2 and earlier, edit the file /System/Library/StartupItems/SystemTuning/SystemTuning - and change the values in the following commands: + and change the values in the following commands: sysctl -w kern.sysv.shmmax sysctl -w kern.sysv.shmmin @@ -3239,7 +3243,7 @@ sysctl -w kern.sysv.shmseg sysctl -w kern.sysv.shmall In OS X 10.3, these commands have been moved to /etc/rc - and must be edited there. + and must be edited there. -- cgit v1.2.3