MySQL FAQ series: How to avoid a sudden increase in the size of the ibdata1 file

0. Introduction

What is the ibdata1 file?

ibdata1 is a file used to build the innodb system tablespace. This file contains the metadata, undo records, modification buffer, and double write buffer of the innodb table. If the file-per-table option is turned on, the file does not necessarily contain data for all tables. When the innodb_file_per_table option is turned on, the data and indexes of the newly created table will not be stored in the system tablespace, but in the .ibd file of each table.

Obviously, this file will grow larger and larger. The innodb_autoextend_increment option specifies the step size of the file that will grow automatically each time. The default value is 8M.

What causes the ibdata1 file to grow larger and larger?

ibdata1 stores data, indexes, caches, etc., and is the most important data of MYSQL. So as the database gets bigger, the tables will also get bigger, which is unavoidable. If time goes by and the size gets bigger, it will not be so convenient for us to deal with logs and space, and we will not know where to start. Next we will deal with such a situation and store data in separate databases.

What should I do when the size of InnoDB's shared tablespace file ibdata1 increases dramatically?

1. Problem Background

Friends who use MySQL/InnoDB may also have troubles. For some reason, the ibdata1 file inexplicably grows larger, and they don’t know how to shrink it, just like a man’s belly after the age of 30. Fortunately, my belly has not grown yet, hoho~

Before we officially start, we need to know what the ibdata1 file is used for.

The ibdata1 file is a shared tablespace file of the InnoDB storage engine. The file mainly stores the following data:

• data dictionary
• double write buffer
• insert buffer/change buffer
• Rollback segments
• undo space
• Foreign key constraint system tables

In addition, when the option innodb_file_per_table = 0 , InnoDB table data & indexes need to be stored in the ibdata1 file. The default size of the ibdata1 file is 12MB starting from version 5.6.7, and the default size before that was 10MB. The relevant option is innodb_data_file_path. For example, I usually set it like this:

innodb_data_file_path = ibdata1:1G:autoextend

Of course, regardless of whether innodb_file_per_table = 1 is enabled, the ibdata1 file must exist because it must store the data that the InnoDB engine relies on and requires, especially the rollback segments and undo space marked in bold above. They are the biggest reasons for the increase in the size of the ibdata1 file, which we will discuss in detail below.

2. Cause Analysis

We know that InnoDB supports MVCC. Similar to ORACLE, it uses undo log and redo log to implement MVCC features. When a row of data is modified in a transaction, InnoDB will store an old version of the data in the undo log. If another transaction wants to modify the data at this time, it will store the latest visible version of the data in the undo log. And so on. If there are N transactions to modify the data, N historical versions need to be stored (slightly different from ORACLE, InnoDB's undo log is not entirely a physical block, but mainly a logical log. You can refer to the InnoDB source code or other related materials for this). These undo logs need to wait until the transaction is completed and then be judged again based on the visibility to other transactions determined by the transaction isolation level to confirm whether these undo logs can be deleted. This work is called purge (purge work is not just about deleting expired unused undo logs, there are other things as well, which I will talk about later).

Then the question arises: if there is a transaction that needs to read the historical version of a large amount of data, and the transaction cannot be committed or rolled back this morning for some reason, and after the transaction is initiated, a large number of transactions need to modify the data, the undo logs generated by these new transactions cannot be deleted, resulting in a pile-up. This is one of the main reasons for the increase in the size of the ibdata1 file. The most classic scenario for this situation is backing up a large amount of data, so we recommend that the backup work be placed on a dedicated slave server instead of on the master server.

In another case, InnoDB's purge work is unable to purge the undo logs that can be deleted in time due to poor file i/o performance or other reasons, resulting in accumulation. This is another major reason for the increase in the size of the ibdata1 file. This scenario occurs when the server hardware configuration is relatively weak and is not upgraded in time to keep up with business development.

A relatively rare problem is that there is a bug in the early MySQL version running on a 32-bit system. When the total amount of undo log to be purged exceeds a certain value, the purge thread simply gives up resistance and no longer purifies. This problem was encountered more frequently when we used the 32-bit MySQL 5.0 version in the early days. We once encountered a situation where this file grew to more than 100G. Later, we spent a lot of effort to migrate all these instances to 64-bit systems and finally solved this problem.

The last one is that the value of the option innodb_data_file_path was not adjusted at the beginning or was set very small, which inevitably led to the increase of the ibdata1 file. The my.cnf reference file officially provided by Percona has never increased this value, which puzzles me. Is it to leave a secret door intentionally like the xx that I often complain about, so as to facilitate subsequent optimization for customers? (My mind is too dark, not good~~)

To summarize, the following are the reasons for the sudden increase in the size of the ibdata1 file:

• There are a large number of concurrent transactions, which generate a large amount of undo logs;
• There are old transactions that have not been committed for a long time, resulting in a large number of old undo logs;
• Poor file i/o performance and slow purge progress;
• The initial setting is too small and insufficient;
• There is a bug on 32-bit systems.

As a slight side note, another popular database, PostgreSQL, stores the data of each historical version together with the original data tablespace, so the problem in this case does not exist. Therefore, PostgreSQL transaction rollback will be very fast, and vaccum work needs to be done regularly (for details, please refer to PostgreSQL's MVCC implementation mechanism, I may not be completely correct)

3. Solution suggestions

After seeing the above description of the causes of these problems, some students may think that this is easy to solve. Just shrink the size of the ibdata1 file and reclaim the table space. Tragically, as of now, InnoDB has no way to reclaim/shrink the ibdata1 file tablespace. Once the ibdata1 file is enlarged, the only way to restore the original size is to back up the data, restore it, and reinitialize the instance, or back up and restore each independent tablespace file in turn to a new instance. There is no better way.

Of course, this problem is not unpreventable. According to the reasons mentioned above, the corresponding recommended countermeasures are:

• Upgrade to 5.6 and above (64-bit) and use independent undo tablespace. Independent undo tablespace is supported since version 5.6. You no longer have to worry about increasing the size of the ibdata1 file.
• During initialization, set the ibdata1 file to at least 1GB;
• Increase the number of purge threads, for example, set innodb_purge_threads = 8 ;
• Improve file i/o capabilities, and install SSDs if necessary;
• Submit transactions in a timely manner and avoid backlogs;
• By default, autocommit = 1 is enabled to avoid forgetting to commit a transaction for a long time.
• Check the development framework to confirm whether autocommit=0 is set. Remember to explicitly commit or rollback after the transaction ends.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.