1.10.2.3 Benchmarking MySQL and PostgreSQL

The only Open Source benchmark that we know of that can be used to benchmark MySQL Server and PostgreSQL (and other databases) is our own. It can be found at http://www.mysql.com/information/benchmarks.html.

We have many times asked the PostgreSQL developers and some PostgreSQL users to help us extend this benchmark to make it the definitive benchmark for databases, but unfortunately we haven't gotten any feedback for this.

We, the MySQL developers, have, because of this, spent a lot of hours to get maximum performance from PostgreSQL for the benchmarks, but because we don't know PostgreSQL intimately, we are sure that there are things that we have missed. We have on the benchmark page documented exactly how we did run the benchmark so that it should be easy for anyone to repeat and verify our results.

The benchmarks are usually run with and without the --fast option. When run with --fast we are trying to use every trick the server can do to get the code to execute as fast as possible. The idea is that the normal run should show how the server would work in a default setup and the --fast run shows how the server would do if the application developer would use extensions in the server to make his application run faster.

When running with PostgreSQL and --fast we do a VACUUM after every major table UPDATE and DROP TABLE to make the database in perfect shape for the following SELECTs. The time for VACUUM is measured separately.

When running with PostgreSQL 7.1.1 we could, however, not run with --fast because during the INSERT test, the postmaster (the PostgreSQL daemon) died and the database was so corrupted that it was impossible to restart postmaster. After this happened twice, we decided to postpone the --fast test until the next PostgreSQL release. The details about the machine we run the benchmark on can be found on the benchmark page.

Before going to the other benchmarks we know of, we would like to give some background on benchmarks.

It's very easy to write a test that shows any database to be the best database in the world, by just restricting the test to something the database is very good at and not testing anything that the database is not good at. If one, after doing this, summarises the result as a single figure, things are even easier.

This would be like us measuring the speed of MySQL Server compared to PostgreSQL by looking at the summary time of the MySQL benchmarks on our web page. Based on this MySQL Server would be more than 40 times faster than PostgreSQL, something that is, of course, not true. We could make things even worse by just taking the test where PostgreSQL performs worst and claim that MySQL Server is more than 2000 times faster than PostgreSQL.

The case is that MySQL does a lot of optimisations that PostgreSQL doesn't do. This is, of course, also true the other way around. An SQL optimiser is a very complex thing, and a company could spend years just making the optimiser faster and faster.

When looking at the benchmark results you should look for things that you do in your application and just use these results to decide which database would be best suited for your application. The benchmark results also show things a particular database is not good at and should give you a notion about things to avoid and what you may have to do in other ways.

We know of two benchmark tests that claim that PostgreSQL performs better than MySQL Server. These are both multi-user tests, a test that we here at MySQL AB haven't had time to write and include in the benchmark suite, mainly because it's a big task to do this in a manner that is fair to all databases.

One is the benchmark paid for by Great Bridge, the company that for 16 months attempted to build a business based on PostgreSQL but now has ceased operations. This is probably the worst benchmark we have ever seen anyone conduct. This was not only tuned to only test what PostgreSQL is absolutely best at, but it was also totally unfair to every other database involved in the test.

Note: We know that even some of the main PostgreSQL developers did not like the way Great Bridge conducted the benchmark, so we don't blame the PostgreSQL team for the way the benchmark was done.

This benchmark has been condemned in a lot of postings and newsgroups, so here we will just briefly repeat some things that were wrong with it.

