PostgreSQL in OpenSolaris and Ubuntu
The latest PostgreSQL package (8.3.3) is available for both the OpenSolaris 2008.05 release as well as Ubuntu 8.04 (Hardy Heron). This offers a critical opportunity to gather results as to the performance of the open source database on x86 systems under a variety of conditions.
Note - As of 7/02/08 PostgreSQL 8.3.1 is available on the OpenSolaris repository, 8.3.3 should be following shortly.
PostgreSQL 8.3.3 is available to download as a binary for OpenSolaris from www.postgresql.org, which includes all the necessary files as well as additional facilities such as pgbench
which was used to gather these results. In Ubuntu you should find
PostgreSQL through the Synaptic Package Manager, just be sure to grab postgres-contrib if you wish to use pgbench to run your tests.
After
initializing a database in your respective directory, you can fine tune
the parameters used by PostgreSQL when the database starts by editing
the postgresql.conf file in that directory.
By default, shared_buffers is set to 24MB, this was increased to 64MB for the tests (if you're running Ubuntu you may have to change kernel setting to allow for more than 32MB of shared memory), also checkpoint_segments were increased from 3 to 32.
At first, we wanted to see the effect of wal_buffer size on database performance across both systems, the default value is set to 64kB, however this was increased and three new values were tested: 512kB, 1MB, and 2MB.
For each value, scaling factors of 1, 10, and 100 where used. The y-axis represents transactions per second, where a transaction is 5 SELECT, UPDATE, and INSERT commands, the amount of transactions required to get an average value was set to 10000. Each case was run 7 times, with the tables vacuumed after each run, the highest and lowest values were dropped and the average was taken.
Both operating systems where installed under identical conditions within Virtual Box, each had 2GB RAM, 20GB hard drive space, and utilized a single 2393 MHz processor from the underlying dual-core Opteron machine.
wal_buffers = 512kB
OpenSolaris has a much easier time moving WAL data utilizing a smaller buffer than Ubuntu. While the gap closes with a scaling factor of 10, with larger tables Ubuntu goes all the way below 50 tps while OpenSolaris is still able to hang around 80 tps.
wal_buffers = 1MB
wal_buffers = 2MB
Raising wal_buffers all the way to 2MB shows that both systems only utilize the extra space with a small scaling factor of 1, they achieve the highest values of the any test with Ubuntu scoring just around 240 tps and OpenSolaris actually hitting 250 tps. Unlike the other tests Ubuntu gains roughly a 30 tps edge moving towards scaling factor 10. OpenSolaris actually shows a lower tps with 2MB wal_buffers than 1MB at scaling factor 10, it could be that with more space OpenSolaris finds a need to create a new log segment during the simulation. By scaling factor 100 both systems are even.
By the end of the tests, a wal_buffer size of 1MB seems to be ideal for both systems.
Within postgresql.conf there are a multitude of settings that can be altered and tuned to get the most out of your specific situation, these few tests offer only a small scope of the variety of values that can be changed within PostgreSQL.
If you use PostgreSQL on an OpenSolaris or Ubuntu system, it would be interesting to see the settings you choose throughout the database to get the best performance.
Excellent work. Thanks for sharing it.
Posted by M. Mortazavi on July 11, 2008 at 12:27 AM PDT #
What about the disk subsystem used on this box? Multi-drive 15k SAS RAID 10 w/BBWC or just a random drive? Are you using synchronous or async commits?
Posted by Rick Branson on July 28, 2008 at 10:10 AM PDT #
This was simply done on a random drive using asynchronous commits. Each VM was just given the same size virtual size partition on the same drive.
Posted by Felix on July 28, 2008 at 10:23 AM PDT #