First "Real" Benchmark for PostgreSQL

anticlimate writes "A new benchmark published on SPEC shows PostgreSQL's performance approaching that of Oracle's and surpassing or on par with MySQL (however the test-hardwares of the other DB systems are somewhat different). The test was put together by PostgreSQL's core developers working at Sun. They certainly are not unbiased, but this is the first 'real' benchmark with PostgreSQL — according to Josh Berkus's blog. The main difference compared to earlier benchmarks (and anecdotes) seems to be the tuning of PostgreSQL."

Comments

Good, but it can be improved.

[khasim]

Get people from each group, give them the requirements and 5 different dollar amounts.

Let each team setup their systems how ever they want at each price point. Some will go with clustered servers. Some will go a single monster server. They know their products best so they'll be the ones best suited to choosing the configuration.

Then run the benchmarks. And keep hammering on them until AFTER the next patch release.

Yeah, it might run fast, but still be a bitch to patch/upgrade.

At $5,000 you might find that a cluster of MySQL boxes beats everything.

At $10,000 maybe something else is best.

$25,000

$50,000

$100,000

etc.

And finally, break it. Break it bad. What happens when something goes wrong? Oracle might cost a lot, but if they can come through with your data they might just be worth it.

If nothing else, you'll get the "best practices" nicely demonstrated by each group. :)

Which MySQL?

[itsdapead]

MySQL is modular - pick'n'mix data storage engines sharing a SQL front end. I can't find the bit in TFA that says which one they compared.

I've always suspected that most MySQL vs Postgres flame wars are based on comparing Postgres with the speed of MySQL/MyISAM (No transactions or relational integrity checks - so, big surprise, dead fast for simple queries) and then waving MySQL/InnoDB around when the functionality issue is raised.

MySQL/MyISAM hits the speed/functionality sweet spot for LAMP data-driven websites, is supported by lots of free webapp software and offered by most decent web hosting services. Comparing it speedwise with Postgres has always been pointless, though. If Postgres has caught up, colour me impressed, but if they're pro-Postgres I bet they're comparing with MySQL/InnoDB (which is a bit closer to like-with-like).

Never quite seen the point of MySQL/InnoDB really - all the advantages of MySQL/MyISAM minus the speed, support by popular webapps, availbility on low-cost hosts... and still lacks the features of Postgres.

Re:Mod parent way up!

[Doctor Memory]

Inserting 20 million rows, all simple inserts, only one primary key (int) with autoincrement for mysql and a sequence for postgres:
Avg Mysql time per 1000 inserts: 3 seconds
Avg Postgres time per 1000 inserts: 15 seconds (and gets worse over time) OK, now do a seven-table join, including a self-join with a correlated subquery (MySQL does those now, right?). I think everybody knows by now that MySQL is pretty much untouchable as long as all you're doing is simple single-table stuff. Kind of like comparing a pickup truck to a moving van: if all you're doing is moving a couple of boxes around, then the pickup kicks. But when you need to move serious loads, then it's the pickup that gets to sit by the curb...

Re:Mod parent way up!

[jaredmauch]

AC, sorry, but I have a postgres install working where I get 70k inserts/second or more with a single index on the table during the day. The first insert of course is faster as the index doesn't exist yet. I'm not sure what you're doing, but I can tell you that I have tuned postgres by increasing some simple parameters. If you're using some Linux package, you're likely not seeing the benefits that are possible by stuff like changing the block size parameter in the source. Yes, it's kinda lame you have to do this, but at the same time, it's not too unreasonable. I'd like it if I could set this larger.

This is on 'decent' hardware running Solaris 10 (amd64). Obviously you need to tweak stuff like wal size, checkpoints, etc.. But getting this type of performance is not hard to do. I can scan an hours worth of data in a short amount of time. Each one of these 'hourly' tables contains roughly 30-32M rows. this is nothing to sneeze at from what I can tell. I haven't had a reason to re-evaluate mysql to see if there are enough tweaks to make it perform similarly, but if you're getting the crappy insert rate that you're talking about, you clearly need to change something as you're doing it wrong if you truly care about performance. E-Mail me if you're interested in my postgresql config files. I'm happy to share to minimize the FUD out there.

