Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit391c381

Browse files
committed
Rename SortMem and VacuumMem to work_mem and maintenance_work_mem.
Make btree index creation and initial validation of foreign-key constraintsuse 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 theirold names in SHOW and SET commands, for backwards compatibility.
1 parent39d715b commit391c381

34 files changed

+269
-188
lines changed

‎contrib/tablefunc/tablefunc.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -865,7 +865,7 @@ get_crosstab_tuplestore(char *sql,
865865
MemoryContextSPIcontext;
866866

867867
/* initialize our tuplestore */
868-
tupstore=tuplestore_begin_heap(true, false,SortMem);
868+
tupstore=tuplestore_begin_heap(true, false,work_mem);
869869

870870
/* Connect to SPI manager */
871871
if ((ret=SPI_connect())<0)
@@ -1246,7 +1246,7 @@ connectby(char *relname,
12461246
oldcontext=MemoryContextSwitchTo(per_query_ctx);
12471247

12481248
/* initialize our tuplestore */
1249-
tupstore=tuplestore_begin_heap(true, false,SortMem);
1249+
tupstore=tuplestore_begin_heap(true, false,work_mem);
12501250

12511251
MemoryContextSwitchTo(oldcontext);
12521252

‎doc/src/sgml/backup.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.34 2004/01/19 20:12:30 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.35 2004/02/03 17:34:02 tgl Exp $
33
-->
44
<chapter id="backup">
55
<title>Backup and Restore</title>
@@ -156,8 +156,8 @@ pg_dump -h <replaceable>host1</> <replaceable>dbname</> | psql -h <replaceable>h
156156
<tip>
157157
<para>
158158
Restore performance can be improved by increasing the
159-
configuration parameter <varname>sort_mem</varname> (see <xref
160-
linkend="runtime-config-resource-memory">).
159+
configuration parameter <varname>maintenance_work_mem</varname>
160+
(see <xreflinkend="runtime-config-resource-memory">).
161161
</para>
162162
</tip>
163163
</sect2>

‎doc/src/sgml/installation.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.193 2004/01/19 21:20:06 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.194 2004/02/03 17:34:02 tgl Exp $ -->
22

33
<chapter id="installation">
44
<title><![%standalone-include[<productname>PostgreSQL</>]]>
@@ -1399,7 +1399,7 @@ kill `cat /usr/local/pgsql/data/postmaster.pid`
13991399
not designed for optimum performance. To achieve optimum
14001400
performance, several server parameters must be adjusted, the two
14011401
most common being <varname>shared_buffers</varname> and
1402-
<varname> sort_mem</varname> mentioned in the documentation.
1402+
<varname>work_mem</varname>.
14031403
Other parameters mentioned in the documentation also affect
14041404
performance.
14051405
</para>

‎doc/src/sgml/perform.sgml

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.40 2004/01/11 05:46:58 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.41 2004/02/03 17:34:02 tgl Exp $
33
-->
44

55
<chapter id="performance-tips">
@@ -684,16 +684,18 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
684684
</para>
685685
</sect2>
686686

687-
<sect2 id="populate-sort-mem">
688-
<title>Increase <varname>sort_mem</varname></title>
687+
<sect2 id="populate-work-mem">
688+
<title>Increase <varname>maintenance_work_mem</varname></title>
689689

690690
<para>
691-
Temporarily increasing the <varname>sort_mem</varname>
691+
Temporarily increasing the <varname>maintenance_work_mem</varname>
692692
configuration variable when restoring large amounts of data can
693693
lead to improved performance. This is because when a B-tree index
694694
is created from scratch, the existing content of the table needs
695-
to be sorted. Allowing the merge sort to use more buffer pages
696-
means that fewer merge passes will be required.
695+
to be sorted. Allowing the merge sort to use more memory
696+
means that fewer merge passes will be required. A larger setting for
697+
<varname>maintenance_work_mem</varname> may also speed up validation
698+
of foreign-key constraints.
697699
</para>
698700
</sect2>
699701

‎doc/src/sgml/plpgsql.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.34 2004/01/24 22:05:08 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.35 2004/02/03 17:34:02 tgl Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -1354,7 +1354,7 @@ SELECT * FROM some_func();
13541354
allow users to define set-returning functions
13551355
that do not have this limitation. Currently, the point at
13561356
which data begins being written to disk is controlled by the
1357-
<varname>sort_mem</> configuration variable. Administrators
1357+
<varname>work_mem</> configuration variable. Administrators
13581358
who have sufficient memory to store larger result sets in
13591359
memory should consider increasing this parameter.
13601360
</para>

‎doc/src/sgml/ref/postgres-ref.sgml

Lines changed: 7 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/postgres-ref.sgml,v 1.42 2003/11/29 19:51:39 pgsql Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/postgres-ref.sgml,v 1.43 2004/02/03 17:34:02 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -41,7 +41,7 @@ PostgreSQL documentation
4141
<arg>-s</arg>
4242
<arg>-t<group choice="plain"><arg>pa</arg><arg>pl</arg><arg>ex</arg></group></arg>
4343
</group>
44-
<arg>-S <replaceable>sort-mem</replaceable></arg>
44+
<arg>-S <replaceable>work-mem</replaceable></arg>
4545
<arg>-W <replaceable>seconds</replaceable></arg>
4646
<arg>--<replaceable>name</replaceable>=<replaceable>value</replaceable></arg>
4747
<arg choice="plain"><replaceable>database</replaceable></arg>
@@ -64,7 +64,7 @@ PostgreSQL documentation
6464
<arg>-s</arg>
6565
<arg>-t<group choice="plain"><arg>pa</arg><arg>pl</arg><arg>ex</arg></group></arg>
6666
</group>
67-
<arg>-S <replaceable>sort-mem</replaceable></arg>
67+
<arg>-S <replaceable>work-mem</replaceable></arg>
6868
<arg>-v <replaceable>protocol</replaceable></arg>
6969
<arg>-W <replaceable>seconds</replaceable></arg>
7070
<arg>--<replaceable>name</replaceable>=<replaceable>value</replaceable></arg>
@@ -197,16 +197,13 @@ PostgreSQL documentation
197197
</varlistentry>
198198

199199
<varlistentry>
200-
<term><option>-S</option> <replaceable class="parameter">sort-mem</replaceable></term>
200+
<term><option>-S</option> <replaceable class="parameter">work-mem</replaceable></term>
201201
<listitem>
202202
<para>
203203
Specifies the amount of memory to be used by internal sorts and hashes
204-
before resorting to temporary disk files. The value is specified in
205-
kilobytes, and defaults to 1024. Note that for a complex query,
206-
several sorts and/or hashes might be running in parallel, and each one
207-
will be allowed to use as much as
208-
<replaceable class="parameter">sort-mem</replaceable> kilobytes
209-
before it starts to put data into temporary files.
204+
before resorting to temporary disk files. See the description of the
205+
<varname>work_mem</> configuration parameter in <xref
206+
linkend="runtime-config-resource-memory">.
210207
</para>
211208
</listitem>
212209
</varlistentry>

‎doc/src/sgml/ref/postmaster.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/postmaster.sgml,v 1.44 2003/12/14 00:15:03 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/postmaster.sgml,v 1.45 2004/02/03 17:34:02 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -541,10 +541,10 @@ PostgreSQL documentation
541541
<para>
542542
Named run-time parameters can be set in either of these styles:
543543
<screen>
544-
<prompt>$</prompt> <userinput>postmaster -csort_mem=1234</userinput>
545-
<prompt>$</prompt> <userinput>postmaster --sort-mem=1234</userinput>
544+
<prompt>$</prompt> <userinput>postmaster -cwork_mem=1234</userinput>
545+
<prompt>$</prompt> <userinput>postmaster --work-mem=1234</userinput>
546546
</screen>
547-
Either form overrides whatever setting might exist for <varname>sort_mem</>
547+
Either form overrides whatever setting might exist for <varname>work_mem</>
548548
in <filename>postgresql.conf</>. Notice that underscores in parameter
549549
names can be written as either underscore or dash on the command line.
550550
</para>

‎doc/src/sgml/runtime.sgml

Lines changed: 44 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.235 2004/01/27 16:51:43 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.236 2004/02/03 17:34:02 tgl Exp $
33
-->
44

55
<Chapter Id="runtime">
@@ -850,37 +850,41 @@ SET ENABLE_SEQSCAN TO OFF;
850850
</varlistentry>
851851

852852
<varlistentry>
853-
<term><varname>sort_mem</varname> (<type>integer</type>)</term>
853+
<term><varname>work_mem</varname> (<type>integer</type>)</term>
854854
<listitem>
855855
<para>
856-
Specifies the amount of memory to be used by internal sort operations and
857-
hash tables before switching to temporary disk files. The value is
856+
Specifies the amount of memory to be used by internal sort operations
857+
andhash tables before switching to temporary disk files. The value is
858858
specified in kilobytes, and defaults to 1024 kilobytes (1 MB).
859859
Note that for a complex query, several sort or hash operations might be
860860
running in parallel; each one will be allowed to use as much memory
861861
as this value specifies before it starts to put data into temporary
862-
files. Also, several running sessions could be doing
863-
sort operations simultaneously. So the total memory used could be many
864-
times the value of <varname>sort_mem</varname>. Sort operations are used
865-
by <literal>ORDER BY</>, merge joins, and <command>CREATE INDEX</>.
862+
files. Also, several running sessions could be doing such operations
863+
concurrently. So the total memory used could be many
864+
times the value of <varname>work_mem</varname>; it is necessary to
865+
keep this fact in mind when choosing the value. Sort operations are
866+
used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
867+
merge joins.
866868
Hash tables are used in hash joins, hash-based aggregation, and
867-
hash-based processing of <literal>IN</> subqueries. Because
868-
<command>CREATE INDEX</> is used when restoring a database,
869-
increasing <varname>sort_mem</varname> before doing a large
870-
restore operation can improve performance.
869+
hash-based processing of <literal>IN</> subqueries.
871870
</para>
872871
</listitem>
873872
</varlistentry>
874873

875874
<varlistentry>
876-
<term><varname>vacuum_mem</varname> (<type>integer</type>)</term>
875+
<term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
877876
<listitem>
878877
<para>
879-
Specifies the maximum amount of memory to be used by
880-
<command>VACUUM</command> to keep track of to-be-reclaimed
881-
rows. The value is specified in kilobytes, and defaults to
882-
8192 kB. Larger settings may improve the speed of
883-
vacuuming large tables that have many deleted rows.
878+
Specifies the maximum amount of memory to be used in maintenance
879+
operations, such as <command>VACUUM</command>, <command>CREATE
880+
INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>.
881+
The value is specified in kilobytes, and defaults to 16384 kilobytes
882+
(16 MB). Since only one of these operations can be executed at
883+
a time by a database session, and an installation normally doesn't
884+
have very many of them happening concurrently, it's safe to set this
885+
value significantly larger than <varname>work_mem</varname>. Larger
886+
settings may improve performance for vacuuming and for restoring
887+
database dumps.
884888
</para>
885889
</listitem>
886890
</varlistentry>
@@ -1412,25 +1416,25 @@ SET ENABLE_SEQSCAN TO OFF;
14121416
<para>
14131417
Various tuning parameters for the genetic query optimization
14141418
algorithm. The recommended one to modify is
1415-
<varname>geqo_effort</varname>, which can range from 1 to 10 with
1416-
a default of 5. Larger values increase the time spent in planning
1417-
but make it more likely that a good plan will be found.
1418-
<varname>geqo_effort</varname> doesn't actually do anything directly,
1419-
it is just used to compute the default values for the other
1420-
parameters. If you prefer, you can set the other parameters by hand
1421-
instead.
1422-
The pool size is the number of individuals in the genetic population.
1423-
It must be at least two, and useful values are typically 100 to 1000.
1424-
If it is set to zero (the default setting) then a suitable default
1425-
is chosen based on <varname>geqo_effort</varname> and the number of
1426-
tables in the query.
1427-
Generations specifies the number of iterations of the algorithm.
1428-
It must be at least one, and useful values are in the same range
1429-
as the pool size.
1430-
If it is set to zero (the default setting) then a suitable default
1431-
is chosen based on the pool size.
1432-
The run time of the algorithm is roughly proportional to the sum of
1433-
pool size and generations.
1419+
<varname>geqo_effort</varname>, which can range from 1 to 10 with
1420+
a default of 5. Larger values increase the time spent in planning
1421+
but make it more likely that a good plan will be found.
1422+
<varname>geqo_effort</varname> doesn't actually do anything directly,
1423+
it is just used to compute the default values for the other
1424+
parameters. If you prefer, you can set the other parameters by hand
1425+
instead.
1426+
The pool size is the number of individuals in the genetic population.
1427+
It must be at least two, and useful values are typically 100 to 1000.
1428+
If it is set to zero (the default setting) then a suitable default
1429+
is chosen based on <varname>geqo_effort</varname> and the number of
1430+
tables in the query.
1431+
Generations specifies the number of iterations of the algorithm.
1432+
It must be at least one, and useful values are in the same range
1433+
as the pool size.
1434+
If it is set to zero (the default setting) then a suitable default
1435+
is chosen based on the pool size.
1436+
The run time of the algorithm is roughly proportional to the sum of
1437+
pool size and generations.
14341438
The selection bias is the selective pressure within the
14351439
population. Values can be from 1.50 to 2.00; the latter is the
14361440
default.
@@ -2840,7 +2844,7 @@ $ <userinput>postmaster -o '-S 1024 -s'</userinput>
28402844
<row>
28412845
<entry><option>-S <replaceable>x</replaceable></option><footnoteref linkend="fn.runtime-config-short">
28422846
</entry>
2843-
<entry><literal>sort_mem = <replaceable>x</replaceable></></entry>
2847+
<entry><literal>work_mem = <replaceable>x</replaceable></></entry>
28442848
</row>
28452849

28462850
<row>
@@ -3230,7 +3234,7 @@ kernel.shmmax = 134217728
32303234
<para>
32313235
In OS X 10.2 and earlier, edit the file
32323236
<filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>
3233-
and change the values in the following commands:
3237+
and change the values in the following commands:
32343238
<programlisting>
32353239
sysctl -w kern.sysv.shmmax
32363240
sysctl -w kern.sysv.shmmin
@@ -3239,7 +3243,7 @@ sysctl -w kern.sysv.shmseg
32393243
sysctl -w kern.sysv.shmall
32403244
</programlisting>
32413245
In OS X 10.3, these commands have been moved to <filename>/etc/rc</>
3242-
and must be edited there.
3246+
and must be edited there.
32433247
</para>
32443248
</listitem>
32453249
</varlistentry>

‎src/backend/access/nbtree/nbtree.c

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
* Portions Copyright (c) 1994, Regents of the University of California
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.109 2004/01/07 18:56:24 neilc Exp $
15+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.110 2004/02/03 17:34:02 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -117,13 +117,14 @@ btbuild(PG_FUNCTION_ARGS)
117117

118118
if (buildstate.usefast)
119119
{
120-
buildstate.spool=_bt_spoolinit(index,indexInfo->ii_Unique);
120+
buildstate.spool=_bt_spoolinit(index,indexInfo->ii_Unique, false);
121121

122122
/*
123-
* Different from spool, the uniqueness isn't checked for spool2.
123+
* If building a unique index, put dead tuples in a second spool
124+
* to keep them out of the uniqueness check.
124125
*/
125126
if (indexInfo->ii_Unique)
126-
buildstate.spool2=_bt_spoolinit(index, false);
127+
buildstate.spool2=_bt_spoolinit(index, false, true);
127128
}
128129

129130
/* do the heap scan */

‎src/backend/access/nbtree/nbtsort.c

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -36,7 +36,7 @@
3636
* Portions Copyright (c) 1994, Regents of the University of California
3737
*
3838
* IDENTIFICATION
39-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.80 2004/01/07 18:56:24 neilc Exp $
39+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.81 2004/02/03 17:34:02 tgl Exp $
4040
*
4141
*-------------------------------------------------------------------------
4242
*/
@@ -112,14 +112,25 @@ static void _bt_load(Relation index, BTSpool *btspool, BTSpool *btspool2);
112112
* create and initialize a spool structure
113113
*/
114114
BTSpool*
115-
_bt_spoolinit(Relationindex,boolisunique)
115+
_bt_spoolinit(Relationindex,boolisunique,boolisdead)
116116
{
117117
BTSpool*btspool= (BTSpool*)palloc0(sizeof(BTSpool));
118+
intbtKbytes;
118119

119120
btspool->index=index;
120121
btspool->isunique=isunique;
121122

122-
btspool->sortstate=tuplesort_begin_index(index,isunique, false);
123+
/*
124+
* We size the sort area as maintenance_work_mem rather than work_mem to
125+
* speed index creation. This should be OK since a single backend can't
126+
* run multiple index creations in parallel. Note that creation of a
127+
* unique index actually requires two BTSpool objects. We expect that the
128+
* second one (for dead tuples) won't get very full, so we give it only
129+
* work_mem.
130+
*/
131+
btKbytes=isdead ?work_mem :maintenance_work_mem;
132+
btspool->sortstate=tuplesort_begin_index(index,isunique,
133+
btKbytes, false);
123134

124135
/*
125136
* Currently, tuplesort provides sort functions on IndexTuples. If we

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp