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

Commit78ef2d3

Browse files
committed
Update documentation about shared memory sizing to reflect current
reality.
1 parent037709e commit78ef2d3

File tree

2 files changed

+143
-38
lines changed

2 files changed

+143
-38
lines changed

‎doc/src/sgml/runtime.sgml

Lines changed: 132 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.349 2005/08/29 21:38:17 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.350 2005/08/30 00:58:47 tgl Exp $
33
-->
44

55
<chapter Id="runtime">
@@ -1161,7 +1161,7 @@ SET ENABLE_SEQSCAN TO OFF;
11611161
buffers is only a buffer descriptor, or about 64 bytes, per
11621162
increment in <varname>temp_buffers</>. However if a buffer is
11631163
actually used an additional 8192 bytes will be consumed for it
1164-
(or in general <symbol>BLCKSZ</symbol> bytes).
1164+
(or in general, <symbol>BLCKSZ</symbol> bytes).
11651165
</para>
11661166
</listitem>
11671167
</varlistentry>
@@ -1183,6 +1183,14 @@ SET ENABLE_SEQSCAN TO OFF;
11831183
This option can only be set at server start.
11841184
</para>
11851185

1186+
<para>
1187+
If you are not using prepared transactions, this parameter may as
1188+
well be set to zero. If you are using them, you will probably
1189+
want <varname>max_prepared_transactions</varname> to be at least
1190+
as large as <xref linkend="guc-max-connections">, to avoid unwanted
1191+
failures at the prepare step.
1192+
</para>
1193+
11861194
<para>
11871195
Increasing this parameter may cause <productname>PostgreSQL</>
11881196
to request more <systemitem class="osname">System V</> shared
@@ -1267,6 +1275,32 @@ SET ENABLE_SEQSCAN TO OFF;
12671275
<sect3 id="runtime-config-resource-fsm">
12681276
<title>Free Space Map</title>
12691277

1278+
<indexterm>
1279+
<primary>free space map</primary>
1280+
</indexterm>
1281+
1282+
<para>
1283+
These parameters control the size of the shared <firstterm>free space
1284+
map</>, which tracks the locations of unused space in the database.
1285+
An undersized free space map may cause the database to consume
1286+
increasing amounts of disk space over time, because free space that
1287+
is not in the map cannot be re-used; instead <productname>PostgreSQL</>
1288+
will request more disk space from the operating system when it needs
1289+
to store new data.
1290+
The last few lines displayed by a database-wide <command>VACUUM VERBOSE</>
1291+
command can help in determining if the current settings are adequate.
1292+
A <literal>NOTICE</> message is also printed during such an operation
1293+
if the current settings are too low.
1294+
</para>
1295+
1296+
<para>
1297+
Increasing these parameters may cause <productname>PostgreSQL</>
1298+
to request more <systemitem class="osname">System V</> shared
1299+
memory than your operating system's default configuration
1300+
allows. See <xref linkend="sysvipc"> for information on how to
1301+
adjust those parameters, if necessary.
1302+
</para>
1303+
12701304
<variablelist>
12711305
<varlistentry id="guc-max-fsm-pages" xreflabel="max_fsm_pages">
12721306
<term><varname>max_fsm_pages</varname> (<type>integer</type>)</term>
@@ -1279,10 +1313,6 @@ SET ENABLE_SEQSCAN TO OFF;
12791313
be tracked in the shared free-space map. Six bytes of shared memory
12801314
are consumed for each page slot. This setting must be more than
12811315
16 * <varname>max_fsm_relations</varname>. The default is 20000.
1282-
The last few lines of a database-wide <command>VACUUM VERBOSE</>
1283-
can help in determining if the the default setting is suitable.
1284-
A <literal>NOTICE</> message is also printed during such an operation
1285-
if the current setting is too low.
12861316
This option can only be set at server start.
12871317
</para>
12881318
</listitem>
@@ -1297,12 +1327,8 @@ SET ENABLE_SEQSCAN TO OFF;
12971327
<para>
12981328
Sets the maximum number of relations (tables and indexes) for which
12991329
free space will be tracked in the shared free-space map. Roughly
1300-
fifty bytes of shared memory are consumed for each slot.
1330+
seventy bytes of shared memory are consumed for each slot.
13011331
The default is 1000.
1302-
The last few lines of a database-wide <command>VACUUM VERBOSE</>
1303-
can help in determining if the the default setting is suitable.
1304-
A <literal>NOTICE</> message is also printed during such an operation
1305-
if the current setting is too low.
13061332
This option can only be set at server start.
13071333
</para>
13081334
</listitem>
@@ -1804,9 +1830,18 @@ SET ENABLE_SEQSCAN TO OFF;
18041830
<para>
18051831
Number of disk-page buffers allocated in shared memory for WAL data.
18061832
The default is 8. The setting need only be large enough to hold
1807-
the amount of WAL data generated by one typical transaction.
1833+
the amount of WAL data generated by one typical transaction, since
1834+
the data is flushed to disk at every transaction commit.
18081835
This option can only be set at server start.
18091836
</para>
1837+
1838+
<para>
1839+
Increasing this parameter may cause <productname>PostgreSQL</>
1840+
to request more <systemitem class="osname">System V</> shared
1841+
memory than your operating system's default configuration
1842+
allows. See <xref linkend="sysvipc"> for information on how to
1843+
adjust those parameters, if necessary.
1844+
</para>
18101845
</listitem>
18111846
</varlistentry>
18121847

