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.
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.
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:
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:
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.
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:
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.
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.
Application Environment Capabilities - Alibaba DevOps Practice Guide Part 15
875 posts | 198 followers
FollowH Ohara - November 20, 2023
Alibaba Clouder - February 11, 2020
JDP - July 9, 2021
ApsaraDB - November 17, 2020
Alibaba Cloud Storage - May 8, 2019
Alibaba Clouder - April 10, 2018
875 posts | 198 followers
Follow
Storage Capacity Unit
Plan and optimize your storage budget with flexible storage services
Learn More
Hybrid Cloud Storage
A cost-effective, efficient and easy-to-manage hybrid cloud storage solution.
Learn More
ApsaraDB RDS for MySQL
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn More
Hybrid Cloud Distributed Storage
Provides scalable, distributed, and high-performance block storage and object storage services in a software-defined manner.
Learn MoreMore Posts by Alibaba Cloud Community
