So I was recently asked to look into why the updated MySQL 5,.6 was slower than the older 5.5
So I started by poking around looking over the standard variables and caches and etc.
The test case was a simple routine that took about twice as long to run on 5.6 than it did on 5.5.
To add to the mix.. the 5.6 version had double the Innodb_buffer_pool_size and of course more ram overall.
So I started some tests with MySQLslap...
Mysqlslap tests show it slower on 5.6
5.6:
mysqlslap --defaults-file=./.my.cnf --concurrency=150 --iterations=130 -query=/test.sql --create-schema=applicationdata --verbose
Benchmark
Average number of seconds to run all queries: 0.028 seconds
Minimum number of seconds to run all queries: 0.019 seconds
Maximum number of seconds to run all queries: 0.071 seconds
Number of clients running queries: 150
Average number of queries per client: 1
5.5:
mysqlslap --defaults-file=./.my.cnf --concurrency=150 --iterations=130 --query=/test.sql --create-schema=applicationdata --verbose
Benchmark
Average number of seconds to run all queries: 0.015 seconds
Minimum number of seconds to run all queries: 0.011 seconds
Maximum number of seconds to run all queries: 0.024 seconds
Number of clients running queries: 150
Average number of queries per client: 1
All of this goes against the public benchmarks
http://dimitrik.free.fr/blog/archives/2013/02/mysql-performance-mysql-56-ga-vs-mysql-55-32cores.html
So I checked disk level --
5.6:
# dd if=/dev/zero of=test bs=1048576 count=2048
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 25.7401 s, 83.4 MB/s
# dd if=test of=/dev/null bs=1048576
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 29.1527 s, 73.7 MB/s
5.5:
# dd if=/dev/zero of=test bs=1048576 count=2048
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 19.9214 seconds, 108 MB/s
# dd if=test of=/dev/null bs=1048576
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 20.0243 seconds, 107 MB/s
Here the disks with 5.5 is slower regardless of MySQL. So in this case.... Look to fixing the disk speed.. MySQL was running fine and will.
So I started by poking around looking over the standard variables and caches and etc.
The test case was a simple routine that took about twice as long to run on 5.6 than it did on 5.5.
To add to the mix.. the 5.6 version had double the Innodb_buffer_pool_size and of course more ram overall.
So I started some tests with MySQLslap...
Mysqlslap tests show it slower on 5.6
5.6:
mysqlslap --defaults-file=./.my.cnf --concurrency=150 --iterations=130 -query=/test.sql --create-schema=applicationdata --verbose
Benchmark
Average number of seconds to run all queries: 0.028 seconds
Minimum number of seconds to run all queries: 0.019 seconds
Maximum number of seconds to run all queries: 0.071 seconds
Number of clients running queries: 150
Average number of queries per client: 1
5.5:
mysqlslap --defaults-file=./.my.cnf --concurrency=150 --iterations=130 --query=/test.sql --create-schema=applicationdata --verbose
Benchmark
Average number of seconds to run all queries: 0.015 seconds
Minimum number of seconds to run all queries: 0.011 seconds
Maximum number of seconds to run all queries: 0.024 seconds
Number of clients running queries: 150
Average number of queries per client: 1
All of this goes against the public benchmarks
http://dimitrik.free.fr/blog/archives/2013/02/mysql-performance-mysql-56-ga-vs-mysql-55-32cores.html
So I checked disk level --
5.6:
# dd if=/dev/zero of=test bs=1048576 count=2048
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 25.7401 s, 83.4 MB/s
# dd if=test of=/dev/null bs=1048576
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 29.1527 s, 73.7 MB/s
5.5:
# dd if=/dev/zero of=test bs=1048576 count=2048
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 19.9214 seconds, 108 MB/s
# dd if=test of=/dev/null bs=1048576
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 20.0243 seconds, 107 MB/s
Here the disks with 5.5 is slower regardless of MySQL. So in this case.... Look to fixing the disk speed.. MySQL was running fine and will.
