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

9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Nick

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.

Stefan Kaltenbrunner

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.

Norton Security

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.

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.

Scott Marlwe

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.

Lou

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)

Shahryar Ghazi

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.