Listening to Josh Berkus presentation on OSCON today I decided to take a closer look at SpecJAppServer benchmarks results which were published by PostgreSQL recently and which as Josh Puts it “This publication shows that a properly tuned PostgreSQL is not only as fast or faster than MySQL, but almost as fast as Oracle (since the hardware platforms are different, it’s hard to compare directly).”
If you look at Benchmark Results List you would see MySQL Scores 720.56 and PostgreSQL scores 778.14 JOPS on 12 cores. This seems to show PostgreSQL is some 10% faster, from the glance view.
If you take a closer look you however would notice hardware is different – MySQL benchmark use Sun Fire X4100 available in Nov 2005 using Opteron 285 CPU, PostgreSQL benchmarks use Sun Fire X4200 M2 available in September 2006, using AMD Opteron 2220 SE. We can see some 5% difference in CPU frequency alone (2600Mhz vs 2800Mhz) and it well may be more in terms of performance due to architecture optimizations.
The other big issue is using MySQL 5.0.27 which has Innodb scalability issues, which were a lot improved in 5.0.37 which I’d expect to show significant gains.
This is not to mention specific tuning for Benchmark which could be performed on database engine level. As Josh now seems to be working for Sun I expect there is significant optimizations which could be done (if they are required).
Working a lot on published SpecJAppServer benchmark while still working for MySQL I remember we spot number of issues in MySQL exposed by this benchmark with only few of them fixed in time to make it into the publication.
It is always hard to argue which performance you would get in the benchmarks in the difference conditions but my expectations would be getting at least 800 JOPS on updated software and hardware configuration, so I would argue MySQL is Slower.
On the other hand It does not really matter… Being 10-20% close in terms of performance is close enough for most applications for other properties such as scaling maintenance to become more important. So I would agree with Josh – PostgreSQL is not slow for this kind of applications and can well be used as MySQL alternative.
However such general transactional application (general so it can work with all databases) is not where most of MySQL sweet spots come from. MySQL performance gains usually come from MySQL unique features – non transactional MyISAM, Multi Value inserts, Query Cache, Merge Tables, Archive Tables, Simple Replication. Most of these do not come implicitly, you’ve got to use them explicitly to get performance advantage.
Regarding general features I guess some things are faster others are slower in MySQL. For example PostgreSQL is slower at connection creation and MySQL is very fast, while SubSelect optimization is very bad in MySQL for many cases until MySQL 5.2
