19.4. Resource Consumption

19.4. Resource Consumption
Prev	Up	Chapter 19. Server Configuration	Home	Next

19.4.1. Memory

shared_buffers (integer) + +

+ Sets the amount of memory the database server uses for shared + memory buffers. The default is typically 128 megabytes + (128MB), but might be less if your kernel settings will + not support it (as determined during initdb). + This setting must be at least 128 kilobytes. However, + settings significantly higher than the minimum are usually needed + for good performance. + If this value is specified without units, it is taken as blocks, + that is BLCKSZ bytes, typically 8kB. + (Non-default values of BLCKSZ change the minimum + value.) + This parameter can only be set at server start. +

+ If you have a dedicated database server with 1GB or more of RAM, a + reasonable starting value for shared_buffers is 25% + of the memory in your system. There are some workloads where even + larger settings for shared_buffers are effective, but + because PostgreSQL also relies on the + operating system cache, it is unlikely that an allocation of more than + 40% of RAM to shared_buffers will work better than a + smaller amount. Larger settings for shared_buffers + usually require a corresponding increase in + max_wal_size, in order to spread out the + process of writing large quantities of new or changed data over a + longer period of time. +

+ On systems with less than 1GB of RAM, a smaller percentage of RAM is + appropriate, so as to leave adequate space for the operating system. +

huge_pages (enum) + +

+ Controls whether huge pages are requested for the main shared memory + area. Valid values are try (the default), + on, and off. With + huge_pages set to try, the + server will try to request huge pages, but fall back to the default if + that fails. With on, failure to request huge pages + will prevent the server from starting up. With off, + huge pages will not be requested. +

+ At present, this setting is supported only on Linux and Windows. The + setting is ignored on other systems when set to + try. +

+ The use of huge pages results in smaller page tables and less CPU time + spent on memory management, increasing performance. For more details about + using huge pages on Linux, see Section 18.4.5. +

+ Huge pages are known as large pages on Windows. To use them, you need to + assign the user right Lock Pages in Memory to the Windows user account + that runs PostgreSQL. + You can use Windows Group Policy tool (gpedit.msc) to assign the user right + Lock Pages in Memory. + To start the database server on the command prompt as a standalone process, + not as a Windows service, the command prompt must be run as an administrator or + User Access Control (UAC) must be disabled. When the UAC is enabled, the normal + command prompt revokes the user right Lock Pages in Memory when started. +

+ Note that this setting only affects the main shared memory area. + Operating systems such as Linux, FreeBSD, and Illumos can also use + huge pages (also known as “super” pages or + “large” pages) automatically for normal memory + allocation, without an explicit request from + PostgreSQL. On Linux, this is called + “transparent huge pages” (THP). That feature has been known to + cause performance degradation with + PostgreSQL for some users on some Linux + versions, so its use is currently discouraged (unlike explicit use of + huge_pages). +

temp_buffers (integer) + +

+ Sets the maximum amount of memory used for temporary buffers within + each database session. These are session-local buffers used only + for access to temporary tables. + If this value is specified without units, it is taken as blocks, + that is BLCKSZ bytes, typically 8kB. + The default is eight megabytes (8MB). + (If BLCKSZ is not 8kB, the default value scales + proportionally to it.) + This setting can be changed within individual + sessions, but only before the first use of temporary tables + within the 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 many 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). +

max_prepared_transactions (integer) + +

+ Sets the maximum number of transactions that can be in the + “prepared” state simultaneously (see PREPARE TRANSACTION). + Setting this parameter to zero (which is the default) + disables the prepared-transaction feature. + This parameter can only be set at server start. +

+ If you are not planning to use prepared transactions, this parameter + should be set to zero to prevent accidental creation of prepared + transactions. If you are using prepared transactions, you will + probably want max_prepared_transactions to be at + least as large as max_connections, so that every + session can have a prepared transaction pending. +

+ When running a standby server, you must set this parameter to the + same or higher value than on the master server. Otherwise, queries + will not be allowed in the standby server. +

work_mem (integer) + +

