PostgreSQL Shared Buffers

The default postgresq.conf settings are optimized for systems from 1998... i.e. default memory settings are typically very, very low for modern systems. What this means is that by default, even through you may be running PostgreSQL on a machine with 8GB of RAM, if you haven't explicitly configured it to use this, it will probably default to something like 32MB, which means it will use a lot of disk I/O, when it could instead cache everything to RAM and run much faster.

One of the settings you need to tweak is the shared_buffers, which is recommended to be between 20% and 25% of total available memory. So if you have 8GB of RAM, and most of that is free, then the shared_buffers setting should be around 2GB (instead of the default 16MB or 32MB).

One issue with this though, is that by default most Linux kernels will not allow this. So when you try to restart postgres, you'll get an error along the lines of:

FATAL: could not create shared memory segment: 
DETAIL: Failed system call was shmget(key=5432001, size=1122263040, 03600).
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 1122263040 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared memory configuration.

To fix this, you need to edit /etc/sysctl.conf and add the following two lines at the bottom:

(for 1GB)
kernel.shmall = 262144
kernel.shmmax = 1073741824
(for 2GB)
kernel.shmall = 524288
kernel.shmmax = 2147483648

Save and exit. This will only take effect when you reboot your computer though. To change the shmmax value immediately without a restart, run the following commands:

 (for 1GB)
sysctl -w kernel.shmmax=1073741824
sysctl -w kernel.shmall=262144
(for 2GB)
sysctl -w kernel.shmmax=2147483648
sysctl -w kernel.shmall=524288

Comments

  1. Version 9.3 (release expected end 2013) fixes this problem, is will use the POSIX shared memory.

    ReplyDelete
  2. Why does the request size after setting shared_buffers to 1024MB in postgresql.conf total 1122263040 bytes - greater than the 1Gb (1073741824 bytes) assigned to kernel.shmax?

    ReplyDelete
  3. I've a problem in setting the shared buffer. i'm using 4GB Ram , postgresql 9.1 ,I tried to increase the shared buffer 2048MB, but its thrown the error like " This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 2191433728 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
    If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. "
    I changed the kernel parameter also.
    Thanks for reply

    ReplyDelete

Post a Comment

Popular posts from this blog

Wkhtmltopdf font and sizing issues

Import Google Contacts to Nokia PC Suite

Can't delete last blank page from Word