Benchmarking Redmine on MySQL with RocksDB and InnoDB storage

Redmine on MyRocks

Introduction

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:

  • Reduces the amount of writes
  • Better compression
  • Reduces random reads

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.

Testing conditions

Software

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.

Datasets

The database is filled before performing the tests with pre-generated projects, users, and tasks:

  1. Small set
    • 30 users
    • 3 projects of the first, 10 subprojects of the second, with 10 users in each project
    • 1,000 issues having 10 comments each
  2. Large set
    • 1,000 users
    • 10 project of the first, 100 subproject of the second, and 10 subprojects of the third level, with 10 users in each project
    • 10,000 issues having 10 comments each
  3. Giant set
    • 10,000 users
    • 100 project of the first, 1,000 subproject of the second, and 100 subprojects of the third level, with 10 users in each project
    • 100,000 issues having 10 comments each

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

myrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16smalllargegiant0 Mb110 Mb220 Mb330 Mb440 Mb550 Mb660 Mb770 Mb880 Mb990 Mb6.71561.168596.52330.527123.246913.16830.113122.832912.754Data set typeFile space usage
sizemyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16
small6.71530.52730.113
large61.168123.246122.832
giant596.523913.168912.754

Source

The bars show the volume of the used space for different databases and different datasets (less is better).

Hardware

  • CPU: 4-core Xeon E31220 3.10GHz
  • RAM: 16 Gb RAM, DDR3 1333
  • HDD: RAID mirror, 2 x Western Digital RE4 1 Tb

Virtual machine

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:

  1. 1 Gb RAM, 4 CPU, HDD 16 Gb
  2. 2 Gb RAM, 4 CPU, HDD 16 Gb
  3. 8 Gb RAM, 4 CPU, HDD 16 Gb

Benchmarking operations

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.

External disk load

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.

Measured values

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:

  • For different configurations of virtual machines and different datasets
  • For various numbers of concurrent Redmine processes (payload) and various external disk loads

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.

Benchmarking results

Small dataset and small virtual machine

  • Virtual machine: 1 Gb RAM, 4 CPU, HDD 16 Gb
  • Dataset: 30 users, 13 projects and subjprojects, 1,000 issues

Operation execution time charts

1) Create issue; 2) Process 10 issues

1gb-4cpu small createmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower1/01/25%1/x11/x21/x42/02/25%2/x12/x22/x44/04/25%4/x14/x24/x400.030.060.090.120.150.180.210.240.2701.12.23.34.45.56.67.78.89.9Parallel jobs / external disk loadMean operation time, secondsRelative slowdown, times
parallel/loadmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower
1/00.0180.0220.0221.2371.233
1/25%0.0180.0250.0261.3781.398
1/x10.0190.0230.0231.2611.248
1/x20.0190.0330.0341.8021.828
1/x40.0180.0380.0382.0542.071
2/00.020.0240.0241.1961.199
2/25%0.0190.0260.0271.3621.388
2/x10.020.0250.0251.2551.27
2/x20.020.0380.0371.8851.819
2/x40.020.0440.0432.1882.154
4/00.0220.0490.0312.2031.385
4/25%0.0240.080.083.4073.384
4/x10.0240.1330.1225.4895.049
4/x20.0230.1580.166.7776.845
4/x40.0230.2270.2269.7139.651
1gb-4cpu small imitatemyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower1/01/25%1/x11/x21/x42/02/25%2/x12/x22/x44/04/25%4/x14/x24/x400.250.50.7511.251.51.7522.2500.91.82.73.64.55.46.37.28.1Parallel jobs / external disk loadMean operation time, secondsRelative slowdown, times
parallel/loadmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower
1/00.2450.2830.2891.1551.18
1/25%0.250.3040.3031.2121.209
1/x10.2630.2920.2931.1121.115
1/x20.2520.3870.3971.5321.573
1/x40.2510.4270.4451.7021.774
2/00.2640.3110.3051.1781.156
2/25%0.2630.3080.3311.1741.259
2/x10.2660.3070.3071.1531.153
2/x20.2680.420.3991.5681.489
2/x40.2730.4780.4691.7541.72
4/00.2950.4840.3381.6391.145
4/25%0.3110.6590.7082.1182.274
4/x10.3131.0330.9933.33.173
4/x20.3271.3441.3114.114.009
4/x40.3082.1492.2456.9847.295

Source

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.

Large dataset and medium virtual machine

  • Virtual machine: 2 Gb RAM, 4 CPU, HDD 16 Gb
  • Dataset: 1,000 users, 120 projects and subjprojects, 10,000 issues

Operation execution time charts

1) Create issue; 2) Process 10 issues

