WWWTech

In the upcoming release PostgreSQL will support huge pages. When a program reserves memory normally this will be done in chunks of 4kb, for performance reasons. Since a program's address space is virtual the CPU and the OS have to translate virtual addresses to physical ones. This is done via a calculation (see Wikipedia for details). Since memory is accessed very often, this calculation is cached in a buffer, the Translation Lookaside Buffer, short TLB. Since this buffer can contain only a limited number of entries using large chunks of memory, especially since 64bit address rooms, can lead to serious performance hits. To counter this modern architectures are able to use larger pages, for example x86 can use pages of 2MB. While a memory segment of 1GB normally would require 262144 pages (1GB / 4KB), with huge pages of 2MB it would require 512 pages. A significant decrease of pages and thus the performance hit for address translation is much lower.

PostgreSQL will support this feature in 9.4. There will be a new configuration option huge_tlb_pages with the possible values on, off and try. While off will turn off this feature, on will enforce this feature. try will try to use huge pages and fall back to traditional 4k pages when failing to.

Keep in mind that you will need to configure the system to allocate huge pages. They can't be moved to swap, so the system will have to allocate them before you can use them:

$ head -1 /path/to/data/directory/postmaster.pid
4170
$ grep ^VmPeak /proc/4170/status
VmPeak:	 6490428 kB

6490428 / 2048 (PAGE_SIZE 2MB) are roughly 3169.154 huge pages, so you will need at least 3170 huge pages:

$ sysctl -w vm.nr_hugepages=3170

Sometimes the kernel is not able to allocate the desired number of huge pages, so it might be necessary to repeat that command or to reboot. Don't forget to add an entry to /etc/sysctl.conf to persist this setting through reboots.