Oracle Blog

The State of the Dolphin...

Thursday Dec 11, 2008

We’re very excited to see MySQL 5.1.30 make it to GA status
and are very proud of the work that the MySQL engineering team has done to get
MySQL 5.1 out of the door.

Maybe you’ve seen some claims by others in the MySQL
community that MySQL 5.1 runs slower than MySQL 5.0.Maybe you’ve also seen some claims by others
in the MySQL community that MySQL 5.1 runs faster than MySQL 5.0.

Guess what?They’re
both right.

With database or any other hardware/software performance
tests, you’re always going to see different results because there are \*so\* many
variables that go into the equation.

That being the case, I thought I’d share a little of what we
in the MySQL QA group have seen on the 5.1 vs. 5.0 front.Part of our job in MySQL QA is to routinely
test each MySQL build and compare it to prior versions before release, to see
if any performance regressions have occurred and I would like to share the most
recent results with you.

We did multiple tests with dbt2 benchmark on 5.1.30 GA and
5.0.72 and the chartsbelow represent
the outcomes with different settings for innodb_thread_concurrency parameter. Finding the optimal setting for this parameter largely depends
on hardware, OS and workload.

Chart 1.Results per threads for the 5.0.72 and 5.1.30
innodb-thread-concurrency=1000

Chart 2 Results per threads for the 5.0.72 and 5.1.30
innodb-thread-concurrency=8

Chart 3.Results per threads for the 5.0.72 and 5.1.30
innodb-thread-concurrency=16

Chart 4. results per threads for the 5.0.72 and 5.1.30
innodb-thread-concurrency=0

As seen above, with innodb_thread_concurrency of 8,16 and 1000, MySQL 5.1 out performs
MySQL 5.0, by 21-41% depending upon the threads we use. This also demonstrates improvements
in scalability in 5.1

The best throughput for this combination of hardware, OS and workload for both 5.0 and 5.1 servers were
achieved with innodb_thread_concurrency=0. There is very little difference between 5.0 and 5.1 in this test
(5.0 has very slight edge over 5.1)

Additionally we also run other tests (sysbench) prior to every release. For 5.1 GA release,we found that in
some cases 5.1 performed better, while in some others there was a dead heat between 5.0 and 5.1.In few
others we found 5.0 winning over 5.1

So what does it mean?

To summarize, the performance numbers will always vary when running benchmark tests based on variety
of factors, so the prudent thing for you to do is download the latest MySQL 5.1 for your platform, put it
through the wringer in your environment, and go with what works best for you

Test Details:

Hardware:

- dl360-g5

- Intel x86_64 (Dual Quad-Core Xeon 5345)

- RAM 16GB

- RAID: Smart Array P400i/ 1+0, CACHE 256MB, 4 SAS Disks 10000RPM

OS:

- SLES 10

- kernel - 2.6.16

- fs - ext3

dbt2 test details:

- version of dbt2 test - 0.37

- scale factor - 10 warehouses

- zero "think time" (-z)

How we run the test:

- each run performed 3 times so represented results are average values

- test sequence for each run looks as following:

- copying data files prepared in advance

- start server

- start test

- test for 15 mins

- stop test

- stop server

The following packages were used:

- 5.0.72 x86_64-glibc32

- 5.1.30 x86_64-glibc32

The following InnoDB options were used:

--innodb_status_file=0

--innodb_data_file_path=ibdata1:100M:autoextend

--innodb_buffer_pool_size=2G

--innodb_additional_mem_pool_size=20M

--innodb_log_file_size=650M

--innodb_log_files_in_group=2

--innodb_log_buffer_size=16M

--innodb_support_xa=0

--innodb_doublewrite=0

--innodb_thread_concurrency=<0,8,16,1000>

--innodb_flush_log_at_trx_commit=1

--innodb_flush_method=O_DIRECT

About the author

Trim Pershad manages the MySQL database System QA team, since joining the company over a year ago.
He also has extensive prior experience in testing databases Part of his team's responsibility is to test the MySQL
database and compare performance results with earlier releases to identify any performance regressions.