PostgreSQL memory allocation

PostgreSQL memory allocation is mainly controlled by shared_buffers, Temp_buffers, Work_mem, maintenance_work_mem parameters.

shared_buffers can also be called a shared buffer, and PostgreSQL will first read the data from the disk into memory, then update it, and then write the data back to disk. The function of shared_buffers is to store data read from disk. The range of settings based on document parameters is generally between 25%~40%. Windows and Linux manage memory differently, and you need to be aware of the settings for shared segment Size (Kernel.shmmax) in Linux.

Temp_buffers is called a temporary buffer, which is used for database session access to temporary table data with a system default value of 8M. You can set this parameter in a separate session, especially if you need to access a larger temporary table, there will be significant performance gains.

Work_memIt can be called working memory or operating memory. It is responsible for internal sort and hash operations, and the appropriate WORK_MEM size ensures that these operations are performed in memory. If the definition is too small, the sort or hash operation will need to swap with the hard disk, which will greatly reduce the performance of the system, so the number of operations that can be done in memory is reduced, and the other parts need to swap with the disk to increase IO performance. System provides the default value is 1M, in the actual production environment, to the system Monitoring data analysis, make the best choice.
There are roughly two ways: estimating methods and calculating methods. The first is a rough estimate based on the size and type of business, general statement run time. The second way is through the monitoring of the database, data collection, and then calculate its size. In short, the right size is critical to the performance of the system.
for WORK_MEM memory allocation, consider the concurrency of the database, Max_ Connections determines the maximum number of concurrent connections for the system. Regardless of how to adjust Work_mem to consider max_connections*work_mem+shared_buffers+temp_buffers+maintenance_work_mem+ It is important that the memory required by the operating system does not exceed the overall RAM size.
work_mem parameter is so important to the performance of the system that it is not likely to adapt to the health of the database in real time, However, it can be a good way to monitor the database operation cycle, summarize the corresponding data, and then customize a special script to modify the size of the work_mem and make it more adaptable to the condition of the system in stages.

Maintenance_work_mem is called maintenance work memory, mainly for database maintenance operations or statements. Try to make these operations in memory. Mainly for Vacuum,create Index,reindex and other operations. When the entire database is vacuum or the larger index is rebuilt, it is necessary to adjust this parameter appropriately.
Postresql document Tip This parameter cannot be configured too large with the Autoacuum feature enabled.

Article turned from: http://www.cnblogs.com/daduxiong/archive/2010/09/21/1832728.html