Comments on: Tune Linux Kernel Parameters For PostgreSQL Optimization

By: Ibrar Ahmed

Ibrar Ahmed — Fri, 07 Sep 2018 11:05:48 +0000

In reply to marcio. Most of the time, the suggestion about the tuning is for the single PostgreSQL server on a machine. For specific server tuning, you need to provide detail about the machine along with the type and size of working workload.

By: marcio

marcio — Fri, 31 Aug 2018 18:25:42 +0000

Hi ! Very good tech informations.
Please let me know how I may compute good parameters for a server with 7 instances of Postgres, considering this study ?!

By: Ibrar Ahmed

Ibrar Ahmed — Thu, 30 Aug 2018 10:22:49 +0000

In reply to Nils. Yes, I am doing benchmark, wait for the next post.

By: Nils

Nils — Thu, 30 Aug 2018 00:47:01 +0000

In reply to Hamid Akhtar. That's very valuable information Hamid, I don't actually know how hugepage allocation works within glibc or whatever memory allocator is being used. I think it's worth looking into, but it's a somewhat exotic topic, this thing has increased in impact after the infamous Meltdown bug since depending on Linux version it'll require a TLB flush. Can we do a benchmark Ibrar?

By: Ibrar Ahmed

Ibrar Ahmed — Wed, 29 Aug 2018 17:00:39 +0000

In reply to Hamid Akhtar. Yes, PostgreSQL specifically load the size of Huge Page form /proc/meminfo Hugepagesize.

By: Hamid Akhtar

Hamid Akhtar — Wed, 29 Aug 2018 14:34:26 +0000

I don’t know much about huge pages on AMD64 (or even AMD64 for that matter), but AFAIK, PostgreSQL doesn’t honour multiple huge page sizes. Even if you have groups of multiple huge pages with different sizes, PostgreSQL will pick up the first line of “/proc/meminfo” containing “Hugepagesize”.

It’ll then sscanf the line to pickup huge page size in kb and ignore whatever comes after that. So, I doubt that in case of PostgreSQL, you’ll see any performance gain.

By: Douglas Hunley

Douglas Hunley — Wed, 29 Aug 2018 14:26:38 +0000

For vm.dirty_background_ratio (and vm.dirty_ratio) you can’t set both the ratio *and* the bytes. You set one or the other.

By: Nils

Nils — Wed, 29 Aug 2018 13:02:32 +0000

Forgot to add, to be able to use huge pages the process user must be a member of the group set in /proc/sys/vm/hugetlb_shm_group, I believe this is also missing from the article. With MySQL at least, InnoDB will allocate regular memory when HugeTLB allocation fails, which usually results in running out of memory since the hugepage memory is reserved. A bonus: (Explicit) Huge pages can’t be swapped out.

By: Nils

Nils — Wed, 29 Aug 2018 13:00:18 +0000

Through sysfs, you can use both 2MiB and 1 GiB pages (on amd64, other architectures may support more / different sizes), assuming AMD64 architecture:

/sys/kernel/mm/hugepages/hugepages-1048576kB/
/sys/kernel/mm/hugepages/hugepages-2048kB/

Usually the TLB will support a combination of different huge page sizes, so it may make sense to set both for optimum performance (the thing to optimize for is reducing TLB misses). When looking for information on this topic please keep in mind that it’s different from TRANSPARENT huge pages.