×
Community Blog Which Storage Type Is Better for MySQL Databases?

Which Storage Type Is Better for MySQL Databases?

This article explains the different storage types for MySQL databases.

By Alain Francois

When a website or an application begins to run slowly over time, in most cases, it is due to an unoptimized database because the volumes to be processed become large. This begins to indicate performance losses in data storage. Each database management system offers a storage engine, and MySQL has the particularity of offering different storage engines. Beyond that, it even allows them to coexist in the same database. This is rather exceptional flexibility because storage engines have specific behaviors as to how to keep the data of tables and indexes. Therefore, they are more or less adapted according to the contexts of use. These storage engines are executing all the requests on the hard drive, where the communication can be slow depending on the technology of your hard drive. There are many storage types. You should think about the operations you plan to perform and when it is related to databases before considering which type to use. This article explores some different storage types and discusses which one fits better for MySQL.

What Is MySQL Storage Engine?

A storage engine is an organized collection of information that constitutes a database. The storage component (or storage engine) is incorporated into a database engine as an element. MySQL supports many storage engines that act as data handlers for different table types, allowing you to choose how data will be stored and accessed in a table. There are three groups of storage engines: memory (Memory...), disk (MyISAM, InnoDB...), and network (NDBcluster...). The storage requirements for table data on a disk depend on several factors because storage engines represent data types and store raw data differently. Table data might be compressed, either for a column or an entire row, complicating the calculation of storage requirements for a table or column. This can put stress onto the disk for extremely complex queries or if many users are running queries at once.

Hard Disk Drive (HDD)

HDDs are the slowest components; all reading or writing is done in blocks. The transfer time is negligible for one block but can become important when thousands of blocks must be read. The writing mechanism is pretty much the same as reading, but it may take a little longer if the controller verifies that the write was done correctly. Read latency makes the disk slow compared to other memory, especially if relocation of read heads is required. Also, the transfer time only becomes significant when reading multiple blocks consecutively. HDD storage is suitable for use cases that meet the following criteria:

  • Your storage will be at least 10 TB of data
  • If you are not planning to use the data to back a latency-sensitive application
  • It is far easier to reliably erase and reuse

Solid State Drive (SSD)

During the past years, flash memory SSD have been relentlessly replacing hard disks as the main storage media because of several advantages, such as fast latency, high IOPS, and low power consumption. SSDs have several components, including internal processors and cache. Low-latency SSDs are mainly used as storage for:

  • The legacy I/O stack overhead makes fast SSDs perform suboptimally. The I/O software stacks that contribute to the latency of data access and the energy consumption on hard disks vary marginally between the segregated direct-attached storage and host.
  • The intervening I/O stack will hinder the ample opportunities for vertical optimization between database engines and SSDs.
  • It makes I/O waiting significantly shorter when combined with higher RAM.

Which Type to Use

SSD storage types are built on flash memory (the same kind used for portable USB thumb drives). This hardware consists of solid-state semiconductor memories. Unlike spinning magnetic disks, the memory slots are direct-accessed, eliminating latency. A technical problem posed by SSDs is that the number of writes possible in the same sector is limited. Also, if your available memory is more than your database size, your server will probably be able to keep all of your data in memory. Therefore, an SSD might be a waste of money.

Alibaba Cloud RDS for MySQL

ApsaraDB RDS is a stable, reliable, and scalable online database service built on top of the Apsara Distributed File System and high-performance SSDs of Alibaba Cloud. ApsaraDB RDS supports the MySQL, SQL Server, PostgreSQL, and MariaDB TX database engines. ApsaraDB RDS is available in the Basic Edition, High-Availability Edition, Cluster Edition, and Enterprise Edition. Aspara RDS for MySQL offers the features below:

1

You can view the features and notice that it is compatible with MySQL 8.0, 5.7, 5.6, and 5.5.

Aspara RDS for MySQL identifies SQL statements that run at low speeds and provides recommendations that help optimize your business code. It also provides a complete suite of database O&M solutions, such as disaster recovery, backup, restoration, monitoring, and migration, to deliver optimal performance and high stability.

Alibaba Cloud has conducted some tests to check the performances. For example, there is a test on Disk I/O-based queries. In this test, only some data that you query is hit in the cache, and the RDS instance needs to read and write data to disks and update the cache to complete queries.

2

Related Blog Articles

Best Practices for MySQL Database Storage Optimization

Data files refer to the files stored in the storage. In the database, they are tables. The tables are mainly composed of data and indexes. Therefore, when you find that your data files are occupying a great deal of instance storage, you should check which table is most responsible.

0 0 0
Share on

Alibaba Cloud Community

496 posts | 39 followers

You may also like

Comments