2gb-4cpu large createmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower1/01/25%1/x11/x21/x42/02/25%2/x12/x22/x44/04/25%4/x14/x24/x400.0070.0140.0210.0280.0350.0420.0490.0560.06300.30.60.91.21.51.82.12.42.7Parallel jobs / external disk loadMean operation time, secondsRelative slowdown, times
parallel/loadmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower
1/00.0180.0240.0241.3011.307
1/25%0.0180.0260.0261.431.408
1/x10.0180.0250.0251.3321.34
1/x20.0190.0360.0361.9551.916
1/x40.0190.0410.0412.2172.185
2/00.020.0260.0261.321.33
2/25%0.0190.0270.0281.4131.432
2/x10.020.0270.0291.3331.444
2/x20.020.0390.041.9532.028
2/x40.020.0480.0472.4582.414
4/00.0220.030.0291.3971.334
4/25%0.0240.0310.031.2521.223
4/x10.0220.0320.0311.4271.39
4/x20.0220.0460.0452.0682.001
4/x40.0230.0560.0542.4192.343
2gb-4cpu large imitatemyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower1/01/25%1/x11/x21/x42/02/25%2/x12/x22/x44/04/25%4/x14/x24/x400.070.140.210.280.350.420.490.560.630.91.051.21.351.51.651.81.952.12.25Parallel jobs / external disk loadMean operation time, secondsRelative slowdown, times
parallel/loadmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower
1/00.2480.2960.2941.1911.185
1/25%0.250.3170.3121.2711.251
1/x10.2550.3430.3241.3461.271
1/x20.2540.4580.4271.8031.681
1/x40.2570.50.4781.9461.861
2/00.2650.3190.3211.2051.212
2/25%0.2650.3380.3391.2771.281
2/x10.2670.340.3361.2731.259
2/x20.270.4750.4791.7571.77
2/x40.2730.5590.5372.051.969
4/00.3280.3660.3621.1171.104
4/25%0.3240.3720.3671.151.135
4/x10.3290.3640.3921.1051.191
4/x20.310.5110.5061.6481.631
4/x40.3310.5950.5811.7991.757

Source

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.

Giant dataset and large virtual machine

  • Virtual machine: 8 Gb RAM, 4 CPU, HDD 16 Gb
  • Dataset: 10,000 users, 1,200 projects and subjprojects, 100,000 issues

Operation execution time charts

1) Create issue; 2) Process 10 issues

8gb-4cpu giant createmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower1/01/25%1/x11/x21/x42/02/25%2/x12/x22/x44/04/25%4/x14/x24/x400.010.020.030.040.050.060.070.080.0900.40.81.21.622.42.83.23.6Parallel jobs / external disk loadMean operation time, secondsRelative slowdown, times
parallel/loadmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower
1/00.020.0260.0291.3011.434
1/25%0.0210.0280.0311.3741.511
1/x10.0210.0360.0381.7341.862
1/x20.0210.0510.0532.4662.585
1/x40.0210.0590.0592.8082.797
2/00.0220.0290.0311.3441.413
2/25%0.0210.0290.0311.3721.468
2/x10.0220.0390.0451.7842.035
2/x20.0220.0590.0622.6892.844
2/x40.0220.0680.0743.0313.308
4/00.0270.0330.0361.2421.348
4/25%0.0270.0340.0371.2611.381
4/x10.0260.0430.0461.6761.793
4/x20.0280.0690.0722.4952.612
4/x40.0270.0860.0883.1753.253
8gb-4cpu giant imitatemyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower1/01/25%1/x11/x21/x42/02/25%2/x12/x22/x44/04/25%4/x14/x24/x400.150.30.450.60.750.91.051.21.3500.511.522.533.544.5Parallel jobs / external disk loadMean operation time, secondsRelative slowdown, times
parallel/loadmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16mysqld-5.6.31 slowermariadb-mysqld-10.1.16 slower
1/00.2550.3130.3091.2291.213
1/25%0.2510.3350.331.3321.314
1/x10.2560.4530.4441.7691.733
1/x20.2580.6240.5572.4222.164
1/x40.260.6750.6212.5952.384
2/00.2670.3380.3351.2661.255
2/25%0.2690.3810.3761.4141.397
2/x10.2740.5730.6012.0922.194
2/x20.2660.7540.7812.8332.931
2/x40.2720.8810.8563.2453.154
4/00.3130.3870.3811.2361.218
4/25%0.3280.4370.4941.3311.506
4/x10.3410.7120.7682.0872.251
4/x20.3381.0411.0683.0843.164
4/x40.331.2591.2423.8123.758

Source

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.

Results analysis

Memory size

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.

Speed: RocksDB vs. 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

createmyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.161/0/2gb-4cpu/large1/0/8gb-4cpu/giant4/x4/2gb-4cpu/large4/x4/8gb-4cpu/giant00.0250.050.0750.10.0180.020.0230.0270.0240.0260.0560.0860.0240.0290.0540.088Parallel jobs / External disk load / Virtual machine / Data set typeMean operation time, seconds
parallel/load/key/sizemyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.16
1/0/2gb-4cpu/large0.0180.0240.024
1/0/8gb-4cpu/giant0.020.0260.029
4/x4/2gb-4cpu/large0.0230.0560.054
4/x4/8gb-4cpu/giant0.0270.0860.088
imitatemyrocks-mysqld-5.6mysqld-5.6.31mariadb-mysqld-10.1.161/0/2gb-4cpu/large1/0/8gb-4cpu/giant4/x4/2gb-4cpu/large4/x4/8gb-4cpu/giant00.40.81.21.60.2480.2550.3310.330.2960.3130.5951.2590.2940.3090.5811.242Parallel jobs / External disk load / Virtual machine / Data set typeMean operation time, seconds

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.

Stability

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.

Conclusion

  1. Redmine with RocksDB has proven to be faster than InnoDB—from 20% at the minimum load to 3 times at the maximum.
  2. Increases in data size and load slowed Redmine with InnoDB by 4 times, whereas Redmine with RocksDB was slowed by only 1.5 times.

P.S. Benchmarking MyRocks with other applications

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.

Published
2017-01-12