Redmine on MySQL with RocksDB is faster than with InnoDB, from 20% to 3 times

We compiled a MySQL fork from Facebook with the RocksDB engine instead of InnoDB and tested it with real applications: Drupal, Wordpress, Redmine.

This is an awesome thing. At low load, the gain is small, tens of percent. But with a high load, the gain is significantly. When RocksDB is added to the stable release in MariaDB, I’m sure that within half a year half of the people will switch from InnoDB to RocksDB. Especially, small sites on cloud / VPS and dedicated servers.

What is so good about MyRocks? Linear recording instead of random and reducing the number of disk operations in general. That is, database transactions generate less disk operations, take up less disk queue, and are written much faster.

I collected the test results of real Redmine scenarios in the article, added an analysis of the results and conclusions. Redmine on MySQL with RocksDB turned out to be faster than with InnoDB - from 20% at minimum load to 3 times at maximum. Later I will prepare materials on Drupal and other PHP-applications.

You can check the operation of MyRocks yourself - at the end of the article there are links to installers and virtual machines with LAMP / LEMP / Ruby stacks, assembled with MyRocks instead of MySQL.

Introduction

Facebook, based on its RocksDB storage, created a storage engine for MySQL - MyRocks . The working implementation of MyRocks in the form of MySQL 5.6 with patches from Facebook is transferred to open source and is hosted on GitHub - https://github.com/facebook/mysql-5.6 . MyRocks is an alternative to InnoDB and offers several advantages:

• Recording volume reduction
• Best compression
• Random Read Reduction

All this in total significantly increases the transaction speed on the HDD and reduce the wear of the SSD, as well as speed up replication.

MariaDB and Percona are already working on integrating MyRocks into their MySQL forks: Facebook MyRocks at MariaDB , Announcing MyRocks in Percona Server for MySQL . MariaDB has announced that MyRocks will be available in release candidate 10.2 this winter. On Jetware, an original implementation of Facebook's MyRocks based on MySQL 5.6 has been added to the number of MySQL alternatives .

Performance testing on synthetic tests shows impressive results. Depending on the type of storage device, the speed gain is from 20% to 10x. LinkBench test results can be found in the Yoshinori Matsunobu MyRocks publication: A space- and write-optimized MySQL database and the Mark Callaghan blog , for example, MyRocks: use less IO on writes to have more IO for reads . These tests are largely focused on large volumes of databases (tens and hundreds of gigabytes) and powerful machines.

In addition to synthetic tests and tests on large volumes of data, we decided to test and evaluate the performance gain for typical web applications and small sites.

First we test Redmine. We know how it works, we actively use it in development, and therefore testing also has practical value for us - if the result is good, we switch to MyRocks.

Test conditions

Software

We use Redmine 3.3.1 with Ruby 2.3.1, in the default configuration, without additional plugins.

As database servers we use:

• MyRocks MySQL 5.6.27.75 from Facebook repository, commit bc17d30 ( show variables )
• MySQL 5.6.31 ( show variables )
• MariaDB 10.1.16 ( show variables )

All binaries are compiled with a single GCC 4.9.3 compiler, with recommended build and optimization options.

Operating system - Ubuntu 14.04 x86_64, Linux kernel 3.13.0. The file system is ext4.

Datasets

Before performing the tests, the database is filled with pre-generated projects, users and tasks. There are also three such options:

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

Most real-world use cases of Redmine are between Small and Large. Giant level volumes are much less common.

Space occupied

Initial data

The columns show the amount of space used for different databases and different data sets (less is better)

Equipment

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

Virtual machine

We mimic the work of Redmine in conditions similar to working with a cloud provider or on an office server. To do this, we do not select the entire physical server, but place it in a virtual machine with much fewer resources and simulate the different load on the disk system from neighbors. As a virtualization platform, Xen 4.6 is used, in dom0 - Linux kernel 3.16.7. The storage device is partitioned using LVM, the usual linear, without thin provision and snapshots. The volume is located in the middle of the HDD.

Three virtual machine configurations were used:

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

Test Operations

We check the speed of the operations most commonly used in Redmine - creating tasks, adding comments to tasks, changing the status and responsible person of a task. From these operations, we created two tests, two work cycles of the task:

• Task creation

  
  

  Creating a task in a project by one project participant, assigning it to another project participant and adding 10 comments to it on behalf of other project participants.

  
  

• Processing 10 tasks

  
  

  Getting the user 10 tasks assigned to him, transferring them all to In Progress, and then alternately transferring the task to Resolved and assigning it to the task creator.

  
  

Tests are conducted on 1, 2 and 4 parallel Redmine processes.

Third-party disk load

