What is the ibdata 1 file?

Ibdata 1 is a file used to build the table space of innodb system, including metadata of innodb table, record cancellation, modification buffer and double-write buffer. If the "One file per table" option is turned on, the file may not 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 exist in the system tablespace, but in. Ibd files of their respective tables.

Obviously, this file will get bigger and bigger. The innodb_autoextend_increment option specifies the step size of this file every time it automatically grows, and the default is 8M.

What causes the file of ibdata 1 to become larger and larger?

Ibdata 1 stores data, indexes and caches, which is the most important data of MYSQL. So as the database gets bigger and bigger, the table will get bigger, which is inevitable. If it gets longer and bigger, it will be less convenient for us to deal with logs and space, and we don't know where to start. Next, we must deal with this situation and store the data in different libraries.

What should I do if the file size of * * * shared tablespace file ibdata 1 of InnoDB increases sharply?

1, problem background

Children's shoes with MySQL/InnoDB may also have troubles. I don't know why, the file of ibdata 1 is inexplicably long, and I don't know how to shrink it back, just like a man's stomach after 30 years old. Sweat, it's gratifying that my stomach hasn't grown yet, hoho~

Before we officially begin, we need to know what the ibdata 1 file is for.

The Ibdata 1 file is the * * * tablespace file of InnoDB storage engine, which mainly stores the following data:

data dictionary

Double write buffer

Insert buffer/replace buffer

Rollback segment

Undo spaces

Foreign key constraint system table

In addition, when the option InnoDB_file_per_table = 0, innodb table & needs to be stored in the ibdata 1 file. Index. The ibdata 1 file starts from version 5.6.7, and the default size is 12MB. Prior to this, the default size was 10MB, and its related option was innodb_data_file_path. For example, I usually set it like this:

Innodb _ data _ file _ path = ibdata1:1g: automatic extension.

Of course, whether innodb_file_per_table = 1 is enabled or not, the file ibdata 1 must exist, because it must store the necessary data of the above innodb engine, especially the rollback segment and undo space marked in bold above, which is the biggest reason for the increase of the file size of ibdata 1, and we will discuss it in detail below.

2. Cause analysis

As we know, InnoDB supports MVCC similar to ORACLE, and adopts undo log and redo log to realize MVCC features. When a row of data in a transaction is modified, InnoDB stores the old version of the row of data in the undo log. If another transaction wants to modify this row of data at this time, it will store the latest visible data version of this thing in the undo log, and so on. If there are n transactions to modify these data, it is necessary to store n copies of version history (slightly different from ORACLE, the undo log of InnoDB is not entirely a physical block, but mainly a logical log, so you can check the source code or other related materials of InnoDB). These revocation logs need to wait for the end of the transaction, and judge again according to the visibility of other transactions determined by the transaction isolation level to confirm whether these revocation logs can be deleted. This job is called purge(purge job is not only to delete expired revocation logs, but also something else, which will be discussed later).

Then the problem is coming. If a version history needs to read a large amount of data in a transaction, but this transaction cannot be committed or rolled back this morning for some reasons, and a large number of transactions need to modify these data after the transaction is initiated, then the undo logs generated by these new transactions will never be deleted and form a pile, which is one of the main reasons for the increase in the file size of ibdata 1. The most classic scenario of this situation is a lot of data backup, so we suggest that the backup work be placed on a dedicated slave server, not on the master server.

On the other hand, due to poor file i/o performance or other reasons, the cleaning work of InnoDB has been unable to clear the deleted revocation log in time, which is another main reason for the increase of the file size of ibdata 1. This happens when the hardware configuration of the server is weak and it is not upgraded in time to keep up with the business development.

A rare one is a bug in the early MySQL version running on a 32-bit system. When it is found that the total amount of revocation logs to be cleared exceeds a certain value, the clearing thread directly gives up resistance and does not clear any more. This problem was encountered when we used the 32-bit MySQL version in the early days. We once encountered the situation that this file rose to more than 100 g. Later, we made great efforts to migrate all these examples to the 64-bit system, and finally solved this problem.

Finally, the value of the option innodb_data_file_path is not adjusted or set very small at first, which inevitably leads to the increase of ibdata 1 files. The my.cnf reference document provided by Percona has never added this value, which makes me confused. Do you deliberately leave a secret door like xx that I often spit out, so as to help customers optimize later? (The brain is too dark, which is not good ~ ~)

To sum up briefly, there are several reasons why the file size of ibdata 1 has skyrocketed:

There are a large number of concurrent transactions, resulting in a large number 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; ;

The file i/o performance is poor and the cleaning progress is slow;

Initialization setting is too small;

There is a bug in the 32-bit system.

To add a little digression, another popular database, PostgreSQL, stores the version history data together with the original data table space, so this situation is no problem, and therefore the transaction rollback of PostgreSQL will be very fast, and it is necessary to do vacuum work regularly (see the MVCC implementation mechanism of PostgreSQL for details, I may not be completely correct).

3. Suggestions on solutions

Seeing the above description of the causes of these problems, some students may find this easy to handle. Reducing the file size of ibdata 1 will not end the tablespace. Sadly, so far, InnoDB has no way to recover/shrink the tablespace of the ibdata 1 file. Once the belly of the ibdata 1 file is enlarged, we can only restore the original size by backing up the data first, then reinitializing the instance, or restoring the independent tablespace file to the new instance in turn. Besides, there is no better way.

Of course, this problem is not impossible to prevent. According to the above reasons, the corresponding suggestions and countermeasures are:

Upgrade to 5.6 and above (64-bit) and adopt a separate undo tablespace. Since version 5.6, independent undo tablespaces have been supported, so there is no need to worry about making the ibdata 1 file bigger.

When initializing settings, set the ibdata 1 file to at least 1GB.

Increase the number of cleaning threads, such as setting innodb _ purge _ threads = 8；;

In order to improve file i/o capability, SSD should be installed quickly.

Submit the transaction in time and don't overstock;

Automatically submit = 1 is turned on by default to avoid forgetting that a transaction has not been submitted for a long time;

Check the development framework to confirm whether autocommit=0 is set, and remember that there is an explicit commit or rollback after the transaction ends.

abstract