Huge pages and PostgreSQL
Introduction
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 0 |
Or
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/postmaster.pid 3695 |
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)) 141 |
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 |
or
echo never > /sys/kernel/mm/transparent_hugepage/enabled |
Vadim Yatsenko
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