Consider using huge pages in PostgreSQL
PostgreSQL is currently running on servers with 416GB of RAM, and soon will be running on servers with 624GB of RAM. With the usual OS memory page size, this means a huge number of memory pages to manage. Which in turn creates some pressure on the memory caching algorithms for PostgreSQL and may decrease performance. Or, conversely, may not allow the https://postgresqlco.nf/en/doc/param/shared_buffers?version=9.6 to be raised, allowing to directly cache more data, without a regression due to the large number of pages to manage.
Huge pages can be used, via the configuration parameter huge_pages. Current setting for PostgreSQL is try
. This means that even THP (Transparent Huge Pages) may be used (not currently as /sys/kernel/mm/transparent_hugepage/enabled
is set to madvise
, but if that would change, PostgreSQL may use THPs). THPs typically decrease performance, sometimes notably, for PostgreSQL, and are generally not recommended.
This issue proposes the following:
- Consider using huge pages. Determine the number of huge pages required and set
sysctl
parameters accordingly. - Benchmark PostgreSQL with huge pages, leaving
shared_buffers
unchanged. - Repeat the benchmarks with huge pages and higher values of
shared_buffers
. - Pick the best combination (if any is better than the current one) and deploy to production.
This issue has as a prerequisite that https://gitlab.com/gitlab-com/gl-infra/infrastructure/issues/7817 is done, in order to have a tool to measure adequately the database performance.