What Would You Want to See in Database Benchmarks?

David Lang asks: "With the release of MySQL 5.0, PostgreSQL 8.1, and the flap over Oracle purchasing InnoDB, the age old question of performance is coming up again. I've got some boxes that were purchased for a data warehouse project that isn't going to be installed for a month or two, and could probably squeeze some time in to do some benchmarks on the machines. However, the question is: what should be done that's reasonably fair to both MySQL and PostgreSQL? We all know that careful selection of the benchmark can seriously skew the results, and I want to avoid that (in fact I would consider it close to ideal if the results came out that each database won in some tests). I would also not like to spend time generating the benchmarks only to have the losing side accuse me of being unfair. So, for both MySQL and PostgreSQL advocates, what would you like to see in a series of benchmarks?"

"The hardware I have available is as follows:

• 2x dual Opteron 8G ram, 2x144G 15Krpm SCSI
• 2x dual Opteron 8G ram, 2x72G 15Krpm SCSI
• 1x dual Opteron 16G ram, 2x36G 15Krpm SCSI 16x400G 7200rpm SATA

I would prefer to use Debian Sarge as the base install of the systems (with custom built kernels), but would compile the databases from source rather then using binary packages.

For my own interests, I would like to at least cover the following bases: 32 bit vs 64 bit vs 64 bit kernel + 32 bit user-space; data warehouse type tests (data >> memory); and web prefs test (active data RAM)

What specific benchmarks should be run, and what other things should be tested? Where should I go for assistance on tuning each database, evaluating the benchmark results, and re-tuning them?"

easy, TPC

[ZuggZugg]

TPC-C and especialy TPC-H for DW benchmarking. Buy the benchmark kit and run it...done. If you're really serious have it independantly audited and submit the results to the TPC.org. You could probably wrangle up some sponsors to help foot the bill.

Good luck.

Re:Very large database operations

[captainclever]

Oh yes.. mysql makes a copy of the table for every ALTER TABLE command i think, even if you want to drop an index. This sucks royally. Sometimes it's quicker to add and remove indexes when you want to run a few queries on a large table (at least it is with postgres). Locking the table whilst dropping/creating indexes is a huge pain in the ass - but won't show up in benchmark results. This also means that if you don't have enough disk space to hold a copy of the table, you can't easily alter it :(

Contact us on the pgsql-performance list

[jeffroe]

We'd love to see some benchmarks run on this equipment. It's a great chance for us to evaluate and boost postgresql performance in general. Can you contact us directly? You can find a subscription link here: http://archives.postgresql.org/pgsql-performance/ [postgresql.org] as well as the thread regarding your ask slashdot question here: http://archives.postgresql.org/pgsql-performance/2 005-11/msg00514.php [postgresql.org]