Posted

May 27, 2014

Tags

MySQL, MODX, MariaDB, Percona Server, Performance

Testing MySQL drop-in replacements with MODX Revolution

MODX Revolution tests using MySQL drop-in replacements MariaDB and Percona Server with different storage engines.

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

 Max TimeMin TimeAvg TimeMin AvgMax AvgMax 95thMin 95thAvg 95thPageloadsTotal Time
MariaDB 10.0.11 with TokuDB 2.131353855 1.07883215 1.502151875 1.405026555 1.763738871 2.022300005 1.130641937 1.513115358 7164 10761.40852
MariaDB 10.0.11 with XtraDB 8.889400482 1.122812033 1.540829582 1.446582317 1.805940867 1.982198954 1.282016039 1.603595297 7022 10819.90951
Percona Server 5.6.14-tokudb with TokuDB 2.256978035 1.162441015 1.580878521 1.505892515 1.852781773 1.985646009 1.285663843 1.590114925 6900 10908.15315
Percona Server 5.6.14-tokudb with XtraDB 7.926084042 1.176048994 1.582551181 1.505157709 1.859403014 1.931830883 1.285657883 1.569305935 6844 10830.92167
MySQL 5.6.17 with InnoDB 2.822474003 1.235456944 1.67416389 1.588027597 1.969543219 2.188744068 1.402735949 1.766384926 6577 11010.78759

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.

MariaDB VS * Max Time Min Time Avg Time Min Avg Max Avg Max 95th Min 95th Avg 95th Pageloads Total Time
M Toku VS P Toku 5.725372786 7.460836726 5.107095006 6.930179621 4.924230264 -1.829066333 12.83131524 4.962542868 3.754266212 1.354384838
M Toku VS M Xtra 122.6421789 3.995185376 2.542092829 2.914548534 2.364469589 -2.002799941 12.54832663 5.806117345 2.001973777 0.542144684
M Toku VS P Xtra 115.2327312 8.622791039 5.212774412 6.881430515 5.28072853 -4.575929556 12.83085353 3.645872592 4.568817818 0.643868863
M Toku VS My InnoDB 27.90246926 13.53545626 10.830914 12.22838159 11.02538426 7.905120925 21.48072838 15.44561949 8.543774107 2.290803051
M Xtra VS My InnoDB -103.602997 9.553185997 8.294530538 9.322139129 8.666562931 9.90400077 8.993002725 9.661162246 6.544598868 1.748712663
Percona Server VS *                    
P Toku VS P Xtra 111.3438367 1.163826131 0.105749789 -0.0488073 0.356730173 -2.747438103 -0.000463614 -1.3172661 0.814901048 0.710531466
P Toku VS My InnoDB 22.26602254 6.089994617 5.731745206 5.309450686 6.10944628 9.730669867 8.709426757 10.50320326 4.793351636 0.936490853
P Xtra VS My InnoDB -94.9636224 4.92704152 5.626080671 5.358223269 5.753029565 12.46977367 8.709889491 11.81638222 3.978839133 1.64699492

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)

Share your Thoughts

0 Responses

Your email address will not be published

Please enter your name.
Please enter valid email address.
Please enter your website address.
Please enter your message.
TwitterX Error: Could not load tweets as Twitter responded with the error: 'Invalid or expired token.'.
-->

Contact me with email or add my account to skype

the_dunnock@outlook.com

the_dunnock@outlook.com