CoolThreads Try and Buy Application Resources

PostgreSQL Tuning Tips for Sun Fire T2000 Systems Running Solaris

Latest binaries

Tuning /etc/system

Recommend: Solaris 10 11/06 as the OS to use.

Apply the tuning parameters listed at:
http://www.sun.com/servers/coolthreads/tnb/parameters.jsp

For Data Warehousing type of workloads also use:

set maxphys = 1048576

Only set the following parameter if you have plenty of available physical memory and UFS is used instead of ZFS:

set segmap_percent=50

For ZFS please use the following patches (recommended for Solaris 10 11/06):

• 118833-36 SunOS 5.10: kernel patch
• 124204-03 SunOS 5.10: zfs patch
• 122660-07 SunOS 5.10: zones jumbo patch
• 120986-08 SunOS 5.10: mkfs and newfs patch
• 123839-04 SunOS 5.10: Fault Manager patch

Reboot the system for the above changes to take effect.

Important notes about the /etc/system settings above:

• Setting maxphys as shown can help performance if your workload is doing I/O to and from a UFS filesystem in greater than 64K size chunks. Also consider creating filesystems with the following command: "newfs -C 128 /dev/rawdevicename"
• The default segmap_percent setting is 12% of physical memory. If your system has enough memory, increasing this value can improve filesystem performance. If your system does not have enough physical memory, increasing this value could cause paging, reducing performance.
• The appropriate values for System V IPC tunables depend on your setting in postgresql.conf such as shared_buffer, max_connection, etc.
• For Solaris 10, changes to System V IPC tunables are not usually necessary - default values are large enough for most typical environments. Should changes to the defaults be required, though, use the prctl command to set resource controls (rctls) for affected projects. For the sake of backward compatability, values may still be set in /etc/system, although those values take effect only if they are larger than the Solaris 10 defaults.

Tuning /etc/project

For bufferpools bigger than 25% of RAM and high concurrent users (Replace string "postgres" with the username of PostgreSQL database user) :

I/O Tunning

Putting the log into a different filesystem with the forcedirectio option can improve performance for OLTP workloads. Here's a sample entry from /etc/vfstab showing a filesystem with forcedirectio enabled:

/dev/dsk/c1t1d0s0 /dev/rdsk/c1t1d0s0 /pglog ufs 1 yes forcedirectio

Forcedirectio can also be enabled dynamically (applies only until the next reboot):

mount -o remount,forcedirectio /pglog

After running initdb, move $PGDATA/pg_log to your log partition mount point.

The Sun Fire T2000's internal disks are shipped with write cache disabled. To maximize write performance, it is possible to enable the disk write cache using the format -e command, but at the risk of losing data in the event of a power failure. For the best disk I/O performance combined with full data protection, use an external storage array in a RAID 1 + 0 configuration.

PostgreSQL Tuning

Performance tests have been carried out on a Sun Fire T2000 system with 8 cores and 32 GB memory using an internal OLTP workload located on the internal drives. The test database was less than 2GB in size, though, and could fit into memory. Our main interest was CPU scaleability, and indeed PostgreSQL scales quite well on the T2000 when increasing the number of users.

The testing showed that a number of parameters in postgresql.conf can be tuned to improve OLTP performance. The following settings provided the best throughput:

wal_sync_method = fdatasync
wal_buffers = 256
checkpoint_segments = 96
effective_cache_size = 128000
shared_buffer = 430000
max_fsm_pages = 208000
max_fsm_relations = 10000
commit_delay = 5
max_connections = 1000

The above parameters were tuned for our OLTP test workload and the results may vary for your application.

The following links provide some guidelines for determining the optimal settings for your specific workload: