MariaDB 10 VS. Oracle MySQL 5.6 Benchmarked

So after the GA release of MariaDB I couldn’t wait to go for another round of benchmarking just to see the difference in results between Oracle’s MySQL 5.6 and MariaDB 10.0 – it was clear from the results on my previous posting that MariaDB was significantly faster than Oracle’s MySQL however Oracle had more consistent performance.

This time I performed the benchmarks a little differently, rather than using CentOS 6.5 as in previous tests I used Oracle Linux 6 – this is the flavour of Linux I’m currently running on my eng./test server. The hardware is the same as last time [HP Gen8 Proliant Server with 4x Intel® Xeon® E3 Processors, 4GB ECC RAM & a Corsair ForceGS SATA III SSD rated at 6G/s (benchmarked at 576 MB/s)]  however the configuration was also slightly different. I decided to include a separate test for XFS and Ext4 just to see how much of a difference in performance I would see. I was in a bit of a hurry so I just used the default Ext4 settings (no tuning whatsoever) and the XFS settings I had posted in the “5 step LVM/XFS for MySQL” article (https://sql2nosql.wordpress.com/2013/11/19/5-step-lvmxfs-for-mysql/).

I ran the same tpcc-mysql benchmark using 5 data marts and 100 concurrent connections for 120 seconds. The results I have posted here are from the 2nd of a set of 2 tests per scenario. The TpmC results are… *drum roll* as follows:

MariaDB 10 Ext4 Oracle MySQL 5.6 XFS Oracle MySQL 5.6 Ext4 MariaDB 10 XFS
29760 29285.5 28998.5 28917.5

The conclusion is that this time around performance is guaranteed by both implementations, I haven’t gone into major tuning activities on either to see how far I can get with tweaking, the results are based on identical “my.cnf” configurations which I will add at the end of this article.

I’ve also included graphs of the results listed before as well as 10 second interval graphs to show a visual representation of the fluctuations in performance… based on the numbers however it is pretty clear that both implementations are pretty much on par (and up to scratch) so well done to Oracle for the 5.6 implementation of MySQL!

It was interesting to note that MariaDB performed far better on Ext4 than XFS while Oracle’s MySQL was fairly consistent on both. The most stable results were exhibited by MariaDB on XFS, surprisingly enough these were the slowest tests however the difference in performance between each of the tests was marginal. As a side note I guess the performance difference between MySQL 5.5 and 5.6 would be more evident on a more high end server (i.e something with at least 64x cores and over 128 MB RAM), on the hardware I’m benchmarking on it seems 5.5 is actually a bit faster.

Hopefully I’ll have time in the near future to dive deeper into the variety of storage engines offered by MariaDB and work a bit more on tuning these beasts ~ perhaps next time I’ll post some more interesting results!

10 sec interval legend:
BLUE
– MariaDB 10 Ext4
ORANGE – Oracle 5.6 XFS
YELLOW – Oracle 5.6 Ext4
GREEN – MariaDB 10 XFS

MySQL 5.6 vs. MariaDB 10

[root@engineering local]# cat mysql-5.6.17-linux-glibc2.5-x86_64/my.cnf

[client]
port        = 3307
socket        = /db-datadir/data-mysql-5.6.17/mysql.sock

[mysqld]

port        = 3307
socket          = /db-datadir/data-mysql-5.6.17/mysql.sock
basedir         = /usr/local/mysql
datadir         = /db-datadir/data-mysql-5.6.17
pid-file        = /db-datadir/data-mysql-5.6.17/workstation.pid
back_log = 50
max_connections = 100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=/db-datadir/data-mysql-5.6.17/mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 2
server-id = 1
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

[root@engineering local]# cat mariadb-10.0.10-linux-x86_64/my.cnf |grep -v ‘#’

[client]
port        = 3306
socket        = /db-datadir/data-mariadb-10.0.10/mysql.sock

[mysqld]

port        = 3306
socket        = /db-datadir/data-mariadb-10.0.10/mysql.sock
basedir        = /usr/local/mysql
datadir        = /db-datadir/data-mariadb-10.0.10
pid-file    = /db-datadir/data-mariadb-10.0.10/workstation.pid
back_log = 50
max_connections = 100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 240K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=/db-datadir/data-mariadb-10.0.10/mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 2
server-id = 1
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s