Huge pages and PostgreSQL

Huge pages and PostgreSQL


Hugepages is a mechanism that allows the Linux kernel to utilize the multiple page size capabilities of modern hardware architectures. Linux uses pages as the basic unit of memory, where physical memory is partitioned and accessed using the basic page unit. The default page size is 4096 Bytes in the x86 architecture. Hugepages allows large amounts of memory to be utilized with a reduced overhead. Linux uses “Translation Lookaside Buffers” (TLB) in the CPU architecture. These buffers contain mappings of virtual memory to actual physical memory addresses. So utilizing a huge amount of physical memory with the default page size consumes the TLB and adds processing overhead.

The Linux kernel is able to set aside a portion of physical memory to be able be addressed using a larger page size. Since the page size is higher, there will be less overhead managing the pages with the TLB. Systems with large amount of memory can be configured to utilize the memory more efficiently by setting aside a portion dedicated for hugepages. The actual size of the page is dependent on the system architecture.

By using huge pages, you can get a noticeable increase in speed and increase responsiveness in applications that actively work with memory.

Huge pages in PostgreSQL

In PostgreSQL 9.4, there is a new GUC called huge_pages, that controls the behaviour. There are 3 values for this GUC: on, off and try. By default, it is set to try, which means PostgreSQL will try to use huge pages, if there are enough huge pages in the kernel, and otherwise will not use it. Off will disable, and on will force using huge pages. When this parameter is set to on, and if kernel does not have enough huge pages, PostgreSQL will fail to start.

vi /var/lib/pgsql/9.4/data/postgresql.conf
huge_page = try

A typical x86 system will have a Huge Page Size of 2048 kBytes. The huge page size may be found by looking at the /proc/meminfo :


cat /proc/meminfo | grep Hugepagesize
Hugepagesize: 2048 kB


To view the current setting using the /proc entry:

cat /proc/sys/vm/nr_hugepages


sysctl vm.nr_hugepages
vm.nr_hugepages = 0

In order to see how many pages you need, first let’s find the pid of postmaster process:

head -n 1 /var/lib/pgsql/9.6/data/

Now, let’s find VmPeak in the proc status:

grep ^VmPeak /proc/3695/status
VmPeak:   288392 kB

MPeak is the peak value of using virtual memory. This option allows you to determine the minimum bar from which you should repulse, but this way of determining is also random.

echo $((288392 / 2048 + 1))

Let’s divide VmPeak by Hugepagesize. It will be 141 huge pages, so PostgreSQL will need at least 141 huge pages. On CentOS 7, the good way to enable this is using /etc/sysctl.d directory:

echo “vm.nr_hugepages=141” >> /etc/sysctl.d/postgresql-9.6.conf

It is necessary to restart the PostgreSQL server

systemctl start postgresql-9.6.service

Use of huge pages can be viewed like this:

grep ^HugePages /proc/meminfo
HugePages_Total:    141
HugePages_Free:     121
HugePages_Rsvd:      10
HugePages_Surp:       0

Transparent Huge Pages

Starting from RHEL6, Transparent HugePages are implemented and enabled by default. They are meant to improve memory management by allowing HugePages to be allocated dynamically by the “khugepaged” kernel thread, rather than at boot time like conventional HugePages.

In certain circumstances, Transparent HugePages have shown to negatively impact PostgreSQL performance. The performance issue with Transparent HugePages typically manifests itself in the form of high system time.  Using tools like vmstat and top, monitor the user, system, wait, and idle CPU utilization. Should the system CPU utilization increase over time, it is possible that the OS is having to spend more time managing the Transparent HugePages and Anonymous memory segments.  Disabling Transparent HugePages can alleviate that bottleneck.

echo never > /sys/kernel/mm/transparent_hugepage/defrag


echo never > /sys/kernel/mm/transparent_hugepage/enabled


Emil Shkolnik
1 Comment
  • Vadim Yatsenko
    Posted at 19:34, November 25, 2017

    That is, postgres always uses the locale that the OS provides to it. A list of supported character sets can be found in the documentation. Sorting rules always define two parameters: LC_COLLATE and LC_CTYPE. They

Post a Reply to Vadim Yatsenko Cancel Reply


This site uses Akismet to reduce spam. Learn how your comment data is processed.