Sunday, February 5, 2017

PostgreSQL tuning

About PostgreSQL

Periodic maintenance

Conventional (disk based)

Number of planned connections.

Expected memory to be available in the OS and PostgreSQL buffer caches, not an allocation!. Used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not.
Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, and 3/4 of memory is a more aggressive but still reasonable amount.

Page caching.
25% of system RAM and no more than 8GB.

Alternative?: higher values may perform much better if you can comfortably fit the working set inside shared_buffers leaving a generous amount of memory left over for other purposes. 

Intermediante results, sort, hashjoins, materialized views, etc.
4 to 64MB. 

Maintenance operations like vacuuming and index creation.
5% of system RAM and no more than 512MB. 

No more than 16MB. 

Deprecated on 9.5. Now max_wal_size.
Better performance on bulk data loads. 

0.9 will decrease the performance impact of checkpointing on a busy system.

Increasing checkpoint_timeout from 5 minutes to a larger value, such as 15 minutes, can reduce the I/O load on your system, especially when using large values for shared_buffers.

random_page_cost and seq_page_cost
Planner parameters.
An estimate for the relative cost of disk seeks.
2 and 1 (0.1 for both values if the DB completely fits in memory).



No comments:

Post a Comment