Tuning PostgreSQL
In our tests, the default PostgreSQL settings are not optimized. This page outlines many of the optimization steps we and our customers have used to tune PostgreSQL for use with ListManager.
The suggestions below are provided for illustrative and educational purposes only. Lyris Technologies does not provide technical support for installation, use or maintenance of PostgreSQL.
You may find more information about tuning PostgreSQL at:
http://www.phpbuilder.com/columns/smith20010821.php3
1. Increase the buffer size. Postgres uses a shared memory segment among its subthreads to buffer data in memory. The default is 512k, which is inadequate. On many of our installs, we've bumped it to ~16M, which is still small. If you can spare enough memory to fit your whole database in memory, do so. Each buffer is 8 kb, so set it to:
shared_buffers = (the lesser of
(total memory you can spare) and
(size of database + 512k)
) / 8192
Example:
shared_buffers=48000
The buffer can be set with the shared_buffers variable in $PGDATA/postgresql.conf or at runtime with
the -B flag to postmaster.
Before you can increase the buffer, however, you must make sure the OS will allow the size of shared
memory segment you want. In Linux, check the value with
cat /proc/sys/kernel/shmmax
If this number is less than (shared_buffers * 8192), put the following line in /etc/sysctl.conf
kernel.shmmax = <shared_buffers * 8192>
This will change the value after a reboot. You can change it in runtime
by running:
cat <shared_buffers * 8192> > /proc/sys/kernel/shmmax
2. Increase the max_ connections in $PGDATA/postgresql.conf if necessary. ListManager requires 25;
you may need more if you have other applications using PostgreSQL.
3. Increase the sort heap by increasing sort_mem in $PGDATA/postgresql.conf. The default PostgreSQL
setting (512k) is very low. The optimal value may vary, but 4Mb per process should be sufficient:
sort_mem = 4096
4. Increase effective_cache_ size in $PGDATA/postgresql.conf. This setting relates to how much memory you have free when the shared buffers have been allocated. In this example, each block is 8k, so there is 32Mb cache available:
effective_cache_size = 4000
5. If PostgreSQL complains it is running out of transaction logs, increase the wal_ buffers in $PGDATA/postgresql.conf.
Optimal settings will vary depending on your load. In this example, the transaction logs will be recycled
every 6 hours:
wal_buffers = 8
6. VACUUM the database. Vacuuming the database regularly is highly recommended to keep the ListManager indexes up to date.
7. Set fsync in $PGDATA/postgresql.conf to false. Fsync is a postgres configuration parameter, set in the postgresql.conf file, that determines how PostgreSQL writes new data to disk. When fsync is set to true, data is not buffered but instead written directly to disk, which can dramatically slow down performance but allows for better data recovery in the event of a crash. If fsync is set to false, new data is buffered, thus increasing performance but risking the loss of buffered data if the database crashes.
|
|