Slashdot Mirror
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:
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?"
- 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
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?"
Send an open email to the dev teams on both projects, and ask for their opinions on what should be tested. It might take 3-4 rounds of back and forth to settle on a set of reasonable benchmarks and settings, but at least that way both sides are involved from the beginning.
GPL'd web-based tradewars themed space game
...is some honest benchmarks of Oracle against Postgres. That would be far more interesting. Does anyone happen to have a copy of Oracle that isn't covered by obnoxious "no benchmarking" licence clauses?
I would be interested in performance for a single user, but accessing a very large table and/or very complicated queries.
Most benchmarks only deal with multi-user performance. Most problems I had to solve dealed with managing large datasets with complicated relationships, but only one user that has to access them.
Althought I highly suspect that mysql is not suited for that sort of usage anyway. But I am curious how postgresql would compare there with firebird.
in fact I would consider it close to ideal if the results came out that each database won in some tests
With an attitude like that, there's no point running benchmarks. The idea is that you run the benchmarks to get an idea of how the databases perform. But it seems you are already rejecting one possible result (that one database performs worse than others in all respects) because you don't consider it "fair".
Well life isn't fair. I'm sure people worked hard on all databases, but that doesn't mean they all have value. Sometimes people try hard and fail. And you want to ignore the numbers that tell you this because you think it's fairer that way? Give me a break, you don't want to run a real benchmark, you want to run something that will tell you what you have already decided upon is the best.
Compile your application to use each database
Now go and compare which database runs fastest on your application
Anything else just doesn't matter - your application is going to be different than every benchmark, so what you need is to run your application on the database and see what happens.
What I have usually found is that while you can highly tune the database, and have great database benchmarks - most of those are ruined by completely brain dead applications that do very stupid things, ruining any kind of performance the database will give you
I have mod points and I am not afraid to use them
Porn. Lots and lots of porn.
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.
I would love to see operations on very large databases, say 100 million or 1 billion records (or even more). Operations like bulk loading, inserting, querying, deleting; against indexed and un-indexed tables; reindexing a whole table (*).
(*) Reindexing caused me a ton of grief. I inherited a huge mysql db once that required an emergency reindex. Unfortunately mysql locked the table while it did a full table copy, which took hours.
_______
2B1ASK1
How I would do it:
1) take a snapshot of the database
2) turn on the query log for the server and run it for a day
3) install the snapshot on your test servers
4) play back the query log and see which one goes the fastest.
There's no reason to complicate it by trying to stress certain functions. I'm looking for real application performance, and I have no control over the queries that the programmers are throwing at the box (unless I see it bogging down the server and I go and hit them over the head).
--Ajay
At work I had a problem recently where there was significant performance loss because there was a flood of very many very small and simple queries. I had to replace a bunch of them with one very large very complicated query to make things run smoothly. Figure out just how many simple select queries you can flood the server with before it starts to choke. Also make an absolutely enourmous query, really really big, and see how many seconds it takes to get the result on each of the setups.
The GeekNights podcast is going strong. Listen!
1x dual Opteron
.GT. RAM. It doesn't even mean that the data is a whole lot bigger than RAM.
It'll spend too much time task switching. Better to have 2 "less than half" slower CPUs, than 1 really fast CPU.
16G ram,
Could you put the 16G into one of the 2x boxes?
2x36G 15Krpm SCSI 16x400G 7200rpm SATA
data warehouse type tests (data >> memory);
DW does not mean that data
"I don't know, therefore Aliens" Wafflebox1
He didn't claim to be a true scientist. He didn't claim that this result is what MySQL developers or PostgreSQL developers will die over.
It's the Thanksgiving weekend, and one of the readers want to do something fun with his spare machines.
In fact, he's trying to run a test scenario that I happened to be interested in.
Unless you can go help him on the project itself personally, STFU and go back to your corner.
Nobody needs a destructive criticism.
Writer, thank you for doing this for me. I've been meaning to do the same for quite some time but I haven't found the time nor the facility.
Whatever you do, keep up the good work!
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/ as well as the thread regarding your ask slashdot question here: http://archives.postgresql.org/pgsql-performance/2 005-11/msg00514.php
With an attitude like that, there's no point running benchmarks. The idea is that you run the benchmarks to get an idea of how the databases perform. But it seems you are already rejecting one possible result (that one database performs worse than others in all respects) because you don't consider it "fair".
Part of the issue with benchmarking is that different types of queries can result in wildly different performance ratios between RDBMS's.
For example:
Try joining a really large table in PostgreSQL against a table with exactly 0 rows (and has just been vacuumed and analyzed-- this requires an outer join). This sort of query kill's Pgsql's performance because it assumes a 10-page size to a physically empty table (it does this to avoid really bad plans as the table grows). While this may seem like a corner case, it can be a real problem when you are trying to run third party software when you don't use the entire package (and it leaves some tables empty).
If you have to run a general battery of tests in cooperation with the developers on both sides, I think it is only fair to seek out areas where every RDBMS chokes. These weak points can then be documented and avoided.
In essence I think that it would be best if every RDBMS loses at least one test badly. It would also be informative if each RDBMS won at least one test, assuming these were fairly executed.
Now for tests, I can think of a few of them:
Category I: Strength/Weakness comparison: Run sets of standardized queries through the RDBMS as quickly as possible. See how it scales with concurrency, etc. Helpful if each RDBMS wins at least one test here.
Category II: Have DB experts in each system create a script (or at least set of queries) to accomplish X complicated task. Compare these in terms of run times with low-concurrency and high concurrency environments. These tests would be done on standard data sets. These are the most relevant in terms of real benchmarking both the least informative regarding actual implimentation decisions.
Category III: Seek out weak points in RDBMS's and provide case studies of what causes each one to choke.
LedgerSMB: Open source Accounting/ERP
Postgres has several replication options, there are ones that work like pgpool (although I don't remember the name at the moment) and others that do asyncronous replication (slone is the biggest name there).
none of them are part of the core postgres distribution, but that's a deliberate choice.
none of the processors are dual core.
there are 5 machiens total I can use
all of them have two single core opterons (ranging from 246 to 252, I'd have to double check to see which machines have which processors)
16G ram in a dual cpu machine is easy (but not cheap, ~$350/G) 8 sockets with 2G per socket.
if you have a data warehouse with less data then you have ram it's a very tiny warehouse.
in my case I'm looking at a months worth of raw data being ~3TB these machines are for a proof of concept that will only hold about a months worth of data (I have another machine to hold the raw data compressed for archival purposes)
David Lang
none of the Opterons are Dual core. they are 246 or 252 SMP machines with 5 machines total available. the one with 16 SATA drives is useing 3Ware 9500 series controllers.
as I get information from Postgres and MySQL folks about the tests I will make sure to post it here. I would love to see other people with different hardware run the same tests.
David Lang
Who cares about performance? If you want to do something useful, then test reliability... Pull the power on the server. Have a failure on a drive, see if you can rescue any data. Corrupt a sector by overwriting it with 0s and see what the engine does.
Performance you can (almost) always go to a bigger box - if your whole engine goes down and can't recover because of a few bad sectors or insufficient logging or such, you're screwed.
Peter.
There are all different kinds of workloads for database servers. Is the workload mostly transactional, moving around thousands of tiny buckets of data, all interrelated by constraints, foreign keys, and triggers, and all being done by thousands of users at once? Or is the workload one where you're trundling through hundreds of gigabytes of data to mine for certain critical points hidden in them, and only a few users at a time will be hitting the system?
Are we talking about workgroup size database apps, or enterprise class stuff?
Does it need to run well on small machines, with limited I/O, or does it need to be able to take advantage of very large machines, with tons of CPU, RAM, and hard drives?
I would love to see some comparisons of the standard TPC tests. Several of these are already implemented by the OSDL folks on top of PostgreSQL to test it and the linux kernel on top of large machines. I'd love to see them ported to MySQL 5.0 and some comparisons done.
I would REALLY want to see the tests be between PostgreSQL 8.0/8.1, MySQL-innodb, MySQL-isam, firebird, MaxDB, and so on. I.E. NOT just MySQL-isam and PostgreSQL. and not done by folks who can't tune one or the other of the databases, so I think the OSDL folks would be a great choice here, if they wanted to expand their test suites.
--- It is not the things we do which we regret the most, but the things which we don't do.
I'm not sure benchmarks are really the best way to measure between those two products. Pretty much the common wisdom exists here:
MySQL is much faster
Postgres does a lot more
If you can tolerate feature poor you go with MySQL for ease + speed
If you can tolerate slow you go with Postgres
If you need feature rich and fast you go with Oracle
If you don't have dedicated DBAs then you must not care about reliability go with SQLserver
Anyway:
1) MySQL has corruption problems. Good measures (load testing) would be worthwhile.
2) Detailed lists of what the major ones do and don't have
3) How much faster is MySQL for bulk operations 1m line read, 1m line change, 1m line write, 1m line delete repeat all 10x.
I'm not sure your equipment really addresses this. Anyway I like the suggestion of asking the two sides. One thing that is important though is that good databases (like Oracle) can be tuned and thus perform much better on the test if you allow for tuning. I think you should definitely treat "tuned" and "default" as two seperate entries in your benchmarks.
..the TRUTH!
several years ago when MySQL didn't support transactions it was definantly faster then postgres.
As MySQL has added features it has gotten more complicated and slower (the ISAM tables are significantly faster then INNODB, but they don't support advanced features like transactions)
In the meantime Postgres has gotten significantly faster
so the question is have they met yet, and whichever one is faster, how much faster is it?
the common wisdom used to be that Linux was slow, didn't scale, and wasn't secure. At one point this was correct, I don't think any of these accusations would hold up today.
David Lang