MySQL 5.6 and MySQL 5.5 performance comparison

 

Preamble

I have seen many blog post over Internet questioning the performances of MySQL 5.6. There is even one on world know mysqlperformance blog from Peter Zaitsev called Is MySQL 5.6 slower than MySQL 5.5?. I had caution look to all comments and I have to say that results from various person that replied were diverging a lot…

On top of this, internally I have more and more pressure to make MySQL 5.6 standard release for MySQL. So I have finally decided to conduct very basic test using a tool I have already work on called Sysbench (0.4.12).

For this performance comparison testing I have used Oracle Linux Server release 6.4 64 bits (updated from Oracle public yum at the time of writing of this post) under VirtualBox 4.2.10 r84104. I have allocated 2 cores of my physical processor (Intel Xeon E5507 at 2.27 GHz) and 4GB of RAM (I have 8 GB physical RAM). I downloaded MySQL Community Server generic Linux 64 bits edition (tar files) for MySQL 5.5.30 and MySQL 5.6.10 (latest releases available at the time of writing of this post).

Configuration

To have two MySQL installations on same server I had to refine a bit my way of installing MySQL, so chosen the below naming convention:

/mysql/backup/x.y.z for backup files if any
/mysql/data01/x.y.z for database files (letting capability to create data02 to spread the I/Os)
/mysql/dump/x.y.z for log files
/mysql/logs/x.y.z for mysql binary logs
/mysql/software/x.y.z for binaries

Where x.y.z if MySQL exact release, under /mysql/software/x.y.z I create a conf directory to store my.cnf, pid and lock files.

I add below aliases and PATH to mysql OS account:

PATH=$PATH:$HOME/bin:/mysql/sysbench/sysbench-0.4.12/sysbench
 
export PATH
 
# MySQL
alias start_mysql55='cd /mysql/software/5.5.30/; ./bin/mysqld_safe --defaults-file=/mysql/software/5.5.30/conf/my.cnf &'
alias stop_mysql55="kill \`ps -ef | grep /mysql/software/5.5.30/bin/mysqld | grep -v grep | awk '{print \$2}'\`"
 
alias start_mysql56='cd /mysql/software/5.6.10/; ./bin/mysqld_safe --defaults-file=/mysql/software/5.6.10/conf/my.cnf &'
alias stop_mysql56="kill \`ps -ef | grep /mysql/software/5.6.10/bin/mysqld | grep -v grep | awk '{print \$2}'\`"
 
alias mysql55='/mysql/software/5.5.30/bin/mysql --defaults-file=/mysql/software/5.5.30/conf/my.cnf --user=root'
alias mysql56='/mysql/software/5.6.10/bin/mysql --defaults-file=/mysql/software/5.6.10/conf/my.cnf --user=root'

Then configuring Sysbench for MySQL 5.5 or 5.6 is as simple as (please refer to my other post for issues). I have chosen to recompile it using libraries of exact release I’m currently testing:

./configure --with-mysql-includes=/mysql/software/5.5.30/include --with-mysql-libs=/mysql/software/5.5.30/lib/
make
export LD_LIBRARY_PATH=/mysql/software/5.5.30/lib
./configure --with-mysql-includes=/mysql/software/5.6.10/include --with-mysql-libs=/mysql/software/5.6.10/lib/
make
export LD_LIBRARY_PATH=/mysql/software/5.6.10/lib

On both MySQL instances I created a sysbench account (test default database will be reused):

GRANT ALL PRIVILEGES ON *.* TO 'sysbench'@'localhost' IDENTIFIED BY 'sysbench' WITH GRANT OPTION;
FLUSH PRIVILEGES;

To create my my.cnf file I started from ./support-files/my-small.cnf file adding few specific parameters, no memory tuning, just straightforward. The one for MySQL 5.5.30:

[client]
port = 3316
socket = /mysql/software/5.5.30/conf/mysql.sock
 
[mysqld]
port = 3316
socket = /mysql/software/5.5.30/conf/mysql.sock
basedir = /mysql/software/5.5.30
datadir = /mysql/data01/5.5.30
pid-file = /mysql/software/5.5.30/conf/5.5.30.pid
log-bin = /mysql/logs/5.5.30/mysql-bin.log
log_error = /mysql/dump/5.5.30/server1.err
slow_query_log_file = /mysql/dump/5.5.30/slow.log
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
server-id	= 1
performance_schema = off
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
 
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
 
[mysqlhotcopy]
interactive-timeout

And the one for MySQL 5.6.10:

[client]
port = 3326
socket = /mysql/software/5.6.10/conf/mysql.sock
 
[mysqld]
port = 3326
socket = /mysql/software/5.6.10/conf/mysql.sock
basedir = /mysql/software/5.6.10
datadir = /mysql/data01/5.6.10
pid-file = /mysql/software/5.6.10/conf/5.6.10.pid
log-bin = /mysql/logs/5.6.10/mysql-bin.log
log_error = /mysql/dump/5.6.10/server1.err
slow_query_log_file = /mysql/dump/5.6.10/slow.log
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
server-id = 1
innodb_file_per_table = off
performance_schema = off
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
 
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
 
[mysqlhotcopy]
interactive-timeout

Finally sysbench commands are:

sysbench --test=oltp --mysql-socket=/mysql/software/5.5.30/conf/mysql.sock --mysql-port=3316 --mysql-user=sysbench --mysql-password=sysbench
--mysql-db=test --oltp-table-size=10000000 --num-threads=1 --oltp-read-only=on --init-rng=on --max-requests=0 --max-time=300 [prepare | run]
 
sysbench --test=oltp --mysql-socket=/mysql/software/5.6.10/conf/mysql.sock --mysql-port=3326 --mysql-user=sysbench --mysql-password=sysbench
--mysql-db=test --oltp-table-size=10000000 --num-threads=1 --oltp-read-only=on --init-rng=on --max-requests=0 --max-time=300 [prepare | run]

Remark:
So creating a table with 10 million rows (2400 MB around), initializing random number before each test and running it for 5 minutes with different number of threads (from 1 to 64). Reading multiple posts around and having double checked in MySQL 5.6 official documentation it appears that performance schema is activated by default starting with MySQL 5.6.6 (off by default for previous releases and in all 5.5.x releases) so the performance_schema = off in my.cnf. I finally tested performance activating it as I personally flag this feature with high interest…
Even if it does not change too much as sysbench benchmark is made of one table I also set innodb_file_per_table to off to avoid having one InnoDB file per table and club all of them in big ibdata1 datafile.

Performance comparison results and conclusion

Number of threads MySQL 5.5.30
Number of TPS
MySQL 5.6.10
Performance Schema=off, number of TPS
MySQL 5.6.10
Performance Schema=on, number of TPS
1 143 132 124
8 459 463 388
16 463 463 388
32 432 453 383
64 417 441 370

With an Excel chart:

MySQL 5.6 vs MySQL 5.5 performance
MySQL 5.6 vs MySQL 5.5 performance

From my small test there is not much difference from performance point of view between MySQL 5.5 and MySQL 5.6… if you deactivate Performance Schema. Even if it adds lots of value it may impact your workload up to 20%… With pretty high number of threads (>16) I even see MySQL 5.6 in front of MySQL 5.5.

References

This entry was posted in MySQL and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>