@@ -3952,9 +3987,11 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
39523987
</indexterm>
39533988
<listitem>
39543989
<para>
3955-
The shared lock table issized on the assumption that at most
3990+
The shared lock table iscreated with room to describe locks on
39563991
<varname>max_locks_per_transaction</varname> *
3957-
<varname>max_connections</varname> distinct objects will need to
3992+
(<xref linkend="guc-max-connections"> +
3993+
<xref linkend="guc-max-prepared-transactions">) objects;
3994+
hence, no more than this many distinct objects can
39583995
be locked at any one time. (Thus, this parameter's name may be
39593996
confusing: it is not a hard limit on the number of locks taken
39603997
by any one transaction, but rather a maximum average value.)
@@ -3963,6 +4000,14 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
39634000
have clients that touch many different tables in a single
39644001
transaction. This option can only be set at server start.
39654002
</para>
4003+
4004+
<para>
4005+
Increasing this parameter may cause <productname>PostgreSQL</>
4006+
to request more <systemitem class="osname">System V</> shared
4007+
memory than your operating system's default configuration
4008+
allows. See <xref linkend="sysvipc"> for information on how to
4009+
adjust those parameters, if necessary.
4010+
</para>
39664011
</listitem>
39674012
</varlistentry>
39684013

@@ -4653,9 +4698,10 @@ $ <userinput>postmaster -o '-S 1024 -s'</userinput>
46534698
relevant for <productname>PostgreSQL</>). Almost all modern
46544699
operating systems provide these features, but not all of them have
46554700
them turned on or sufficiently sized by default, especially systems
4656-
with BSD heritage. (For the <systemitem class="osname">QNX</> and
4657-
<systemitem class="osname">BeOS</> ports, <productname>PostgreSQL</>
4658-
provides its own replacement implementation of these facilities.)
4701+
with BSD heritage. (For the <systemitem class="osname">Windows</>,
4702+
<systemitem class="osname">QNX</> and <systemitem class="osname">BeOS</>
4703+
ports, <productname>PostgreSQL</> provides its own replacement
4704+
implementation of these facilities.)
46594705
</para>
46604706

