Tuesday Dec 15, 2009

A new MySQL Milestone Release (5.5.0-M2) has been published

Following our new Milestone-based release model, we've now published MySQL 5.5.0-M2. Please see the "What's New in MySQL 5.5" section of the reference manual for a summary of the most notable changes. The MySQL 5.5.0 Changelog provides a much more detailed list of changes, bug fixes and improvements.

Highlights in this release include:

The InnoDB plugin (currently at version 1.0.5) replaces the built-in InnoDB storage engine. This version includes a number of important performance improvements, especially when running on multi-core CPUs with many concurrent transactions.

Support for "semisynchronous" replication. This is a very useful feature to check out, if you use MySQL replication in a high-availability scenario and you want to ensure that at least one replication slave has received the latest transaction that was committed on the master. The master waits for confirmation from the slave after a commit before it returns control to the application. See the chapter "Semisynchronous replication" in the reference manual for more details. Mark Callaghan and Giuseppe Maxia also provide some more background information about this feature in their blog posts.

You can now use SIGNAL and RESIGNAL statements in stored routines and triggers, as defined by the SQL standard. These statements can be used to raise an error inside of your routines and can define appropriate actions, e.g. displaying an error message or issuing warnings. Check out the reference manual or take a look at Roland Bouman's blog post that provides a quick overview by giving a practical example.

The table partitioning functionality that was introduced with MySQL 5.1 received a large number of bug fixes and improvements. For example, you can now use two new types of user-defined partitioning: RANGE COLUMNS and LIST COLUMNS, providing more flexibility when it comes to defining how a table should be partitioned (e.g. by defining ranges or lists based on DATE, DATETIME or strings). Ranges based on multiple columns are now possible, too. For more information, please see the chapters "RANGE Partitioning", and "LIST Partitioning" in the reference manual.

Other improvements include new LOAD XML statement, which allows you to read data from an XML file into a table, a new TO_SECONDS() date function to calculate the number of seconds since the year zero and a ton of bug fixes.

Binary packages and sources are now available from the MySQL 5.5 download pages. The Bazaar source tree of this milestone release is available on Launchpad, too, in case you want to take a closer look at the ongoing development work in this branch. So please download and toy around with this release – we are looking forward to your feedback!

Thursday Dec 11, 2008

Performance regression testing results or just statistics?

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

In our case we ran CPU bound scenario (dbt2 benchmark with 10 warehouses that fits into buffer pool) and 
varying innodb_thread_concurrency parameter by setting it to: 0, 8, 16, 1000.

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.

About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
5
6
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today