If the disk space of the MySQL database instance is full, the instance becomes read-only, and the write operation is disabled. The application cannot write data to the database, thereby affecting the operation of the service. This section describes how to view space usage, as well as the causes and solutions to various space issues.
Viewing Space Usage
You can go to Instance Management > Basic Info to view the usage of storage space and backup space. This page only displays the current total space usage. The disk space occupied by each type of data and historical information about space usage are not available.
You can go to Basic Info > View Monitoring Indicator > Disk Usage to view the historical information and change curve.
Insufficient Storage Caused by Excessive Binlog Log Files
Causes and Issues:
The binlog is retained for seven days by default. If you are migrating a large amount of data and the disk space of the instance is too small, a large number of binlogs will be generated in a short period of time, resulting in insufficient disk space.
Solution:
To resolve this issue, you can use PURGE BINARY LOGS BEFORE '2023-06-26 10:00:00' or PURGE BINARY LOGS TO 'mysql-bin.010' to temporarily clear the local binlogs and release disk space on the mysql command line, or temporarily modify the expire_logs_days parameter of the instance.
Insufficient Storage Caused by Excessive Indexes on the Table
Causes and Issues:
In general, a table contains primary key indexes and secondary indexes. The more the secondary indexes, the higher the tablespace.
Solution:
Optimize the data structure, reduce the number of secondary indexes, merge indexes, establish compound indexes, etc.
Insufficient Storage Caused by Large Fields
Causes and Issues:
If large fields of the BLOB or TEXT, or long field VARCHAR are defined in the table structure, a larger tablespace is occupied.
Solution:
Optimize the table data structure, and compress the data before inserting.
Insufficient Storage Caused by Excessive Tablespace Fragmentation
Causes and Issues:
Too much idle tablespace causes a high fragmentation ratio of an InnoDB table. InnoDB manages tablespace by page. If some records of a full page are deleted and no new records are inserted into the positions from which the records are deleted, a large number of idle tablespaces are generated. Fragmentation appears in the tablespace when the MySQL table is deleted, updated, or inserted several times. Regularly organize the tablespace and eliminate fragmentation to improve performance in accessing the tablespace.
Solution:
Run the following commands to find out the largest 10 tables in the tablespace that can release over 100 MB of space.
mysql> select table_name,round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 100 order by data_free_mb desc limit 10; +------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | +------------+----------------+--------------+ | sbtest1 | 232 | 274 | +------------+----------------+--------------+ 1 row in set (0.02 sec) |
Using alter table ... force to organize the tablespace has the same effect as executing the OPTIMIZE TABLE tablename command, which applies to tables in the InnoDB, MyISAM, and ARCHIVE engines. However, InnoDB tables do not support the OPTIMIZE TABLE command. You can use alter table sbtest1 engine=innodb instead to organize the tablespace during off-peak hours.
mysql> OPTIMIZE TABLE sbtest1; +----------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+----------+----------+-------------------------------------------------------------------+ | sbtest.sbtest1 | optimize | note | Table does not support optimize, doing recreate + analyze instead | | sbtest.sbtest1 | optimize | status | OK | +----------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (1 min 25.24 sec) mysql> alter table sbtest1 engine=innodb; Query OK, 0 rows affected (1 min 3.06 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Insufficient Storage Caused by Excessively Large Temporary Tablespace
Causes and Issues:
When you perform a semi-join operation, a distinction operation, or an order by operation that does not use an index, a temporary table is created. If the temporary table contains an excessive amount of data, the temporary tablespace may be excessively high. When you perform data definition language (DDL) statements to rebuild tablespaces, the temporary file that is generated from an index-based sort operation is large. In addition, some online DDL operations do not support the Instant algorithm but are executed only on new tables. The original table is deleted after the DDL execution. Therefore, you can find two tables when the DDL statements are being executed.
Solution:
You can view the EXPLAIN execution plan to check whether Using Temporary is used. For large table DDL, check whether the space of the instance is sufficient. If insufficient, scale up the disk in advance.