Performance Comparison for High Availability Solutions of MySQL Database
Date
2020-06-10
Authors
Alaa Yaser Fayez Mohsen
الاء ياسر فايز محسن
Journal Title
Journal ISSN
Volume Title
Publisher
Al-Quds University
Abstract
MySQL is one of the most common relational database management system that is used around the world and keeping it high available is important for most of its users. This research concentrates on MySQL database high availability solutions, where the database performance is evaluated and compared between two different high availability solutions. Noting that there are multiple choices for high availability solutions of MySQL such as master-slave replication with manual or automatic failover, MySQL NDB cluster, Galera Cluster for MySQL, MySQL with Solaris Cluster, Oracle MySQL Cloud Service. In this research the chosen solutions are InnoDB cluster (MySQL replication) and DRBD (Distributed Replicated Block Device) that depends on replicated disk architecture. InnoDB cluster is provided officially by MySQL. Up to our knowledge, there are no researches found related to performance comparison between these two solutions, and both are considered an effective solution for high availability with automatic failover. Each solution is evaluated using different replication modes of single primary topology. Two modes of DRBD cluster is evaluated, which are Protocol A and Protocol C. Also, two consistency levels are evaluated for InnoDB cluster, which are eventual and before_and_after levels. The results are analyzed and compared, so that would be helpful for decision makers in picking an appropriate high availability database solution for a given application taking into account its cost.
The clusters are configured as recommended (best practice) for best performance and some variables are changed with multiple values to have the best case. For faire comparison purpose a benchmark tool is used as workload testing tool. The clusters are tested using read and write quires. As a result, we found that InnoDB outperforms the performance of DRBD for write tests. It shows higher performance in terms of throughput and total time. On the other hand, for read test it outperforms DRBD when the number of concurrent users is high. This is because InnoDB cluster
has two nodes for read queries, and the received requests will be routed on two nodes. But when the number of concurrent users is low the DRBD shows better performance