|
|
| PostgreSQL 8.2.6 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 17. Server Configuration | Fast Forward | Next |
Sets the amount of memory the database server uses for shared memory buffers. The default is typically 32 megabytes (32MB), but may be less if your kernel settings will not support it (as determined during initdb). This setting must be at least 128 kilobytes and at least 16 kilobytes times max_connections. (Non-default values of BLCKSZ change the minimum.) However, settings significantly higher than the minimum are usually needed for good performance. Several tens of megabytes are recommended for production installations. This parameter can only be set at server start.
Increasing this parameter may cause PostgreSQL to request more System V shared memory than your operating system's default configuration allows. See Section 16.4.1 for information on how to adjust those parameters, if necessary.
Sets the maximum number of temporary buffers used by each database session. These are session-local buffers used only for access to temporary tables. The default is eight megabytes (8MB). The setting can be changed within individual sessions, but only up until the first use of temporary tables within a session; subsequent attempts to change the value will have no effect on that session.
A session will allocate temporary buffers as needed up to the limit given by temp_buffers. The cost of setting a large value in sessions that do not actually need a lot of temporary buffers is only a buffer descriptor, or about 64 bytes, per increment in temp_buffers. However if a buffer is actually used an additional 8192 bytes will be consumed for it (or in general, BLCKSZ bytes).
Sets the maximum number of transactions that can be in the "prepared" state simultaneously (see PREPARE TRANSACTION). Setting this parameter to zero disables the prepared-transaction feature. The default is five transactions. This parameter can only be set at server start.
If you are not using prepared transactions, this parameter may as well be set to zero. If you are using them, you will probably want max_prepared_transactions to be at least as large as max_connections, to avoid unwanted failures at the prepare step.
Increasing this parameter may cause PostgreSQL to request more System V shared memory than your operating system's default configuration allows. See Section 16.4.1 for information on how to adjust those parameters, if necessary.
Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files. The value is defaults to one megabyte (1MB). 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 files. Also, several running sessions could be doing such operations concurrently. So the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
Specifies the maximum amount of memory to be used in maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 16 megabytes (16MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings may improve performance for vacuuming and for restoring database dumps.
Specifies the maximum safe depth of the server's execution stack. The ideal setting for this parameter is the actual stack size limit enforced by the kernel (as set by ulimit -s or local equivalent), less a safety margin of a megabyte or so. The safety margin is needed because the stack depth is not checked in every routine in the server, but only in key potentially-recursive routines such as expression evaluation. The default setting is two megabytes (2MB), which is conservatively small and unlikely to risk crashes. However, it may be too small to allow execution of complex functions. Only superusers can change this setting.
Setting max_stack_depth higher than the actual kernel limit will mean that a runaway recursive function can crash an individual backend process. On platforms where PostgreSQL can determine the kernel limit, it will not let you set this variable to an unsafe value. However, not all platforms provide the information, so caution is recommended in selecting a value.
These parameters control the size of the shared free space map, which tracks the locations of unused space in the database. An undersized free space map may cause the database to consume increasing amounts of disk space over time, because free space that is not in the map cannot be re-used; instead PostgreSQL will request more disk space from the operating system when it needs to store new data. The last few lines displayed by a database-wide VACUUM VERBOSE command can help in determining if the current settings are adequate. A NOTICE message is also printed during such an operation if the current settings are too low.
Increasing these parameters may cause PostgreSQL to request more System V shared memory than your operating system's default configuration allows. See Section 16.4.1 for information on how to adjust those parameters, if necessary.
Sets the maximum number of disk pages for which free space will be tracked in the shared free-space map. Six bytes of shared memory are consumed for each page slot. This setting must be at least 16 * max_fsm_relations. The default is chosen by initdb depending on the amount of available memory, and can range from 20k to 200k pages. This parameter can only be set at server start.
Sets the maximum number of relations (tables and indexes) for which free space will be tracked in the shared free-space map. Roughly seventy bytes of shared memory are consumed for each slot. The default is one thousand relations. This parameter can only be set at server start.
Sets the maximum number of simultaneously open files allowed to each server subprocess. The default is one thousand files. If the kernel is enforcing a safe per-process limit, you don't need to worry about this setting. But on some platforms (notably, most BSD systems), the kernel will allow individual processes to open many more files than the system can really support when a large number of processes all try to open that many files. If you find yourself seeing "Too many open files" failures, try reducing this setting. This parameter can only be set at server start.
This variable specifies one or more shared libraries that are to be preloaded at server start. If more than one library is to be loaded, separate their names with commas. For example, '$libdir/mylib' would cause mylib.so (or on some platforms, mylib.sl) to be preloaded from the installation's standard library directory. This parameter can only be set at server start.
PostgreSQL procedural language libraries can be preloaded in this way, typically by using the syntax '$libdir/plXXX' where XXX is pgsql, perl, tcl, or python.
By preloading a shared library, the library startup time is avoided when the library is first used. However, the time to start each new server process may increase slightly, even if that process never uses the library. So this parameter is recommended only for libraries that will be used in most sessions.
Note: On Windows hosts, preloading a library at server start will not reduce the time required to start each new server process; each server process will re-load all preload libraries. However, shared_preload_libraries is still useful on Windows hosts because some shared libraries may need to perform certain operations that only take place at postmaster start (for example, a shared library may need to reserve lightweight locks or shared memory and you can't do that after the postmaster has started).
If a specified library is not found, the server will fail to start.
Every PostgreSQL-supported library has a "magic block" that is checked to guarantee compatibility. For this reason, non-PostgreSQL libraries cannot be loaded in this way.
During the execution of VACUUM and ANALYZE commands, the system maintains an internal counter that keeps track of the estimated cost of the various I/O operations that are performed. When the accumulated cost reaches a limit (specified by vacuum_cost_limit), the process performing the operation will sleep for a while (specified by vacuum_cost_delay). Then it will reset the counter and continue execution.
The intent of this feature is to allow administrators to reduce the I/O impact of these commands on concurrent database 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 commands do not significantly interfere with the ability of the system to perform other database operations. Cost-based vacuum delay provides a way for administrators to achieve this.
This feature is disabled by default. To enable it, set the vacuum_cost_delay variable to a nonzero value.
The length of time, in milliseconds, that the process will sleep when the cost limit has been exceeded. The default value is zero, 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.
The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page. The default value is one.
The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content. The default value is 10.
The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again. The default value is 20.
The accumulated cost that will cause the vacuuming process to sleep. The default value is 200.
Note: 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 limit. To avoid uselessly long delays in such cases, the actual delay is calculated as vacuum_cost_delay * accumulated_balance / vacuum_cost_limit with a maximum of vacuum_cost_delay * 4.
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 subsection can be used to tune the behavior for local needs.
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). It then sleeps for bgwriter_delay milliseconds, and repeats. The default value is 200 milliseconds (200ms). 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 parameter can only be set in the postgresql.conf file or on the server command line.
To reduce the probability that server processes will need to issue their own writes, the background writer tries to write buffers that are likely to be recycled soon. In each round, it examines up to bgwriter_lru_percent of the buffers that are nearest to being recycled, and writes any that are dirty. The default value is 1.0 (1% of the total number of shared buffers). This parameter can only be set in the postgresql.conf file or on the server command line.
In each round, no more than this many buffers will be written as a result of scanning soon-to-be-recycled buffers. The default value is five buffers. This parameter can only be set in the postgresql.conf file or on the server command line.
To reduce the amount of work that will be needed at checkpoint time, the background writer also does a circular scan through the entire buffer pool, writing buffers that are found to be dirty. In each round, it examines up to bgwriter_all_percent of the buffers for this purpose. The default value is 0.333 (0.333% of the total number of shared buffers). With the default bgwriter_delay setting, this will allow the entire shared buffer pool to be scanned about once per minute. This parameter can only be set in the postgresql.conf file or on the server command line.
In each round, no more than this many buffers will be written as a result of the scan of the entire buffer pool. (If this limit is reached, the scan stops, and resumes at the next buffer during the next round.) The default value is five buffers. This parameter can only be set in the postgresql.conf file or on the server command line.
Smaller values of bgwriter_all_percent and bgwriter_all_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 these two values. Similarly, smaller values of bgwriter_lru_percent and bgwriter_lru_maxpages reduce the extra I/O load caused by the background writer, but make it more likely that server processes will have to issue writes for themselves, delaying interactive queries. To disable background writing entirely, set both maxpages values and/or both percent values to zero.