46614707
<para>
@@ -4695,8 +4741,7 @@ $ <userinput>postmaster -o '-S 1024 -s'</userinput>
46954741
<row>
46964742
<entry><varname>SHMMAX</></>
46974743
<entry>Maximum size of shared memory segment (bytes)</>
4698-
<entry>250 kB + 8.2 kB * <xref linkend="guc-shared-buffers"> +
4699-
14.2 kB * <xref linkend="guc-max-connections"> up to infinity</entry>
4744+
<entry>at least several megabytes (see text)</entry>
47004745
</row>
47014746

47024747
<row>
@@ -4764,14 +4809,17 @@ $ <userinput>postmaster -o '-S 1024 -s'</userinput>
47644809
bytes, of a shared memory segment. If you get an error message from
47654810
<function>shmget</> like <errorname>Invalid argument</>, it is
47664811
likely that this limit has been exceeded. The size of the required
4767-
shared memory segment varies both with the number of requested
4768-
buffers (<option>-B</> option) and the number of allowed connections
4769-
(<option>-N</> option), although the former is the most significant.
4770-
(You can, as a temporary solution, lower these settings to eliminate
4771-
the failure.) As a rough approximation, you can estimate the
4772-
required segment size as suggested in <xref
4773-
linkend="sysvipc-parameters">. Any error message you might get will
4774-
contain the size of the failed allocation request.
4812+
shared memory segment varies depending on several
4813+
<productname>PostgreSQL</> configuration parameters, as shown in
4814+
<xref linkend="shared-memory-parameters">.
4815+
You can, as a temporary solution, lower some of those settings to
4816+
avoid the failure. As a rough approximation, you can estimate the
4817+
required segment size as 500 kB plus the variable amounts shown in
4818+
the table. (Any error message you might get will include the exact
4819+
size of the failed allocation request.) While it is possible to get
4820+
<productname>PostgreSQL</> to run with <varname>SHMMAX</> as small as
4821+
1 MB, you need at least 4 MB for acceptable performance, and desirable
4822+
settings are in the tens of megabytes.
47754823
</para>
47764824

