PostgreSQL memory Parameters

The configuration file of PostgreSQL is PostgreSQL in the database directory. CONF file. Versions later than 8.0 support parameters such as K, M, and G. You only need to modify the corresponding parameters and restart the PG service.

Shared_buffers:This is the most important parameter. PostgreSQL uses shared_buffers to deal with the kernel and disk, so it should be as large as possible to make more data cached in shared_buffers. It is generally reasonable to set 10% of the actual Ram, such as 50000 (400 m)

Work_mem:Before pgsql 8.0, it is called sort_mem. When executing the sorting operation, PostgreSQL determines whether to split a large result set into several small temporary files with few size queries based on the size of work_mem. Obviously, the splitting result reduces the sorting speed. Therefore, adding work_mem helps increase the sorting speed. Usually set to 2%-4% of the actual Ram, depending on the size of the result set to be sorted, such as 81920 (80 m)

Effective_cache_size:It is the maximum cache that PostgreSQL can use. This number should be large enough for an independent pgsql server. For example, the 4 GB memory can be set to 3.5 GB (437500)

Maintence_work_mem:The memory defined here is only used in create index, vacuum, and so on, so the usage frequency is not high, but these commands often consume a large amount of resources, therefore, the execution of these commands should be completed as soon as possible: large memory size for maintence_work_mem, such as 512 M (524288)

Max_connections:Generally, max_connections aims to prevent max_connections * work_mem from exceeding the actual memory size. For example, if you set work_mem to 2% of the actual memory size, in extreme cases, if 50 queries have sorting requirements, and 2% of the memory is used, this will lead to the generation of swap, and the system performance will be greatly reduced. Of course, if there is 4 GB memory, the probability of 50 such large queries at the same time should be very small. However, you need to understand the relationship between max_connections and work_mem.