Almost every PostgreSQL I get in touch with is not configured to use huge pages, which is quite a surprise as it can give you a performance boost. Actually it is not the PostgreSQL instance you need to configure but the operating system to provide that. PostgreSQL will use huge pages by default when they are configured and will fall back to normal pages otherwise. The parameter which controls that in PostgreSQL is huge_pages which defaults to “try” leading to the behavior just described: Try to get them, otherwise use normal pages. Lets see how you can do that on RedHat and CentOS. I’ll write another post about how you do that for Debian based distributions shortly.
What you need to know is that RedHat as well as CentOS come with tuned profiles by default. This means kernel parameters and other settings are managed through profiles dynamically and not anymore by adjusting /etc/sysctl (although that works as well). When you are in virtualized environment (VirtualBox in my case) you probably will see something like this:
| 1 2 |
|
Virtual guest is maybe not the best solution for database server as it comes with those settings (especially vm.dirty_ratio and vm.swappiness):
| 1 2 3 4 5 6 7 |
|
What we do at dbi services is to provide our own profile which adjusts the settings better suited for a database server.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
|
What has all this to do with larges pages you might think. Well, tuning profiles can also be used to configure them and for us this is the preferred method because we can do it all in one file. But we before we do that lets look at the PostgreSQL instance:
| 1 2 3 4 5 6 7 8 9 10 11 |
|
As said at the beginning of this post the default behavior of PostgreSQL is to use them if available. The question now is: How can you check if you have huge pages configured on the operating system level? The answer is in the virtual /proc/meminfo file:
| 1 2 3 4 5 6 7 |
|
Alle “HugePages” statistics report a zero so this system definitely is not configured to provide huge pages to PostgreSQL. AnonHugePages is for Transparent Hugepage and it is common recommendation to disable them for database servers. So we have two tasks to complete:
- Disable transparent huge pages
- Configure the system to provide enough huge pages for our PostgreSQL instance
For disabling transparent huge pages we just need to add the following lines to our tuning profile:
| 1 2 |
|
When transparent huge pages are enabled you can see that in the following file:
| 1 2 |
|
Once we switch the profile to our own profile:
| 1 2 3 |
|
… you’ll notice that it is disabled from now on:
| 1 2 |
|
Task one completed. For configuring the operating system to provide huge pages for our PostgreSQL we need to know how many huge pages we require. How do we do that? The procedure is documented in the PostgreSQL documentation. Basically you start your instance and then check how many you would require. In my case, to get the PID of the postmaster process:
| 1 2 |
|
To get the VmPeak for that process:
| 1 2 |
|
As the huge page size is 2MB on my system (which should be default for most systems):
| 1 2 |
|
… we will require at least 344340/2048 huge pages for this PostgreSQL instance:
| 1 2 |
|
All we need to do is to add this to our tuning profile in the “[sysctl]” section:
| 1 2 |
|
Re-set the profile and we’re done:
| 1 2 3 4 5 6 7 8 |
|
This confirms that we now have 170 huge pages of which all of them are free to consume. Now lets configure PostgreSQL to only start when it can get the amount of huge pages required by switching the “huge_pages” parameter to “on” and restart the instance:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
As the instance started all should be fine and we can confirm that by looking at the statistics in /proc/meminfo:
| 1 2 3 4 5 6 7 |
|
You might be surprised that not all (actually only 8) huge pages are used right now but this will change as soon as you put some load on the system:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
|
Hope this helps …
