SpecJAppServer2004 and PostgreSQL

PostgreSQL has been silently winning customers based on its merits without any real publicity on the commercial benchmark front of the game. Well now that is changing with the publication of the first commercial benchmark ever for PostgreSQL (that I am aware) with the publication of the new SpecJAppServer2004 result. While the result to many may not seen significant it is definitely significant for PostgreSQL and/or Solaris customers and also for the Open Source Software community (specially OpenSolaris, Glassfish and Postgres communities).

Anyway you will hear more about that from my team member Tom Daly and our PostgreSQL guru  Josh Berkus soon. Hopefully from others too ( Scott, Robert et all) . So what am I going to write about? I think I will stick to the technical details side of the configuration specially regarding postgresql on the journey to the number 778 which I think is just the begining.

A standard setup (according to me) is to compile the binaries and use it. But like most applications that I came across thats not the end of it. First we tweaked the postgresql.conf  and took quite a number of runs to get the background writer, randon page cost, full page writes  etc to get uniform and better performance. Also the network tunings were also helpful in handling the huge volume. But still as usual you reach a bottleneck as you increase the transactions.

Then the logs were moved to a separate file system which seemed to help. This was boosted by mounting that filesystem with forcedirectio. (noatime and logging was already being used).

Initially we thought that it wouldnt help much to put the database files (other than logs) on unbuffered filesystem. We were proved wrong by the boost that we saw by moving the $PGDATA files also on "forcedirectio" filesystem.

Here is where Dave Cramer's changes to the JDBC drivers to allow caching at the JDBC level helped.

Then we saw some additional boost by using the new FILLFACTOR storage option in PostgreSQL 8.2.x. Playing with fillfactor of 70 and 80% for different tables and index we finally settled with the schema in the full disclosure archive jar. 

Ofcourse now that TABLESPACES are supported in PostgreSQL 8.2.x we decided to use it in a very simple way by using it to move all indices to a different tablespace which did result in yet another small boost (again on unbuffered filesystem).

Then there were couple of surprising tweaks in the postgresql.conf that helped us cross into the winning runs.. commit_delay=10 which really helped to cut down lots of writes and then wal_buffers which helped us to smooth the response times for the runs.

In the end a big thanks to EnterpriseDB for their support of the benchmark. 

 Overall I think the benchmark exercise has raised the interest in PostgreSQL over here and I think it also highlights the potentials of PostgreSQL since we did not change any code as it was the vanilla source code from postgresql.org.

Why wait for more benchmarks, try PostgreSQL 8.2.4  out yourself on Solaris of course !!

 

Disclosure Statement:
Sun Fire X4200 (6 chips, 12 cores) - 778.14 SPECjAppServer2004 JOPS@Standard
SPEC, SPECjAppServer reg tm of Standard Performance Evaluation Corporation. All results from www.spec.org as of July 8,2007