I once did benchmarking

[Stinking Pig]

I worked for a company whose product ran on MS-SQL, PostgreSQL, and Oracle. Should I explain why we didn't support MySQL or not? It'll draw fanboys either way. I used the same server, reinstalled the OS (Red Hat Enterprise 3 or Windows 2000) and database between each test, and rebuilt the application server to be extra sure.

Since it was more difficult to write Oracle-compliant SQL and we didn't have a lot of Oracle customers, the developers didn't care to spend time on it, and our stuff ran about 20 percent slower there. That's after a lot of tuning time, it was 50% slower on a default install. Oracle 9 took two days to install and tune, plus another two days of preparation. I was particularly underwhelmed that I had to deal with stupid errors like tarballs that extracted onto themselves and assumptions about the shell being used. At the time, Oracle was a very Solaris-like experience; user-unfriendly to the extreme.

Postgresql 7 ran great; it was neck and neck with MS-SQL in all tests, after proper tuning, and 30 percent slower on a default install. Postgres took half a day to install and tune, but it took me a week and conversation with the postgres mailing lists to find out what needed tuning. Still, we were able to put together a document that took users from bare metal to RHEL+Postgres in four hours if they had all their media handy.

Microsoft SQL Server 2000 ran great with no tuning at all, and took fifteen minutes to install. It also cost as much as paying me to do the entire set of tests. OS installation/patching times and tested workloads were the same for all three tests.

YMMV.

Re:Mod parent way up!

[arivanov]

Who cares if MySQL does them or not. Show me the developers that can both develop applications and do SQL. That is a dieing breed. Most developers nowdays go for a really trivial schema and an abstraction layer. At that point the only thing that matters is row speed on simple table operations and there MySQL or in-memory OO database frameworks with a simple backing store wipe the floor. This is the reality of life. And it is not going to get better. If you look at the books on the market the only book that used to teach "proper" SQL (with joins and the lot) strictly from the context of application development was the old DB2 bible. It has not been reprinted since the late 90-es. All the rest that is out there is either heavily slanted toward the app side or towards the DB side (usually the latter). Add to that the fact that many universities try to teach "real life software engineering skills" instead of proper data structure and data manipulation classes and the picture is complete: http://www.joelonsoftware.com/articles/ThePerilsof JavaSchools.html. Add to that the fact that DBDs when you actually corner them to ask something meaningfull answer with SQL technobabble like in your post. To the average developer it sounds like fortran. And if it looks like fortran, walks like fortran and talks like fortran it gotta be fortran. From the point of view of a average software engineer SQL and especially stored procedures look like a blast from the past. He expects to see objects, constructors, destructors, private and public structures. And what does he see? He sees something that looks like written by his grandparents. As a result he turns around and starts doing delete/insert/last_insert_id instead of replace and sequential deletions in software instead of foreign keys. I have tried in the past to work with developers who write commercial apps on top of SQL to optimise their code. And I have wanted to scream all along. In 95% of the cases you deal with either one of the following:

• A nice schema designed once upon a time properly by a proper DBD that is vandalised in the application abstraction layer because the developers are are sorely pissed off by the endless wingeing of the SQL server and/or its abissmal performance. So they take the matters in their own hands and violate ACID by cashing and bypassing restrictions in the app. Sooner or later someone comes around and says - WTF, why don't we rewrite this all in software and sod off the expensive database. And surprise surprise it ends up being done in MySQL.
• An abissmal schema or no schema at all where all restrictions are done in the app. That is MySQL country all the way.

MySQL is a result of the way current software development is taught and done. Unless Jo the Average Developer starts understanding how to use SQL in his application (and he does not) and unless SQL data representation grows up to modern non-fortran-like OO semantics MySQL will proliferate. And if you think that MySQL is bad think twice. There are the object persistence frameworks and in-memory crap that follow in its wake.