Best Practices for RDS MySQL Storage Optimization

Local Upgrade vs. Cross-Host Upgrade

It should be noted that an elastic upgrade may take one of two forms: a local upgrade/degrade or a cross-host upgrade. In the former, the disk storage of the host where the instance is located is sufficient to accommodate the upgrade. In this situation, the upgrade is swift and will not influence your application.

Data Files

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 further check which table is most responsible. You can see which table is holding the most system storage through data dictionaries:

  • The size of disk space depends on potential data growth trends;
  • Whether to delete or archive data depends on the data retention period;
  • When designing tables, select reasonable data types, field sizes, and storage engines for sharding and table sharding.

Log Files

ApsaraDB for RDS MySQL uses a master/slave M-M high availability architecture. Mater/slave data synchronization relies on binlog. RDS will regularly back up the logs to OSS and then clear the local binlog to diminish the amount of space it takes up. When log storage encounters an error like the one in the figure below, the rate at which binlog grows may exceed the upload speed from RDS to OSS.

Temporary Files

These are files written from memory to disk when the database performs large file operations, and memory proves insufficient. This may lead to the creation of substantial temporary files during significant database operations (order by, group by, distinct).

System Files

This refers to the files created during the installation of the database. These system files are crucial to the normal operation. For MySQL, these files include ibdatal and ib_logfile0. The below graphic shows “other files” taking up a large amount of space. In this situation, you can refer to how to locate the problem causing ibdatal to grow continuously.

Conclusion

In conclusion, issues concerning storage space can be complicated, but following best practice from the very beginning of system design can save a lot of complications later on.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com