Monday, 18 May, 2009
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):
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! :-)