MySQL Database Management System Forks Comparison and Usage ...

MySQL Database Management System Forks Comparison and Usage Oļegs Čapligins1, Andrejs Ermuiža2 1. Jātnieku iela 77a-67, Daugavpils LV-5410, Latvia 2. LBK/EDU, Maskavas iela 273/2-56, Rīga LV-1063, Latvia

ABSTRACT In 2010, Oracle Corporation has completed its acquisition of Sun Microsystems. In addition to Sun’s hardware projects, Oracle received sole rights to MySQL – the world most popular client-server open source database management system. Since the acquisition, Oracle has discontinued several open source projects and the open source community had certain concerns about MySQL. In response to MySQL licensing policy changes open source community created several independent development branches. Nowadays there are three major forks from MySQL codebase – Percona Server, MariaDB and MySQL Community Server. The purpose of this paper is to compare different forks of MySQL database management system, discuss at performance test results and reveal practical aspects of certain types of tasks. There are revealed performance tests results and possibilities to optimize certain parameters of MySQL database management systems and given classification of forks usage effectiveness. The paper consists of six parts: introduction for better subject comprehension and justification with chosen research subjects list; brief comparison of storage engines available in evaluated forks; experiments for the performance evaluation; analysis and optimization options based upon retrieved results and a set of conclusions. Key Words: Database, MySQL, Forks, Performance

1. INTRODUCTION MySQL is the second popular relational DBMS and the first popular relational open source clientserver RDBMS, according to figures from solid IT GmbH. The system shows stable and dynamic growth throughout the last 15 years. However, in 2010 Oracle Inc. has acquired Sun Microsystems with all its capital assets including MySQL. This acquisition lead to immediate rift in MySQL community due to ambivalent Oracle’s licensing policy. MySQL 5.5 codebase was forked (taken as a source framework) for alternative development branches: MariaDB, Percona Server, Drizzle, WebScaleSQL. The main purpose of this paper is to determine the best out-of-box solution among the major MySQL family forks. Currently three branches have taken the lead: Percona Server from Percona LLC, MariaDB from MariaDB Corporation Ab and MySQL Community Server from Oracle Inc. In the furtherance of this goal there will be performed the comparative analysis of storage engines, performance metrics and available solutions for backup and restore procedures.

2. STORAGE ENGINES One of the most significant traits that points MySQL out of other RDBMS is variability of available storage engines – software components that handle the SQL operations for different table types. Prior to 1

MySQL 5.5 the default storage engine was non-transactional MyISAM, for 5.5 and later InnoDB with transaction support became the default engine. The rift in MySQL community and new forks development lead to new storage engines forthcoming. Due to certain issues in the InnoDB licensing policy, the XtraDB fork as InnoDB alternative was developed by Percona LLC. It was implemented in both Percona Server and MariaDB as a backward-compatible solution for InnoDB tables. Federated storage engine has gone the similar way and evolved to FederatedX storage engines in MySQL branches. Table 1. Default storage engines availability Storage Engine Archive Aria Blackhole Cassandra SE Connect CSV Federated InnoDB Memory Merge Mroonga MyISAM NDB SphinxSE Spider TokuDB

MySQL Yes No Yes No No Yes Yes Yes Yes Yes No Yes Yes No No No

Percona Yes No Yes No No Yes FederatedX XtraDB Yes Yes No Yes No No No Yes

MariaDB Yes Yes Yes Yes Yes Yes FederatedX XtraDB Yes Yes Yes Yes No Yes Yes Yes

