Movatterモバイル変換

Increase the amount of memory allocated by the database for data caching. To do this, use theshared_buffers configuration parameter. For good performance, it is recommended to set this parameter to at least 25% of the available system RAM.

-- shared_buffers = RAM/4

Increase the maximum amount of memory used for temporary buffers within each database session to ensure that all temporary tables are handled correctly. To do this, use thetemp_buffers configuration parameter.

The recommended value should be between32MB and256MB. The exact value depends on the amount of memory on the server and the number of concurrent connections. The more connections and the smaller the amount of memory, the smaller the parameter value should be.

-- temp_buffers = 32..256MB

Increase the maximum amount of memory to be used by a single query operation before writing to temporary disk files. To do this, use thework_mem configuration parameter.

When choosing the parameter value, note that a complex query might perform several sort and hash operations at the same time, and each of them can use the specified amount of memory. Also, several running sessions can perform such operations concurrently. Therefore, the total used memory can be many times greater than the specified value. You can fine-tune the parameter value by monitoring the number of temporary files created in the system.

-- work_mem = RAM/32..64 or 32MB..256MB

Increase the maximum amount of memory to be used by maintenance database operations, such asVACUUM orCREATE INDEX. To do this, use themaintenance_work_mem configuration parameter.

Note that only one of these operations can be executed at a time by a database session. Therefore, you can set this value significantly larger thanwork_mem. Larger values can improve performance for vacuuming and restoring database dumps.

-- maintenance_work_mem = RAM/16..32 or work_mem * 4 or 256MB..4GB

In the case of significant memory fragmentation, specify the following environment variable in the/etc/systemd/system/postgresql.service file:

-- Environment = MALLOC_MMAP_THRESHOLD_= 8192

Decrease the delay between activity rounds for the background writer using thebgwriter_delay configuration parameter. Note that a too high parameter value will increase the load on the checkpoint process and the backend processes, while a too low value will result in the full load of one of the cores.

-- bgwriter_delay = 20ms

Increase values for thebgwriter_lru_multiplier andbgwriter_lru_maxpages configuration parameters that control the number of dirty buffers written by the background writer in each round.

-- bgwriter_lru_multiplier = 4.0-- bgwriter_lru_maxpages = 400

Specify the number of concurrent disk I/O operations that any individualPostgres Pro session attempts to initiate in parallel. To do this, use theeffective_io_concurrency configuration parameter. Currently, this parameter affects bitmap heap scans only.

For magnetic drives, a good starting point for this parameter is the number of separate drives comprising a RAID 0 stripe or RAID 1 mirror used for the database. However, too high parameter values will keep the disk array busy, which results in extra CPU overhead.

SSD disks can often process many concurrent requests, so the best value might be in the hundreds.

-- effective_io_concurrency = 2

Set themax_parallel_workers_per_gather configuration parameter to 0. This disables parallel queries that can be started byGather andGather Merge nodes. Parallel queries may consume very significantly more resources than non-parallel queries.

-- max_parallel_workers_per_gather = 0

Leave thefsync configuration parameter set toon (default). In this case, thePostgres Pro server tries to make sure that updates are physically written to disk by issuing thefsync() system calls. This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.

Although disabling thefsync parameter often leads to a performance benefit, this can result in unrecoverable data corruption in case of a power failure or system crash.

-- fsync = on

Important note: if your RAID has a cache and works in the write-back mode, make sure that the disk controller cache has a valid battery. Otherwise, data written to the cache will be lost in the case of a power failure, and thePostgres Pro server will be unable to recover the data.

Set thesynchronous_commit configuration parameter tooff. This means that thePostgres Pro server does not wait for writing WAL records to disk but returns a success indication to the client.

Unlikefsync, setting this parameter tooff does not create any risk of database inconsistency. An operating system or database crash might only result in some recent transactions being lost. Thus, disabling thesynchronous_commit parameter is useful when high performance is very important.

-- synchronous_commit = off

Increase values for thecheckpoint_timeout,min_wal_size, andmax_wal_size configuration parameters to reduce the frequency of checkpoints. Too frequent checkpoints lead to the significant load on the disk system.

