Обсуждение: Benchmark comparing PostgreSQL, MySQL and Oracle

Hi,

I've made a benchmark comparing PostgreSQL, MySQL and Oracle under three environments: GNU/Linux-x86, Solaris-x86 (same
machineas 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

On Friday 20 February 2009, Sergio Lopez <sergio.lopez@nologin.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.-Vola
>tile-Storage..html

How did you get permission from Oracle to publish benchmarks?

--
Even a sixth-grader can figure out that you can’t borrow money to pay off
your debt

El Fri, 20 Feb 2009 08:36:44 -0800
Alan Hodgson <ahodgson@simkin.ca> escribió:

> On Friday 20 February 2009, Sergio Lopez <sergio.lopez@nologin.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.-Vola
> >tile-Storage..html
>
> How did you get permission from Oracle to publish benchmarks?
>

Damn, my Oracle's Evaluation License should be void now ;-). Sometimes,
software licenses are somewhat funny.

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

> On Fri, Feb 20, 2009 at 6:28 AM, Sergio Lopez
> <sergio.lopez@nologin.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.

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

60% what?

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

I can believe that MySQL could come out faster than PG because I've
had previous experience with it being blindingly fast.  Of course I've
also had experience with it having amazingly poor data integrity.  I
would be pretty surprised if Oracle were in general twice as fast as
PG - what are they doing that much better than what we're doing?  I
could certainly imagine it being true in cases that rely on specific
features we lack (e.g. join removal)?

...Robert

On Fri, Feb 20, 2009 at 2:48 PM, Jonah H. Harris <jonah.harris@gmail.com> wrote:
> On Fri, Feb 20, 2009 at 1:15 PM, Sergio Lopez <sergio.lopez@nologin.es>
> wrote:
>>
>> 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.

ISTM you are the one throwing out unsubstantiated assertions without
data to back it up.  OP ran benchmark. showed hardware/configs, and
demonstrated result.  He was careful to hedge expectations and gave
rationale for his analysis methods.

If you think he's wrong, instead of picking on him why don't you run
some tests showing alternative results and publish them...leave off
the oracle results or use a pseudo-name or something.

merlin

On Fri, Feb 20, 2009 at 4:34 PM, Jonah H. Harris <jonah.harris@gmail.com> wrote:
> On Fri, Feb 20, 2009 at 3:40 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>
>> ISTM you are the one throwing out unsubstantiated assertions without
>> data to back it up.  OP ran benchmark. showed hardware/configs, and
>> demonstrated result.  He was careful to hedge expectations and gave
>> rationale for his analysis methods.
>
> As I pointed out in my last email, he makes claims about PG being faster
> than Oracle and MySQL based on his results.  I've already pointed out
> significant tuning considerations, for both Postgres and Oracle, which his
> benchmark did not take into account.
>
> This group really surprises me sometimes.  For such a smart group of people,
> I'm not sure why everyone seems to have a problem pointing out design flaws,
> etc. in -hackers, yet when we want to look good, we'll overlook blatant
> flaws where benchmarks are concerned.

The biggest flaw in the benchmark by far has got to be that it was
done with a ramdisk, so it's really only measuring CPU consumption.
Measuring CPU consumption is interesting, but it doesn't have a lot to
do with throughput in real-life situations.  The benchmark was
obviously constructed to make PG look good, since the OP even mentions
on the page that the reason he went to ramdisk was that all of the
databases, *but particularly PG*, had trouble handling all those
little writes.  (I wonder how much it would help to fiddle with the
synchronous_commit settings.  How do MySQL and Oracle alleviate this
problem and we can usefully imitate any of it?)

Still, if you read his conclusions, he admits that he's just trying to
show that they're in the same ballpark, and that might well be true,
even with the shortcomings of the tests.

Personally, I'm not as upset as you seem to be about the lack of
perfect tuning.  Real-world tuning is rarely perfect, either, and we
don't know that his tuning was bad.  We do know that whatever tuning
he did was not adequately documented, and we can suspect that items
mentioned were not tuned, but we really don't know that.  We have
plenty of evidence from these lists that fiddling with shared_buffers
(either raising or even sometimes lowering it), page and tuple costs,
etc. can sometimes produce dramatic performance changes.  But that
doesn't necessarily tell you anything about what will happen in a real
life application with a more complex mix of queries where you can't
optimize for the benchmark.

>> If you think he's wrong, instead of picking on him why don't you run
>> some tests showing alternative results and publish them...leave off
>> the oracle results or use a pseudo-name or something.
>
> One of these days I'll get some time and post my results.  I'm just pointing
> out obvious flaws in this benchmark.  If Sergio wants to correct them and/or
> qualify them, that's cool with me.  I just don't like people relying on
> questionable and/or unclear data.