+ Sets the base maximum amount of memory to be used by a query operation + (such as a sort or hash table) before writing to temporary disk files. + If this value is specified without units, it is taken as kilobytes. + The default value is four megabytes (4MB). + Note that for a complex query, several sort or hash operations might be + running in parallel; each operation will generally be allowed + to use as much memory as this value specifies before it starts + to write data into temporary files. Also, several running + sessions could be doing such operations concurrently. + Therefore, 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. +

+ Hash-based operations are generally more sensitive to memory + availability than equivalent sort-based operations. The + memory available for hash tables is computed by multiplying + work_mem by + hash_mem_multiplier. This makes it + possible for hash-based operations to use an amount of memory + that exceeds the usual work_mem base + amount. +

hash_mem_multiplier (floating point) + +

+ Used to compute the maximum amount of memory that hash-based + operations can use. The final limit is determined by + multiplying work_mem by + hash_mem_multiplier. The default value is + 1.0, which makes hash-based operations subject to the same + simple work_mem maximum as sort-based + operations. +

+ Consider increasing hash_mem_multiplier in + environments where spilling by query operations is a regular + occurrence, especially when simply increasing + work_mem results in memory pressure (memory + pressure typically takes the form of intermittent out of + memory errors). A setting of 1.5 or 2.0 may be effective with + mixed workloads. Higher settings in the range of 2.0 - 8.0 or + more may be effective in environments where + work_mem has already been increased to 40MB + or more. +

maintenance_work_mem (integer) + +

+ Specifies the maximum amount of memory to be used by maintenance + operations, such as VACUUM, CREATE + INDEX, and ALTER TABLE ADD FOREIGN KEY. + If this value is specified without units, it is taken as kilobytes. + It defaults + to 64 megabytes (64MB). 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 might improve + performance for vacuuming and for restoring database dumps. +

+ Note that when autovacuum runs, up to + autovacuum_max_workers times this memory + may be allocated, so be careful not to set the default value + too high. It may be useful to control for this by separately + setting autovacuum_work_mem. +

+ Note that for the collection of dead tuple identifiers, + VACUUM is only able to utilize up to a maximum of + 1GB of memory. +

autovacuum_work_mem (integer) + +

+ Specifies the maximum amount of memory to be used by each + autovacuum worker process. + If this value is specified without units, it is taken as kilobytes. + It defaults to -1, indicating that + the value of maintenance_work_mem should + be used instead. The setting has no effect on the behavior of + VACUUM when run in other contexts. + This parameter can only be set in the + postgresql.conf file or on the server command + line. +

+ For the collection of dead tuple identifiers, autovacuum is only able + to utilize up to a maximum of 1GB of memory, so + setting autovacuum_work_mem to a value higher than + that has no effect on the number of dead tuples that autovacuum can + collect while scanning a table. +

logical_decoding_work_mem (integer) + +

+ Specifies the maximum amount of memory to be used by logical decoding, + before some of the decoded changes are written to local disk. This + limits the amount of memory used by logical streaming replication + connections. It defaults to 64 megabytes (64MB). + Since each replication connection only uses a single buffer of this size, + and an installation normally doesn't have many such connections + concurrently (as limited by max_wal_senders), it's + safe to set this value significantly higher than work_mem, + reducing the amount of decoded changes written to disk. +

max_stack_depth (integer) + +

+ 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. + If this value is specified without units, it is taken as kilobytes. + The default setting is two megabytes (2MB), which + is conservatively small and unlikely to risk crashes. However, + it might 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, + the server will not allow this variable to be set to an unsafe + value. However, not all platforms provide the information, + so caution is recommended in selecting a value. +

shared_memory_type (enum) + +

+ Specifies the shared memory implementation that the server + should use for the main shared memory region that holds + PostgreSQL's shared buffers and other + shared data. Possible values are mmap (for + anonymous shared memory allocated using mmap), + sysv (for System V shared memory allocated via + shmget) and windows (for Windows + shared memory). Not all values are supported on all platforms; the + first supported option is the default for that platform. The use of + the sysv option, which is not the default on any + platform, is generally discouraged because it typically requires + non-default kernel settings to allow for large allocations (see Section 18.4.1). +

dynamic_shared_memory_type (enum) + +