-- checkpoint_timeout = 15min-- min_wal_size = 512MB..4G-- max_wal_size = 2 * min_wal_size

Specify thecommit_delay configuration parameter if the average number of transactions per second (TPS) exceeds 1000. This parameter adds a time delay before a WAL flush is initiated and can improve the group commit throughput.

-- commit_delay = 1000

Specify theeffective_cache_size configuration parameter to define the planner's assumption about the effective size of the disk cache that is available to a single query.

The query optimizer's performance depends on the amount of allocated RAM. A higher value makes it more likely index scans will be used, while a lower value results in using sequential scans.

-- effective_cache_size = RAM - shared_buffers

Specify therandom_page_cost configuration parameter to define the planner's estimate of the cost of a non-sequentially-fetched disk page.

The parameter value is specified relative to theseq_page_cost parameter, which is set to1.0 by default. Reducing therandom_page_cost value will cause the system to prefer index scans to sequential scans. The recommended value depends on the seek time of the disk system. The smaller the seek time, the smaller the parameter value should be (but not less than1.0).

-- random_page_cost = 1.5-2.0 (for RAID) or 1.1-1.3 (for SSD)

Increase the value for thefrom_collapse_limit configuration parameter. The planner will merge sub-queries into upper queries if the resultingFROM list would have no more than this many items. Note that smaller parameter values reduce planning time but can lead to less efficient query plans.

-- from_collapse_limit = 20

Increase the value of thejoin_collapse_limit configuration parameter. The planner will rewrite explicitJOIN constructs (exceptFULL JOINs) into lists ofFROM items whenever a list contains no more than this many items. Note that smaller parameter values reduce planning time but can lead to less efficient query plans.

-- join_collapse_limit = 20

Leave thegeqo configuration parameter set toon (default). This enables the genetic query optimizer (GEQO) that does query planning using the heuristic search.GEQO reduces planning time for complex queries joining many relations but sometimes may produce plans that are less efficient than plans chosen by the regular exhaustive-search algorithm.

To manage the use ofGEQO, specify thegeqo_threshold configuration parameter.GEQO will be used to plan queries that involve at least the specified number ofFROM items.

-- geqo = on-- geqo_threshold = 12

Set thejit configuration parameter tooff to disable Just-in-Time (JIT) compilation. This compilation is beneficial primarily for long-running CPU-bound queries, such as analytical queries. For short queries, the added overhead will be higher than the time thatJIT compilation can save.

-- jit = off

Leave theautovacuum configuration parameter set toon (default) to run the autovacuum launcher daemon. Note that disabling the autovacuum launcher daemon will lead to increasing the database size and significant performance degradation.

-- autovacuum = on

Increase the maximum number of autovacuum processes that may be running at the same time using theautovacuum_max_workers configuration parameter. The more write requests are executed in the system, the more autovacuum processes are required.

-- autovacuum_max_workers = CPU cores/4..2 (but no less than 4)

Decrease the minimum delay between autovacuum rounds using theautovacuum_naptime configuration parameter. If a parameter value is too high, there can be not enough time to clean the required tables. This will lead to increasing the database size and significant performance degradation. However, a too low parameter value leads to the useless load.

-- autovacuum_naptime = 20s

Specify the directory in which to create temporary tables and indexes on temporary tables using thetemp_tablespaces configuration parameter. Typically,1C solutions use a lot of temporary tables. To increase the performance when working with these tables, locate this directory on separate fast disks.

Before this, you should first create the tablespace using theCREATE TABLESPACE command. If characteristics of the target disks differ from the main disks, specify the corresponding value for therandom_page_cost parameter in this command.

-- temp_tablespaces ='tablespace_name'

Set therow_security configuration parameter tooff to raise an error if a query's results are filtered by arow-level security policy.

-- row_security = off

Set thestandard_conforming_strings configuration parameter tooff to enable backslash escapes (\) in all strings.

-- standard_conforming_strings = off

Set theescape_string_warning configuration parameter tooff to switch off the warning about using the backslash escape symbol.

-- escape_string_warning = off