Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you more information about ibdata1 shared tablespace files. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Database disk utilization has been increasing, of which 323G was used by ibdata1 and continued to increase for 31 days.

1. First of all, we see that innodb_file_per_table is enabled.

Version 2, version 5.6 undo information

Show variables like'% undo%';1 innodb_undo_directory. 2 innodb_undo_logs 1283 innodb_undo_tablespaces 0

Then the reasons for the increase of ibdata1 files are as follows:

Because the InnoDB engine table supports multiple versions of concurrency control (MVCC), the Undo information required for the query is saved in the system file ibdata1.

If there is a query against an InnoDB table that does not end for a long time, and there are a large number of data changes in the table during the query process

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 internal mechanism of MySQL, ibdata1 files do not support shrinkage at this time.

Therefore, such a situation can only be solved by switching between active and standby, or migrating, or increasing storage space.

Recommendation: monitor and clean up sessions or transactions that have been executed for too long.

So today's example is roughly as follows:

Through show processlist, you can see that a sql has been executed for 31 days and is still in a query,Sending data state. The three tables of the SQL itself are associated with each other, and there is no association condition. After that, the sql is dropped by kill, and the new backup is rebuilt by switching instances.

1.2 data file

For cases where data files take up a lot of space, you can reduce the space footprint by cleaning up the data, such as drop table and truncate table to clean up data that is no longer needed.

Explain 3 common questions:

1.2.1 data capacity of information_schema.tables queries

Information_schema.tables provides part of the statistical information of the table obtained according to the sample, so there is a difference between the table, the library data size and the actual data file occupied size obtained by the following query (usually less than the actual data file occupied space)

Select table_name, concat (round ((data_length + index_length) / 1024)), 'MB') from information_schema.tables where table_schema =' TESTDB' and table_name = 'TESTTABLE'

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

Note: even if the statistics are re-collected through the analyze table command, the resulting value is usually less than the actual data file footprint; for example, the 16143 MB in this example is also less than the actual data file footprint of the table.

Due to the data holes in data files after frequent DML, please refer to the calculation method that is closer to the space occupied by actual data files:

Select sum (data_length + index_length + data_free) / 1024 / 1024from information_schema.tables

Note:

Because sampling statistics are provided in information_schema.tables, this calculation method is closer to the real space occupancy when the statistical data are closer to the actual situation.

1.2.2 delete delete data

The delete operation cannot directly reclaim the data file space occupied by the deleted data, just like emptying the water in the swimming pool without changing the area of the pool. And the delete operation will generate the corresponding Binlog file, which will further worsen the space usage. After the data is deleted by the delete operation, the space needs to be reclaimed through the optimize table tab_name; operation.

1.2.3 deleting a backup

Self-built MySQL may take up space for backups, but RDS backups on the cloud are placed on the background OSS and do not occupy users' RDS instance space, so deleting backups cannot solve the instance space problem. Moreover, deleting a backup will affect the recoverability of the instance, so it is strongly recommended that you do not consider deleting the backup under any circumstances.

1.3 temporary documents

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

I have encountered a case where the customer sorting operation leads to a large ibtmp1, which takes up a lot of space and needs to be released, so it can only be restarted or switched to a standby library, and then the new backup is restarted and released.

Ib_logfile log file:

The ib_logfile0 and ib_logfile1 log files save the transaction log information of the InnoDB engine table, and the file size is fixed and cannot be changed. Larger size helps to reduce the number of transaction log file switching and improve instance performance in the scenario of high concurrency transactions.

The above is the ibdata1 shared tablespace file that Xiaobian shared for you has been increasing, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.