The load is created using a utility fiothat reads and writes 50/50 random blocks to the remainder of the disk. We simulated several levels of disk load, which are typical for typical virtual machine operation cases - for providers of public clouds and VPS, or when several virtual machines running VMWare, Hyper-V, KVM, or XenServer on our own server.

To simulate incomplete loading, we run fio with IOPS restriction using a key --rate_iopsand measure disk utilization. At 100% single-threaded load, this is about 80 IOPS. 25% recovery is generated by a load of 14 IOPS. A large load is simulated by increasing the number of threads with a wrench --iodepth.

Depending on the number of neighboring virtual machines, the nature of their work and peak loads, disk load can seriously differ both in cloud providers and VPS, and on your own server. Therefore, we tested in the absence of a third-party load, with a slight single-threaded load (14 IOPS, 25%), and with full third-party loads in 1, 2, and 4 flows.

Measured values

We measure the total execution time of each Redmine operation on a large number of operations and compare the average execution time. The first 10% of the results are ignored - on them we warm up the system. The last 10% of the results are ignored in order to exclude tail distortion due to different times of completion of parallel processes.

Measurements are carried out in different combinations of conditions:

• for different virtual machine configurations and different data sets
• for a different number of concurrent Redmine processes (main load) and different third-party disk loads

Runtime is measured for all three databases - MyRocks MySQL, MySQL and MariaDB. We also compute the difference in MySQL MyRocks speed relative to MySQL and MariaDB. The collected data is presented in graphs.

Test results

Small dataset and small virtual machine

• Virtual Machine: 1 Gb RAM, 4 CPU, 16 Gb HDD
• Dataset: 30 users, 13 projects and subprojects, 1000 tasks

Operational timelines

1) task creation; 2) processing 10 tasks

Initial data

The columns show the time it took to complete the operation (less is better). The lines show how many times the MySQL or MariaDB server was slower than the MySQL MyRocks server.

Minimum load - one Redmine process and no third-party load. Maximum load - 4 Redmine processes and 4 full third-party disk load flows.

We see that the task creation time for MyRocks with an increase in the load to the maximum changes slightly, and increases from 0.018 sec to 0.023 sec, by 23%. For MySQL and MariaDB, the minimum task creation time is 0.022 seconds and increases tenfold to 0.23 seconds at maximum load. With minimal workload, MySQL and MariaDB are 24% slower than MyRocks; at maximum load, they are slower 9.5 times.

The task processing time for MyRocks grows from 0.245 seconds at minimum load to 0.327 seconds at maximum, by 33%. For MySQL and MariaDB, the minimum task processing time increases by about 7 times - from 0.283 seconds at minimum load to 2.245 seconds at maximum.

There is not enough RAM for efficient read caching, and this greatly affects InnoDB speed.

Large dataset and medium virtual machine

• Virtual Machine: 2 Gb RAM, 4 CPU, 16 Gb HDD
• Dataset: 1000 users, 120 projects and subprojects, 10000 tasks

Operational timelines

1) task creation; 2) processing 10 tasks

Initial data

The columns show the time it took to complete the operation (less is better). The lines show how many times the MySQL or MariaDB server was slower than the MySQL MyRocks server.

Minimum load - one Redmine process and no third-party load. Maximum load - 4 Redmine processes and 4 full third-party disk load flows.

In this configuration, the resources of the virtual machine better correspond to the amount of data and load. For MyRocks, the task creation time remains the same - from 0.018 seconds to 0.023 seconds, growing by 23%. For MySQL and MariaDB, the minimum time becomes a little longer - 0.023 seconds and grows only twice - up to 0.056 seconds at maximum load. They are slower than MyRocks, by 30% at minimum load, and 2.3 times at maximum.

For processing tasks, the situation is similar. MyRocks runtime with increasing load increases slightly from 0.248 sec to 0.331 sec. For MySQL and MariaDB, the minimum time is already 10% longer than for the Small Dataset and is 0.296 seconds. At maximum load, the time increases almost twice - up to 0.595 seconds. MySQL and MariaDB are slower than MyRocks, at 18% at minimum load and 80% at maximum.

Gigantic dataset and large virtual machine

• Virtual Machine: 8 Gb RAM, 4 CPU, 16 Gb HDD
• Dataset: 10,000 users, 1,200 projects and subprojects, 100,000 tasks

Operational timelines

1) task creation; 2) processing 10 tasks

Initial data

The columns show the time it took to complete the operation (less is better). The lines show how many times the MySQL or MariaDB server was slower than the MySQL MyRocks server.

Minimum load - one Redmine process and no third-party load. Maximum load - 4 Redmine processes and 4 full third-party disk load flows.

