Possible Enable Huge Pages for PostgreSQL Services: A Focused Article
In this article, we will discuss how to enable huge pages for a PostgreSQL v16 server with a 10GB shared buffer. Huge pages are an extension to the standard page size in PostgreSQL, which can improve performance for large databases. By default, PostgreSQL uses 8KB pages, but with huge pages, each page can be up to 2MB.
Understanding Huge Pages
Huge pages are an extension to the standard page size in PostgreSQL. They can improve performance by reducing the number of context switches required when dealing with large data sets. Huge pages are particularly beneficial for databases that frequently access large blocks of data, such as those used in data warehousing or OLAP (Online Analytical Processing) systems.
Checking the Current Huge Page Settings
Before configuring huge pages, it's essential to check the current settings. You can do this by querying the shared_buffers and effective_cache_size parameters in PostgreSQL.
-- Query to check shared_buffers and effective_cache_size
SELECT shared_buffers, effective_cache_size FROM pg_stat_db;
Configuring Huge Pages
To configure huge pages, you need to set the huge_pages parameter in the postgresql.conf file. The default huge page size is 1GB, but you can change this value if needed. In our case, we have a 10GB shared buffer, so we need to allocate 11 huge pages (11 * 1GB = 11GB).
Steps to Configure Huge Pages
- Edit the postgresql.conf file:
-- Edit the postgresql.conf file
sudo vi /etc/postgresql//main/postgresql.conf
- Add or modify the following line:
-- Set the number of huge pages
huge_pages = true
-- Set the huge page size (default is 1GB)
work_mem = 11GB
-- Set the maximum number of huge pages (11 in our case)
max_connections = 1100
- Restart the PostgreSQL server:
-- Restart the PostgreSQL server
sudo systemctl restart postgresql@-main
Verifying the Huge Page Configuration
After configuring huge pages, you can verify the settings by querying the pg_stat_statements table.
-- Query to check the huge pages configuration
SELECT query, pg_size_pretty(total_size) as total_size, pg_size_pretty(huge_size) as huge_size
FROM pg_stat_statements
WHERE query LIKE '%huge%'
ORDER BY total_size DESC
LIMIT 1;