DimitriK's (dim) Weblog

So, Why finally PostgreSQL is slower on db_STRESS Benchmark comparing to MySQL ?...

Last month when I've published my results obtained with PostgreSQL 8.3.7 on db_STRESS benchmark I was surprised they were much lower comparing to MySQL, as well some signs alarmed me there is something goes wrong with PostgreSQL... So, once it was possible, I took my time and prepared another testing on the same M5000 server I published MySQL results last week.

I would say I discovered a lot of new things benchmarking PostgreSQL ! I'm not kidding :-) And without going too much in detail, the main gain seems to me of MySQL over PostgreSQL was a lower cost on executing a single query. The query in question was the second SELECT in db_STRESS.

SELECT-2 execution time:

• MySQL 5.4: 0.44ms
• PostgreSQL 8.4: 1.3ms
• PostgreSQL 8.4 prepared statement: 0.98ms

Again, if PostgreSQL being slower on a single query scaled much more far on say 24 cores - it'll be not really a problem. But the result on 24 cores is only slightly better than on 16, and still lower comparing to MySQL... Of course, it's only true since MySQL 5.4 :-)

However, PostgreSQL made a big progress and performs very well on the mixed workload (Read+Write). Since 8.3 version it has a kind of "delayed" checkpoint - instead to flush all modified data at one (like it was in 8.2 and earlier) it has an option to say "I give you N sec to flush all data, don't need to stress, but finish work on time!" - and it works very well! :-)

Here are some PostgreSQL results on db_STRESS (as usual, X-axis representing a number of concurrent users (sessions), Y-axis representing a TPS level) - oh, and "prep" in name means prepared statements, buf4096 - means buffer pool set to 4096MB, etc. (all test descriptions are explained in the final report):

Read-Only Workload

Read+Write Workload

Scalability issue

As usually, all other details you may find in the full report: http://dimitrik.free.fr/db_STRESS_PostgreSQL_837_and_84_May2009.html 

Any comments are welcome! :-)