Reasons and workarounds for high usage of MySQL instance space

When a user uses a MySQL instance, he or she encounters a space usage alarm or even a case where the instance limit is locked. In the instance basic information of the RDS console, the following information appears:

This article will introduce the common causes of high space usage and the corresponding solutions. For MySQL version 5.6 instances, you can unlock instances after upgrading instance specifications and storage space, and for how to upgrade instance configurations, see Change Configuration.

Common causes

MySQL instance space usage is high, there are four main reasons:

• Binlog file occupies a high.

• Data files occupy high.

• The temp file is high.

• System files occupy high.

View space usage

You can view the usage of instance space through the diagnostic reports in DMS.

1. Log on to the database on the DMS console.

2. Select Performance > Diagnostic reports.

3. Click Start Diagnostics to create a report that is running on the current instance, as shown in:

4. Click View Report to see the instance space usage in the diagnostic report, as shown in:

   Diagram Description:

   • Ins_size: The overall space of the instance.

   • Other_size: System files and temporary files use space.

   • Data_size: Data File usage space.

   • Binlog_size-binlog: File occupies space.

Workaround Upgrade Instance Specifications

Upgrading instance specifications is one of the effective ways to solve space problems. Currently, RDS supports an instance of the exclusive physical machine specification, with a maximum storage capacity of up to 3T. We recommend that you upgrade your instance specification to an exclusive physical machine, and for billing details about exclusive physical machine instances, see the RDS detailed pricing information for your cloud database, and the steps to upgrade your instance specifications are as follows.

1. Log in to the RDS management console.

2. Select the region where the target instance resides.

3. Click the ID of the target instance to go to the basic information page.

4. In the configuration Information Bar, click Change Configuration.

   Note: If you have a monthly subscription instance, click Upgrade Now or renew/renew.

5. On the Change instance page, change the instance specification to an exclusive physical machine and select a larger storage space, as shown in.

6. Click Confirm Changes.

Other methods

This section describes how to solve spatial problems without upgrading the instance specifications.

Binlog file High-occupancy solution

The Binlog file records the transaction information of the instance, is the MySQL instance HA architecture as well as the high availability, recoverability Foundation, is not to shut down.

The RDS instance automatically cleans up at intervals (uploaded to OSS and removed from the instance space) to the Binlog file for the last 18 hours. If the instance DML operation generates a large amount of Binlog data in a short time, it is possible to lock up more than the instance disk space limit.

In this case, the RDS console can be used to clean up (upload the Binlog file to OSS and remove it from the instance space) Binlog data.

Operation Steps

1. Log in to the RDS management console.

2. Select the region where the target instance resides.

3. Click the ID of the target instance to go to the basic information page.

4. Select Backup Recovery in the left menu bar.

5. Click one button to upload the Binlog.

   Description

   • One-click Upload Binlog will submit the cleanup task asynchronously in the background, so it will return shortly after clicking. The cleanup task will first upload the completed write Binlog to the RDS OSS (non-user-purchased OSS), and then delete the Binlog file from the instance space, and the Binlog file currently being written to cannot be cleaned due to an incomplete write. Therefore, there is a delay in the cleanup process and it is recommended that you wait patiently for a certain amount of time after you click, and do not click the button more than once.

   • For operations such as DML (such as DML operations involving large fields) that result in rapid generation of Binlog, multiple click-to-Binlog buttons may appear, but Binlog space occupancy is still up, because uploading Binlog files to the backup space and deleting from the instance space Except that the processing speed is not the same as the speed at which the instance generates the Binlog file, in which case it is recommended that you consider upgrading disk space and troubleshoot the cause of the Binlog rapid build.

Resolution for high data file usage

For data files with high space consumption, you can reduce space consumption by cleaning up the data, such as by drop table and truncate table commands to clean up data that is no longer needed.

In addition, here are two methods that users often use to clean up data files in space, but in practice and for the intended effect, the reasons and workarounds are as follows:

• deleteDelete data with actions

  deleteThe operation is not able to directly reclaim the data file space occupied by the deleted data, which is like emptying the water in the pool but the floor area of the pool will not change.

  deleteafter deleting the data with the operation, you need to optimize table tab_name; recycle the space by operation, see the RDS for MySQL delete data after the display space is not reduced.

• Delete a backup

  The RDS backup is placed on the background OSS and does not occupy the user's RDS instance space, so deleting the backup does not resolve the space problem of the instance. And deleting a backup can affect the recoverability of the instance, and it is strongly recommended that you do not consider deleting the backup in any circumstances.

Query method for data file space consumption

Method One:

Data files in the frequent DML after the phenomenon of data hole, the data file obtained by the following query takes up more space than the actual data file footprint calculation method:

1. select sum(data_length + index_length + data_free) / 1024 / 1024 from information_schema.tables;

Method Two:

Information_schema.tables provides some statistical information about the tables obtained from the sample, so there is a discrepancy between the table, library data size, and actual data file footprint (usually less than the actual data file footprint) obtained by the following query.

Note: Because the sampling statistics are provided in the Information_schema.tables, the calculation is relatively close to the real space occupancy situation when the statistics are close to the actual data.

1. Selecttable_name, Concat(Round((Data_length+Index_length) / 1024 / 10242), ' mbfrom Information_schema.tableswhere table_schema = ' and table_name = large_tab_01

The query results are as follows:

As you can see, there is a difference in the amount of table data that is fed back before and after collecting the statistics for the table. Even if the analyze table statistics are collected by command, the resulting values are usually less than the actual data file footprint, such as 16143 MB in this example is less than the actual space occupied by the data file of the table.

Workaround for temporary file high occupancy

Temporary files are freed automatically with the end of the query or the termination of the session, so if a temporary file causes the instance to be full and locked, you can free up space by terminating the session.

For information on how to terminate a session, see how RDS for MySQL terminates a session.

For frequently asked questions about temporary files, see the causes and handling of the RDS for MySQL table '/home/mysql/xxxx/xxxx/#tab_name ' are full.

Https://help.aliyun.com/knowledge_detail/51682.html

System file high-occupancy solution

System files involve space files and ib_logfile0, ib_logfile1 log files for ibdata1 system tables.

Ibdata1 file

The InnoDB engine table supports multiple version concurrency control (MVCC), so the Undo information required for the query is saved in the system file IBDATA1.

If there is a query that does not end for a long time on a InnoDB table, and the table has a large number of data changes during the query, a large amount of Undo information is generated, resulting in an increase in the size of the Ibdata1 file. Due to the limitations of the MySQL internal mechanism, IBDATA1 files are currently not supported for shrinking.

Therefore, if this is the case, the better solution is to purchase the same configured RDS instance in the same region as the availability zone, and migrate the data to the new instance with DTS tools, without upgrading the disk space.

It is recommended that you monitor and clean up sessions or transactions that take too long to run, and see RDS MySQL Management for long-running queries for details.

Ib_logfile log files

The Ib_logfile0 and ib_logfile1 log files save the transaction log information for the InnoDB engine table, their file size is fixed and cannot be changed. Larger dimensions in scenarios with high concurrency transactions help reduce the number of transaction log file transitions and improve instance performance.

Reasons and workarounds for high usage of MySQL instance space