The issue of optimization becomes very acute when performance slowdowns occur, and there is very little documentation on this topic, especially in Russian. Below, we will attempt to examine the main aspects of PostgreSQL optimization.
Disks and File Systems
If your server has multiple physical disks, you can distribute the database files and transaction log across different disks:
– Stop the server.
– Move the pg_clog and pg_xlog directories, located in the database directory, to another disk.
– Create symbolic links at the old locations.
– Start the server.
Configuration Settings (by editing the postgresql.conf file)
Resource Configuration
shared_buffers
The size of shared memory between PostgreSQL processes, which is needed to perform active operations. Do not specify too large a value, as PostgreSQL also uses the disk cache.
Values:
Medium data volume and 256-512 MB of available memory: 16-32 MB
Large data volume and 1-4 GB of available memory: 64-256 MB
temp_buffers
Buffer for temporary objects, mainly for temporary tables.
Can be set to around 16 MB.
max_prepared_transactions
The number of simultaneously prepared transactions.
For 1C work, this parameter is not significant, as PREPARE TRANSACTION is not used there.
Can be left at the default value – 5.
work_mem
Special memory used for sorting and caching tables for a single query.
When setting this parameter, consider the number of concurrent queries executed at the same time.
For 1-4 GB of memory, it is recommended to set 32-128 MB.
maintenance_work_mem
Memory used for VACUUM, CREATE INDEX, ALTER TABLE, and FOREIGN KEY operations.
A larger value should be set than for work_mem. Too large values will lead to swap usage.
For 1-4 GB of memory, it is recommended to set 128-512 MB.
max_stack_depth
A special stack for the server; ideally, it should match the stack size set in the OS kernel. Setting a value larger than in the kernel may lead to errors.
It is recommended to set 2-4 MB.
max_fsm_relations
The maximum number of tables for which free space will be tracked in the shared free space map. This data is collected by VACUUM.
Set the parameter according to the number of tables in your database with some margin. (Applicable for versions up to 8.4)
max_fsm_pages
The number of blocks for which free space information will be stored. The information is stored in shared memory, requiring 6 bytes per record.
Using this parameter allows avoiding the use of VACUUM FULL for the database; VACUUM will suffice. (Applicable for versions up to 8.4)
This parameter should be at least 16 * max_fsm_relations.
This parameter is set automatically when creating the database using the initdb utility.
It can also be set manually: as an initial approximation, you can take half of the average number of records modified (UPDATE or DELETE) between VACUUM command runs.
You can estimate this value (the database must have been running for some time) by executing:
VACUUM FULL ANALYZE;
NOTICE: number of page slots needed (196272) exceeds max_fsm_pages (153600)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 196272.
max_files_per_process
The maximum number of files that can be opened by a process and its child processes simultaneously.
Reduce this parameter if you encounter the "Too many open files" message during operation.
Writing transaction logs to disk
commit_delay and commit_siblings
The commit_delay value is expressed in microseconds (0 by default). The commit_siblings value is expressed as a count (5 by default).
commit_delay defines the delay between a record entering the transaction log buffer and its flush to disk. If, upon successful transaction completion, at least commit_siblings transactions are active, the record will be delayed for the duration of commit_delay. If another transaction completes within this time, their changes will be flushed to disk together using a single system call. These parameters can improve performance if many small transactions are executed in parallel.
fsync
This parameter controls flushing data from the cache to disk upon transaction completion.
If you set its value to
fsync=off
data will not be written to disk immediately after operations complete. This can significantly increase the speed of insert and update operations, but there is a risk of database corruption in case of a failure (unexpected power loss, OS crash, or disk subsystem failure).
Use this option only if you have reliable UPS (Uninterruptible Power Supply) and software that shuts down the system when battery levels are low.
Do not disable fsync when running PostgreSQL on the Windows platform due to system instability.
wal_sync_method
The method used to force data writes to disk.
If fsync=off, this parameter is not used.
Possible values:
open_datasync – writes data using the open() method with the O_DSYNC parameter
fdatasync – calls the fdatasync() method after each commit
fsync_writethrough – calls fsync() after each commit, ignoring parallel processes
fsync – calls fsync() after each commit
open_sync – writes data using the open() method with the O_SYNC parameter
Not all methods are available on certain platforms. By default, the first available method in the system is selected.
full_page_writes
Set this parameter to off if fsync=off. Otherwise,
When this parameter is on, PostgreSQL writes the contents of each page to the transaction log during the first modification of a table after a checkpoint. This is necessary because pages may be written partially if the OS crashes during the process. This results in new data being mixed with old data on disk. Transaction log record-level logging may not be sufficient to fully recover data after a crash. full_page_writes ensures correct recovery at the cost of increased data written to the transaction log (since the transaction log always starts from a checkpoint. The only way to reduce write volume is by increasing the checkpoint interval).
wal_buffers
The amount of memory used in SHARED MEMORY for maintaining transaction logs.
With available memory of 1-4GB, it is recommended to set 256-1024KB.
Query Optimization
enable_nestloop
Defines the use of the query planner. In some cases, disabling the planner can reduce the execution time of individual queries, sometimes significantly. However, in the vast majority of cases, the planner helps reduce query execution time. It should not be disabled, at least not permanently.
random_page_cost
Sets the planner's estimate of the "cost" of non-sequential data scanning. The default is 4.0. Decreasing this value relative to seq_page_cost will cause the planner to prefer index scans, while increasing it will make index scans "more expensive". You can change both values to alter the ratio of the "cost" of disk I/O operations relative to the "cost" of CPU usage, which will be described by the following parameters.
On servers with fast disk arrays, it makes sense to reduce the initial setting to 3.0, 2.5, or even 2.0. If the active part of your database is much larger than the available RAM, try increasing the parameter value. You can also approach the selection of the optimal value from the perspective of query performance. If the query planner prefers sequential scans over index scans more often than necessary, lower the value. Conversely, if the planner chooses to scan a slow index when it shouldn't, it makes sense to increase the setting. After making changes, carefully test the results on the widest possible set of queries. Never set random_page_cost below 2.0; if you feel that random_page_cost needs to be lowered further, it is more reasonable in this case to change the planner's statistics settings.
cpu_tuple_cost
Sets the planner's estimate of the "cost" of processing each row during query execution. The default is 0.01.
cpu_index_tuple_cost
Sets the planner's estimate of the "cost" of processing each index entry during an index scan operation. The default is 0.005.
cpu_operator_cost
Sets the planner's estimate of the "cost" of executing each operator or function during query execution. The default is 0.0025.
effective_cache_size
Passes information to the query planner about the amount of memory used by the OS for file caching, per query.
You can view this parameter in the OS settings:
For Windows: Task Manager, Performance tab, Physical Memory - System Cache.
For Linux: run the free command, the required value is in the cached column (in kB).
This value should be divided by the number of concurrent queries at any given moment (average number of database connections + buffer).
default_statistics_target
Sets the depth of statistics for tables. Higher values may increase the execution time of the ANALYZE command but will improve query plan generation.
A value of around 100 is recommended.
constraint_exclusion
Enables or disables the planner's use of CONSTRAINTs in tables when building query plans.
It is recommended to set the value to on. If you change CONSTRAINTs on tables, you must update their statistics by running ANALYZE; otherwise, incorrect query plans will be generated.
Statistics Collection
stats_command_string
Whether to pass information about the currently executing command and its start time to the statistics collector.
Set to on
stats_start_collector
Whether to enable statistics collection.
Set to on
stats_row_level, stats_block_level
Whether to collect activity information at the row and block levels, respectively.
Set to
stats_row_level=on
stats_block_level=off
stats_reset_on_server_start
Whether to reset statistics upon server restart.
Set to off
Autovacuum
VACUUM – "garbage collection". VACUUM reclaims space occupied by "dead" data. During normal data operations, PostgreSQL does not physically delete data from tables; this is done by the VACUUM operation.
autovacuum
Whether to enable autovacuum (automatic execution of VACUUM); set to on.
autovacuum_naptime
Pause between autovacuum runs.
Depends on how frequently data in your tables is updated. It may be around 5 minutes; the default is 1 minute.
autovacuum_analyze_threshold
Specifies the minimum number of inserts, updates, or deletes required to trigger an ANALYZE command on any table. This parameter can be changed for individual tables by modifying their storage parameters.
autovacuum_vacuum_threshold
Specifies the minimum number of inserts, updates, or deletes required to trigger a VACUUM command on any table. This parameter can be changed for individual tables by modifying their storage parameters.
Locks
max_locks_per_transaction
Number of locks per transaction: set to approximately 250.
deadlock_timeout
Deadlock detection timeout. Set to approximately 2 seconds.
Possible Error
After modifying the PostgreSQL configuration file, PostgreSQL may fail to start with the following error:
FATAL: could not create shared memory segment:...
Failed system call was shmget(key=5432001, size=140075008, 03600).
This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with a larger SHMMAX. To reduce the request size (currently 140075008 bytes), reduce PostgreSQL's shared_buffers parameter (currently 16384) and/or its max_connections parameter (currently 10).
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is required.
The PostgreSQL documentation contains more information about shared memory configuration.
To fix this, you need to increase the SHMMAX parameter in the system. This can be done as follows (for Linux OS):
Execute the command:
echo 150829120 > /proc/sys/kernel/shmmax
specifying your new value for the parameter (you can find the required value in the PostgreSQL error log). This operation sets the parameter on the fly, but the changes will be lost after a system reboot. To prevent this, add the following line to the /etc/sysctl.conf file:
kernel.shmmax = 150829120
specifying your own value.
Example configuration for a server with 2GB of RAM
/var/lib/pgsql/data/postgresql.conf:
to be done
/etc/sysctl.conf:
to be done
Average configuration
Average configuration for maximum performance. For a specific case, other settings may be more suitable. Carefully study this guide and configure PostgreSQL based on this information.
RAM — memory size
* shared_buffers = 1/8 of RAM or more (but no more than 1/4);
* work_mem = 1/20 of RAM;
* maintenance_work_mem = 1/4 of RAM;
* max_fsm_relations = planned number of tables in databases * 1.5;
* max_fsm_pages = max_fsm_relations * 2000;
* fsync = true;
* wal_sync_method = fdatasync;
* commit_delay = from 10 to 100;
* commit_siblings = from 5 to 10;
* effective_cache_size = 0.9 of the cached value shown by free;
* random_page_cost = 2 for fast CPUs, 4 for slow CPUs;
* cpu_tuple_cost = 0.001 for fast CPUs, 0.01 for slow CPUs;
* cpu_index_tuple_cost = 0.0005 for fast CPUs, 0.005 for slow CPUs;
* autovacuum = on
* autovacuum_vacuum_threshold = 1800
* autovacuum_analyze_threshold = 900
Database maintenance