Postgres shared_buffers memory limit

Classic Servoy
1

Hi PostgreSQL experts

As we have a parser running for days on parsing the swiss railway timetable, we are for quites some time looking for ways to increase speed. As it’s a game between Servoy and PostgreSQl speed – still not clear which one is slowing down most (or both equal .-)

So I experimented with memory settings on Postgres and found, that I can’t go beyond some 230MB for shared buffers (see attachment). Making this value for example 512MB hinders the database to startup. Any idea why there is a limit for this (may be only in our environment). Our server has 16GB of memory.

I am also interested to hear which memory settings influence the speed of the database most?

Thanks and regards,

pg memory.png
pg memory.png1038×932 127 KB

2

Just for info, this are our currently used postgres.conf settings.

Regards,

pg memory current.png
pg memory current.png749×795 92.2 KB

3

Hi Robert,

Sounds like your system shared memory is set too low (something that the EDB installer automatically fixes for you by the way). Apple ships with these settings set very low.
You find these settings in /etc/sysctl.conf.

For reference here are the sysctl.conf settings I have on my server with 16GB:

kern.sysv.shmmax=4294967296
kern.sysv.shmmin=1
kern.sysv.shmmni=256
kern.sysv.shmseg=64
kern.sysv.shmall=1048576

Also for more information on tuning PostgreSQL check out this PostgreSQL Wiki page.

Hope this helps.

4

Hi Robert

Are you saying that the values in /etc/sysctl.conf are limiting the values in postgres.conf?
What are your memory settings in postgres.conf?

Best regards,

PS: We use the installers from William Kyngesbury (http://www.kyngchaos.com/software/postgres), because we use PostgreSQL with PostGIS, QGIS and the qgis mapserver, and William’s package delivers this all coherently in a fine way. As far as I know is EDB for Postgres installation only?

ROCLASI:
Hi Robert,

Sounds like your system shared memory is set too low (something that the EDB installer automatically fixes for you by the way). Apple ships with these settings set very low.
You find these settings in /etc/sysctl.conf.

For reference here are the sysctl.conf settings I have on my server with 16GB:

kern.sysv.shmmax=4294967296

kern.sysv.shmmin=1
kern.sysv.shmmni=256
kern.sysv.shmseg=64
kern.sysv.shmall=1048576



Also for more information on tuning PostgreSQL check out this [PostgreSQL Wiki page](http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server).

Hope this helps.