Re: Benchmark comparing PostgreSQL, MySQL and Oracle - Mailing list pgsql-performance
| From | Jonah H. Harris |
|---|---|
| Subject | Re: Benchmark comparing PostgreSQL, MySQL and Oracle |
| Date | |
| Msg-id | 36e682920902201148p50a4785fj140053ef41fbb254@mail.gmail.com Whole thread Raw |
| In response to | Re: Benchmark comparing PostgreSQL, MySQL and Oracle (Sergio Lopez <sergio.lopez@nologin.es>) |
| Responses |
Re: Benchmark comparing PostgreSQL, MySQL and Oracle
Re: Benchmark comparing PostgreSQL, MySQL and Oracle Re: Benchmark comparing PostgreSQL, MySQL and Oracle |
| List | pgsql-performance |
On Fri, Feb 20, 2009 at 1:15 PM, Sergio Lopez <sergio.lopez@nologin.es> wrote:
When you make comments such as "As for databases, both Oracle and MySQL show nice numbers, but it's PostgreSQL who stands in the top, giving consistent results with each environment and workload", you should make sure that your test is correct. Otherwise you're making statements without any real basis-in-fact.
Oracle's buffer cache is different than Postgres'. And there are several other tuning paramaters which control how the buffer cache and I/O between cache and disk is performed. Making them the same size means nothing. And, as I said, you still didn't mention other important tuning parameters in MySQL, Postgres, or Oracle. So either you don't know about them, or you didn't bother to tune them, which is odd if you were trying to run a truly comparative benchmark.
Did you fix the bug in, I believe, the Order Status transaction that can cause an endless loop? I would call giving the Postgres optimizer correct statistics and leaving Oracle and MySQL with defaults an optimization.
BenchmarkSQL is flawed. You need to review the code more closely.
You're right, it's not the same. Oracle can benefit by using real direct I/O, not half-baked simulations which still cause double-buffering between the linux page cache and the database buffer cache.
Your benchmark was flawed. You made condescending statements about Oracle and MySQL based on your bad data. That's why they don't let you do it.
Your benchmark was flawed, you didn't tune correctly, and you made statements based on bad data; refute that logic :)On the other hand, I've neved said that what I've done is the
Perfect-Marvelous-Definitive Benchmark, it's just a personal project,
and I don't have an infinite amount of time to invest on it.
When you make comments such as "As for databases, both Oracle and MySQL show nice numbers, but it's PostgreSQL who stands in the top, giving consistent results with each environment and workload", you should make sure that your test is correct. Otherwise you're making statements without any real basis-in-fact.
Having this said, the benchmark is not as unfair as you thought. I've
taken care to prepare all databases to meet similar values for their
cache, buffers and I/O configuration (to what's possible given their
differences), and the I've left the rest as comes by default (for
Oracle I've used the OLTP template).
Oracle's buffer cache is different than Postgres'. And there are several other tuning paramaters which control how the buffer cache and I/O between cache and disk is performed. Making them the same size means nothing. And, as I said, you still didn't mention other important tuning parameters in MySQL, Postgres, or Oracle. So either you don't know about them, or you didn't bother to tune them, which is odd if you were trying to run a truly comparative benchmark.
Yes, BenchmarkSQL is NOT the perfect tool for database benchmarking and
it is NOT a valid TPC-C test (I've made this clear in the article), but
I've looked at its source (you assume I blindly used it, but actually
I've even made some changes to make it work with Ingres for other
purposes) and I find it fair enough due to the simplicity of the
queries it executes. I found no other evident optimization than the
"vacuum analyze" in the LoadData application.
Did you fix the bug in, I believe, the Order Status transaction that can cause an endless loop? I would call giving the Postgres optimizer correct statistics and leaving Oracle and MySQL with defaults an optimization.
Obviously, you can optimize the queries to perform better in Oracle,
the same way you can do with any other DB, but doing that would be
cheating. The key here is to keep the queries as simple as possible,
and BenchmarkSQL does this nicely.
BenchmarkSQL is flawed. You need to review the code more closely.
Of course, my benchmark it's somewhat peculiar by the fact (that you
haven't mentioned) that all databases files reside in volatile storage
(RAM) by using tmpfs, which makes something similar (but not the
same) as using DIRECT_IO with an extremly fast storage. But, again, all
databases are given equal consideration.
You're right, it's not the same. Oracle can benefit by using real direct I/O, not half-baked simulations which still cause double-buffering between the linux page cache and the database buffer cache.
Finally, about the license issue, (also) not trying to be rude,
forbiding people to publish benchmark of their products is simply
stupid (and it lacks for legal basis in most countries). The only reason
they do this is to scare kids and be able to make up their own results.
Of course, if you allow people to publish benchmarks there will be
some loosely done, but also there'll be others properly made (and made
by people non-related with any database vendor).
Your benchmark was flawed. You made condescending statements about Oracle and MySQL based on your bad data. That's why they don't let you do it.
IMHO, worse than having loosely done benchmarks is having people saying
things like "if you fix the bugs, Oracle (out of the box in OLTP
config) will come out 60%" or "Oracle comes out twice as fast as PG on
Linux" without any proof to support this words. At least, benchmarks
are refutable by using logic.
--
Jonah H. Harris, Senior DBA
myYearbook.com
pgsql-performance by date: