The default value of wal_buffers is -1. At this time, wal_buffers use shared_buffers, and the size of wal_buffers is 1/32 of shared_buffers.
The default value of autovacuum_work_mem is -1, and the value of maintenance_work_mem is used at this time.
1 Don't use wal_buffers, autovacuum_work_mem
The calculation formula is:
max_connections*work_mem + max_connections*temp_buffers +shared_buffers+(autovacuum_max_workers * maintenance_work_mem）
Assume that the configuration of PostgreSQL is as follows:
max_connections = 100 temp_buffers=32MB work_mem=32MB shared_buffers=19GB autovacuum_max_workers = 3 maintenance_work_mem=1GB #default value64MB
select( (100*(32*1024*1024)::bigint) + (100*(32*1024*1024)::bigint) + (19*(1024*1024*1024)::bigint) + (3 * (1024*1024*1024)::bigint ) )::float8 / 1024 / 1024 / 1024 --output 28.25
At this time, when the pg is fully loaded, it can use up to 28.25GB of memory. When the physical content is 32GB, there is still 3.75GB of memory for the operating system to use.
2 Use wal_buffers, not use autovacuum_work_mem
The calculation formula is:
max_connections*work_mem + max_connections*temp_buffers +shared_buffers+wal_buffers+(autovacuum_max_workers * autovacuum_work_mem）
Assume that the configuration of PostgreSQL is as follows:
max_connections = 100 temp_buffers=32MB work_mem=32MB shared_buffers=19GB wal_buffers=16MB #--the default value of with-wal-segsizeautovacuum_max_workers = 3 maintenance_work_mem=1GB
select( (100*(32*1024*1024)::bigint) + (100*(32*1024*1024)::bigint) + (19*(1024*1024*1024)::bigint) + (16*1024*1024)::bigint + (3 * (1024*1024*1024)::bigint ) )::float8 / 1024 / 1024 / 1024 --output 28.26
At this time, when the PG is fully loaded, it uses up to 28.5GB of memory, the physical content is 32GB, and there is also 3.5GB of memory for the operating system to use.
3 Use wal_buffers and autovacuum_work_mem at the same time [recommended to use]
The calculation formula is:
max_connections*work_mem + max_connections*temp_buffers +shared_buffers+wal_buffers+(autovacuum_max_workers * autovacuum_work_mem）+ maintenance_work_mem
Assume that the configuration of PostgreSQL is as follows:
max_connections = 100 temp_buffers=32MB work_mem=32MB shared_buffers=19GB wal_buffers=262143kb autovacuum_max_workers = 3 autovacuum_work_mem=256MB maintenance_work_mem=2GB
select( (100*(32*1024*1024)::bigint) + (100*(32*1024*1024)::bigint) + (19*(1024*1024*1024)::bigint) + (262143*1024)::bigint + (3 * (256*1024*1024)::bigint ) + ( 2 * (1024*1024*1024)::bigint ) )::float8 / 1024 / 1024 / 1024 --output 28.01
At this time, when the pg loads peak, use up to 28.25GB of memory. When the physical content is 32GB, there is still 3.75GB of memory for the operating system to use. It is recommended that all memory consumption be based on the hardware configuration, that is, this configuration is used.
Supplement: Postgresql memory usage configuration
shared_buffers：This is the most important parameter. Postgresql deals with the kernel and disk through shared_buffers, so it should be as large as possible to allow more data to be cached in shared_buffers. It is generally reasonable to set to 10% of the actual RAM, such as 50000 (400M)
work_mem： Before pgsql 8.0, it was called sort_mem. When postgresql performs sorting operations, it will decide whether to split a large result set into several small temporary files with little sizes based on the size of work_mem. Obviously the result of the split is to slow down the sorting speed. Therefore, increasing work_mem helps to improve the speed of sorting. Usually set to 2% -4% of the actual RAM, depending on the size of the result set to sort, such as 81920(80M)
effective_cache_size：It is the maximum cache that postgresql can use. This number should be large enough for independent pgsql servers. For example, 4G memory can be set to 3.5G (437500)
maintenance_work_mem：The memory defined here is only used when CREATE INDEX, VACUUM, etc., so it is not used very frequently, but these instructions often consume a lot of resources, so these instructions should be executed quickly as soon as possible: give maintenance_work_mem a large memory, such as 512M (524288)
max_connections： Generally, the purpose of max_connections is to prevent max_connections * work_mem from exceeding the actual memory size. For example, if work_mem is set to 2% of the actual memory, in extreme cases, if 50 queries have sorting requirements and all use 2% of the memory, it will lead to swap generation and system performance will be greatly reduced. Of course, if you have 4G of memory, the chance of 50 such large queries appearing at the same time should be very small. However, be clear about the relationship between max_connections and work_mem.
Configuration Host: 32GB
shared_buffers = 1024MB work_mem = 1MB effective_cache_size = 20480MB maintenance_work_mem = 1024MB max_connections = 8000
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.