From dc199eafa7193992a8b03b3b1fcb14fdbea277f5 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 8 Aug 2004 19:42:57 +0000 Subject: [PATCH] Document background writer control parameters, do some editorial work on other recent changes in runtime parameter list. --- doc/src/sgml/runtime.sgml | 397 +++++++++++------- src/backend/utils/misc/postgresql.conf.sample | 33 +- 2 files changed, 265 insertions(+), 165 deletions(-) diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index c97e387861..d393a6d180 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ @@ -513,10 +513,11 @@ postmaster -c log_connections=yes -c log_destination='syslog' env PGOPTIONS='-c geqo=off' psql - (This works for any libpq-based client application, not just - psql.) Note that this won't work for - parameters that are fixed when the server is started, such as the port - number. + (This works for any libpq-based client application, not + just psql.) Note that this won't work for + parameters that are fixed when the server is started, nor for + parameters that require superuser permissions to change (not even + if you are logging in as superuser). @@ -666,7 +667,7 @@ SET ENABLE_SEQSCAN TO OFF; to request more System V shared memory or semaphores than your operating system's default configuration allows. See for information on how to - adjust these parameters, if necessary. + adjust those parameters, if necessary. @@ -713,11 +714,11 @@ SET ENABLE_SEQSCAN TO OFF; unix_socket_group (string) - Sets the group owner of the Unix domain socket. (The owning + Sets the owning group of the Unix-domain socket. (The owning user of the socket is always the user that starts the server.) In combination with the option unix_socket_permissions this can be used as - an additional access control mechanism for this socket type. + an additional access control mechanism for Unix-domain connections. By default this is the empty string, which uses the default group for the current user. This option can only be set at server start. @@ -729,9 +730,9 @@ SET ENABLE_SEQSCAN TO OFF; unix_socket_permissions (integer) - Sets the access permissions of the Unix domain socket. Unix + Sets the access permissions of the Unix-domain socket. Unix domain sockets use the usual Unix file system permission set. - The option value is expected to be an numeric mode + The option value is expected to be a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number @@ -741,7 +742,7 @@ SET ENABLE_SEQSCAN TO OFF; The default permissions are 0777, meaning anyone can connect. Reasonable alternatives are - 0770 (only user and group, see also under + 0770 (only user and group, see also unix_socket_group) and 0700 (only user). (Note that actually for a Unix domain socket, only write permission matters and there is no point in setting or revoking @@ -900,7 +901,7 @@ SET ENABLE_SEQSCAN TO OFF; to request more System V shared memory than your operating system's default configuration allows. See for information on how to - adjust these parameters, if necessary. + adjust those parameters, if necessary. @@ -1081,7 +1082,7 @@ SET ENABLE_SEQSCAN TO OFF; The intent of this feature is to allow administrators to reduce the I/O impact of these commands on concurrent database - activity. There are some situations in which it is not very + activity. There are many situations in which it is not very important that maintenance commands like VACUUM and ANALYZE finish quickly; however, it is usually very important that these @@ -1097,6 +1098,23 @@ SET ENABLE_SEQSCAN TO OFF; + + vacuum_cost_delay (integer) + + + The length of time, in milliseconds, that the process will sleep + when the cost limit has been exceeded. + The default value is 0, which disables the cost-based vacuum + delay feature. Positive values enable cost-based vacuuming. + Note that on many systems, the effective resolution + of sleep delays is 10 milliseconds; setting + vacuum_cost_delay to a value that is + not a multiple of 10 may have the same results as setting it + to the next higher multiple of 10. + + + + vacuum_cost_page_hit (integer) @@ -1137,25 +1155,8 @@ SET ENABLE_SEQSCAN TO OFF; vacuum_cost_limit (integer) - The accumulated cost that will cause the vacuuming process to briefly - nap. The default value is 200. - - - - - - vacuum_cost_delay (integer) - - - The length of time, in milliseconds, that the process will nap - when the cost limit has been exceeded. - The default value is 0, which disables the cost-based vacuum - delay feature. Positive values enable cost-based vacuuming. - Note however that on many systems, the effective resolution - of sleep delays is 10 milliseconds; setting - vacuum_cost_delay to a value that is - not a multiple of 10 may have the same results as setting it - to the next higher multiple of 10. + The accumulated cost that will cause the vacuuming process to sleep. + The default value is 200. @@ -1163,7 +1164,7 @@ SET ENABLE_SEQSCAN TO OFF; - There are certain bulk operations that hold critical locks and should + There are certain operations that hold critical locks and should therefore complete as quickly as possible. Cost-based vacuum delays do not occur during such operations. Therefore it is possible that the cost accumulates far higher than the specified @@ -1176,6 +1177,85 @@ SET ENABLE_SEQSCAN TO OFF; + + + Background Writer + + + Beginning in PostgreSQL 8.0, there is a separate server + process called the background writer, whose sole function + is to issue writes of dirty shared buffers. The intent is + that server processes handling user queries should seldom or never have + to wait for a write to occur, because the background writer will do it. + This arrangement also reduces the performance penalty associated with + checkpoints. The background writer will continuously trickle out dirty + pages to disk, so that only a few pages will need to be forced out when + checkpoint time arrives, instead of the storm of dirty-buffer writes that + formerly occurred at each checkpoint. However there is a net overall + increase in I/O load, because where a repeatedly-dirtied page might + before have been written only once per checkpoint interval, the + background writer might write it several times in the same interval. + In most situations a continuous low load is preferable to periodic + spikes, but the parameters discussed in this section can be used to tune + the behavior for local needs. + + + + + bgwriter_delay (integer) + + + Specifies the delay between activity rounds for the + background writer. In each round the writer issues writes for some + number of dirty buffers (controllable by the following parameters). + The selected buffers will always be the + least recently used ones among the currently dirty buffers. + It then sleeps for bgwriter_delay milliseconds, + and repeats. + Note that on many systems, the effective resolution + of sleep delays is 10 milliseconds; setting + bgwriter_delay to a value that is + not a multiple of 10 may have the same results as setting it + to the next higher multiple of 10. + This option can only be set at server start or in the + postgresql.conf file. + + + + + + bgwriter_percent (integer) + + + In each round, no more than this percentage of the currently dirty + buffers will be written (rounding up any fraction to the next whole + number of buffers). + This option can only be set at server start or in the + postgresql.conf file. + + + + + + bgwriter_maxpages (integer) + + + In each round, no more than this many dirty buffers will be written. + This option can only be set at server start or in the + postgresql.conf file. + + + + + + + Smaller values of bgwriter_percent and + bgwriter_maxpages reduce the extra I/O load caused by + the background writer, but leave more work to be done at checkpoint + time. To reduce load spikes at checkpoints, increase the values. + + + @@ -1203,8 +1283,6 @@ SET ENABLE_SEQSCAN TO OFF; to make sure that updates are physically written to disk. This insures that a database cluster will recover to a consistent state after an operating system or hardware crash. - (Crashes of the database server itself are not - related to this.) @@ -1218,6 +1296,9 @@ SET ENABLE_SEQSCAN TO OFF; However, if the system crashes, the results of the last few committed transactions may be lost in part or whole. In the worst case, unrecoverable data corruption may occur. + (Crashes of the database server itself are not + a risk factor here. Only an operating-system-level crash + creates a risk of corruption.) @@ -1251,6 +1332,7 @@ SET ENABLE_SEQSCAN TO OFF; open_sync (write WAL files with open() option O_SYNC), and open_datasync (write WAL files with open() option O_DSYNC). Not all of these choices are available on all platforms. + If fsync is off then this setting is irrelevant. This option can only be set at server start or in the postgresql.conf file. @@ -1261,9 +1343,42 @@ SET ENABLE_SEQSCAN TO OFF; wal_buffers (integer) - Number of disk-page buffers in shared memory for WAL - logging. The default is 8. This option can only be set at - server start. + Number of disk-page buffers allocated in shared memory for WAL data. + The default is 8. The setting need only be large enough to hold + the amount of WAL data generated by one typical transaction. + This option can only be set at server start. + + + + + + commit_delay (integer) + + + Time delay between writing a commit record to the WAL buffer + and flushing the buffer out to disk, in microseconds. A + nonzero delay can allow multiple transactions to be committed + with only one fsync() system call, if + system load is high enough that additional transactions become + ready to commit within the given interval. But the delay is + just wasted if no other transactions become ready to + commit. Therefore, the delay is only performed if at least + commit_siblings other transactions are + active at the instant that a server process has written its + commit record. The default is zero (no delay). + + + + + + commit_siblings (integer) + + + Minimum number of concurrent open transactions to require + before performing the commit_delay delay. A larger + value makes it more probable that at least one other + transaction will become ready to commit during the delay + interval. The default is five. @@ -1303,42 +1418,9 @@ SET ENABLE_SEQSCAN TO OFF; Write a message to the server logs if checkpoints caused by - the filling of checkpoint segment files happens more - frequently than this number of seconds. The default is 30 - seconds. Zero turns off the warning. - - - - - - - commit_delay (integer) - - - Time delay between writing a commit record to the WAL buffer - and flushing the buffer out to disk, in microseconds. A - nonzero delay allows multiple transactions to be committed - with only one fsync() system call, if - system load is high enough additional transactions may become - ready to commit within the given interval. But the delay is - just wasted if no other transactions become ready to - commit. Therefore, the delay is only performed if at least - commit_siblings other transactions are - active at the instant that a server process has written its - commit record. The default is zero (no delay). - - - - - - commit_siblings (integer) - - - Minimum number of concurrent open transactions to require - before performing the commit_delay delay. A larger - value makes it more probable that at least one other - transaction will become ready to commit during the delay - interval. The default is five. + the filling of checkpoint segment files happen closer together + than this many seconds. The default is 30 seconds. + Zero turns off the warning. @@ -1377,7 +1459,6 @@ SET ENABLE_SEQSCAN TO OFF; Planner Method Configuration - These configuration parameters provide a crude method for influencing the query plans chosen by the query optimizer. If @@ -1395,7 +1476,6 @@ SET ENABLE_SEQSCAN TO OFF; particular column using ALTER TABLE SET STATISTICS. - @@ -1744,7 +1824,7 @@ SET ENABLE_SEQSCAN TO OFF; - By default, this variable is set to + By default, this variable is set the same as from_collapse_limit, which is appropriate for most uses. Setting it to 1 prevents any reordering of inner JOINs. Thus, the explicit join order @@ -1973,8 +2053,8 @@ SET ENABLE_SEQSCAN TO OFF; Controls whether or not the SQL statement that causes an error condition will also be recorded in the server log. All SQL - statements that cause an error of the specified level, or a - higher level, are logged. The default is + statements that cause an error of the specified level or + higher are logged. The default is PANIC (effectively turning this feature off for normal use). Valid values are DEBUG5, DEBUG4, DEBUG3, @@ -1996,11 +2076,11 @@ SET ENABLE_SEQSCAN TO OFF; Sets a minimum statement execution time (in milliseconds) - for statement to be logged. All SQL statements - that run in the time specified or longer will be logged with + that causes a statement to be logged. All SQL statements + that run for the time specified or longer will be logged with their duration. Setting this to zero will print all queries and their durations. Minus-one (the default) - disables this. For example, if you set it to + disables the feature. For example, if you set it to 250 then all SQL statements that run 250ms or longer will be logged. Enabling this option can be useful in tracking down unoptimized queries in your applications. @@ -2016,12 +2096,13 @@ SET ENABLE_SEQSCAN TO OFF; Runs the server silently. If this option is set, the server will automatically run in background and any controlling - terminals are disassociated. Thus, no messages are written to - standard output or standard error (same effect as - postmaster's option). Unless - syslog logging is enabled, using this option - is discouraged because it makes it impossible to see error - messages. + terminals are disassociated (same effect as + postmaster's option). + The server's standard output and standard error are redirected + to /dev/null, so any messages sent to them will be lost. + Unless syslog logging is selected or + redirect_stderr is enabled, using this option + is discouraged because it makes it impossible to see error messages. @@ -2188,11 +2269,13 @@ SET ENABLE_SEQSCAN TO OFF; below - anything else that looks like an escape is ignored. Other characters are copied straight to the log line. Some escapes are only recognised by session processes, and do not apply to - processes without controlling sessions. Syslog produces its own + background processes such as the postmaster. Syslog + produces its own timestamp and process ID information, so you probably do not want to use those escapes if you are using syslog. This option can only be set at server start or in the - postgresql.conf configuration file. + postgresql.conf configuration file. + @@ -2238,7 +2321,7 @@ SET ENABLE_SEQSCAN TO OFF; %c Session ID. A unique identifier for each session. It is 2 4-byte hexadecimal numbers (without leading zeros) - separated by a dot. The numbers + separated by a dot. The numbers are the Session Start Time and the Process ID, so this can also be used as a space saving way of printing these items. Yes @@ -2258,7 +2341,7 @@ SET ENABLE_SEQSCAN TO OFF; %x Does not produce any output, but tells non-session processes to stop at this point in the string. Ignored by - session backends. + session processes. No @@ -2277,7 +2360,7 @@ SET ENABLE_SEQSCAN TO OFF; log_statement (string) - Controls which SQL statement are logged. Valid values are + Controls which SQL statements are logged. Valid values are all, ddl, mod, and none. ddl logs all data definition commands like CREATE, ALTER, and @@ -2571,8 +2654,7 @@ SET ENABLE_SEQSCAN TO OFF; Aborts any statement that takes over the specified number of - milliseconds. A value of zero turns off the timer, which is - the default value. + milliseconds. A value of zero (the default) turns off the limitation. @@ -2760,28 +2842,28 @@ SET ENABLE_SEQSCAN TO OFF; If a dynamically loadable module needs to be opened and the - specified name does not have a directory component (i.e. the + file name specified in the CREATE FUNCTION or + LOAD command + does not have a directory component (i.e. the name does not contain a slash), the system will search this - path for the specified file. (The name that is used is the - name specified in the CREATE FUNCTION or - LOAD command.) + path for the required file. The value for dynamic_library_path has to be a - list of absolute directory names separated by colon or, in windows - environments with semi-colon. If a directory name starts - with the special value $libdir, the + list of absolute directory paths separated by colons (or semi-colons + on Windows). If a list element starts + with the special string $libdir, the compiled-in PostgreSQL package - library directory is substituted. This where the modules - provided by the PostgreSQL - distribution are installed. (Use pg_config - --pkglibdir to print the name of this directory.) For - example: + library directory is substituted for $libdir. This + is where the modules provided by the standard + PostgreSQL distribution are installed. + (Use pg_config --pkglibdir to find out the name of + this directory.) For example: dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' - or, in a windows environment: + or, in a Windows environment: dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' @@ -2923,15 +3005,15 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' This controls whether CREATE TABLE and CREATE TABLE AS will include OIDs in newly-created tables, if neither WITH OIDS - or WITHOUT OIDS have been specified. It - also determines whether OIDs will be included in the table + nor WITHOUT OIDS have been specified. It + also determines whether OIDs will be included in tables created by SELECT INTO. In PostgreSQL &version; default_with_oids defaults to true. This is also the behavior of previous versions of PostgreSQL. However, assuming that tables will contain OIDs by default is not - encouraged. Therefore, this option will default to false in a + encouraged. This option will probably default to false in a future release of PostgreSQL. @@ -2940,7 +3022,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' this option should left enabled. To ease compatibility with future versions of PostgreSQL, this option should be disabled, and applications that require OIDs - on certain tables should explictely specify WITH + on certain tables should explicitly specify WITH OIDS when issuing the CREATE TABLE statements for the tables in question. @@ -2963,7 +3045,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' = expr) are treated as expr IS NULL, that is, they return true if expr evaluates to the null value, - and false otherwise. The correct behavior of + and false otherwise. The correct SQL-spec-compliant behavior of expr = NULL is to always return null (unknown). Therefore this option defaults to off. @@ -2983,8 +3065,8 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' - Note that this option only affects the literal = - operator, not other comparison operators or other expressions + Note that this option only affects the exact form = NULL, + not other comparison operators or other expressions that are computationally equivalent to some expression involving the equals operator (such as IN). Thus, this option is not a general fix for bad programming. @@ -3004,7 +3086,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' Compiled-in Options - The following options are available read-only, and are determined + The following parameters are read-only, and are determined when PostgreSQL is compiled. As such, they have been excluded from the sample postgresql.conf file. These options determine @@ -3084,11 +3166,10 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' Customized Options - The following was designed to allow options not normally known to - PostgreSQL to be declared in the posgresql.conf - file and/or manipulated using the SET in a controlled - manner so that add-on modules to the postgres proper (such as lanugage - mappings for triggers and functions) can be configured in a unified way. + This feature was designed to allow options not normally known to + PostgreSQL to be added by add-on modules + (such as procedural languages). This allows add-on modules to be + configured in the standard ways. @@ -3098,33 +3179,13 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' custom_variable_classes - This variable specifies one or several classes to be used for custom - variables. A custom variable is a variable not normally known to - the PostgreSQL proper but used by some add - on module. - - - - Aribtrary variables can be defined for each class specified here. Those - variables will be treated as placeholders and have no meaning until the - module that defines them is loaded. When a module for a specific class is - loaded, it will add the proper variable definitions for the class - associated with it, convert any placeholder values according to those - definitions, and issue warnings for any placeholders that then remains. - - - - Here is an example what custom variables might look like: - - -custom_variable_class = 'plr,pljava' -plr.foo = '/usr/lib/R' -pljava.baz = 1 -plruby.var = true <== this one would generate an error - - - - + This variable specifies one or several class names to be used for + custom variables, in the form of a comma-separated list. A custom + variable is a variable not normally known + to PostgreSQL proper but used by some + add-on module. Such variables must have names consisting of a class + name, a dot, and a variable name. custom_variable_classes + specifies all the class names in use in a particular installation. This option can only be set at server start or in the postgresql.conf configuration file. @@ -3132,7 +3193,34 @@ plruby.var = true <== this one would generate an error - + + + The difficulty with setting custom variables in + postgresql.conf is that the file must be read before add-on + modules have been loaded, and so custom variables would ordinarily be + rejected as unknown. When custom_variable_classes is set, + the server will accept definitions of arbitrary variables within each + specified class. These variables will be treated as placeholders and + will have no function until the module that defines them is loaded. When a + module for a specific class is loaded, it will add the proper variable + definitions for its class name, convert any placeholder + values according to those definitions, and issue warnings for any + placeholders of its class that remain (which presumably would be + misspelled configuration variables). + + + + Here is an example of what postgresql.conf might contain + when using custom variables: + + +custom_variable_classes = 'plr,pljava' +plr.path = '/usr/lib/R' +pljava.foo = 1 +plruby.bar = true # generates error, unknown class name + + + Developer Options @@ -3166,6 +3254,17 @@ plruby.var = true <== this one would generate an error + + debug_shared_buffers (integer) + + + Number of seconds between ARC reports. + If set greater than zero, emit ARC statistics to the log every so many + seconds. Zero (the default) disables reporting. + + + + pre_auth_delay (integer) diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 696fc53c9d..09ccdb2fb8 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -70,17 +70,6 @@ #maintenance_work_mem = 16384 # min 1024, size in KB #max_stack_depth = 2048 # min 100, size in KB -#vacuum_cost_page_hit = 1 # 0-10000 credits -#vacuum_cost_page_miss = 10 # 0-10000 credits -#vacuum_cost_page_dirty = 20 # 0-10000 credits -#vacuum_cost_limit = 200 # 0-10000 credits -#vacuum_cost_delay = 0 # 0-1000 milliseconds - -# - Background writer - -#bgwriter_delay = 200 # 10-5000 milliseconds -#bgwriter_percent = 1 # 0-100% of dirty buffers -#bgwriter_maxpages = 100 # 1-1000 buffers max at once - # - Free Space Map - #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each @@ -91,6 +80,20 @@ #max_files_per_process = 1000 # min 25 #preload_libraries = '' +# - Cost-Based Vacuum Delay - + +#vacuum_cost_delay = 0 # 0-1000 milliseconds +#vacuum_cost_page_hit = 1 # 0-10000 credits +#vacuum_cost_page_miss = 10 # 0-10000 credits +#vacuum_cost_page_dirty = 20 # 0-10000 credits +#vacuum_cost_limit = 200 # 0-10000 credits + +# - Background writer - + +#bgwriter_delay = 200 # 10-5000 milliseconds +#bgwriter_percent = 1 # 1-100% of dirty buffers +#bgwriter_maxpages = 100 # 1-1000 buffers max at once + #--------------------------------------------------------------------------- # WRITE AHEAD LOG @@ -102,14 +105,14 @@ #wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8 # min 4, 8KB each +#commit_delay = 0 # range 0-100000, in microseconds +#commit_siblings = 5 # range 1-1000 # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # 0 is off, in seconds -#commit_delay = 0 # range 0-100000, in microseconds -#commit_siblings = 5 # range 1-1000 # - Archiving - @@ -206,7 +209,7 @@ #log_min_duration_statement = -1 # -1 is disabled, in milliseconds. -#silent_mode = false # DO NOT USE without Syslog! +#silent_mode = false # DO NOT USE without syslog or redirect_stderr # - What to Log - @@ -240,8 +243,6 @@ #log_executor_stats = false #log_statement_stats = false -#debug_shared_buffers = 0 # 0-600 seconds - # - Query/Index Statistics Collector - #stats_start_collector = true -- 2.40.0