We support columns in Percona often receive files on MySQL ibdata1 this problem.
When the monitor server sends a warning about the MySQL server storage when the panic began - that the disk is almost full.
After some investigation you realize that most of the site space is InnoDB shared tablespace ibdata1 use. And you have enabled innodbfileper_table, so the question is:
ibdata1 saved what?
When you enable innodb_file_per_table, the table is stored in their own table space, but the shared table space is still in the memory of other InnoDB internal data:
Data dictionary, which is the metadata InnoDB table
Double write buffer
Some of which are on the Percona server can be configured to avoid growing too large. For example, you can change the maximum set by innodbibufmax_size buffer, or set innodbdoublewritefile to double write buffer stored in a separate file.
MySQL version 5.6 you can also create an external undo table space, so they can put their files to alternative storage to ibdata1. You can look at this document.
What causes ibdata1 rapid growth?
When the first command MySQL problem Usually we need to do is:
SHOW ENGINE INNODB STATUS / G
This will show some of our valuable information. We ** TRANSACTION (transaction) ** and began to check, and then we will find this:
--- TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id> = 36F, sees < 36F
This is one of the most common reasons for a fairly old 14 days before the transaction created. This state is active, which means that InnoDB has created a snapshot of the data, it is necessary to maintain the old page in the revocation of the log in order to safeguard a consistent view of the database until the transaction begins. If your database has a large number of write task, it means that stores a lot of undo page.
If you can not find any long-running transactions, you can monitor INNODB STATUS of the other variables, "History list length (the length of the history list)" shows some waiting for clear operation. In this case the problem occurs frequently because the cleanup thread (the main thread or older) can not come in as fast as the speed of these records processed revoked.
How I check what is stored in the ibdata1 Lane?
Unfortunately, MySQL does not provide a view of what is stored in the shared table space ibdata1 information, but there are two tools would be helpful. The first one is Marc Callaghan made a modified version innochecksum, publish it in this bug report.
It is quite easy to use:
# ./innochecksum / Var / lib / mysql / ibdata1
0 bad checksum
3 max index_id
All 20608 19272 undo log in page. It takes up 93% of the table space.
The second look-up table space the way content is produced by Jeremy Cole InnoDB Ruby tools. It is a check of the internal structure of InnoDB more advanced tools. For example, we can use the space-summary parameter to get a list of each page and its data type. We can use standard Unix tools to count the number of undo log page:
# Innodb_space -f / var / lib / mysql / ibdata1 space-summary | grep UNDO_LOG | wc -l
Although in this particular case, innochedcksum faster and easier to use, but I recommend that you use Jeremy tools to learn more about the InnoDB internal data distribution and internal structure.
Well, now we know where the problem lies. The next question:
How can I solve the problem?
The answer to this question is very simple. If you can commit statement, you do it. If not, you have to kill the thread to begin the rollback process. That will stop ibdata1 growth, but it is clear that there will be loopholes in your software, some people will encounter an error. Now that you know how to identify the problem, you need to use your own debugging tools or general query log to find out who or what caused the problem.
If a problem occurs in the cleanup thread, the solution usually is to upgrade to the new version, the new version uses a separate thread clear alternative to the main thread. For more information view the document
Is there any way reclaim the space that has been used it?
No, there is not an easy and fast way. InnoDB table space never shrink ... see 10-year-old bug reports, the latest updates from James Day (thank you):
When you delete some rows, the page is marked as deleted later reuse, but the space will not be recovered from. The only way is to use the new ibdata1 start the database. To do this you should use mysqldump need to do a full backup logic, then stop and delete all the MySQL database, ib_logfile, ibdata1 file. When you restart MySQL time will create a new shared table space. Then restore the logical backup.
to sum up
When ibdata1 file grows too fast, it is often forgotten in the long-running transaction MySQL caused. Try to solve the problem as quickly as possible (to submit or kill the transaction), because they do not go through the pain of mysqldump slow process, you can not reclaim wasted disk space.