Databases: Tuning PostgreSQL

Re: Databases: Tuning PostgreSQL

by Pascal Maury -
Number of replies: 0
Picture of Plugin developers
Hello,

I work on loading tests and I look at the configuration of PostgreSQL for Moodle.
First some info:

Soft
Moodle 3.1
PostgreSQL 9.2
php 5.6
Apache 2.4

Server
Virtual Machine
2 vCPUs
12 GB RAM

Currently the server hosts both the web server and the database server.

Goal
60 concurrent connections at least
Before separating the web server and the postgresql server, I would like to fix correctly the PostgreSQL configuration.


Configuring PostgreSQL
I try to set the values ​​for these variables:

max_connections
shared_buffers
effective_cache_size
wal_buffers
checkpoint_segments
work_mem
maintenance_work_mem

What values ​​did you set for theses variables?


I read the page: https://docs.moodle.org/31/en/Performance_recommendations
There are conflicting recommendations with the postgreSQL documentation page (https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) :


> shared_buffers

Docs postgreSQL: If you-have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1/4 of the memory in your system.
Moodle Perf. Recom. : Set shared_buffers to something reasonable. For versions up to 8.1 my testing has shown that peak performance is almost always obtained with buffers < 10000, so if you are using such a version, and have more than 512M of RAM just set shared_buffers to 10,000 (8MB).

With 12 GB RAM, (6GB because the server hosts the 2 services) should I set "1/4 of memory"  (1.5 GB) or 8 MB ?



> effective_cache_size

Docs postgreSQL: 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.

Moodle Perf. Recom. : PostgreSQL will also assume that the operating system is caching its files, so setting effective_cache_size to a reasonable value is also a good idea. A reasonable value will usually be (total RAM - RAM in use by programs). If you are running Linux and leave the system running for a day or two you can look at 'free' and under the 'cached' column you will see what it currently is. Consider taking that number (which is kB) and dividing it by 10 (i.e. allow 20% for other programs cache needs and then divide by 8 to get pages). If you are not using a dedicated database server you will need to decrease that value to account for usage by other programs.

I understand the beginning "A reasonable value will usually be (total RAM - RAM in use by programs)" but not the part "Consider taking that number (which is kB) and dividing it by 10 (i.e. allow 20% for other programs cache needs and then divide by 8 to get pages)."


> wal_buffers

Docs postgreSQL:  Increasing wal_buffers from its tiny default of a small number of kilobytes is helpful for write-heavy systems. Benchmarking generally suggests that just increasing to 1MB is enough for some large systems, and given the amount of RAM in modern servers allocating a full WAL segment (16MB, the useful upper-limit here) is reasonable. Changing wal_buffers requires a database restart.
PostgreSQL 9.1 and later  Starting with PostgreSQL 9.1 wal_buffers defaults to being 1/32 of the size of shared_buffers, with an upper limit of 16MB (reached when shared_buffers=512MB).  PostgreSQL 9.1 also changes the logic for selecting the default wal_sync_method such that on newer Linux kernels, it will still select fdatasync as its method--the same as on older Linux versions

Moodle Perf. Recom. : Wal_buffers = 64

I do not know what to set here neither. The Moodle page says "64" but 64 what? 64KB, 64MB?


What values ​​did you set for theses variables?
Thanks for any others explanations or tips about postgreSQL and Moodle.


Pascal
Average of ratings: -