GNU Info
(mysql.info)MySQL-PostgreSQL benchmarks
Benchmarking MySQL and PostgreSQL
.................................
The only open source benchmark that we know of that can be used to
benchmark MySQL 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 `SELECT's. 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 deamon) 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 next PostgreSQL release.
The details about the machine we run the benchmark 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 best
database in the world, by just restricting the test to something the
database is very good at and not test anything that the database is not
good at. If one after this publishes the result with a single figure,
things are even easier.
This would be like us measuring the speed of MySQL compared to
PostgreSQL by looking at the summary time of the MySQL benchmarks on
our web page. Based on this MySQL 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 is more than 2000 times faster than
PostgreSQL.
The case is that MySQL does a lot of optimizations that PostgreSQL
doesn't do. This is of course also true the other way around. An SQL
optimizer is a very complex thing, and a company could spend years on
just making the optimizer 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 shows 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 claims that PostgreSQL performs
better than MySQL. These both where 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 against all databases.
One is the benchmark paid for by Great Bridge, which you can read about
at: `http://www.greatbridge.com/about/press.php?content_id=4'.
This is the probably worst benchmark we have ever seen anyone conduct.
This was not only tuned to only test what PostgreSQL is absolutely best
at, it was also totally unfair against every other database involved in
the test.
*NOTE*: We know that not even some of the main PostgreSQL developers
did like the way Great Bridge conducted the benchmark, so we don't
blame them for the way the benchmark was made.
This benchmark has been condemned in a lot of postings and newsgroups so
we will here just shortly repeat some things that where 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 this as a "standard"
benchmark tool is to stretch the truth a long way.
* Great Bridge admitted that they had optimized 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. To say "This process optimizes indexes and
frees up disk space a bit. The optimized 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 ten.
* 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 `SELECT's and `JOIN's (especially
after a `VACUUM()'), but doesn't perform as well on `INSERT's or
`UPDATE's. The benchmarks seem to indicate that only `SELECT's
were done (or very few updates). This could easily explain they
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 that 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 application
uses 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 got 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:
- Running with a debug version of our ODBC driver
- Running on a Linux system that wasn't optimized for threads
- Using an old MySQL version when there was a recommended newer
one available
- Not starting MySQL with the right options for heavy
multi-user use (the default installation of MySQL is tuned
for minimal resource use).
Great Bridge did run a new test, with our optimized ODBC driver
and with better startup options for MySQL, 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.
According to what we know, 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 phpbuider
(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 had a problem with five users in his tests, when
we know that there are users with similar machines as his that are using
MySQL 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 either had some
problems with many threads, such as kernels before 2.4, which had a
problem with many threads on multi-CPU machines. We have documented in
this manual how to fix 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 with.
In any of the above 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 changes and the above benchmarks are not that
relevant anymore. MySQL now have a couple of different table handlers
with different speed/concurrency tradeoffs. Note: Table types. It
would be interesting to see how the above tests would run with the
different transactional table types in MySQL. PostgreSQL has of course
also got new features since the test was made. As the above test are
not publicly available there is no way for us to know how the database
would preform in the same tests today.
Conclusion:
The only benchmarks that exist today that anyone can download and run
against MySQL and PostgreSQL is the MySQL benchmarks. We here at MySQL
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 a 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 shows 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
under certain conditions. We would just like to see a fair test where
they are very good so that we could get some friendly competition going!
For more information about our benchmarks suite Note: MySQL
Benchmarks.
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.
automatically generated by info2www version 1.2.2.9