Difference between revisions of "Postgresql and shared memory"

From OpenVZ Virtuozzo Containers Wiki
Jump to: navigation, search
(page created, prompted by Kirill's email)
 
(fixed page size, linked to memory page, linked HN and VEs, categorized)
Line 5: Line 5:
 
PostgreSQL uses shared memory. As such, you have to check two things about the VE.
 
PostgreSQL uses shared memory. As such, you have to check two things about the VE.
  
1. UBC's shmpages setting for this VE. This dictates how many pages (8k apiece) are available to the VE, e.g. shmpages=16384 gives a limit of 128 MB of shared memory.
+
1. UBC's shmpages setting for this VE. This dictates how many pages (one page is usually 4K, see [[memory page]] for more details) are available to the VE, e.g. shmpages=16384 gives a limit of 64 MB of shared memory.
  
2. "/sbin/sysctl kernel.shmmax"  This is the VE's self-imposed limit on how much shared memory may be allocated in a single request.
+
2. "/sbin/sysctl kernel.shmmax"  This is the VE's self-imposed limit on how much shared memory may be allocated in a single request, in bytes.
  
 
Get that? Not only does the HN impose a limit on the VE's total shared memory usage, but the VE itself has a setting for the maximum amount in a single chunk.
 
Get that? Not only does the HN impose a limit on the VE's total shared memory usage, but the VE itself has a setting for the maximum amount in a single chunk.
Line 17: Line 17:
 
* 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.
 
* 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.
  
* The sysctl kernel.shmmax value set in the HN/VE0 applies only to the HN, not to VEs.
+
* The sysctl kernel.shmmax value set in the [[HN]]/[[VE0]] applies only to the [[HN]], not to [[VE]]s.
 +
 
 +
[[Category:FAQ]]
 +
[[Category:Kernel]]
 +
[[Category:Troubleshooting]]

Revision as of 00:54, 17 April 2007

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.

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 uses shared memory. As such, you have to check two things about the VE.

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

2. "/sbin/sysctl kernel.shmmax" This is the VE's self-imposed limit on how much shared memory may be allocated in a single request, in bytes.

Get that? Not only does the HN impose a limit on the VE's total shared memory usage, but the VE itself has a setting for the maximum amount in a single chunk.

Some other notes:

  • Note that shared memory is part of the VE's overall memory usage. 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.
  • The sysctl kernel.shmmax value set in the HN/VE0 applies only to the HN, not to VEs.