Subject: Runtime Basics

From: Josh Berkus <[email-masked]>
Date: Thu, 30 Oct 2003 16:27:25 -0800

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.

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 31 Oct 2003 07:59:54 +0100 (CET)

> Attached is my "runtime basics" patch to Runtime.sgml (and to one line in

Please send a context diff (diff -c).

-- 
Peter Eisentraut   [email-masked]

---

From: Jeroen Ruigrok/asmodai <[email-masked]>
Date: Fri, 31 Oct 2003 08:16:47 +0100

>Can someone check my SGML and OK it?

Most of the time you can run nsgmls or onsgmls on it to verify, assuming
you have stuff like that installed (sp or opensp IIRC).

-- 
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai / kita no mono
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7  9D88 97E6 839B 2EAC 625B
http://www.tendra.org/   | http://www.in-nomine.org/~asmodai/diary/
Earth to earth, ashes to ashes, dust to dust...

---

From: Josh Berkus <[email-masked]>
Date: Fri, 31 Oct 2003 09:23:54 -0800

Peter, Jeroen,

> Please send a context diff (diff -c).

Will do.  

> you have stuff like that installed (sp or opensp IIRC).

Yeah.  Unfortuantely, most of my SGML tools on my laptop seem to be broken 
pending spending 3-6 hours figuring out which package is broken exactly and 
re-building it.   Doing multiple upgrades in-place of a major Linux distro 
(SuSE) seems to produce some notably Windows-like effects ....

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---

From: Josh Berkus <[email-masked]>
Date: Fri, 31 Oct 2003 12:21:54 -0800

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>

---

From: Peter Eisentraut <[email-masked]>
Date: Sat, 1 Nov 2003 00:13:30 +0100 (CET)

> > Please send a context diff (diff -c).

The SGML is not valid.  Please validate it.

"Parameter" is the correct term, not "option".

"runtime" is spelled "run time".

There is no such thing as INITDB.  Maybe you mean initdb.

Please follow the existing indentation scheme.  Never put tabs in a
documentation source file.

Mark up parameter names using <varname>.

Don't use quotes ("), use <quote>.

And finally, can you explain what you intend to accomplish by this change?
I just see a bunch of things moved around and some things changed along
the way.  Some of these things seem to make sense, but some do not.

---

From: Josh Berkus <[email-masked]>
Date: Fri, 31 Oct 2003 15:37:50 -0800

Peter,

> the way.  Some of these things seem to make sense, but some do not.

Never mind, then.  Obviously I don't have time to do this properly, so it's 
probably better not to do it at all.

-- 
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology 	[email-masked]
    and data management solutions 	(415) 565-7293
   for law firms, small businesses 	 fax 621-2533
    and non-profit organizations. 	San Francisco

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 10 Nov 2003 18:48:07 -0500 (EST)

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours to HEAD and 7.4.X.

---------------------------------------------------------------------------

> > Please send a context diff (diff -c).

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  [email-masked]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---

From: Josh Berkus <[email-masked]>
Date: Mon, 10 Nov 2003 16:51:06 -0800

Bruce,

> I will try to apply it within the next 48 hours to HEAD and 7.4.X.

Please kill this patch.  Peter pointed out a number of SGML mistakes and 
contextual problems which I don't have time to fix.   

-- 
-Josh Berkus

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 10 Nov 2003 19:56:33 -0500 (EST)

Patch withdrawn by author.

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 10 Nov 2003 19:57:06 -0500 (EST)

> contextual problems which I don't have time to fix.   

I was wondering if this was the same patch, and I was sure you would
tell me if it was --- withdrawn.

---

From: Josh Berkus <[email-masked]>
Date: Mon, 10 Nov 2003 20:26:29 -0800

Bruce,

> tell me if it was --- withdrawn.

Yeah, sorry.  I thought that it wouldn't be considered "submitted" until I 
sent it to pgsql-patches.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---

From: Tom Lane <[email-masked]>
Date: Mon, 10 Nov 2003 23:50:22 -0500

> Your patch has been added to the PostgreSQL unapplied patches list at:

Wasn't this patch withdrawn?

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 11 Nov 2003 00:13:25 -0500 (EST)

> Wasn't this patch withdrawn?

Yep, removed from queue.