A tenfold increase in the amount of data slightly increased the task creation time for all databases: 0.020 sec for MyRocks, 0.026-0.029 for MySQL and MariaDB. Increasing the load slows MyRocks by 35% to 0.027 seconds. For MySQL and MariaDB, the load increase affects the speed more - at maximum load, the time increases 3 times - to 0.088 seconds, and they turn out to be 3.2 times slower than MyRocks.

When processing tasks, the MyRocks execution time increases by 32%, from 0.255 to 0.33 seconds. For MySQL and MariaDB, the time increases 4 times - from 0.309 to 1.242 seconds. And they are 3.8 times behind MyRocks.

The amount of data has already grown to such a size that random write delays when updating InnoDB indices begin to affect and the difference in speed between RocksDB and InnoDB at maximum loads has grown again.

Results Analysis

Memory size

For Redmine, 1 Gb is the minimum recommended. For effective caching of data in page cache, the memory size is already insufficient, so the speed is very sensitive to the load on the disk. Delays already occur on SELECT queries, since they have to read data from the disk. The smaller storage capacity of RocksDB has led to more efficient read caching than for InnoDB. Therefore, even with a heavy load, the speed of operations at MyRocks has changed very slightly.

When the memory is increased to 2 Gb, the main data used already fits in the page cache and the database server no longer needs to constantly read it from disk. In this case, the disk is a narrow neck only with changes in the database. Transactions are written to disk without a writeback cache, and intense disk load increases the wait time for writing to complete.

The organization of data storage in RocksDB, which facilitates linear recording, and the reduced amount of recorded data, reduce the number of write operations. Therefore, we observe that even with high disk load, the transaction speed in RocksDB only slightly decreases and significantly exceeds the speed when using InnoDB.

RocksDB vs InnoDB Speed

Based on the operating principle of RocksDB, we expected acceleration in transactions. On synthetic performance tests, developers received a 10-fold increase in the speed of the DBMS. For applications such as Redmine, the runtime of an operation consists of the runtime of the Ruby script and the runtime of the query in the database. Of course, replacing the storage engine with RocksDB will not increase the speed of Ruby, and this component remains unchanged. But even with this in mind, the increase in speed due to the acceleration of the database was impressive.

Here we present the boundary test results for a 2 Gb virtual machine and a Big data set, and for an 8 Gb virtual machine and a Giant data set. We do not take into account the high-load testing for a 1 Gb virtual machine here, as this is a case of extreme lack of resources.

Operational timelines

1) task creation; 2) processing 10 tasks

Columns show the execution time of an operation (less is better)

Minimum (1 Redmine process without third-party disk load) and maximum load (4 Redmine processes and 4 threads of full third-party disk load)

With a low load, Redmine on MyRocks was 15% -25% faster than on MySQL and MariaDB. The size of the stored data has little effect on this speed for both RocksDB and InnoDB - an increase in the number of Redmine tasks by a factor of 10 increased the execution time by about 10%.

With a high load (an increase in the number of parallel processes and an increase in external disk load), the behavior changes completely. The MyRocks gap has become more - from 2-fold to almost 4-fold . The size of the stored data also began to significantly affect the speed - a 10-fold increase in the number of Redmine tasks significantly (1.5-2 times) slowed down the execution speed on servers with InnoDB, and less noticeably slowed down execution on RocksDB (0-15%).

The simultaneous increase in data volume and high load slowed down Redmine with MyRocks by 1.5 times, while Redmine on MySQL and MariaDB became 4 times slower.

Work stability

During testing, we found a nuance in the behavior of one of the Redmine SQL queries when searching with parent issues in mind. Because of it, some types of searches were slower in MyRocks. But this is a small omission on the part of Redmine - it parent_iddid not have an index in the table. We also encountered a small bug that caused the CPU to be consumed after some conflicting transactions in MyRocks.

We did not encounter other problems. According to the developers, Facebook has been using MyRocks in production for a long time.

You can use MyRocks right now or wait for more testing after the appearance of MyRocks in MariaDB release candidate 10.2 or in Percona Server for MySQL. The MyRocks package is available in the Jetware repository as one of the alternatives mysqldin stack designers , for example, PHP LAMP / LEMP), Ruby RAMP / REMP, or applications, for example, Redmine.

A few weeks ago, we transferred our internal Redmine server to MyRocks and successfully work in it.

Conclusion

1. Redmine with RocksDB was faster than with InnoDB - from 20% at minimum load to 3 times at maximum.
2. A 4x increase in data volume and load slowing Redmine with InnoDB only 1.5 times slows Redmine with RocksDB.

PS Testing MyRocks with other applications

In this test, we tested the performance of MyRocks for a Redmine application. In the following tests, we are going to test the performance of MyRocks with PHP applications. Most likely, Drupal will be the first.

Article in English