Table of contents
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:
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
