Folks,
Attached is my "runtime basics" patch to Runtime.sgml (and to one line in
installation.sgml).
Can someone check my SGML and OK it?
This isn't the comprehensive "getting up and running as a pgsql DBA" that I
would have liked, but I find I'm running out of time. It also uses a
varlist in a section of runtime.sgml, and links to section headers in the
rest of the document. I know that this was not the desired approach, but
better this than nothing at all ....
--
-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 -r1.214 runtime.sgml
2c2
< $Header: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v 1.214 2003/10/17 22:38:20 tgl Exp $
---
> $Header: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v 1.213 2003/10/10 02:08:42 momjian Exp $
27c27
< this user because compromised systems could then modify their own
---
> this user because compromised systems could then modify their own
469c469
< One way to set these parameters is to edit the file
---
> One way to set these options is to edit the file
479c479
< One parameter is specified per line. The equal sign between name and
---
> One option is specified per line. The equal sign between name and
520c520
< parameters that are fixed when the server is started, such as the port
---
> options that are fixed when the server is started, such as the port
524a525,534
> 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>
530c540
< settings. Per-database settings override anything received
---
> settings. Such per-database settings override anything received
533c543
< settings; both are overridden by per-session options.
---
> settings.
537,545c547,552
< 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.
---
> 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.
546a554,655
>
> <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>
548,551d656
< <para>
< The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
< command allows inspection of the current values of all parameters.
< </para>
553,561d657
< <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>
<
569c665
<
---
>
599a696,701
>
> <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>
742c844
<
---
>
837,838c939
< usually needed for good performance. Values of a few thousand
< are recommended for production installations. This option can
---
> usually needed for good performance. This option can
848a950,962
>
> <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>
>
858a973,974
> </para>
> <para>
867c983
< hash-based processing of <literal>IN</> subqueries. Because
---
> hash-based processing of <literal>IN</> subqueries. Because
871a988,997
> <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>
884a1011,1016
> <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>
903a1036,1044
> <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>
914c1055,1056
< The default is 1000.
---
> 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.
1069c1211
<
---
>
1095a1238,1247
> <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>
1285c1437
<
---
>
1313a1466,1471
> <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>
1327a1486,1492
> <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>
1432a1598,1673
> <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>
1943a2185,2205
> </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.
Index: installation.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/installation.sgml,v
retrieving revision 1.174
diff -r1.174 installation.sgml
1174,1176c1174,1176
< Other parameters in <![%standalone-include[the documentation]]>
< <![%standalone-ignore[<xref linkend="runtime-config-resource">]]>
< also affect performance.
---
> See <![%standalone-include[the documentation]]>
> <![%standalone-ignore[<xref linkend="runtime-config-basics">]]>
> for a list of the options which most administrators adjust.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
