Wednesday, April 20, 2005

Tuning Solaris 10 x86 for PostgreSQL 8.0.2

Recently I ran few tests against PostgreSQL on Solaris 10 x86. My main aim was to see how I can improve PostgreSQL performance when deployed on Solaris 10 x86. These are basically the changes that I did to improve performance on my setup.

First since PostgreSQL relies on file system cache to do most of the buffering activities, I found that the default Solaris 10 x86 does not help it a lot. Specially for repeat queries since it won't keep the database files in file system cache since they could be as big as 1GB.

Hence I made the following changes in /etc/system to help improve the caching of the database files.

NOTE: These are the only tunables defined in my /etc/system

* Setting 10GB for file system cache out of 16GB RAM
set segmapsize=10737418240
set ufs:freebehind=0

In my case since PostgreSQL was the only application running setting segmap_percent=80 helps a lot.. The default value is 12. Realistically it should be reasonably set like 50% or so.. (Assuming you have enough memory to spare since it will take it out of the freelist)

This allows the 1GB datafiles of PostgreSQL to be in cache and reused for a repeat usage. It improves IO performance quite a bit specially if the next query uses the same datafiles (rows).

Also the next thing I found that WAL - Write Ahead Log of PostgreSQL was becoming the bottleneck during writes since it shared the same cached filesystem as the database files. As per with my talks with Josh Berkus it helped by moving it to a different file system. What helped more was actually putting it on a file system with "forcedirectio" option turned on.

DISCLAIMER: I haven't cut-n-paste the scripts below so there could be typos while typing them in (use at your own risk).

mount -o forcedirectio /dev/rdsk/cntndnsn /mypath/pg_xlog
tunefs -a 128 /mypath/pg_xlog
cp -R -p $PGDATA/base/pg_xlog/* /mypath/pg_xlog
rm -rf $PGDATA/base/pg_xlog
ln -s /mypath/pg_xlog $PGDATA/base/pg_xlog

Also at the same time changing maxcontig of $PGDATA to 128 also helps.

What we did above is put database on "cached" file system and the WAL on "directio" filesystem and it helps improve loading data into PostgreSQL.

Try this and let me know what's the change in your performance.


Post a Comment