PostgreSQL 8.3 vs. 8.2 - a simple benchmark


I ran across an interesting post that benchmarks the newest version of PosrgreSQL (8.3) versus the previous release (8.2). PostgreSQL is one of the most used databases for web 2.0 startups (behind MySQL).

The below is from the post.

With 8.3 just around the corner more and more people are actually starting to test 8.3 with their code base and wondering if it will be worth to switch/upgrade and so did I.
The following is not really an in-depth benchmark but meant as a simple testing of 8.3 on a very specific (but not uncommon) workload and with small set of different configuration parameters.

All the testing is done on a DL380 G5 with two Quadcore Intel Xeon CPUs (X5345 - so 2,33Ghz per core) 12GB of RAM and 6 disks in a RAID1+0 with 512MB BBWC on a Smartarray P400.
The box is running Debian/Etch AMD/64 with the debian supplied kernel (2.6.18 more or less) and a current -HEAD snapshot of PostgreSQL (more or less BETA4 code) compiled as a 64bit binary.

The benchmark database is initialized with a scaling factor of 100 (equals to 10M rows) which seems to be a reasonable size for a table in an OLTP style database) all testing was done with 100 clients and 100000 transactions/client which comes out to 10M transactions and an average runtime of about 1,5 hours.
The first test is a simple comparison of 8.2 vs 8.3B4 in both the default (ie. unchanged postgresql.conf) and one with a somewhat reasonable tuning:

* checkpoint_segments=192
* maintenance_work_mem=128MB
* shared_buffers=1536MB
* wal_buffers=1024kB
* effective_cache_size=3084MB
* filesystem(ext3) mounted with noatime


what we can see here is that 8.3B4 is about 2.2x faster than 8.2 out-of-the-box for this very update heavy workload and is able to keep that advantage after similar tuning is applied to both instances.
The main reason for this rather large improvement is HOT. pgbench is one of the workloads that benefit most from it and a bit of preliminary testing with similiar real-life workloads (session tables or stuff like the SQL-Bayes support in Spamassassin) show equally impressive gains.

the second thing I tried to test was the effect of various shared_buffer settings on the transaction rate(the other parts of the configuration stayed the same as in the "tuned" variant above):


there are two main things to note here - one is the obvious one that the scaling on the x-axis is not linear.
The other one is that the best performance seems to be around 1,5GB of shared memory which is about the size of the database on-disk in 8.3. Higher settings to not help and in fact cause a slight performance degeneration.

Read the full post here

From these tests it looks like 8.3 is going to very good release. With databases performance tuning is critical to having your application run its best.

As always if you have any questions about the above you can comment on this post or message me on Social Ajaxonomy. Also if you have an interesting site, library or story you can use the link on the side bar of the blog (or click here) to submit the item.

[...] PostgreSQL 8.3 - Finally Released! By David Hurth - Posted on February 4th, 2008 Tagged:  Ajax After much waiting ProstgreSQL 8.3 has been released. This is a much better performing release from version 8.2. You can find some benchmarks that I wrote about previously here. [...]


Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <pre> <div> <blockquote> <object> <embed> <img> <param>
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.

More information about formatting options

This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Copy the characters (respecting upper/lower case) from the image.