Some values in the Table 1 are shown not as boolean vaulues but as a strings, representing equivalent replacement (e.g. XtraDB is fully functional replacement for InnoDB storage engines). As can be seen, MariaDB provides the widest set of storage engines among all MySQL forks. However, Percona LLC founder Peter Zaitsev (2010, May 8) in MySQL Performance Blog (retrieved from https://www.percona.com/blog/2010/05/08/the-doom-of-multiple-storage-engines) referred to multiple engines support as to “doom” stating that “as nice as it sounds the in theory this benefit comes at very significant cost in performance, operational and development complexity”.

3. PERFORMANCE Performance is the one of the most popular criterions of DBMS efficiency evaluation. Dedicated and independent test environment was created for every test stage to assure results fidelity. Every test was repeated three times to lower results scatter and direct average value was taken as the final result. 3.1. Test Environment Considering that MySQL popularity rests on web applications, environment for experiment rebuilds average low-budget VPS (virtual private server) configuration: 2-cores CPU Intel® Xeon® CPU E5-2640 @ 2.00 GHz, RAM 4 GB DDR-3 and HDD 32 GB SCSI. The system is built in VMware ESXi 5.5 environment on the independent host.

2

Operating system: Linux CentOS 7.1.1503 in minimal installation mode without graphical interface. Additional software: JRE (Java Runtime Environment) 1.7.0, Maven 3.3 and Perl 5 with all necessary dependencies. Tests are performed in open source tool LinkBench created by Facebook Inc. Test subjects: MySQL Community Server 5.7.9, Percona Server 5.6.27-76, MariaDB 5.5.44 and MariaDB 10.1.9. It was decided to exclude MariaDB 10.0 from the experiment due to its obsolence. MariaDB 5.5 is the closest to the official MySQL codebase and MariaDB 10.1 represents the cutting edge of development and provides the most advanced approaches in DBMS stack. All test subjects run on the identical configuration with mostly default values with following parameters digression: max_connections = 300; innodb_buffer_pool_size = 3G; innodb_file_per_table = 1; innodb_use_native_aio = 1; innodb_flush_log_at_trx_commit = 1; innodb_log_buffer_size = 256M; innodb_doublewrite = 0; innodb_adaptive_flushing = 1. 3.2. Test Results Due to the fact that two storage engines (InnoDB and MyISAM) currently are in the lead in most applications, the experiment convers them both. Table 2. InnoDB/XtraDB performance results Operation Inserting 2M entries Real payload emulation, no warmup Real payload emulation, 10 minute warmup

MySQL 50981 3248 3440

Percona 37998 2616 2660

MariaDB 5 38254 1179 1399

MariaDB 10 53670 1440 1753

The writing capabilities differ by less than 25%, but from the other hand, the real payload emulation mode reveals much bigger difference. Table 2 results are displayed in graphical mode below for more vivid representation. 4000

Cold start, no warmup 10 minutes warmup

3500

Queries per second

3000 2500 2000 1500 1000 500 0 MySQL

Percona

MariaDB 5

Figure 1. InnoDB/XtraDB performance results 3

MariaDB 10

Second test covers performance with MyISAM tables. It should be tested due to MyISAM storage engine popularity in widely used old versions of content management systems Wordpress, Joomla and Drupal. Table 3. MyISAM/Aria performance results Operation

MySQL

Percona

Inserting 2M entries Real payload emulation, no warmup Real payload emulation, 10 minute warmup

37128 612* 1809 *

34503 113 80

MariaDB 5 MyISAM Aria 22248 50433 601 1350 799 684

MariaDB 10 MyISAM Aria 32176 14494 692 748 694 1273

* It is significant, that asterisk-marked results represent test iterations when system was incapable of operating in the regular mode. MySQL Community Server stopped responding and concurrent threads were lowered from 50 to 20 to get any result. Figure 2 below represents results in the form of chart to provide easier comparison. 1200

Cold start, no warmup 10 minute warmup

Queries per second

1000 800 600 400 200 0 MySQL

Percona MariaDB 5 Figure 2. MyISAM/Aria performance results

MariaDB 10

Retrieved data proves that MyISAM storage engine performance suffers from significant performance loss in stringent environment and big amount of concurrent threads. Aria storage engine does not solve performance issues; it can be an option only due to its compatibility with MyISAM and less fragmentation susceptibility and as a more fail proof storage engine. As a lateral result there is noticeable performance boost putting into comparison results in cold start mode and with warmup. It leads us to a conclusion that in general case every MySQL database server needs certain amount of time to gain momentum for optimal performance.

4. ANALYSIS AND OPTIMIZATION Significant difference between MyISAM and InnoDB results leads to the conclusion that the issue is possibly connected with overall system performance. Virtual environment can be taken as an advantage while analyzing performance metrics in VMware vCenter. 4

On the next page three figures represent three crucial system metrics: central processor, hard disk and operating memory. All figures are in the same time scale and have MyISAM segment on the left side before 1:25 PM and InnoDB on the right side of the figure after 1:25 PM. Figures are vertically aligned and have its size adjusted for better perception.

Figure 3. CPU utilization

Figure 4. HDD utilization

5

Figure 5. RAM utilization Retrieved data analysis revealed that MyISAM performance bottleneck is CPU. InnoDB from the other hand takes more resources from RAM and HDD, but has lesser impact on CPU, leaving processor time for other tasks. It is appropriate to put the reference here to the Table 3, where MySQL Community Server was not able to handle default settings for MyISAM tables and hang the system with 50 concurrent threads. The reason is displayed on Figure 3 as 100% CPU load. High HDD load for InnoDB is stipulated by setting innodb_file_per_table = 1 which is responsible for creating separate file per each table in the database. This setting can have negative impact on the performance if particular workflow implies constant table manipulation: creating, truncating, altering or deleting tables. For this case it is recommended to set innodb_file_per_table parameter to 0. 4.1. Optimization Due to environment and data structure variability the all-around solution for performance optimization does not exist. However, one of the common used solutions for performance optimization is open source utility script mysqltuner.pl by Major Hayden. It requires Perl interpreter with dependencies perl-MySQL and perl-Data-Dumper. The script analyzes running MySQL server metrics and displays suggestions for performance optimization.

Figure 6. Mysqltuner recommendations, output last section 6

Nevertheless, the utility results may be inaccurate due to short uptime, it was decided to adjust recommended settings and run LinkBench performance tests against MyISAM tables again in MySQL Community Server.

Figure 7. Test result after recommended settings adjustment After adjusting settings query_cache_type = 1, thread_cache_size = 12, key_buffer_size = 1024 and running command OPTIMIZE TABLE for each LinkBench MyISAM table, the performance increased in 377% – 2308 requests per second against 612. For additional test, LinkBench threads amount was raised to 100 threads to see if adjustments improved the way in which MySQL handles concurrent threads on MyISAM tables. Moreover, MySQL Community server handled increased load perfectly. These results are shown on Figure 8 below. Comparing charts in Figure 8 to aforementioned Figures 3 and 5, it is clearly seen that CPU stopped suffering from bottleneck syndrome and MySQL utilizes RAM in more optimal way.

Figure 8. Performance after setting optimized parameters for MyISAM However, this adjustment has negative impact on InnoDB tables (up to 30% performance degrade) and solution is suitable only for MyISAM or Aria storage engines.

5. BACKUP AND RESTORE There are several strategies to ensure data reservation for MySQL solutions: dedicated software, database file manipulation and replication. Database file manipulation (i.e. copying) most likely will not lead to the desired result for active database under load due to data mal-synchronization between transactions. Furthermore, only MyISAM and ARCHIVE tables are capable of file-to-file backup. To avoid this pessimistic scenario, it is possible to use file system snapshots supported by ZFS file system, LVM (logical volume manager) or third-party software (e.g. R1Soft Hot Copy). In any case upon the database file copying all tables are blocked and no data can be retrieved or written into the database. Built-in solution mysqlhotcopy provides similar functionality and works only with MyISAM and ARCHIVE tables. For a long period, the default and most common backup solution was mysqldump utility. However, it should be taken into account, that without table locking it is possible to retrieve data with disrupted logical relations (or even malformed data) due to mal-synchronization between transactions.

7

The rift in MySQL community in 2010 brought not only disorder and confusion – Percona LLC developers created utility Percona XtraBackup. This software allows creating full and incremental backups without locking database, rows or tables. 5.1. Testing Backup Impact on Server Performance Below on Figure 9 there is the merged chart from PhpMyAdmin software displaying how mysqldump and Percona XtraBackup influences the overall server query performance. To retrieve these results, the LinkBench test was started to create a payload and after certain amount of time, the backup process was started from command line. The red area on the chart represents the backup process interval.

Figure 9. Backup solutions performance impact As it is seen, mysqldump completely blocks all queries to the server until full backup is made. From the other hand, Percona XtraBackup provides lock-free solution and can be used in production environment without negative impact on the performance. This result is achieved by means of XtraBackup mechanics – it uses InnoDB binary logs, whilst mysqldump reads data directly from the database. Table 3. InnoDB/XtraDB performance results Backup solution mysqldump Percona XtraBackup

Time 36.196 sec 45.245 sec

Comparing the backup performance, the Percona XtraBackup takes 30% more time to back up the 1.5 GB database than mysqldump. However, performance is not really significant in this case – the reliability and convenience should have more weight upon choosing the backup solution.

6. CONCLUSIONS In retrospective of looking for the most efficient out-of-box solution, the MySQL Community Server from Oracle Inc. is the optimal choice as MySQL RDBMS ready for running with minimal 8

expenses on tuning. This statement is valid only with the proviso that InnoDB/XtraDB storage engine is used. There are no arguments for choosing MyISAM over InnoDB/XtraDB storage engine. The last one provides reliable, transactional and fast storage with constraints ensuring data integrity. In case it is necessary to handle great flow of writing requests, it can be tuned by adjusting commit delay parameter. As a backup solution, it is recommended to use Percona XtraBackup. Considering previous conclusion in can be used on any of MySQL forks with tables utilizing InnoDB/XtraDB storage engine.

REFERENCES Percona XtraDB Cluster Documentation. (n.d.). Retrieved November 20, 2015 from https://www. percona.com/software/documentation Mike Peters. (n.d.). MySQL Storage Engines. Retrieved January 10, 2016 from http://www. softwareprojects.com Robert Greiner. (n.d.). CAP Theorem: Revisited. Retrieved January 14, 2016 from http:// robertgreiner.com/2014/08/cap-theorem-revisited/ Russell J.T. Dyer. (2015). Learning MySQL and MariaDB. O'Reilly Media Schwartz B., Zaitsev P., Tkachenko V. (2012). High Performance MySQL (Third Edition). O'Reilly Media

9