Peter,
> Please send a context diff (diff -c).
Attached.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
Index: runtime.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.214
diff -c -r1.214 runtime.sgml
*** runtime.sgml 17 Oct 2003 22:38:20 -0000 1.214
--- runtime.sgml 31 Oct 2003 19:48:38 -0000
***************
*** 1,5 ****
<!--
! $Header: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v 1.214 2003/10/17 22:38:20 tgl Exp $
-->
<Chapter Id="runtime">
--- 1,5 ----
<!--
! $Header: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v 1.213 2003/10/10 02:08:42 momjian Exp $
-->
<Chapter Id="runtime">
***************
*** 24,30 ****
that is managed by the server, and should not be shared with other
daemons. (For example, using the user <literal>nobody</literal> is a bad
idea.) It is not advisable to install executables owned by
! this user because compromised systems could then modify their own
binaries.
</para>
--- 24,30 ----
that is managed by the server, and should not be shared with other
daemons. (For example, using the user <literal>nobody</literal> is a bad
idea.) It is not advisable to install executables owned by
! this user because compromised systems could then modify their own
binaries.
</para>
***************
*** 466,472 ****
</para>
<para>
! One way to set these parameters is to edit the file
<filename>postgresql.conf</filename><indexterm><primary>postgresql.conf</></>
in the data directory. (A default file is installed there.) An
example of what this file might look like is:
--- 466,472 ----
</para>
<para>
! One way to set these options is to edit the file
<filename>postgresql.conf</filename><indexterm><primary>postgresql.conf</></>
in the data directory. (A default file is installed there.) An
example of what this file might look like is:
***************
*** 476,482 ****
syslog = 2
search_path = '$user, public'
</programlisting>
! One parameter is specified per line. The equal sign between name and
value is optional. Whitespace is insignificant and blank lines are
ignored. Hash marks (<literal>#</literal>) introduce comments
anywhere. Parameter values that are not simple identifiers or
--- 476,482 ----
syslog = 2
search_path = '$user, public'
</programlisting>
! One option is specified per line. The equal sign between name and
value is optional. Whitespace is insignificant and blank lines are
ignored. Hash marks (<literal>#</literal>) introduce comments
anywhere. Parameter values that are not simple identifiers or
***************
*** 517,564 ****
</programlisting>
(This works for any <application>libpq</>-based client application, not just
<application>psql</application>.) Note that this won't work for
! parameters that are fixed when the server is started, such as the port
number.
</para>
<para>
Furthermore, it is possible to assign a set of option settings to
a user or a database. Whenever a session is started, the default
settings for the user and database involved are loaded. The
commands <command>ALTER DATABASE</command> and <command>ALTER
USER</command>, respectively, are used to configure these
! settings. Per-database settings override anything received
from the <command>postmaster</command> command-line or the
configuration file, and in turn are overridden by per-user
! settings; both are overridden by per-session options.
</para>
<para>
! Some parameters can be changed in individual <acronym>SQL</acronym>
! sessions with the <xref linkend="SQL-SET" endterm="SQL-SET-title">
! command, for example:
! <screen>
! SET ENABLE_SEQSCAN TO OFF;
! </screen>
! If <command>SET</> is allowed, it overrides all other sources of
! values for the parameter. Superusers are allowed to <command>SET</>
! more values than ordinary users.
</para>
- <para>
- The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
- command allows inspection of the current values of all parameters.
- </para>
- <para>
- The virtual table <structname>pg_settings</structname>
- (described in <xref linkend="view-pg-settings">) also allows
- displaying and updating session run-time parameters. It is equivalent
- to <command>SHOW</> and <command>SET</>, but can be more convenient
- to use because it can be joined with other tables, or selected from using
- any desired selection condition.
- </para>
-
<sect2 id="runtime-config-connection">
<title>Connections and Authentication</title>
--- 517,660 ----
</programlisting>
(This works for any <application>libpq</>-based client application, not just
<application>psql</application>.) Note that this won't work for
! options that are fixed when the server is started, such as the port
number.
</para>
<para>
+ Some options can be changed in individual <acronym>SQL</acronym>
+ sessions with the <command>SET</command> command, for example:
+ <screen>
+ SET ENABLE_SEQSCAN TO OFF;
+ </screen>
+ See the <acronym>SQL</acronym> command language reference for
+ details on the syntax.
+ </para>
+
+ <para>
Furthermore, it is possible to assign a set of option settings to
a user or a database. Whenever a session is started, the default
settings for the user and database involved are loaded. The
commands <command>ALTER DATABASE</command> and <command>ALTER
USER</command>, respectively, are used to configure these
! settings. Such per-database settings override anything received
from the <command>postmaster</command> command-line or the
configuration file, and in turn are overridden by per-user
! settings.
</para>
<para>
! The virtual table <structname>pg_settings</structname> allows
! displaying and updating session run-time parameters. It contains one
! row for each configuration parameter; the columns are shown in
! <xref linkend="runtime-pgsettings-table">. This table allows the
! configuration data to be joined with other tables and have a
! selection criteria applied.
</para>
+
+ <para>
+ An <command>UPDATE</command> performed on <structname>pg_settings</structname>
+ is equivalent to executing the <command>SET</command> command on that named
+ parameter. The change only affects the value used by the current session. If
+ an <command>UPDATE</command> is issued within a transaction that is later
+ aborted, the effects of the <command>UPDATE</command> command disappear when
+ the transaction is rolled back. Once the surrounding transaction is
+ committed, the effects will persist until the end of the session, unless
+ overridden by another <command>UPDATE</command> or <command>SET</command>.
+ </para>
+
+ <table id="runtime-pgsettings-table">
+ <title><literal>pg_settings</> Columns</title>
+
+ <tgroup cols=3>
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>name</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>run-time configuration parameter name</entry>
+ </row>
+
+ <row>
+ <entry><literal>setting</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>current value of the parameter</entry>
+ </row>
+
+ <row>
+ <entry><literal>context</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>context required to set the parameter's value</entry>
+ </row>
+
+ <row>
+ <entry><literal>vartype</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>parameter type</entry>
+ </row>
+
+ <row>
+ <entry><literal>source</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>source of the current parameter value</entry>
+ </row>
+
+ <row>
+ <entry><literal>min_val</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>minimum allowed value of the parameter</entry>
+ </row>
+
+ <row>
+ <entry><literal>max_val</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>maximum allowed value of the parameter</entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <sect2 id="runtime-config-basics">
+ <title>Basic Runtime Setup</title>
+
+ <para>
+ PostgreSQL's default runtime setup is very conservative, and more appropriate to an
+ old workstation than a modern server machine. As such, you will certainly want to
+ change some of the runtime settings before running PostgreSQL for the first time.
+ After INITDB, database administrators will want to change most or
+ all of the following settings.
+ </para>
+ <itemizedlist>
+ <listitem>In <xref linkend="runtime-config-connection-settings">,
+ <varname>tcpip_socket</varname> and <varname>max_connections</varname> </listitem>
+ <listitem>In <xref linkend="runtime-config-resource-memory">,
+ <varname>shared_buffers</varname>, <varname>sort_mem</varname>, and <varname>vacuum_mem</varname></listitem>
+ <listitem>In <xref linkend="runtime-config-resource-fsm">, <varname>max_fsm_pages</varname></listitem>
+ <listitem>In <xref linkend="runtime-config-wal-checkpoints">, <varname>checkpoint_segments</varname></listitem>
+ <listitem>In <xref linkend="runtime-config-query-constants">,
+ <varname>effective_cache_size</varname> and <varname>random_tuple_cost</varname></listitem>
+ <listitem>In <xref linkend="runtime-config-logging">, <varname>syslog</varname> and possibly other logging options</listitem>
+ <listitem>
+ </itemizedlist>
+ <para>
+ Please note that the configuration of a lot of the above items references "available RAM."
+ Available RAM is not the total memory in your machine, but rather the amount of system
+ RAM available to PostgreSQL and not being used by other applications. The distinction
+ is important on systems which also run other significant software, such as a web server.
+ Also, please note
+ the warning regarding <systemitem class="osname">System V</>
+ shared memory in <xref linkend="sysvipc"> before setting max_connections or shared_buffers.
+ </para>
<sect2 id="runtime-config-connection">
<title>Connections and Authentication</title>
***************
*** 566,572 ****
<title>Connection Settings</title>
<variablelist>
!
<varlistentry>
<term><varname>tcpip_socket</varname> (<type>boolean</type>)</term>
<listitem>
--- 662,668 ----
<title>Connection Settings</title>
<variablelist>
!
<varlistentry>
<term><varname>tcpip_socket</varname> (<type>boolean</type>)</term>
<listitem>
***************
*** 597,602 ****
--- 693,704 ----
allows. See <xref linkend="sysvipc"> for information on how to
adjust these parameters, if necessary.
</para>
+
+ <para>
+ When setting this value, keep in mind that applications which connect
+ intermittently or use pooled connections will not need as many connections
+ as active users.
+ </para>
</listitem>
</varlistentry>
***************
*** 739,745 ****
</para>
</listitem>
</varlistentry>
!
<varlistentry>
<indexterm>
<primary>SSL</primary>
--- 841,847 ----
</para>
</listitem>
</varlistentry>
!
<varlistentry>
<indexterm>
<primary>SSL</primary>
***************
*** 834,841 ****
the server. This setting must be at least 16, as well as at
least twice the value of <varname>max_connections</varname>;
however, settings significantly higher than the minimum are
! usually needed for good performance. Values of a few thousand
! are recommended for production installations. This option can
only be set at server start.
</para>
--- 936,942 ----
the server. This setting must be at least 16, as well as at
least twice the value of <varname>max_connections</varname>;
however, settings significantly higher than the minimum are
! usually needed for good performance. This option can
only be set at server start.
</para>
***************
*** 846,851 ****
--- 947,965 ----
allows. See <xref linkend="sysvipc"> for information on how to
adjust these parameters, if necessary.
</para>
+
+ <para>
+ The shared memory buffer is a "holding area" for PostgreSQL requests being
+ fed to the kernel buffer. As such, they should always be a minority of
+ available RAM. Most users doing profiling to date have reported successful
+ performance with values equal to 6% to 12% of available RAM, with higher values
+ being used for databases which run large complex queries or large updates
+ and data transformations. The initdb script will have already set your
+ <varname>shared_buffers</varname> to 1000 or the maximum supported by your
+ operating system. To increase the shared memory supported,
+ see <xref linkend="sysvipc">.
+ </para>
+
</listitem>
</varlistentry>
***************
*** 856,861 ****
--- 970,977 ----
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).
+ </para>
+ <para>
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
***************
*** 864,874 ****
times the value of <varname>sort_mem</varname>. Sort operations are used
by <literal>ORDER BY</>, merge joins, and <command>CREATE INDEX</>.
Hash tables are used in hash joins, hash-based aggregation, and
! hash-based processing of <literal>IN</> subqueries. Because
<command>CREATE INDEX</> is used when restoring a database,
increasing <varname>sort_mem</varname> before doing a large
restore operation can improve performance.
</para>
</listitem>
</varlistentry>
--- 980,1000 ----
times the value of <varname>sort_mem</varname>. Sort operations are used
by <literal>ORDER BY</>, merge joins, and <command>CREATE INDEX</>.
Hash tables are used in hash joins, hash-based aggregation, and
! hash-based processing of <literal>IN</> subqueries. Because
<command>CREATE INDEX</> is used when restoring a database,
increasing <varname>sort_mem</varname> before doing a large
restore operation can improve performance.
</para>
+ <para>
+ If you have lots of available RAM and few concurrent users, or
+ if you need to run queries that pull large amounts of data and aggregates
+ it, then you want a higher <varname>sort_mem</varname>. If you have
+ many concurrent requests, or have many compound-complex queries with lots
+ of joins and subselects, lower the sort_mem. Common settings include
+ 512K for a high-concurrency web server, or 32MB for a data warehouse.
+ Keep in mind that you can set this value per connection so that you can
+ allocate memory differently for atypical queries.
+ </para>
</listitem>
</varlistentry>
***************
*** 882,887 ****
--- 1008,1019 ----
8192 kilobytes. Larger settings may improve the speed of
vacuuming large tables that have many deleted tuples.
</para>
+ <para>
+ Users who regularly update or load large quantities of data
+ may want to increase this value, possibly as mush as 64MB or 128MB
+ for single-user databases. Administrators of most databases will find
+ the default value sufficient.
+ </para>
</listitem>
</varlistentry>
***************
*** 901,906 ****
--- 1033,1047 ----
16 * <varname>max_fsm_relations</varname>. The default is 20000.
This option can only be set at server start.
</para>
+ <para>
+ This number should be increased to keep pace with the quantity of
+ data updated or deleted between <command>VACUUMs</command>. A good
+ way to set it is to do periodic <command>VACUUM VERBOSE ANALYZE</command>,
+ add up the data pages reclaimed, and make sure that your <varname>max_fsm_relations</varname>
+ is greater than this amount. With a proper setting, you will be able
+ to eliminate <command>VACUUM FULL</command> and <command>REINDEX</command>
+ on many types of databases.
+ </para>
</listitem>
</varlistentry>
***************
*** 911,917 ****
Sets the maximum number of relations (tables and indexes) for which
free space will be tracked in the shared free-space map. Roughly
fifty bytes of shared memory are consumed for each slot.
! The default is 1000.
This option can only be set at server start.
</para>
</listitem>
--- 1052,1059 ----
Sets the maximum number of relations (tables and indexes) for which
free space will be tracked in the shared free-space map. Roughly
fifty bytes of shared memory are consumed for each slot.
! The default is 1000. It should be set to the total expected tables
! on all databases plus a margin of about 200 to cover the system tables.
This option can only be set at server start.
</para>
</listitem>
***************
*** 1066,1072 ****
</para>
</listitem>
</varlistentry>
!
<varlistentry>
<term><varname>wal_buffers</varname> (<type>integer</type>)</term>
<listitem>
--- 1208,1214 ----
</para>
</listitem>
</varlistentry>
!
<varlistentry>
<term><varname>wal_buffers</varname> (<type>integer</type>)</term>
<listitem>
***************
*** 1093,1098 ****
--- 1235,1250 ----
default is three. This option can only be set at server start
or in the <filename>postgresql.conf</filename> file.
</para>
+ <para>
+ This is possibly the most influential setting for large data loads,
+ such as restore of a database. If you expect to be updating or inserting
+ large quantites of data in a batch, then cautiously increase this
+ parameter. When checking if you have enough disk space, beware that
+ PostgreSQL may use as many as double your setting under peak load.
+ Using the <varname>checkpoint_warning</varname> setting below is an
+ excellent way to determine if you need to increase <varname>checkpoint_segments</varname>
+ for normal use.
+ </para>
</listitem>
</varlistentry>
***************
*** 1282,1288 ****
</para>
</listitem>
</varlistentry>
!
</variablelist>
</sect3>
<sect3 id="runtime-config-query-constants">
--- 1434,1440 ----
</para>
</listitem>
</varlistentry>
!
</variablelist>
</sect3>
<sect3 id="runtime-config-query-constants">
***************
*** 1311,1316 ****
--- 1463,1474 ----
data files). This is measured in disk pages, which are
normally 8192 bytes each. The default is 1000.
</para>
+ <para>
+ You should usually set this value to 60% to 75% of your available
+ RAM, so that PostgreSQL will have an accurate idea of how much
+ kernel cache there is for queries. This setting can have a dramatic
+ effect on how the query planner uses indexes.
+ </para>
</listitem>
</varlistentry>
***************
*** 1325,1330 ****
--- 1483,1495 ----
lower value makes it more likely an index scan will be
used. The default is four.
</para>
+ <para>
+ For database servers on modern hardware including adequate
+ disk I/O, you will want to lower this value. Common settings
+ are 2 or 2.5 for medium-end or multi-purpose servers, and 1.5 to 2
+ for high-end dedicated hardware. The default is suitable for
+ workstations.
+ </para>
</listitem>
</varlistentry>
***************
*** 1430,1435 ****
--- 1595,1676 ----
</variablelist>
</sect3>
+ <sect3 id="runtime-config-query-statistics">
+ <title>Query and Index Statistics Collector</title>
+ <variablelist>
+
+ <varlistentry>
+ <term><varname>STATS_START_COLLECTOR</varname> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Controls whether the server should start the
+ statistics-collection subprocess. This is on by default, but
+ may be turned off if you know you have no interest in
+ collecting statistics. This option can only be set at server
+ start.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>STATS_COMMAND_STRING</varname> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Enables the collection of statistics on the currently
+ executing command of each session, along with the time at
+ which that command began execution. This option is off by
+ default. Note that even when enabled, this information is not
+ visible to all users, only to superusers and the user owning
+ the session being reported on; so it should not represent a
+ security risk. This data can be accessed via the
+ <structname>pg_stat_activity</structname> system view; refer
+ to <xref linkend="monitoring"> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>STATS_BLOCK_LEVEL</varname> (<type>boolean</type>)</term>
+ <term><varname>STATS_ROW_LEVEL</varname> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ These enable the collection of block-level and row-level statistics
+ on database activity, respectively. These options are off by
+ default. This data can be accessed via the
+ <structname>pg_stat</structname> and
+ <structname>pg_statio</structname> family of system views;
+ refer to <xref linkend="monitoring"> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>STATS_RESET_ON_SERVER_START</varname> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ If on, collected statistics are zeroed out whenever the server
+ is restarted. If off, statistics are accumulated across server
+ restarts. The default is on. This option can only be set at
+ server start.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>DEFAULT_STATISTICS_TARGET</varname> (<type>integer</type>)</term>
+ <listitem>
+ <para>
+ Sets the default statistics target for table columns that have not
+ had a column-specific target set via <command>ALTER TABLE SET
+ STATISTICS</>. Larger values increase the time needed to do
+ <command>ANALYZE</>, but may improve the quality of the planner's
+ estimates. The default value is 10.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect3>
<sect3 id="runtime-config-query-other">
<title>Other Planner Options</title>
***************
*** 1941,1946 ****
--- 2182,2208 ----
is restarted. If off, statistics are accumulated across server
restarts. The default is on. This option can only be set at
server start.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect3>
+
+ <sect3 id="runtime-config-logging-statistics">
+ <title>Statistics Logging</title>
+ <variablelist>
+
+ <varlistentry>
+ <term><varname>LOG_STATEMENT_STATS</varname> (<type>boolean</type>)</term>
+ <term><varname>LOG_PARSER_STATS</varname> (<type>boolean</type>)</term>
+ <term><varname>LOG_PLANNER_STATS</varname> (<type>boolean</type>)</term>
+ <term><varname>LOG_EXECUTOR_STATS</varname> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ For each query, write performance statistics of the respective
+ module to the server log. This is a crude profiling
+ instrument.
</para>
</listitem>
</varlistentry>
Index: installation.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/installation.sgml,v
retrieving revision 1.174
diff -c -r1.174 installation.sgml
*** installation.sgml 29 Oct 2003 13:42:55 -0000 1.174
--- installation.sgml 31 Oct 2003 19:48:39 -0000
***************
*** 1171,1179 ****
<varname>shared_buffers</varname> and <varname> sort_mem</varname>
mentioned in <![%standalone-include[the documentation]]>
<![%standalone-ignore[<xref linkend="runtime-config-resource-memory">]]>.
! Other parameters in <![%standalone-include[the documentation]]>
! <![%standalone-ignore[<xref linkend="runtime-config-resource">]]>
! also affect performance.
</para>
</sect2>
--- 1171,1179 ----
<varname>shared_buffers</varname> and <varname> sort_mem</varname>
mentioned in <![%standalone-include[the documentation]]>
<![%standalone-ignore[<xref linkend="runtime-config-resource-memory">]]>.
! See <![%standalone-include[the documentation]]>
! <![%standalone-ignore[<xref linkend="runtime-config-basics">]]>
! for a list of the options which most administrators adjust.
</para>
</sect2>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
