Union Memory ’s SSD helps MySQL build a distributed database with high performance and low latency

With the rapid development of emerging software and hardware technologies such as information technology, big data, and artificial intelligence, data in various industries is experiencing an explosive growth. Traditional centralized databases are no longer able to meet the demands of scenarios involving massive data storage and high concurrency. In the face of emerging data processing needs such as massive, heterogeneous, and multi-source data, distributed databases have become the preferred choice for enterprises to address future large-scale data scenarios due to their advantages of high performance, high availability, and fault tolerance.

In 2021, distributed databases in China accounted for about 20%, mainly open source databases represented by MySQL and PostgreSQL. As a popular open source database, MySQL has been widely used in the Internet, finance, transportation, retail and other industries, serving key businesses such as data storage and processing in cloud scenarios. MySQL has extremely high requirements for storage IO. In cloud environments, MySQL databases typically use local NVMe SSDs to store data, so as to achieve higher throughput performance for database instances.

The Union Memory's enterprise-grade and data-center-grade NVMe SSDs feature high performance, low latency, and high reliability, greatly improving storage I/O performance. It's suitable for various business models such as read-intensive or mixed read and write, and can effectively meet the performance requirements of MySQL databases. This article demonstrates the high performance and low latency of Union Memory's SSD in different application scenarios of MySQL.

Technical introduction

MySQL is one of the most popular relational database management systems. Due to its open-source nature and advantages such as small size, fast speed, and low TCO (total cost of ownership), MySQL is widely used in large, medium, and small enterprises. MySQL architecture is mainly divided into the Server Layer and the Storage Engine Layer. The Server layer is divided into connector, cache, analyzer, optimizer, and actuator. All the functions of the cross-storage engine are implemented in this layer; the storage engine is pluggable, and common storage engines include MyISAM, InnoDB, Memory, etc.

Figure 1: Technical architecture of MySQL

There are usually two ways to implement local disks on MySQL: LVM and direct mounting. Using LVM in Linux systems provides more convenient storage management, while direct mounting offers more refined storage solutions, such as, separation of data disks and log disks, manual balanced deployment of services to different SSD disks to achieve optimal performance combinations, etc. Users can choose the methods according to their actual needs.

Validation of technical solution

1 Environment for verification

1.1 Hardware configuration for the verification

Type	Model	Hardware configuration
Server	2U2-way server	CPU: Intel Xeon Gold 6336Y @2.4GHz
		Memory: 256GB
		Network adapter: 2*2 ports 25GE Ethernet card
Hard disc	Union Memory SSD	System disk: 2 * 480 GB SATA Data disk: 4 * 3.2TB NVMe (UH831a)
Switch	25GE switch	24-port/48-port 25GE switches, with a minimum of 2 units for typical scenarios, stacking supported

1.2 Software configuration for the verification

Type	Model	Version
Operating System	CentOS (x86)	7.6
Database	MySQL (open source)	5.7.27-log MySQL Community Sever
Test tools	Sysbench	1.0.18

1.3 Networking plan for the verification

Figure 2: MySQL networking plan architecture

2 Verification method

Ø Step 1: During the test, use two Union Memory's NVMe SSDs as local disks. Store binlog files of MySQL database separately on one disk, while other MySQL files are stored on another NVMe SSD. Use the "nvm list" command to confirm whether the capacity, specifications, and FW version of the SSDs are correct.

Ø Step 2: Partition the two NVMe SSDs equally according to their capacity, format them into XFS file systems, and mount them to the corresponding database directories.

Ø Step 3: The test involves 200 tables, with each table containing 10 million rows of records, resulting in a total of 2 billion rows of data.

3 Verification results

Configure the data tables in the Sysbench testing, and set threads for traversing from 32/50/64/100/200/300. The verification results of Union Memory's SSD in three scenarios of MySQL (read-only, write-only, mixed read-write) are as follows:

3.1 Performance of Union Memory's SSD in MySQL read-only scenario

Figure 3: Performance comparison between Union Memory's SSD and SSDs of other manufacturers in MySQL read-only scenario

According to Figure 3, it can be seen that in terms of MySQL read performance, Union Memory's SSD performs optimally between 50 - 100 threads. Compared to the SSDs of other manufacturers, there is a maximum improvement of 122% in TPS and a maximum reduction of 91% in latency. In the MySQL read-only verification scenario, the advantages of Union Memory's SSD are obvious.

3.2 Performance of Union Memory's SSD in MySQL write-only scenario

Figure 4: Performance comparison between Union Memory's SSD and SSDs of other manufacturers in MySQL write-only scenario

As shown in Figure 4, in the MySQL write-only scenario, the Union Memory's SSD has always maintained a significant advantage with strong performance. Compared to SSDs from other manufacturers, Union Memory's SSD shows a maximum improvement of 266% in throughput. In terms of latency, Union Memory's SSD demonstrates a maximum reduction of approximately 83%. Therefore, it can be seen that Union Memory's UH8 series SSDs can greatly enhance the storage performance in MySQL write-only scenario.

3.3 Performance of Union Memory's SSD in MySQL read-write scenario

Figure 5: Performance comparison between Union Memory's SSD and SSDs of other manufacturers in MySQL read-write scenario

From Figure 5, it can be seen that Union Memory's SSD outperforms in MySQL read-write scenario, both in terms of throughput and latency. Compared to SSDs of other manufacturers, Union Memory's SSD shows a maximum improvement of approximately 49% in throughput and a maximum reduction of 65% in latency.

Conclusion:

The above test data demonstrates that Union Memory's SSD has strong and impressive performance in various testing scenarios of MySQL, including read-only, write-only, and read-write scenarios. It effectively enhances the storage performance of MySQL system and helps enterprises build high-performance, low-latency distributed databases in cloud environments.