Testing MySQL drop-in replacements with MODX Revolution

There are loads of benchmarks, white papers and comparisons between different MySQL drop-in's and storage engines. Most of them are run using Sysbench or similar tool which populates the database, updates and runs different query plans. And to be frank, from own experience it is perfect tool for optimizing MySQL instance on different configurations. Then comes the 'But', but these tests show general performance only. That's pretty much the reason why I decided to benchmark MODX using the most popular snippet of all: getResources and slightly modified getResources that supports ranged queries regardless of what is the parent or resource list; you need to have one or the other for the snippet to work so slight stab between the ribs was necessary.

Database Management Systems and Engines Used

• MariaDB 10.0.11 with XtraDB and TokuDB
• Percona Server 5.6.14-tokudb with XtraDB and TokuDB
• MySQL 5.6.17 with InnoDB

To keep the competition even, I disabled query cache's and for MariaDB disabled the subquery cache also. Here's the slim configuration used. It could have lot more options set that possibly could pinch bit more speed to queries. But this article is not about the perfect configuration. There are great ones in mysql performance blog which is great source for MySQL related topic in general. But the config then.

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP
# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
innodb-strict-mode             = 1
# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096
# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 4G

The test bench

 The test bench is running Fedora 20, processor Core i7-3610QM CPU @ 2.30GHz and 16Gb of memory. HDD is some typical WDD HDD that laptop manufacturers buy in bundles. MODX Revolution version 2.2.11-dev was used with 30000 resources in tree where 5000 have parent 0. The page load timing was started in the beginning of the index file and logged in to csv file after handle request (Which required modification to modResponse class). As httpd server Apache 2 was used with almost default configuration.

The getResources were run with first fetching all siblings and itself with parents=`23` and second loaded range with calculation 23 - 20 and 23 + 20 so in total 41 resources. The tests were run using siege with 8 consecutive "users" and time limit set to 30 minutes. The url's list has 100 randomly picked urls which then was used for all tests. The resource cache was also disabled so every page load actually visited database.

Results

* Green highlights the best performing dbms/engine in it's category
* Values in Seconds

Clear win for MariaDB with TokuDB. The values do cheat the eye as the differences are in milliseconds. Same chart as visual bar chart.

Next table should give bit more clearer idea of how much there really is difference between the configurations in percentages.

Legend: M = MariaDB, My = MySQL, P = Percona Server, Toku = TokuDB (Surprising), Xtra = XtraDB (Even more surprising)

There is some odd variance that does not make sense. But those odd throws could be simply caused by computer doing something on it's own which could explain odd times. The table is quite self explanatory. TokuDB is definitely the engine to choose when looking at read performance (unless you want to use MyISAM but that's out of the question now).

Conclusion for this highly suspectable and definitely not scientific benchmark

I guess most now think that you should choose MariaDB with TokuDB, it is fair conclusion but I would not leave Percona Server out of the equation. Both of the servers perform lot better against Oracle's MySQL server so choosing it ain't really reasonable especially when the numbers show drastic differences. To choose TokuDB or XtraDB is completely different thing then. Both perform better that InnoDB, if you need fulltext indexes then XtraDB is your only option really. TokuDB does not have fulltext indexes yet. TokuDB also does have better compression without losing performance. Though I do not have db size values up right now. Could be that I'll add them later on to this post.

If you are planning to run application with lot of subqueries MariaDB has improved subquery handling which explains bit of the performance improvements. If thinking of which of the organizations have more skill regarding MySQL programming and configuration = Both. So eventually the decision has to be done, as suggestion I would try both MySQL Sandbox and read about how they differ from MariaDB.org and Percona.com.

Personally changed from Percona to MariaDB during these benchmarks, but could have stayed with Percona. Maybe I just chose to support country men (Finnish)