Benchmarking 8.4 - Chapter 1/Read-Only workloads

Benchmarking is always a difficult and complex matter any whatever you are doing you are likely to make mistakes or bias (on purpose or not) the results into one direction or the other. However you need to have a goal when doing benchmarking.
My intention here is to see how PostgreSQL 8.4 behaves under a number of simple and clear scenarios on what I would consider a fairly typical 2U/2 socket based server in 2009:

• IBM x3650 M2
• 2x Quadcore Intel E5530 (Nehalem) - so 8 Cores/16 Threads
• 34GB RAM
• CentOS 5.3/x86_64
• 2x 146GB SAS 10k using Integrated Mirror(IM) on the embedded LSI Logic controller (OS)
• PostgreSQL 8.4RC1
• Dualport QLE2562 8GBit FC adapter connected to a NetApp FAS3140 (PostgreSQL data directory)

I'm planning to do a number of different testing scenarios on that hardware - the first thing(as in this post) I'm going to deal with is read-only benchmarking using pgbench.
While PostgreSQL very often shows its strengths as robust and scalable OLTP-platform with an intermix of concurrent INSERT/UPDATE/DELETE and some SELECTs it is also more and more used as a platform for "mostly read - very rarely written" kind of applications.

Unless otherwise mentioned the postgresql.conf used for this testing used the following non-default settings and the database used was UTF8. The benchmark client was running on the same box as the database itself and connecting using a unix socket.

• shared_buffers = 2048MB
• work_mem = 32MB
• maintenance_work_mem = 2048MB
• wal_buffers = 8192kB
• checkpoint_segments = 192
• effective_cache_size = 16384MB

Pgbench is the typically used off-the-shelf benchmarking tool used with postgresql. It has some scalability issues in itself(more on that later) but in general it's behaviour is well understood which makes it still a valuable testing platform.

The first chart shows pgbench in "SELECT only" mode ran for 240s each time with an increasing number of concurrency and increasing tables sizes. A scale factor of 10 corresponds to 1000000 rows in the table and the largest scale factor results in a table with 100M rows. All those fit easily into the OS buffercache so there is no (noticable) IO at all going on here.

While slightly degrading performance with an increasing table size is expected, the transaction rate drop after exceeding the number of available cores is pretty severe and likely needs some more investigation. The server itself still had plenty of idle time (around 30% at the lowest time which also corresponds to the highest transaction rates at 12/16 connections).

The following graph shows a comparision between the different protocol choices available. "simple" is the default mode for pgbench, where as "prepared" is the same as "extended" but uses prepared statements. This tests used a scale factor of 100(as in 10M rows).

Using prepared statements is clearly of great help for this kind of workload but it is worth mentioning that even in the fastest case of 115000 tps with 8 clients the server still showed ~50% of idle time. I'm not sure why the performance does not increase with 12 and 16 connections yet but it seems clear that we are looking at some scaling issue withing pgbench too.

To validate this theory I also did some testing using sysbench. The following sysbench numbers were generated using --oltp-read-only=on and --db-ps-mode=auto. A table size of 10M rows and a maximum runtime of 240 seconds was used for both pgbench and sysbench.

It is important to stress that the schema and the queries sysbench uses are more complex than what pgbench does so this is a bit of an apples to oranges comparison. The default schema for sysbench uses two char() columns which do have a noticable overhead so I added a run with those columns changed to varchar().

The sysbench results show a pretty different picture from the pgbench results. The system is using all the CPU and hitting no noticable scaling issues if the number of connections exceeds the number of cores/threads available.