+ Specifies the dynamic shared memory implementation that the server + should use. Possible values are posix (for POSIX shared + memory allocated using shm_open), sysv + (for System V shared memory allocated via shmget), + windows (for Windows shared memory), + and mmap (to simulate shared memory using + memory-mapped files stored in the data directory). + Not all values are supported on all platforms; the first supported + option is the default for that platform. The use of the + mmap option, which is not the default on any platform, + is generally discouraged because the operating system may write + modified pages back to disk repeatedly, increasing system I/O load; + however, it may be useful for debugging, when the + pg_dynshmem directory is stored on a RAM disk, or when + other shared memory facilities are not available. +

19.4.2. Disk

temp_file_limit (integer) + +

+ Specifies the maximum amount of disk space that a process can use + for temporary files, such as sort and hash temporary files, or the + storage file for a held cursor. A transaction attempting to exceed + this limit will be canceled. + If this value is specified without units, it is taken as kilobytes. + -1 (the default) means no limit. + Only superusers can change this setting. +

+ This setting constrains the total space used at any instant by all + temporary files used by a given PostgreSQL process. + It should be noted that disk space used for explicit temporary + tables, as opposed to temporary files used behind-the-scenes in query + execution, does not count against this limit. +

19.4.3. Kernel Resource Usage

max_files_per_process (integer) + +: + 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 actually support if many 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. +

19.4.4. Cost-based Vacuum Delay

+ 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 short period of time, as 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 where it is not + 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 for manually issued + VACUUM commands. To enable it, set the + vacuum_cost_delay variable to a nonzero + value. +

vacuum_cost_delay (floating point) + +

+ The amount of time that the process will sleep + when the cost limit has been exceeded. + If this value is specified without units, it is taken as milliseconds. + The default value is zero, which disables the cost-based vacuum + delay feature. Positive values enable cost-based vacuuming. +

+ When using cost-based vacuuming, appropriate values for + vacuum_cost_delay are usually quite small, perhaps + less than 1 millisecond. While vacuum_cost_delay + can be set to fractional-millisecond values, such delays may not be + measured accurately on older platforms. On such platforms, + increasing VACUUM's throttled resource consumption + above what you get at 1ms will require changing the other vacuum cost + parameters. You should, nonetheless, + keep vacuum_cost_delay as small as your platform + will consistently measure; large delays are not helpful. +

vacuum_cost_page_hit (integer) + +

+ 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. +

vacuum_cost_page_miss (integer) + +

+ 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. +

vacuum_cost_page_dirty (integer) + +

+ 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. +

vacuum_cost_limit (integer) + +

+ 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. +

19.4.5. Background Writer

+ There is a separate server + process called the background writer, whose function + is to issue writes of “dirty” (new or modified) shared + buffers. When the number of clean shared buffers appears to be + insufficient, the background writer writes some dirty buffers to the + file system and marks them as clean. This reduces the likelihood + that server processes handling user queries will be unable to find + clean buffers and have to write dirty buffers themselves. + However, the background writer does cause a net overall + increase in I/O load, because while a repeatedly-dirtied page might + otherwise be written only once per checkpoint interval, the + background writer might write it several times as it is dirtied + in the same interval. The parameters discussed in this subsection + 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). It then sleeps for + the length of bgwriter_delay, and repeats. + When there are no dirty buffers in the + buffer pool, though, it goes into a longer sleep regardless of + bgwriter_delay. + If this value is specified without units, it is taken as milliseconds. + 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 + might 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. +
bgwriter_lru_maxpages (integer) + +: + In each round, no more than this many buffers will be written + by the background writer. Setting this to zero disables + background writing. (Note that checkpoints, which are managed by + a separate, dedicated auxiliary process, are unaffected.) + The default value is 100 buffers. + This parameter can only be set in the postgresql.conf + file or on the server command line. +
bgwriter_lru_multiplier (floating point) + +: + The number of dirty buffers written in each round is based on the + number of new buffers that have been needed by server processes + during recent rounds. The average recent need is multiplied by + bgwriter_lru_multiplier to arrive at an estimate of the + number of buffers that will be needed during the next round. Dirty + buffers are written until there are that many clean, reusable buffers + available. (However, no more than bgwriter_lru_maxpages + buffers will be written per round.) + Thus, a setting of 1.0 represents a “just in time” policy + of writing exactly the number of buffers predicted to be needed. + Larger values provide some cushion against spikes in demand, + while smaller values intentionally leave writes to be done by + server processes. + The default is 2.0. + This parameter can only be set in the postgresql.conf + file or on the server command line. +
bgwriter_flush_after (integer) + +: + Whenever more than this amount of data has + been written by the background writer, attempt to force the OS to issue these + writes to the underlying storage. Doing so will limit the amount of + dirty data in the kernel's page cache, reducing the likelihood of + stalls when an fsync is issued at the end of a checkpoint, or when + the OS writes data back in larger batches in the background. Often + that will result in greatly reduced transaction latency, but there + also are some cases, especially with workloads that are bigger than + shared_buffers, but smaller than the OS's page + cache, where performance might degrade. This setting may have no + effect on some platforms. + If this value is specified without units, it is taken as blocks, + that is BLCKSZ bytes, typically 8kB. + The valid range is between + 0, which disables forced writeback, and + 2MB. The default is 512kB on Linux, + 0 elsewhere. (If BLCKSZ is not 8kB, + the default and maximum values scale proportionally to it.) + This parameter can only be set in the postgresql.conf + file or on the server command line. +

