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
I think what this benchmark really shows, though, is that you don’t have to sacrifice speed to get the reliability and flexibility of PostgreSQL. You just don’t have nearly the community support that MySQL has, and that is the one real advantage of MySQL. If you’re aware of how Postgres works, the choice to go with pgsql then becomes pretty clear, at least while MySQL is stuck in 5.x.
Right. Flexibility does not always have to come at large cost, if properly designed you can limit the overhead… but not get away from it fully.
With MySQL MySQL version 4.1 and 5.0 became slower than MySQL 4.0 for many simple operations at large extent because extensions were done without flexible architecture designed for infinite extensions from the start.
Also if you compare MySQL and PostgreSQL MySQL is just flexible in different way – MySQL has large number of storage engines and this causes a lot of performance challenges.
just a small correction – multi value inserts is not exactly a MySQL unique feature it is actually a partial(as far as I know) implementation of the SQL Standard feature “row value constructors” (ID F641). At least DB2 and PostgreSQL (8.2+) have similar capabilities.
Nick, I don’t think the version numbering is the most relevant factor when choosing between database engines for your services. On the other hand, I think MySQL is a great piece of software, still under development, that has a way better community support and paid support from the developer than the pgsql has. This is particularly important for newbies or switching users. Besides, performance results do not always show the pgsql as a better option.
Hey guys!
The MySQL and PostgreSQL results are uncompareable!
The PostgreSQL was run on an 8 core HW while the MySQL was run on 4 core system. They were totally different. And the MySQL response times are far better.
QUOTE:
On the other hand, I think MySQL is a great piece of software, still under development, that has a way better community support and paid support from the developer than the pgsql has.
UNQUOTE:
Bull puckies. PostgreSQL has fantastic community support and you get access to the developers for free. It’s not at all uncommon for someone to find a strange corner case bug, post about it, and get an answer with a patch back in less than 1 day.
I’ve never gotten a mysql patch in day. ever.
Methinks you’ve never used the pgsql-general mailing list to say such things about pgsql community.
Scott,
I should note MySQL was there…. in 1999 Being MySQL user just starting at that time I got fantastic support on mailing list from Monty himself with fixes making into main MySQL tree within days. I surely miss that. However MySQL community is really helpful for general MySQL use – it is rarely users run into the bugs most time they just need help figuring out how to do something.
QUOTE:
#9. nvitya
Hey guys!
The MySQL and PostgreSQL results are uncompareable!
The PostgreSQL was run on an 8 core HW while the MySQL was run on 4 core system. They were totally different. And the MySQL response times are far better.
Comment :: July 10, 2008 @ 7:09 am
**********************************************************************************
I believe you’re looking at the wrong information…
MySQL 5 720.56 12 cores, 6 chips (2 core/chip)
Postgres 8.2 778.14 12 cores, 6 chips (2 cores/chip)
Can someone do a similar benchmark and post the results somewhere with proper configuration and similar hardware? I am dying to know if PostgreSQL is better than MySQL or not and what factors I should be looking at before deciding to use one over the other for a new project.
Any help would be truly appreciated.