Re: Benchmark comparing PostgreSQL, MySQL and Oracle

El Fri, 20 Feb 2009 14:48:06 -0500
"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> escribió:

> On Fri, Feb 20, 2009 at 1:15 PM, Sergio Lopez
> <sergio(dot)lopez(at)nologin(dot)es>wrote:
>
> 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.
>

As I written in the article, I only tuned a few parameters and let the
other out-the-box. More info:

- Oracle:
* AMM, sga_max_size/sga_target_size=4GB (yes, it's pretty low
for a 20 GB RAM machine, but remember I needed to run the tests in
another 10 GB RAM SPARC server and still need some more memory for
database and redo (10 warehouses == about 1 GB of data)
* db_block_size=8k (this also answers the other email)
* filesystem_io=setall (which souldn't make difference, anyway)
* db_writer_processes=2 (with a extremly fast tmpfs, incresing this
will obviously be counterproductive)

- MySQL:
* innodb_buffer_pool_size=4GB
* innodb_log_file_size=512MB

- PostgreSQL:
* effective_cache_size=4GB
* shared_pool_size=512MB
* fsync = on
* synchronous_commit = on
* wal_sync_method = fsync
* checkpoint_segments = 100
* checkpoint_completion_target = 0.7

If you have some suggestions to do about this configurations, please
tell me so I can put them in the next benchmark (which, hopefully, will
use a nice performing SAN instead of tmpfs).

> > 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.
>

The bug was in the Delivery transaction, and yes, I fixed it. It was a
simple bad locking behaviour, solved by properly using the "FOR UPDATE"
clause.

> > 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.
>

Please, could you point the bugs (or at least some of them) you're
referring to? That would be very helpful for me, so I can fix them for
the next benchmark.

> 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.
>

_All_ databases can benefit from direct I/O, specially for their redo
files. But, in this benchmark we don't have double buffering (nor
read-ahead) issues, or do you expect Linux or Solaris to cache data
which is already in RAM (tmpfs)?