+ Smaller values of bgwriter_lru_maxpages and + bgwriter_lru_multiplier 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. +

19.4.6. Asynchronous Behavior

effective_io_concurrency (integer) + +

+ Sets the number of concurrent disk I/O operations that + PostgreSQL expects can be executed + simultaneously. Raising this value will increase the number of I/O + operations that any individual PostgreSQL session + attempts to initiate in parallel. The allowed range is 1 to 1000, + or zero to disable issuance of asynchronous I/O requests. Currently, + this setting only affects bitmap heap scans. +

+ For magnetic drives, a good starting point for this setting is the + number of separate + drives comprising a RAID 0 stripe or RAID 1 mirror being used for the + database. (For RAID 5 the parity drive should not be counted.) + However, if the database is often busy with multiple queries issued in + concurrent sessions, lower values may be sufficient to keep the disk + array busy. A value higher than needed to keep the disks busy will + only result in extra CPU overhead. + SSDs and other memory-based storage can often process many + concurrent requests, so the best value might be in the hundreds. +

+ Asynchronous I/O depends on an effective posix_fadvise + function, which some operating systems lack. If the function is not + present then setting this parameter to anything but zero will result + in an error. On some operating systems (e.g., Solaris), the function + is present but does not actually do anything. +

+ The default is 1 on supported systems, otherwise 0. This value can + be overridden for tables in a particular tablespace by setting the + tablespace parameter of the same name (see + ALTER TABLESPACE). +

maintenance_io_concurrency (integer) + +

+ Similar to effective_io_concurrency, but used + for maintenance work that is done on behalf of many client sessions. +

+ The default is 10 on supported systems, otherwise 0. This value can + be overridden for tables in a particular tablespace by setting the + tablespace parameter of the same name (see + ALTER TABLESPACE). +

max_worker_processes (integer) + +

+ Sets the maximum number of background processes that the system + can support. This parameter can only be set at server start. The + default is 8. +

+ When running a standby server, you must set this parameter to the + same or higher value than on the master server. Otherwise, queries + will not be allowed in the standby server. +

+ When changing this value, consider also adjusting + max_parallel_workers, + max_parallel_maintenance_workers, and + max_parallel_workers_per_gather. +

max_parallel_workers_per_gather (integer) + +

+ Sets the maximum number of workers that can be started by a single + Gather or Gather Merge node. + Parallel workers are taken from the pool of processes established by + max_worker_processes, limited by + max_parallel_workers. Note that the requested + number of workers may not actually be available at run time. If this + occurs, the plan will run with fewer workers than expected, which may + be inefficient. The default value is 2. Setting this value to 0 + disables parallel query execution. +

+ Note that parallel queries may consume very substantially more + resources than non-parallel queries, because each worker process is + a completely separate process which has roughly the same impact on the + system as an additional user session. This should be taken into + account when choosing a value for this setting, as well as when + configuring other settings that control resource utilization, such + as work_mem. Resource limits such as + work_mem are applied individually to each worker, + which means the total utilization may be much higher across all + processes than it would normally be for any single process. + For example, a parallel query using 4 workers may use up to 5 times + as much CPU time, memory, I/O bandwidth, and so forth as a query which + uses no workers at all. +

