PG configuration file is a database directory of the postgresql.conf file, to support future versions 8.0 K, M, G such parameters, modify the parameters as long as the PG service to restart OK.
shared_buffers: This is the most important parameters, postgresql shared_buffers and the kernel and the disk through the deal, it should be as large as possible, so that more data in the cache in the shared_buffers. Usually set to 50% of the actual RAM.
kernel.shmmax: the largest single process cache block size set in sysctl.conf, equal to the physical memory
work_mem can be called working memory or the operating memory. The charge of the internal sort and hash operations, the appropriate size to ensure that these operations work_mem in memory. Defined too small, then, sort or hash operation will need to hard disk swap, this will greatly reduce system performance; too large can result in the completion of the operation in memory to reduce the number of the other part of the operation need to swap disks to increase the IO performance degradation. System provides the default value is 1M, in the actual production environment, to analyze data on the system monitor to make the best choice.
There are basically two ways: estimation and calculation. The first one is based on the size and type of traffic, the general statement that running time to a rough estimate. The second way is through the database, monitoring, data collection, and then calculate its size. In short the appropriate size of the system performance is critical.
Maintenance in the actual analysis can explain analyze statements work_mem size is appropriate. Work_mem parameters set in the statement can take advantage of the size of the memory, to improve the efficiency of the statement.
Work_mem memory allocation for database concurrency should also consider the situation, max_connections system determines the maximum number of simultaneous connections. Must take into account both how to adjust work_mem max_connections * work_mem + shared_buffers + temp_buffers + maintenance_work_mem + operating system memory requirements can not exceed the size of the entire RAM, which is very important.
work_mem parameters on the performance of the system is so important, let's meet the real-time operational status of the database was the unlikely, but can be run through the database of the monitoring period, summed up the appropriate data, and then customize a special script, devoted work_mem to modify the size, make it more responsive to stage the status of the system, after all, a good way. 20% of memory can be set to the maximum 1G.
maintenance_work_mem called the maintenance of working memory, mainly for database maintenance operations or statements. These operations as much as possible in memory. Mainly for VACUUM, CREATE INDEX, REINDEX such an operation. VACUUM on the database index, or the larger reconstruction, an appropriate adjustment of the parameters is necessary.
postresql documents suggest features enabled autoacuum case, the parameters can not be configured too large.
effective_cache_size query optimizer assumes that the available disk cache size, the greater the value of the bias in the use of the index, the smaller the value of the bias in the use of sequential scan. For 75% of physical memory.