PostgreSQL and the Linux Kernel

Question

I have a Postgres 9.5 to serve a OpenStreetMap database (about 500GB of data)

I'm a realy newbie to configure the server and just follow these instructions: http://pgtune.leopard.in.ua/

But... after reading the araqnid's answer here I need to know if changing kernel.shmmax is realy important.

My server have 8 cores, a SATA HDD and 64 GB of RAM and running PostgreSQL + Tomcat + Geoserver and sometimes the map generation is painful (it is fine after all image caches generated).

Using htop I can see the cores and the memory running loose and a very high disk I/O.

what is the correct value for the kernel.shmmax and what more can I change?

Install netdata, look at the metrics and you should find the cause of your slows. — ETL, Mar 29 '18 at 13:37

score 3 · Accepted Answer · answered Mar 29 '18 at 20:38

TL;DR: increasing kernel.shmmax is no longer necessary.

PostgreSQL used to need a large kernel.shmmax before version 9.3, as its "shared buffers" were allocated using SysV shared memory.

It used to have this recommendation: https://www.postgresql.org/docs/9.2/static/kernel-resources.html which is now obsolete (9.2 has been EOL'ed in 2017).

The most important shared memory parameter is SHMMAX, the maximum size, in bytes, of a shared memory segment. If you get an error message from shmget like "Invalid argument", it is likely that this limit has been exceeded. The size of the required shared memory segment varies depending on several PostgreSQL configuration parameters, as shown in Table 17-2. (Any error message you might get will include the exact size of the failed allocation request.) You can, as a temporary solution, lower some of those settings to avoid the failure. While it is possible to get PostgreSQL to run with SHMMAX as small as 2 MB, you need considerably more for acceptable performance. Desirable settings are in the hundreds of megabytes to a few gigabytes.

But that's not the version you're using. For 9.5, see the documentation at https://www.postgresql.org/docs/9.5/static/kernel-resources.html :

PostgreSQL requires a few bytes of System V shared memory (typically 48 bytes, on 64-bit platforms) for each copy of the server. On most modern operating systems, this amount can easily be allocated.

Wow, doesn't know about it. thanks. – Alexander Tolkachev Mar 30 '18 at 08:27 — Alexander Tolkachev, Mar 30 '18 at 08:27

score 1 · Answer 2 · answered Mar 29 '18 at 13:30

1

You should change your kernel.shmmax according shared_buffers parameter configured in your postgresql.conf. If kernel.shmmax would be lesser then configured in postgresql config, it just couldn't start.

answered Mar 29 '18 at 13:30

Alexander Tolkachev

4,513
3
14
23

Well ... my server have started. So is it all ok? No tunning needed here? – Magno C Mar 29 '18 at 14:13
@MagnoC, if PostgreSQL after tuning start without problem, so there is no need to tune `shmmax` in sysctl. – Alexander Tolkachev Mar 29 '18 at 14:50
What about this: https://gist.github.com/malkitsingh/737ea34abec5b079a84dd39e3b0dc2d2 ( Item 2 ) ? – Magno C Mar 29 '18 at 15:44

PostgreSQL and the Linux Kernel

2 Answers2

Linked