I'd love to see more results.  Even if they're not 100% complete and
correct they would give us more of a notion than we have now of where
more work is needed.  I was interested to see that Oracle was the
runaway winner for bulk data load because I did some work on that a
few months back.  I suspect a lot more is needed there, because the
work I did would only help with create-table-as-select or copy, not
retail insert, and even at that I know that the cases I did handle
have room for further improvement.

I am not certain which database is the fastest and suspect there is no
one answer.  But if we get some information that helps us figure out
where we can improve, that is all to the good.

...Robert

On Fri, Feb 20, 2009 at 2:54 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Feb 20, 2009 at 4:34 PM, Jonah H. Harris <jonah.harris@gmail.com> wrote:
>> On Fri, Feb 20, 2009 at 3:40 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>>
>>> ISTM you are the one throwing out unsubstantiated assertions without
>>> data to back it up.  OP ran benchmark. showed hardware/configs, and
>>> demonstrated result.  He was careful to hedge expectations and gave
>>> rationale for his analysis methods.
>>
>> As I pointed out in my last email, he makes claims about PG being faster
>> than Oracle and MySQL based on his results.  I've already pointed out
>> significant tuning considerations, for both Postgres and Oracle, which his
>> benchmark did not take into account.
>>
>> This group really surprises me sometimes.  For such a smart group of people,
>> I'm not sure why everyone seems to have a problem pointing out design flaws,
>> etc. in -hackers, yet when we want to look good, we'll overlook blatant
>> flaws where benchmarks are concerned.
>
> The biggest flaw in the benchmark by far has got to be that it was
> done with a ramdisk, so it's really only measuring CPU consumption.
> Measuring CPU consumption is interesting, but it doesn't have a lot to

Agreed.  As soon as I saw that I pretty much threw the results out the window.

Robert Haas wrote:
>
> The biggest flaw in the benchmark by far has got to be that it was
> done with a ramdisk, so it's really only measuring CPU consumption.
> Measuring CPU consumption is interesting, but it doesn't have a lot to
> do with throughput in real-life situations.
>
... and memory access. Measuring these two in isolation from any
(real/usual) io system is interesting but perhaps only as a curiosity -
however, it would become much more interesting if we could see how the
results change when a disk based filesystem is used (or even raw for the
big O and innodb and filesystem for postgres...).

regards

Mark

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

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

El Fri, 20 Feb 2009 16:54:58 -0500
Robert Haas <robertmhaas@gmail.com> escribió:

> On Fri, Feb 20, 2009 at 4:34 PM, Jonah H. Harris
> <jonah.harris@gmail.com> wrote:
> > On Fri, Feb 20, 2009 at 3:40 PM, Merlin Moncure
> > <mmoncure@gmail.com> wrote:
> >>
> >> ISTM you are the one throwing out unsubstantiated assertions
> >> without data to back it up.  OP ran benchmark. showed
> >> hardware/configs, and demonstrated result.  He was careful to
> >> hedge expectations and gave rationale for his analysis methods.
> >
> > As I pointed out in my last email, he makes claims about PG being
> > faster than Oracle and MySQL based on his results.  I've already
> > pointed out significant tuning considerations, for both Postgres
> > and Oracle, which his benchmark did not take into account.
> >
> > This group really surprises me sometimes.  For such a smart group
> > of people, I'm not sure why everyone seems to have a problem
> > pointing out design flaws, etc. in -hackers, yet when we want to
> > look good, we'll overlook blatant flaws where benchmarks are
> > concerned.
>
> The biggest flaw in the benchmark by far has got to be that it was
> done with a ramdisk, so it's really only measuring CPU consumption.
> Measuring CPU consumption is interesting, but it doesn't have a lot to
> do with throughput in real-life situations.  The benchmark was
> obviously constructed to make PG look good, since the OP even mentions
> on the page that the reason he went to ramdisk was that all of the
> databases, *but particularly PG*, had trouble handling all those
> little writes.  (I wonder how much it would help to fiddle with the
> synchronous_commit settings.  How do MySQL and Oracle alleviate this
> problem and we can usefully imitate any of it?)
>

The benchmark is NOT constructed to make PostgreSQL look good, that
never was my intention. All databases suffered the I/O bottleneck for
their redo/xlog/binary_log files, specially PostgreSQL but closely
followed by Oracle. For some reason MySQL seems to deal better with I/O
contention, but still gives numbers that are less than the half it gives
with tmpfs.

While using the old array (StorageTek T3), I've played with
synchronous_commit, wal_sync_method, commit_delay... and only setting
wal_sync_method = open_datasync (which, in Solaris, completly disables
I/O syncing) gave better results, for obvious reasons.

Anyway, I think that in the next few months I'll be able to repeat the
tests with a nice SAN, and then we'll have new numbers that will be
more near to "real-world situations" (but synthetic benchmarks always
are synthetic benchmarks) and also we'll be able to compare them with
this ones to see how I/O contetion impacts on each database.

Hi all,