47774825
<para>
@@ -4785,16 +4833,16 @@ $ <userinput>postmaster -o '-S 1024 -s'</userinput>
47854833
<para>
47864834
Less likely to cause problems is the minimum size for shared
47874835
memory segments (<varname>SHMMIN</>), which should be at most
4788-
approximately256 kB for <productname>PostgreSQL</> (it is
4836+
approximately500 kB for <productname>PostgreSQL</> (it is
47894837
usually just 1). The maximum number of segments system-wide
47904838
(<varname>SHMMNI</>) or per-process (<varname>SHMSEG</>) are unlikely
47914839
to cause a problem unless your system has them set to zero.
47924840
</para>
47934841

47944842
<para>
47954843
<productname>PostgreSQL</> uses one semaphore per allowed connection
4796-
(<option>-N</> option), in sets of 16. Each such set will also
4797-
contain a 17th semaphore which contains a <quote>magic
4844+
(<xref linkend="guc-max-connections">), in sets of 16. Each such set will
4845+
alsocontain a 17th semaphore which contains a <quote>magic
47984846
number</quote>, to detect collision with semaphore sets used by
47994847
other applications. The maximum number of semaphores in the system
48004848
is set by <varname>SEMMNS</>, which consequently must be at least
@@ -4834,6 +4882,7 @@ $ <userinput>postmaster -o '-S 1024 -s'</userinput>
48344882
for <productname>PostgreSQL</>.
48354883
</para>
48364884

4885+
48374886
<variablelist>
48384887

48394888
<varlistentry>
@@ -5172,6 +5221,59 @@ set semsys:seminfo_semmsl=32
51725221
</varlistentry>
51735222

51745223
</variablelist>
5224+
5225+
5226+
<table id="shared-memory-parameters">
5227+
<title>Configuration parameters affecting
5228+
<productname>PostgreSQL</productname>'s shared memory usage</>
5229+
5230+
<tgroup cols="2">
5231+
<thead>
5232+
<row>
5233+
<entry>Name</>
5234+
<entry>Approximate multiplier (bytes per increment)</>
5235+
</row>
5236+
</thead>
5237+
5238+
<tbody>
5239+
<row>
5240+
<entry><xref linkend="guc-max-connections"></>
5241+
<entry>400 (but see also <varname>max_locks_per_transaction</>)</entry>
5242+
</row>
5243+
5244+
<row>
5245+
<entry><xref linkend="guc-max-prepared-transactions"></>
5246+
<entry>600 (but see also <varname>max_locks_per_transaction</>)</entry>
5247+
</row>
5248+
5249+
<row>
5250+
<entry><xref linkend="guc-max-locks-per-transaction"></>
5251+
<entry>220 * (<xref linkend="guc-max-connections"> +
5252+
<xref linkend="guc-max-prepared-transactions">)</>
5253+
</row>
5254+
5255+
<row>
5256+
<entry><xref linkend="guc-shared-buffers"></>
5257+
<entry>8300</>
5258+
</row>
5259+
5260+
<row>
5261+
<entry><xref linkend="guc-wal-buffers"></>
5262+
<entry>8200</>
5263+
</row>
5264+
5265+
<row>
5266+
<entry><xref linkend="guc-max-fsm-relations"></>
5267+
<entry>70</>
5268+
</row>
5269+
5270+
<row>
5271+
<entry><xref linkend="guc-max-fsm-pages"></>
5272+
<entry>6</>
5273+
</row>
5274+
</tbody>
5275+
</tgroup>
5276+
</table>
51755277
</sect2>
51765278

51775279

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -49,11 +49,10 @@
4949
#listen_addresses = 'localhost'# what IP interface(s) to listen on;
5050
# defaults to localhost, '*' = any
5151
#port = 5432
52-
#max_connections = 100# note: increasing max_connections costs
53-
# about 500 bytes of shared memory per
54-
# connection slot, in addition to costs
55-
# from shared_buffers and
56-
# max_locks_per_transaction.
52+
#max_connections = 100
53+
# note: increasing max_connections costs ~400 bytes of shared memory per
54+
# connection slot, plus lock space (see max_locks_per_transaction). You
55+
# might also need to raise shared_buffers to support more connections.
5756
#superuser_reserved_connections = 2
5857
#unix_socket_directory = ''
5958
#unix_socket_group = ''
@@ -91,14 +90,16 @@
9190
#shared_buffers = 1000# min 16 or max_connections*2, 8KB each
9291
#temp_buffers = 1000# min 100, 8KB each
9392
#max_prepared_transactions = 5# can be 0 or more
93+
# note: increasing max_prepared_transactions costs ~600 bytes of shared memory
94+
# per transaction slot, plus lock space (see max_locks_per_transaction).
9495
#work_mem = 1024# min 64, size in KB
9596
#maintenance_work_mem = 16384# min 1024, size in KB
9697
#max_stack_depth = 2048# min 100, size in KB
9798

9899
# - Free Space Map -
99100

100101
#max_fsm_pages = 20000# min max_fsm_relations*16, 6 bytes each
101-
#max_fsm_relations = 1000# min 100, ~50 bytes each
102+
#max_fsm_relations = 1000# min 100, ~70 bytes each
102103

103104
# - Kernel Resource Usage -
104105

@@ -396,8 +397,10 @@
396397
#---------------------------------------------------------------------------
397398

398399
#deadlock_timeout = 1000# in milliseconds
399-
#max_locks_per_transaction = 64# min 10, ~200*max_connections
400-
# bytes each
400+
#max_locks_per_transaction = 64# min 10
401+
# note: each lock table slot uses ~220 bytes of shared memory, and there are
402+
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
403+
# lock table slots.
401404

402405

403406
#---------------------------------------------------------------------------

0 commit comments

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