The reason and solution of RDS MySQL space problem

Source: https://help.aliyun.com/knowledge_detail/41739.html

The reason and solution of RDS MySQL space problem

Update Time: 2016-07-22 17:20:14

1. Causes

2. Resolve

2.1 Binlog File

2.2 Data files

2.3 Temporary files

2.4 System files

RDS MySQL instances are used in daily use, with the use of a space usage alarm or even exceeding the instance limit being locked.

Like what:

1. Causes

• Binlog files occupy high

• High data File Usage

• Temporary files occupy high

• System files occupy high

Instance space usage can be viewed in the DMS diagnostic report:

• ins_size -instance Overall space

• other_size -Space for system files and temporary file usage

• data_size -Data File usage space

• binlog_size -Binlog file occupies space

Note: To obtain the instance diagnostic report, refer to how to access the RDS instance diagnostic report .

2. Resolve

RDS instances support A separate upgrade of disk space, and upgrading disk space is one of the effective ways to solve space problems. Here's how to resolve a space problem without upgrading space.

2.1 binlog File

The Binlog file records the transaction information for the instance and is the basis for the HA architecture of the RDS MySQL instance and for high availability and recoverability. is not to be closed.

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, you can use the console backup and restore one-click Upload Binlog to clean up (upload the Binlog file to OSS and remove it from the instance space).

One-click Upload Binlog will submit the cleanup task asynchronously in the background, so it will return shortly after clicking. The cleanup task will not delete the Binlog file from the instance space until the Binlog (the Binlog file that is currently being written, which is not cleaned) is uploaded to the RDS oss (non-user-purchased OSS), so there is a delay. It is recommended to wait patiently for a certain amount of time after a click, and it is not recommended to click this button.

Note: For instances where DML operations (such as those involving large segments of the DML operation) result in rapid generation of Binlog, there may be multiple clicks of the "one Click Upload Binlog" button but Binlog space is still up, because uploading Binlog files to backup space and The processing speed of the deleted in the example space 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.

2.2 Data Files

For data files that occupy a high space, you can reduce space consumption by cleaning up the data, such as by drop table and truncate TABLE To clean up data that is no longer needed.

Description of the 3 FAQs:

2.2.1 information_schema.tables query data capacity

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

1. Selecttable_name,Concat(Round ((data_length + index_length / 1024/ 1024,2 mb" from. Tableswhere table_schema = ' rd_test ' and table_name = large_tab_01

You can see that there is a difference in the amount of table data that is fed back and forth before and after collecting the statistics for the table.

Note: Even if the statistics are collected by analyze Table command, the resulting value is usually less than the actual data file footprint, such as 16143 MB in this example is less than the actual data file footprint of the table.

As the data file in the frequent DML after the phenomenon of data hole, more close to the actual data file occupancy space calculation method, please refer to:

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

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

2.2.2 Delete deletes data

The delete 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.

After deleting the data, the delete operation needs to pass optimize table tab_name; Operation to reclaim space. For details, please refer to:RDS for MySQL delete data after the display space is not reduced

2.2.3 Deleting a backup

RDS backups are placed on the background OSS and do not occupy the user's RDS instance space, so deleting a backup does not resolve the space problem for 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 case.

2.3 Temporary Files

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.

To terminate a session, referto: How RDS MySQL terminates a session

For temporary file FAQs, please refer to: RDS MySQL the table '/home/mysql/xxxx/xxxx/#tab_name ' is the reason for full and handling

2.4 System Files

System files involve IBDATA1 system tablespace files and Ib_logfile0, ib_logfile1 log files.

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.

As a result of this, a better solution would be to purchase the same configured RDS instance in the same region as the availability zone without upgrading the disk space, and migrate the data to the new instance with DTS tools.

Recommendation: Monitor and clean up long-running sessions or transactions, refer to:RDS MySQL Management long run queries

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.

The reason and solution of RDS MySQL space problem