As the author of BenchmarkSQL and the founder of EnterpriseDB....  I
can assure you that BenchmarkSQL was NOT written specifically for
PostgreSQL.    It is intended to be a completely database agnostic
tpc-c like java based benchmark.

However; as Jonah correctly points out in painstaking detail:
PostgreSQL is good, but...  Oracle, MySQL/Innodb and and and don't
necessarily suck.  :-)

--Luss

PS:   Submit a patch to BenchmarkSQL and I'll update it.


On 2/20/09, Sergio Lopez <sergio.lopez@nologin.es> wrote:
> El Fri, 20 Feb 2009 16:54:58 -0500
> Robert Haas <robertmhaas@gmail.com> escribió:
>
>> On Fri, Feb 20, 2009 at 4:34 PM, Jonah H. Harris
>> <jonah.harris@gmail.com> wrote:
>> > On Fri, Feb 20, 2009 at 3:40 PM, Merlin Moncure
>> > <mmoncure@gmail.com> wrote:
>> >>
>> >> ISTM you are the one throwing out unsubstantiated assertions
>> >> without data to back it up.  OP ran benchmark. showed
>> >> hardware/configs, and demonstrated result.  He was careful to
>> >> hedge expectations and gave rationale for his analysis methods.
>> >
>> > As I pointed out in my last email, he makes claims about PG being
>> > faster than Oracle and MySQL based on his results.  I've already
>> > pointed out significant tuning considerations, for both Postgres
>> > and Oracle, which his benchmark did not take into account.
>> >
>> > This group really surprises me sometimes.  For such a smart group
>> > of people, I'm not sure why everyone seems to have a problem
>> > pointing out design flaws, etc. in -hackers, yet when we want to
>> > look good, we'll overlook blatant flaws where benchmarks are
>> > concerned.
>>
>> The biggest flaw in the benchmark by far has got to be that it was
>> done with a ramdisk, so it's really only measuring CPU consumption.
>> Measuring CPU consumption is interesting, but it doesn't have a lot to
>> do with throughput in real-life situations.  The benchmark was
>> obviously constructed to make PG look good, since the OP even mentions
>> on the page that the reason he went to ramdisk was that all of the
>> databases, *but particularly PG*, had trouble handling all those
>> little writes.  (I wonder how much it would help to fiddle with the
>> synchronous_commit settings.  How do MySQL and Oracle alleviate this
>> problem and we can usefully imitate any of it?)
>>
>
> The benchmark is NOT constructed to make PostgreSQL look good, that
> never was my intention. All databases suffered the I/O bottleneck for
> their redo/xlog/binary_log files, specially PostgreSQL but closely
> followed by Oracle. For some reason MySQL seems to deal better with I/O
> contention, but still gives numbers that are less than the half it gives
> with tmpfs.
>
> While using the old array (StorageTek T3), I've played with
> synchronous_commit, wal_sync_method, commit_delay... and only setting
> wal_sync_method = open_datasync (which, in Solaris, completly disables
> I/O syncing) gave better results, for obvious reasons.
>
> Anyway, I think that in the next few months I'll be able to repeat the
> tests with a nice SAN, and then we'll have new numbers that will be
> more near to "real-world situations" (but synthetic benchmarks always
> are synthetic benchmarks) and also we'll be able to compare them with
> this ones to see how I/O contetion impacts on each database.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

El Sat, 21 Feb 2009 21:04:49 -0500
"Jonah H. Harris" <jonah.harris@gmail.com> escribió:

> On Fri, Feb 20, 2009 at 8:40 PM, Denis Lussier <
> denis.lussier@enterprisedb.com> wrote:
>
> > Hi all,
> >
> > As the author of BenchmarkSQL and the founder of EnterpriseDB....  I
> > can assure you that BenchmarkSQL was NOT written specifically for
> > PostgreSQL.    It is intended to be a completely database agnostic
> > tpc-c like java based benchmark.
>
>
> With the exception that it analyzes Postgres tables but not Oracle or
> InnoDB, I agree with that.  The goal of BenchmarkSQL was to be a
> database agnostic benchmark kit.
>

I've just made the same tests analyzing Oracle (with the dbms.stats
package) and not analyzing Postgres, and results are almost the same
as the ones obtained before. The queries and schema used by BenchmarkSQL
seem to be too simple to let place for plan optimization.

On the other hand... you were right. My benchmark has a serious flaw,
but it isn't in database configuration, but in the client which runs the
tests, which is a bottleneck for all the environments.

I've just solved this issue, and I'm now running again the tests and
Oracle defeats PostgreSQL by far.

I've taken down the article and I'll bring up it again when I've
collected new numbers.

I must say thanks to your skepticism ;-)

On Mon, Feb 23, 2009 at 1:29 PM, Sergio Lopez <sergio.lopez@nologin.es> wrote:
> El Sat, 21 Feb 2009 21:04:49 -0500
> I've taken down the article and I'll bring up it again when I've
> collected new numbers.

Please do, this subject is very interesting.

Regards.