public inbox for [email protected]help / color / mirror / Atom feed
Runtime Basics 14+ messages / 5 participants [nested] [flat]
* Runtime Basics @ 2003-10-31 00:27 Josh Berkus <[email protected]> 0 siblings, 2 replies; 14+ messages in thread From: Josh Berkus @ 2003-10-31 00:27 UTC (permalink / raw) To: pgsql-docs 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 Attachments: [text/x-diff] basic_runtime.diff (16.0K, 2-basic_runtime.diff) download | inline diff: 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. ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-10-31 06:59 Peter Eisentraut <[email protected]> parent: Josh Berkus <[email protected]> 1 sibling, 2 replies; 14+ messages in thread From: Peter Eisentraut @ 2003-10-31 06:59 UTC (permalink / raw) To: Josh Berkus <[email protected]>; +Cc: pgsql-docs Josh Berkus writes: > Attached is my "runtime basics" patch to Runtime.sgml (and to one line in > installation.sgml). Please send a context diff (diff -c). -- Peter Eisentraut [email protected] ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-10-31 07:16 Jeroen Ruigrok/asmodai <[email protected]> parent: Josh Berkus <[email protected]> 1 sibling, 0 replies; 14+ messages in thread From: Jeroen Ruigrok/asmodai @ 2003-10-31 07:16 UTC (permalink / raw) To: Josh Berkus <[email protected]>; +Cc: pgsql-docs -On [20031031 01:32], Josh Berkus ([email protected]) wrote: >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... ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-10-31 17:23 Josh Berkus <[email protected]> parent: Peter Eisentraut <[email protected]> 1 sibling, 0 replies; 14+ messages in thread From: Josh Berkus @ 2003-10-31 17:23 UTC (permalink / raw) To: pgsql-docs Peter, Jeroen, > Please send a context diff (diff -c). Will do. > 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). 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 ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-10-31 20:21 Josh Berkus <[email protected]> parent: Peter Eisentraut <[email protected]> 1 sibling, 3 replies; 14+ messages in thread From: Josh Berkus @ 2003-10-31 20:21 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-docs Peter, > Please send a context diff (diff -c). Attached. -- -Josh Berkus Aglio Database Solutions San Francisco Attachments: [text/x-diff] basic_runtime.diff (26.0K, 2-basic_runtime.diff) download | inline diff: 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> ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-10-31 23:13 Peter Eisentraut <[email protected]> parent: Josh Berkus <[email protected]> 2 siblings, 1 reply; 14+ messages in thread From: Peter Eisentraut @ 2003-10-31 23:13 UTC (permalink / raw) To: Josh Berkus <[email protected]>; +Cc: pgsql-docs Josh Berkus writes: > > Please send a context diff (diff -c). > > Attached. 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. -- Peter Eisentraut [email protected] ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-10-31 23:37 Josh Berkus <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Josh Berkus @ 2003-10-31 23:37 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-docs Peter, > 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. 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 protected] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-11-10 23:48 Bruce Momjian <[email protected]> parent: Josh Berkus <[email protected]> 2 siblings, 2 replies; 14+ messages in thread From: Bruce Momjian @ 2003-11-10 23:48 UTC (permalink / raw) To: [email protected]; +Cc: Peter Eisentraut <[email protected]>; pgsql-docs 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. --------------------------------------------------------------------------- Josh Berkus wrote: > Peter, > > > Please send a context diff (diff -c). > > Attached. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco [ 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 protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-11-11 00:51 Josh Berkus <[email protected]> parent: Bruce Momjian <[email protected]> 1 sibling, 1 reply; 14+ messages in thread From: Josh Berkus @ 2003-11-11 00:51 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; pgsql-docs Bruce, > 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 kill this patch. Peter pointed out a number of SGML mistakes and contextual problems which I don't have time to fix. -- -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology [email protected] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-11-11 00:56 Bruce Momjian <[email protected]> parent: Josh Berkus <[email protected]> 2 siblings, 0 replies; 14+ messages in thread From: Bruce Momjian @ 2003-11-11 00:56 UTC (permalink / raw) To: [email protected]; +Cc: Peter Eisentraut <[email protected]>; pgsql-docs Patch withdrawn by author. --------------------------------------------------------------------------- Josh Berkus wrote: > Peter, > > > Please send a context diff (diff -c). > > Attached. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco [ 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 protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-11-11 00:57 Bruce Momjian <[email protected]> parent: Josh Berkus <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Bruce Momjian @ 2003-11-11 00:57 UTC (permalink / raw) To: [email protected]; +Cc: Peter Eisentraut <[email protected]>; pgsql-docs Josh Berkus wrote: > Bruce, > > > 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 kill this patch. Peter pointed out a number of SGML mistakes and > 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. -- Bruce Momjian | http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-11-11 04:26 Josh Berkus <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Josh Berkus @ 2003-11-11 04:26 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; pgsql-docs Bruce, > I was wondering if this was the same patch, and I was sure you would > 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 ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-11-11 04:50 Tom Lane <[email protected]> parent: Bruce Momjian <[email protected]> 1 sibling, 1 reply; 14+ messages in thread From: Tom Lane @ 2003-11-11 04:50 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: [email protected]; Peter Eisentraut <[email protected]>; pgsql-docs Bruce Momjian <[email protected]> writes: > Your patch has been added to the PostgreSQL unapplied patches list at: Wasn't this patch withdrawn? regards, tom lane ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Runtime Basics @ 2003-11-11 05:13 Bruce Momjian <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Bruce Momjian @ 2003-11-11 05:13 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected]; Peter Eisentraut <[email protected]>; pgsql-docs Tom Lane wrote: > Bruce Momjian <[email protected]> writes: > > Your patch has been added to the PostgreSQL unapplied patches list at: > > Wasn't this patch withdrawn? Yep, removed from queue. -- Bruce Momjian | http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2003-11-11 05:13 UTC | newest] Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-10-31 00:27 Runtime Basics Josh Berkus <[email protected]> 2003-10-31 06:59 ` Peter Eisentraut <[email protected]> 2003-10-31 17:23 ` Josh Berkus <[email protected]> 2003-10-31 20:21 ` Josh Berkus <[email protected]> 2003-10-31 23:13 ` Peter Eisentraut <[email protected]> 2003-10-31 23:37 ` Josh Berkus <[email protected]> 2003-11-10 23:48 ` Bruce Momjian <[email protected]> 2003-11-11 00:51 ` Josh Berkus <[email protected]> 2003-11-11 00:57 ` Bruce Momjian <[email protected]> 2003-11-11 04:26 ` Josh Berkus <[email protected]> 2003-11-11 04:50 ` Tom Lane <[email protected]> 2003-11-11 05:13 ` Bruce Momjian <[email protected]> 2003-11-11 00:56 ` Bruce Momjian <[email protected]> 2003-10-31 07:16 ` Jeroen Ruigrok/asmodai <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox