Created: 2009-05-05
Last modified: 2009-05-18




PostgreSQL Performance: 8.3 and 8.4 @dbSTRESS






by Dimitri


SSC Team, 2009
Sun Microsystems Inc.





Ces informations sont données à titre indicatif et n'engagent pas Sun Microsystems.

Table of contents


Benchmark Information
Customer Name(s): SSC Team

NDA: no

Contact Information:
     - dimitri (at) sun.com

Dates: May.2009

Keywords: PostgreSQL 8.3.7, PostgreSQL 8.4, MySQL, dbSTRESS


Hardware Configuration
Server(s):
     - M5000 8CPU SPARC64-VII (quad-core bi-thread) 2400Mhz, 64GB RAM, 2x FC-port 4Gbit

Storage:
     - ST6140 2x LUN (500GB RAID1 8HD), each LUN connected by its own fiber channel to the host


Software Configuration
System:
     - Solaris 10 update6
     - UFS

Application(s):
     - PostgreSQL 8.3.7
     - PostgreSQL 8.4 beta1
     - db_STRESS (injector)

Abstract
Overview: During my recent MySQL testing I was curious to see how well performing PostgreSQL on the same workload. Two years on the same test case PostgreSQL was two times faster comparing to MySQL. Now, for my big surprise, it was two time slower then MySQL 5.4. Of course MySQL 5.4 made a huge step in performance improvement, but there were several signs for me - something goes wrong with PostgreSQL... So I've planned to retest it again an go more in depth to understand observed performance difference.

Goal(s):

Result(s): see summary :-)



Benchmark details
My intention is to replay exactly the same tests as in my previous report but on the newest M5000 (quad core) server. So I'll skip all previously made explanations about db_STRESS tests and scenario and go directly to the action.

NOTE: All PostgreSQL binaries are compiled with GCC 4.3.2 in 64bit, with CCFLAGS="-O3".

NOTE: Initially I kept the same buffer pool = 12GB as MySQL. As PostgreSQL is delegating its read cache to the filesystem too it'll be more than enough (and as you may see from workload graphs there is practically no disk access during Read-Only tests).

NOTE: most of tests were run with a second thread enabled on each CPU core. To put attention on this point I add a mention about number of core threads on used labels. For example 8cores become 8cores(2) (or cores=8(2) for ex.) if both threads were activated on each core. Otherwise 8cores remaining only 8 cores were active with only one thread. Also, when test is "executed on N cores" it means PostgreSQL is running within a processor set of N cores, so it cannot use more than N cores for its processing and nobody else may use its cores from its processor set.


First impressions
If on Read+Write workload we may discuss about differences on transactions integrity between PostgreSQL and MySQL and it may be long (even if InnoDB nowdays gives the same security level). So I've concentrated my analyze first on the Read-Only workload.

Observations :

To be honest , I should not consider too much my results on 32 cores as injector processes are running on the same server and may bring some background effects on the database engine. But injectors are using less than 20% CPU and as PostgreSQL is still continuing to improve performance here I present them anyway: specially it gives a good example of performance improvement done within 8.4 version, as well it presents the best result reached by PostgreSQL on this server - 11.000 TPS on the Read-Only workload.


So, WHY PostgrSQL is slower than MySQL 5.4 ?...
But! Even on fully used 32 cores 8.4 is still delivering only 11.000 max TPS, while MySQL 5.4 showing 17.500 TPS with InnoDB on 16 cores! (Again, to be honest , we have to keep in mind that MySQL will not do better on 32 cores as its current scalability is limited to 16 for the moment. But on the same time even we may expect two times better PostgreSQL performance on two times more CPU cores it's still hard to justify a use of 64 cores server to do the same thing as MySQL is delivering on 16 cores). So, that's why it's important anyway what is going wrong here if any...

And I was near to finish my report...
I was near to finish my report here, but few things were still questioning my mind :

Comparing various workload graphs, I discovered that response time was better in some cases then other...

Moving step by step back in my investigations and removing option by option I finally found the only thing which may bring some unexpected changes: default_statistics_target !... - playing with this parameter and trying to obtain the most optimal execution plan I left 8.3.7 database with tables analyzed with target 1000 and continued my tests. While the first results on 8 cores were obtained with a default target - 20 !!!

Let's check now :

Bingo!!! :-))

So on 8cores 8.4 was slower comparing to 8.3.7 just because the default value of default_statistics_target on 8.4 is 100 while it's 20 on 8.3.7!!! Who may imagine it'll make a negative difference???...

Let's replay the same tests once more but with default_statistics_target = 5 and tables re-analyzed, and compare them again! :-))

Observations :


Improving Read+Write workload stability
Now, once SELECT time was greatly improved, another issue should be fixed - current performance on the Read+Write workload is not very stable. Checkpoint is involved currently every 5 minutes and seems an important volume of data should to be flushed within a short period of time. So aggressive write "waves" are resulting in TPS "waves" and should be avoided whenever possible in production environment (for ex.)

All Wrokload Results
Following are results for all tested workloads, PostgreSQL versions, number of cores, and other configuration options. As usually,

Workload STATs
Here you may find all main graphs reflecting system and application activity during most of tested workloads...

Used Abbreviations :



SUMMARY
Final notes :

But finally, why PostgreSQL is slower comparing to MySQL on db_STRESS ?...

Look on the following tables (observed on 16cores):

Response Time(ms) 1 2 4 8 16
PostgreSQL 8.4 1.20 1.20 1.20 1.25 1.40
MySQL 5.4 0.65 0.70 0.70 0.80 0.90

Read-Only TPS 1 2 4 8 16
PostgreSQL 8.4 650 1,300 2,600 5,000 9,000
MySQL 5.4 1,000 2,000 4,000 7,500 12,500


Currently observed the max stable troughput on :

Read-Only TPS PostgreSQL 8.4 PostgreSQL 8.4 prep MySQL 5.4 MySQL 5.Perf build5
8 cores 4,600 6,700 14,000 14,000
16 cores 8,500 12,500 17,500 18,000

Read+Write TPS PostgreSQL 8.4 PostgreSQL 8.4 prep MySQL 5.4 MySQL 5.Perf build5
8 cores 6,000 7,300 7,000 10,000
16 cores 8,500 11,500 7,000 12,000

Next to investigate :