+ For more information on parallel query, see + Chapter 15. +

max_parallel_maintenance_workers (integer) + +

+ Sets the maximum number of parallel workers that can be + started by a single utility command. Currently, the parallel + utility commands that support the use of parallel workers are + CREATE INDEX only when building a B-tree index, + and VACUUM without FULL + option. Parallel workers are taken from the pool of processes + established by max_worker_processes, limited + by max_parallel_workers. Note that the requested + number of workers may not actually be available at run time. + If this occurs, the utility operation will run with fewer + workers than expected. The default value is 2. Setting this + value to 0 disables the use of parallel workers by utility + commands. +

+ Note that parallel utility commands should not consume + substantially more memory than equivalent non-parallel + operations. This strategy differs from that of parallel + query, where resource limits generally apply per worker + process. Parallel utility commands treat the resource limit + maintenance_work_mem as a limit to be applied to + the entire utility command, regardless of the number of + parallel worker processes. However, parallel utility + commands may still consume substantially more CPU resources + and I/O bandwidth. +

max_parallel_workers (integer) + +

+ Sets the maximum number of workers that the system can support for + parallel operations. The default value is 8. When increasing or + decreasing this value, consider also adjusting + max_parallel_maintenance_workers and + max_parallel_workers_per_gather. + Also, note that a setting for this value which is higher than + max_worker_processes will have no effect, + since parallel workers are taken from the pool of worker processes + established by that setting. +

backend_flush_after (integer) + +

+ Whenever more than this amount of data has + been written by a single backend, attempt to force the OS to issue + these writes to the underlying storage. Doing so will limit the + amount of dirty data in the kernel's page cache, reducing the + likelihood of stalls when an fsync is issued at the end of a + checkpoint, or when the OS writes data back in larger batches in the + background. Often that will result in greatly reduced transaction + latency, but there also are some cases, especially with workloads + that are bigger than shared_buffers, but smaller + than the OS's page cache, where performance might degrade. This + setting may have no effect on some platforms. + If this value is specified without units, it is taken as blocks, + that is BLCKSZ bytes, typically 8kB. + The valid range is + between 0, which disables forced writeback, + and 2MB. The default is 0, i.e., no + forced writeback. (If BLCKSZ is not 8kB, + the maximum value scales proportionally to it.) +

old_snapshot_threshold (integer) + +

+ Sets the minimum amount of time that a query snapshot can be used + without risk of a “snapshot too old” error occurring + when using the snapshot. Data that has been dead for longer than + this threshold is allowed to be vacuumed away. This can help + prevent bloat in the face of snapshots which remain in use for a + long time. To prevent incorrect results due to cleanup of data which + would otherwise be visible to the snapshot, an error is generated + when the snapshot is older than this threshold and the snapshot is + used to read a page which has been modified since the snapshot was + built. +

+ If this value is specified without units, it is taken as minutes. + A value of -1 (the default) disables this feature, + effectively setting the snapshot age limit to infinity. + This parameter can only be set at server start. +

+ Useful values for production work probably range from a small number + of hours to a few days. Small values (such as 0 or + 1min) are only allowed because they may sometimes be + useful for testing. While a setting as high as 60d is + allowed, please note that in many workloads extreme bloat or + transaction ID wraparound may occur in much shorter time frames. +

+ When this feature is enabled, freed space at the end of a relation + cannot be released to the operating system, since that could remove + information needed to detect the “snapshot too old” + condition. All space allocated to a relation remains associated with + that relation for reuse only within that relation unless explicitly + freed (for example, with VACUUM FULL). +

+ This setting does not attempt to guarantee that an error will be + generated under any particular circumstances. In fact, if the + correct results can be generated from (for example) a cursor which + has materialized a result set, no error will be generated even if the + underlying rows in the referenced table have been vacuumed away. + Some tables cannot safely be vacuumed early, and so will not be + affected by this setting, such as system catalogs. For such tables + this setting will neither reduce bloat nor create a possibility + of a “snapshot too old” error on scanning. +

Prev	Up	Next
19.3. Connections and Authentication	Home	19.5. Write Ahead Log