is the ibdata1 file in MySQL growing?

We often receive this question about MySQL's ibdata1 file in the Percona Support Section. When the monitoring server sends an alarm about the storage of the MySQL server, the panic begins-that is, the disk is almost full. After a survey, you realize that most of the site space is ibdata1 used by the InnoDB shared table space. And you've enabled innodb_file_per_table, so the question is:

What did Ibdata1 save?

When you enable innodb_file_per_table , the tables are stored in their own tablespace, but the shared table space is still storing other InnoDB internal data:

• Data dictionary, which is the metadata of the InnoDB table
• Change buffer
• Double Write buffers
• Undo Log

Some of these can be configured on the Percona server to avoid excessive growth. For example, you can set the maximum change buffer by innodb_ibuf_max_size, or set Innodb_doublewrite_file to store a double write buffer in a separate file.

In MySQL version 5.6 You can also create an external undo table space, so they can be put into their own files instead of storing to ibdata1. You can look at this document.

What causes ibdata1 to grow rapidly?

The first command we need to perform when MySQL is a problem is:

1	SHOW ENGINE INNODB STATUS/G

This will show us some valuable information. We start with the * * TRANSACTION (TRANSACTION) * * Section and we'll find this

1 2 3 4

--- TRANSACTION 36E, ACTIVE 1256288 sec MySQL thread ID , OS thread handle 0x7f8baaccc700, Query ID 7900290 localhost root show engine innodb status Trx Read view won't see Trx with ID >= 36F, C16>sees < 36F

This is one of the most common causes of a fairly old transaction created 14 days ago. This state is active, which means that InnoDB has created a snapshot of the data, so the old page needs to be maintained in the Undo log to guarantee a consistent view of the database until the transaction begins. If your database has a large number of write tasks, it means that a large number of undo pages are stored.

If you can't find any long-running transactions, you can also monitor other variables in the InnoDB STATUS, and "History list Length" shows some wait cleanup operations. In this case, the problem often occurs because the purge thread (or the old main thread) cannot process the revocation as quickly as these records come in.

How do I check what's been stored in the ibdata1?

Unfortunately, MySQL does not provide the information to see what is stored in the Ibdata1 shared tablespace, but there are two tools that will be helpful. The first is a modified version of Mark Karahan made by Innochecksum, which is published in the vulnerability report.

It is fairly easy to use:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

#./innochecksum/var/lib/mysql/ibdata1 0 Bad checksum fil_page_index 19272 fil_page_undo_log fil_page_inode 1 fil_page_ibuf_free_list 892 fil_page_type_allocated 2 fil_page_ibuf_bitmap 195 fil_page_type_sys 1 fil_page_type_trx_sys 1 fil_page_type_fsp_hdr 1 fil_page_type_xdes 0 fil_page_type_blob 0 fil_page_type_zblob 0 Other 3 max index_id

There are 19,272 undo log pages in all 20608. this takes up the 93% of the table space.

The second way to check the contents of a table space is the InnoDB Ruby tool that Jeremy Core made. It is a more advanced tool to examine the internal structure of the InnoDB. For example, we can use the Space-summary parameter to come to a list of each page and its data type. We can use standard Unix tools to count the number of undo log pages:

1 2	# innodb_space-f/var/lib/mysql/ibdata1 space-summary | grep undo_log | wc-l 19272

Although this particular case makes innochedcksum faster and easier to use, I recommend that you use Jeremy's tools to learn more about the InnoDB internal data distribution and its internal structure.

OK, now we know where the problem lies. Next question:

How can i solve the problem?

The answer to this question is simple. If you can still submit a statement, do it. If not, you have to kill the thread to start the rollback process. That will stop ibdata1 growth, but it's clear that your software will be vulnerable and some people will encounter errors. Now that you know how to identify the problem, you need to use your own debugging tools or a common query log to find out who or what caused the problem.

If the problem occurs when the thread is cleared, the workaround is usually to upgrade to the new version, which uses a separate purge thread instead of the main path. More information view this document

Is there any way to reclaim the space you have used?

No, there is no easy and fast way. InnoDB table space never shrinks ... See the 10-year-old vulnerability report, updated from James Dai (thank you):

When you delete some rows, the page is marked as deleted and reused later, but this space is never recycled. The only way is to start the database with the new ibdata1. To do this you should use mysqldump to make a logical full backup, then stop MySQL and delete all databases, ib_logfile*, ibdata1* files. When you start MySQL again, a new shared table space is created. Then restore the logical backup.

Summarize

When the ibdata1 file grows too fast, it is usually caused by a long-running forgotten transaction in MySQL. Try to solve the problem as quickly as possible (commit or kill the transaction), because you cannot reclaim wasted disk space without the painful slow mysqldump process.

It is also highly recommended to monitor the database to avoid these problems. Our MySQL Monitor plugin includes a Nagios script that can alert you if it finds a running transaction that is too old.

is the ibdata1 file in MySQL growing?