Difference between revisions of "Postgresql and shared memory"

From OpenVZ Virtuozzo Containers Wiki
Jump to: navigation, search
m (Robot: Automated text replacement (-VE +container))
 
Line 1: Line 1:
I tried running PostgreSQL as one of my first experiments, and hit upon some nuances of shared memory, which may be helpful to relate here.
+
When running PostgreSQL or Oracle XE (APEX), some nuances of shared memory may arise.
  
One of the easiest ways to increase Postgres's performance is to turn up the shared_buffers parameter in the postgresql.conf file; this is basically the amount of shared memory which the postmaster will use for buffering everything: table data, indexes, etc. The default value is really small, and if you have RAM to spare you may want to crank it up to 128MB (that's 16384 shared buffers, for pgsql 8.1 and earlier) or even more. But if it then complains that it couldn't allocate the shared memory, this page explains why.
+
==== PostgreSQL ====
 +
One of the easiest ways to increase Postgres's performance is to turn up the shared_buffers parameter in the postgresql.conf file. This is basically the amount of shared memory which the postmaster will use for buffering everything: table data, indexes, etc. The default value is really small, and if you have RAM to spare you may want to crank it up to 128MB (16384 shared buffers, for pgsql 8.1 and earlier) or even more. After changing shared_buffers, it might complain that it couldn't allocate the shared memory, even though plenty of memory exists.
  
PostgreSQL uses shared memory. As such, you have to check two things about the container.
+
==== Oracle XE ====
 +
A similar situation may occur when installing Oracle XE. The installation appears to succeed, but only the listener is running. No database instance is running. The database directory (/usr/lib/oracle/xe/oradata/XE) is empty. The log file /usr/lib/oracle/xe/app/oracle/admin/XE/bdump/alert_XE.log contains only a couple of lines that indicate success (but there should be many lines of output). Files in /usr/lib/oracle/xe/app/oracle/admin/XE/udump have an odd error "skgm warning: ENOSPC creating segment of size..." and suggest a change to the shared memory configuration, even though the shared memory values are what Oracle recommends. During installation, Oracle will add entries to /etc/sysctl.conf to set shared memory parameters.
  
1. UBC's shmpages setting for this container. This dictates how many pages (one page is usually 4K, see [[memory page]] for more details) are available to the container, e.g. shmpages=16384 gives a limit of 64 MB of shared memory.
+
==== Shared Memory ====
 +
There are two things that control shared memory in a container.
  
2. "/sbin/sysctl kernel.shmmax"  This is the container's self-imposed limit on how much shared memory may be allocated in a single request, in bytes.
+
1. The shmpages setting for this container (check [[UBC]]). This dictates how many pages (one page is usually 4K, see [[memory page]] for more details) are available to the container, e.g. shmpages=16384 gives a limit of 64 MB of shared memory.
  
Get that? Not only does the HN impose a limit on the container's total shared memory usage, but the container itself has a setting for the maximum amount in a single chunk.
+
2. "/sbin/sysctl kernel.shmmax"  This is the container's self-imposed limit in bytes of how much shared memory may be allocated in a single request. You may check the shared memory configuration with "ipcs -l".
  
Some other notes:
+
The HN imposes a limit on the container's total shared memory usage through shmpages, and the container itself imposes a limit on the container's total shared memory usage through kernel.shmmax. If the size of shmpages is less than kernel.shmmax, the database will not be able to allocate sufficient memory.
  
* Note that shared memory is part of the container's overall memory usage. It is not a second memory pool.
+
==== Notes ====
 +
* Shared memory is taken from the container's overall memory allocation. It is not a second memory pool.
  
* Note that processes other than PostgreSQL may be using shared memory, and also that shmpages includes stuff other than IPC shared memory (tmpfs, shmem, etc) So don't set shmpages to exactly the amount you want to give Postgres.
+
* Other processes may be using shared memory, and shmpages includes things other than IPC shared memory (tmpfs, shmem, etc.). If this is the case for your container, set shmpages higher than the database requires.
  
 
* The sysctl kernel.shmmax value set in the [[HN]]/[[CT0]] applies only to the [[HN]], not to [[container]]s.
 
* The sysctl kernel.shmmax value set in the [[HN]]/[[CT0]] applies only to the [[HN]], not to [[container]]s.

Latest revision as of 04:06, 14 February 2010

When running PostgreSQL or Oracle XE (APEX), some nuances of shared memory may arise.

PostgreSQL[edit]

One of the easiest ways to increase Postgres's performance is to turn up the shared_buffers parameter in the postgresql.conf file. This is basically the amount of shared memory which the postmaster will use for buffering everything: table data, indexes, etc. The default value is really small, and if you have RAM to spare you may want to crank it up to 128MB (16384 shared buffers, for pgsql 8.1 and earlier) or even more. After changing shared_buffers, it might complain that it couldn't allocate the shared memory, even though plenty of memory exists.

Oracle XE[edit]

A similar situation may occur when installing Oracle XE. The installation appears to succeed, but only the listener is running. No database instance is running. The database directory (/usr/lib/oracle/xe/oradata/XE) is empty. The log file /usr/lib/oracle/xe/app/oracle/admin/XE/bdump/alert_XE.log contains only a couple of lines that indicate success (but there should be many lines of output). Files in /usr/lib/oracle/xe/app/oracle/admin/XE/udump have an odd error "skgm warning: ENOSPC creating segment of size..." and suggest a change to the shared memory configuration, even though the shared memory values are what Oracle recommends. During installation, Oracle will add entries to /etc/sysctl.conf to set shared memory parameters.

Shared Memory[edit]

There are two things that control shared memory in a container.

1. The shmpages setting for this container (check UBC). This dictates how many pages (one page is usually 4K, see memory page for more details) are available to the container, e.g. shmpages=16384 gives a limit of 64 MB of shared memory.

2. "/sbin/sysctl kernel.shmmax" This is the container's self-imposed limit in bytes of how much shared memory may be allocated in a single request. You may check the shared memory configuration with "ipcs -l".

The HN imposes a limit on the container's total shared memory usage through shmpages, and the container itself imposes a limit on the container's total shared memory usage through kernel.shmmax. If the size of shmpages is less than kernel.shmmax, the database will not be able to allocate sufficient memory.

Notes[edit]

  • Shared memory is taken from the container's overall memory allocation. It is not a second memory pool.
  • Other processes may be using shared memory, and shmpages includes things other than IPC shared memory (tmpfs, shmem, etc.). If this is the case for your container, set shmpages higher than the database requires.
  • The sysctl kernel.shmmax value set in the HN/CT0 applies only to the HN, not to containers.