Facebook created MyRocks, a storage engine for MySQL, based on their storage RocksDB. A working implementation of MyRocks in the form of MySQL 5.6 source with Facebook patches is open sourced and hosted on GitHub — https://github.com/facebook/mysql-5.6. As an alternative to InnoDB, RocksDB offers several advantages:
All this greatly increases the transaction speed on the HDD, reduces wear on the SSD, and accelerates replication.
MariaDB and Percona are already working to integrate MyRocks into their MySQL forks: Facebook MyRocks at MariaDB, Announcing MyRocks in Percona Server for MySQL. MariaDB announced that MyRocks will be available in release candidate 10.2 this winter. Jetware has included Facebook’s original MyRocks implementation into MySQL alternatives.
Benchmarking on synthetic tests shows impressive results. Depending on the type of storage device, the gain in speed is from 20% to 10 times. LinkBench test results are available in the publications of Yoshinori Matsunobu MyRocks: A space- and write-optimized MySQL database and on Mark Callaghan’s blog, e.g., MyRocks: use less IO on writes to have more IO for reads. These tests are mainly oriented toward large data sets (tens or hundreds of gigabytes) and powerful machines.
In addition to the synthetic tests and benchmarking on large data sets, we decided to measure and evaluate the performance benefits for typical Web applications and small- and medium-sized sites.
First, we tested Redmine. We know how it works and we use it actively in our development, therefore, testing also has practical value for us: if the result is good, then we’ll switch to MyRocks.
We use Redmine 3.3.1 on Ruby 2.3.1 (default configuration) without additional plug-ins.
As database servers, we use:
All binaries are built by GCC 4.9.3, with recommended build and optimization settings.
Operating system - Ubuntu 14.04 x86_64, Linux kernel 3.13.0. Filesystem - ext4.
The database is filled before performing the tests with pre-generated projects, users, and tasks:
Most real-use cases with Redmine have sizes ranging between small and large datasets. Cases of the giant dataset level are much rarer.
File space usage
The bars show the volume of the used space for different databases and different datasets (less is better).
We imitate Redmine’s work in conditions similar to the cloud provider or on the office server. For this purpose, we allocate not the entire physical server, but a virtual machine with far fewer resources, and simulate different disk system loads from neighbors. We use Xen 4.6 as the virtualization platform with Linux kernel 3.16.7 in dom0. The storage device is partitioned using LVM - simple linear, without thin provision and snapshots. The volume is located in the middle of the HDD.
We use three configurations:
We test the speed of the most commonly used Redmine operations: create issue, add comments to issue, and change the status and the assigned person of issue. Using these operations, we create and test two tasks:
Create issue
Create issue in a project by one of the project members, assign the issue to another member of the project, and add 10 comments to the issue from other project members.
Process 10 issues
Select some random user, get 10 issues assigned to this user, set them all to the “In progress” status, then change each one to “Resolved” status and assign it back to the author.
Benchmarks are carried out with 1, 2 and 4 parallel Redmine processes.
The load is created using the fio utility that reads and writes 50/50 random blocks in the remaining part of the disk. We simulate several disk load levels, which represent the typical use cases of virtual machines from public cloud and VPS providers, or when running multiple virtual machines on your own server under VMWare, Hyper-V, KVM, or XenServer.
To simulate partial load, we run fio with IOPS limits using the --rate_iops option and measure the disk utilization. With 100% single-threaded load, we have about 80 IOPS. A limit of 14 IOPS creates 25% load utilization. The greater load is simulated by increasing the IO flow number using the --iodepth option.
Depending on the number of neighboring virtual machines and the nature of their work and the load peaks, the disk load may be quite different on public cloud, VPS, or your own server. Therefore, we performed tests in the absence of external loads, with little single-flow load (14 IOPS, 25%) and with the full external load, with 1, 2, and 4 flows.
We measure the full time for each Redmine operation execution on a large number of operations and compare the average execution time. The first 10% of the results are ignored as they warm up the system. The last 10% of the results are ignored in order to avoid tail distortion due to the different completion times of parallel processes.
Measurements are made with different condition combinations:
Execution time is measured for all three databases, MyRocks MySQL, MySQL, and MariaDB. We also calculate the difference in MyRocks MySQL speed relative to MySQL and MariaDB. The collected data are presented as charts.
Operation execution time charts
1) Create issue; 2) Process 10 issues
The bars show the full operation execution time (less is better). The lines show how many times the MySQL or MariaDB server was slower than MyRocks MySQL.
Minimum load: one Redmine process and the absence of external load. Maximum load - four Redmine processes and four full flows of the external disk load.
We see that the time to create an issue for MyRocks varies slightly when increasing load to the maximum, from 0.018 seconds to 0.023 seconds at 23%. For MySQL and MariaDB, the minimum issue creating time is 0.022 seconds, which increases tenfold to 0.23 seconds at maximum load. At minimum load, MySQL and MariaDB are slower at 24% than MyRocks; at maximum load, they are 9.5 times slower.
Issue processing time for MyRocks increases from 0.245 seconds with a minimum load to 0.327 seconds at maximum load, at 33%. For MySQL and MariaDB, the minimum issue processing time increases about seven times, from 0.283 seconds at minimum load to 2.245 seconds at the maximum.
The amount of RAM is not enough to effectively cache reads and this seriously affects the InnoDB speed.
Operation execution time charts
1) Create issue; 2) Process 10 issues
The bars show the full operation execution time (less is better). The lines show how many times the MySQL or MariaDB server was slower than MyRocks MySQL.
Minimum load: one Redmine process and the absence of external load. Maximum load - four Redmine processes and four full flows of the external disk load.
In this configuration, virtual machine resources better fit the data size and load profile. For MyRocks, the issue creating time remains the same (0.018–0.023 seconds), growing by 23%. For MySQL and MariaDB, the minimum time becomes a little more—from 0.023 seconds (growing only two times) to 0.056 seconds at maximum load; in other words, they are 30% slower than MyRocks at minimum load and 2.3 times slower at the maximum.
For issue processing, the situation is similar. Execution time for MyRocks grows slightly with increasing load: from 0.248 seconds to 0.331 seconds. For MySQL and MariaDB, the minimum time is 10% more than for a small dataset (0.296 s). At the maximum load, the time is almost doubled (0.595 s). MySQL and MariaDB are 18% slower than MyRocks at minimum load and 80% slower at the maximum.
Operation execution time charts
1) Create issue; 2) Process 10 issues
The bars show the full operation execution time (less is better). The lines show how many times the MySQL or MariaDB server was slower than MyRocks MySQL.
Minimum load: one Redmine process and the absence of external load. Maximum load - four Redmine processes and four full flows of the external disk load.
A ten-fold growth in data size slightly increased the issue creating time for all databases: 0.020 seconds for MyRocks, 0.026 for MySQL, and 0.029 for MariaDB. Load increment slows MyRocks by 35% (to 0.027 seconds). Load increment affects MySQL and MariaDB more significantly; for example, at maximum load, the time is increased by 3 times (to 0.088 seconds) and they become 3.2 times slower than MyRocks.
For issue processing, the execution time in MyRocks increases by 32% (0.255 to 0.33 seconds), whereas for MySQL and MariaDB it increases by 4 times (0.309 to 1.242 seconds); that is, they lag behind MyRocks by 3.8 times.
The amount of data grows to such a size that they begin to affect the random write delays when updating the InnoDB indexes and the difference in speed between RocksDB and InnoDB at maximum loads increases.
1 Gb RAM is the minimum recommended for Redmine. This memory is too small for efficient data caching in the page cache; thus, performance is very sensitive to the disk load. The delays happen even in SELECT-queries since they have to read data from the disk. The smaller amount of stored data in RocksDB leads to more efficient read caching than with InnoDB. Therefore, the operations speed is only slightly changed when using MyRocks even under heavy load.
After doubling the memory, the main data now fit in the page cache, which means the database server no longer needs to constantly read them from disk. In this case, the disk is a bottleneck only for database changes. Transactions are written on the disk without the writeback cache and the intensive disk load increases the time for write completion.
The organization of data storage in RocksDB supports linear writing while the lower writable data size reduces the number of write operations. Thus, we observe that even at high disk load, the speed of the transaction in RocksDB is only slightly reduced and is much higher than when using InnoDB.
Knowing the work principle of RocksDB, we expected accelerated transactions. The developers obtained a 10x speed boost of the database in synthetic tests. For applications such as Redmine, the full time of the operation consists of the Ruby-script execution time and the time of the database query. Of course, the replacement of the storage engine on RocksDB does not increase the speed of Ruby and this part remains unchanged. However, even taking all this into account, the speed increment resulting from the database acceleration was impressive.
Here, we present the edge case test results for the 2 Gb virtual machine and the large dataset, and the 8 Gb virtual machine and the giant dataset. We do not consider testing with a high load for the 1 Gb virtual machine due to an extreme shortage of resources.
Operation execution time charts
1) Create issue; 2) Process 10 issues
The bars show the full operation execution time (less is better).
Minimum load: one Redmine process and the absence of external load. Maximum load - four Redmine processes and four full flows of the external disk load.
At low load, Redmine on MyRocks is 15%–25% faster than MySQL and MariaDB with InnoDB. The size of the stored data has little influence on speed for either RocksDB or InnoDB. For all databases, increasing the Redmine issues number 10-fold increases execution time only by about 10%.
At high load (increased number of parallel processes and increased external disk load), the behavior changes completely as the gain of MyRocks rose from 2-fold to almost 4-fold. The size of the stored data also had a noticeable effect on the speed. A 10-fold increase in the Redmine issues number significantly (1.5–2 times) slowed the speed of servers with InnoDB and (less markedly) slowed the RocksDB (0%–15%).
The simultaneous increase in stored data size and high load slowed Redmine work with MyRocks by 1.5 times, whereas Redmine on MySQL and MariaDB became 4 times slower.
We have discovered a nuance in behavior with some Redmine SQL queries with issue parent conditions. This caused speed degradation on some kinds of search in MyRocks. However, it is not a bug in MyRocks, but a small omission in Redmine: the issues table had no index for the parent_id column. We also encountered a little bug that leads to high CPU consumption after conflict transactions in MyRocks.
We are not faced with other problems. According to developers, Facebook has used MyRocks in production for a long time.
You can use MyRocks right now or wait for more extensive testing after the appearance of MyRocks in MariaDB release candidate 10.2 or Percona Server for MySQL.
MyRocks is published in the Jetware software collection as one of the mysqld alternatives and is available in the stack constructors for PHP (LAMP, LEMP), Ruby (RAMP, REMP), or in the application’s constructors, e.g., Redmine.
A few weeks ago, we switched our internal Redmine server to MyRocks and are successfully working on it now.
In this benchmarking, we tested the performance of MyRocks with the Redmine application. In the following, we test the performance of MyRocks with some PHP-applications. Most likely, the first will be Drupal.
