Re: Benchmark comparing PostgreSQL, MySQL and Oracle

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

> On Fri, Feb 20, 2009 at 6:28 AM, Sergio Lopez
> <sergio(dot)lopez(at)nologin(dot)es>wrote:
>
> > Hi,
> >
> > I've made a benchmark comparing PostgreSQL, MySQL and Oracle under
> > three environments: GNU/Linux-x86, Solaris-x86 (same machine as
> > GNU/Linux) and Solaris-SPARC. I think you might find it interesting:
> >
> >
> > http://blogs.nologin.es/slopez/archives/17-Benchmarking-Databases-I.-Volatile-Storage..html
>
>
> Sorry Segio,
>
> In addition to violating your Oracle license, you need to learn a
> couple things about benchmarking.
>
> First of all, you need to do some research on the benchmark kit
> itself, rather than blindly downloading and using one. BenchmarkSQL
> has significant bugs in it which affect the result. I can say that
> authoritatively as I worked on/with it for quite awhile. Don't trust
> any result that comes from BenchmarkSQL. If you fix the bugs, Oracle
> (out of the box in OLTP config) will come out 60%.
>
> Oracle comes out twice as fast as PG on Linux. And, unless you're
> using a significant number of warehouses, MySQL+InnoDB will come out
> better than PG as well.
>
> Second, I didn't see anything in your Oracle settings for parallelism
> and I/O tuning. Did you set them? And, based on what you presented,
> you didn't set configure the SGA appropriately given the hardware
> mentioned. What was your log buffer set to?
>
> Third, did you manually analyze the Oracle/MySQL databases, because
> BenchmarkSQL will automatically analyze Postgres' tables to help the
> optimizer... did you do the same for the other databases?
>
> Fourth, it didn't look like you tuned PG properly either. What was
> shared_buffers, wal_buffers, and wal_sync_method set to?
>
> Fifth, did you do an out-of-the-box install of Oracle, or a custom
> one? If out of the box, did you choose OLTP or General?
>
> There's lots of other things I could go on about in regard to
> flushing all the caches prior to starting the benchmarks, filesystem
> options, etc.
>
> Not trying to be rude, but *THIS* is why Oracle, IBM, Microsoft, et
> al. don't want people running benchmarks without their permission.
> When performing benchmarks, there are a lot of things to take into
> consideration. If you're just performing out-of-the-box tests, then
> that's fine, but you have to make sure the benchmark kit doesn't
> optimize itself for any one of those databases (which it does for PG).
>

First, thanks for your thoughts, I found them very interesting.

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.

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

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.

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.

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.

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

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.