• The tests were run with an expensive commercial tool that makes it impossible for an Open Source company like us to verify the benchmarks, or even check how the benchmarks were really done. The tool is not even a true benchmark tool, but an application/setup testing tool. To refer to this as a ``standard'' benchmark tool is to stretch the truth a long way.
• Great Bridge admitted that they had optimised the PostgreSQL database (with VACUUM before the test) and tuned the startup for the tests, something they hadn't done for any of the other databases involved. They say ``This process optimises indexes and frees up disk space a bit. The optimised indexes boost performance by some margin.'' Our benchmarks clearly indicate that the difference in running a lot of selects on a database with and without VACUUM can easily differ by a factor of 10.
• The test results were also strange. The AS3AP test documentation mentions that the test does ``selections, simple joins, projections, aggregates, one-tuple updates, and bulk updates.'' PostgreSQL is good at doing SELECTs and JOINs (especially after a VACUUM), but doesn't perform as well on INSERTs or UPDATEs. The benchmarks seem to indicate that only SELECTs were done (or very few updates). This could easily explain the good results for PostgreSQL in this test. The bad results for MySQL will be obvious a bit down in this document.
• They did run the so-called benchmark from a Windows machine against a Linux machine over ODBC, a setup that no normal database user would ever do when running a heavy multi-user application. This tested more the ODBC driver and the Windows protocol used between the clients than the database itself.
• When running the database against Oracle and MS-SQL (Great Bridge has indirectly indicated the databases they used in the test), they didn't use the native protocol but instead ODBC. Anyone that has ever used Oracle knows that all real applications use the native interface instead of ODBC. Doing a test through ODBC and claiming that the results had anything to do with using the database in a real-world situation can't be regarded as fair. They should have done two tests with and without ODBC to provide the right facts (after having gotten experts to tune all involved databases, of course).
• They refer to the TPC-C tests, but they don't mention anywhere that the test they did was not a true TPC-C test and they were not even allowed to call it a TPC-C test. A TPC-C test can only be conducted by the rules approved by the TPC Council (http://www.tpc.org/). Great Bridge didn't do that. By doing this they have both violated the TPC trademark and miscredited their own benchmarks. The rules set by the TPC Council are very strict to ensure that no one can produce false results or make unprovable statements. Apparently Great Bridge wasn't interested in doing this.
• After the first test, we contacted Great Bridge and mentioned to them some of the obvious mistakes they had done with MySQL Server:
  • Running with a debug version of our ODBC driver
  • Running on a Linux system that wasn't optimised for threads
  • Using an old MySQL version when there was a recommended newer one available
  • Not starting MySQL Server with the right options for heavy multi-user use (the default installation of MySQL Server is tuned for minimal resource use)
  Great Bridge did run a new test, with our optimised ODBC driver and with better startup options for MySQL Server, but refused to either use our updated glibc library or our standard binary (used by 80% of our users), which was statically linked with a fixed glibc library. From what we are able to determine, Great Bridge did nothing to ensure that the other databases were set up correctly to run well in their test environment. We are sure, however, that they didn't contact Oracle or Microsoft to ask for their advice in this matter.
• The benchmark was paid for by Great Bridge, and they decided to publish only partial, chosen results (instead of publishing it all).

Tim Perdue, a long-time PostgreSQL fan and a reluctant MySQL user, published a comparison on PHPbuilder (http://www.phpbuilder.com/columns/tim20001112.php3).

When we became aware of the comparison, we phoned Tim Perdue about this because there were a lot of strange things in his results. For example, he claimed that MySQL Server had a problem with five users in his tests, when we know that there are users with similar machines running MySQL Server with 2000 simultaneous connections doing 400 queries per second. (In this case the limit was the web bandwidth, not the database.)

It sounded like he was using a Linux kernel that had some problems with many threads, such as kernels before 2.4, which had a problem with many threads on multi-CPU machines. This manual describes the fix for this and Tim should be aware of this problem.

The other possible problem could have been an old glibc library and that Tim didn't use a MySQL binary from our site, which is linked with a corrected glibc library, but had compiled a version of his own. In any of these cases, the symptom would have been exactly what Tim had measured.

We asked Tim if we could get access to his data so that we could repeat the benchmark and if he could check the MySQL version on the machine to find out what was wrong and he promised to come back to us about this. He has not done that yet.

Because of this we can't put any trust in this benchmark either.

Over time things also change and the preceding benchmarks are no longer very relevant. MySQL Server now has a couple of different storage engines with different speed/concurrency tradeoffs. See section 7 MySQL Table Types. It would be interesting to see how the above tests would run with the different transactional table types in MySQL Server. PostgreSQL has, of course, also got new features since the test was made. As these tests are not publicly available there is no way for us to know how the database would perform in the same tests today.

Conclusion:

The only benchmarks that exist today that anyone can download and run against MySQL Server and PostgreSQL are the MySQL benchmarks. We here at MySQL AB believe that Open Source databases should be tested with Open Source tools. This is the only way to ensure that no one does tests that nobody can reproduce and use this to claim that one database is better than another. Without knowing all the facts it's impossible to answer the claims of the tester.

The thing we find strange is that every test we have seen about PostgreSQL, that is impossible to reproduce, claims that PostgreSQL is better in most cases while our tests, which anyone can reproduce, clearly show otherwise. With this we don't want to say that PostgreSQL isn't good at many things (it is!) or that it isn't faster than MySQL Server under certain conditions. We would just like to see a fair test where PostgreSQL performs very well, so that we could get some friendly competition going.

For more information about our benchmark suite, see section 5.1.4 The MySQL Benchmark Suite.

We are working on an even better benchmark suite, including multi-user tests, and a better documentation of what the individual tests really do and how to add more tests to the suite.

User Comments

Can MySQL now run the OSDB tests? If so, we
should really get some numbers on that, as
Postgresql can now run it. It would be nice to
have a NEUTRAL test to compare the two on, not
something MySQL wrote, since, no matter how fair
you try to be, you'll always be accused of
favoring MySQL in your benchmark

Add your own comment.