my.cnf entry: innodb_data_file_path = ibdata1:12M:autoextend
Content of ibdata1 file : When you have innodb_file_per_table enabled, the tables are stored in their own tablespace but the shared tablespace is still used to store other InnoDB’s internal data:
a) Table Data Pages (Information on .ibd files)
b) Table Index Pages (Information on .ibd files)
c) Data Dictionary
d) MVCC Control Data
i) Undo Space
ii) Rollback Segments
e) Double Write Buffer (Pages Written in the Background to avoid OS caching)
f) Insert Buffer (Changes to Secondary Indexes/Change buffer)
ibdata1 files grows and keeps on growing: This is the most common reason, a pretty old transaction created few days/long ago. The status is ACTIVE, that means InnoDB has created a snapshot of the data so it needs to maintain old pages in undo to be able to provide a consistent view of the database since that transaction was started. If your database is heavily write loaded that means lots of undo pages are being stored.
If you don’t find any long-running transaction you can also monitor another variable from the INNODB STATUS, the “History list length.” It shows the number of pending purge operations. In this case the problem is usually caused because the purge thread (or master thread in older versions) is not capable to process undo records with the same speed as they come in.
Reclaim space back from ibdata1: No, it is not possible at least in an easy and fast way. InnoDB tablespaces never shrink.
When you delete some rows, the pages are marked as deleted to reuse later but the space is never recovered. The only way is to start the database with fresh ibdata1. To do that you would need to take a full logical backup with mysqldump. Then stop MySQL and remove all the databases, ib_logfile* and ibdata* files. When you start MySQL again it will create a new fresh shared tablespace. Then, recover the logical dump.
When the ibdata1 file is growing too fast within MySQL it is usually caused by a long running transaction that we have forgotten about. Try to solve the problem as fast as possible (commiting or killing a transaction) because you won’t be able to recover the wasted disk space without the painfully slow mysqldump process.
Check the content of ibdata1: Details been provided since 5.7 onwards
./5.7.10/bin/innochecksum --page-type-summary --verbose ibdata1
File::ibdata1
================PAGE TYPE SUMMARY==============
#PAGE_COUNT PAGE_TYPE
===============================================
5545 Index page
20100 Undo log page
7 Inode page
2743 Insert buffer free list page
733 Freshly allocated page
2 Insert buffer bitmap
179 System page
1 Transaction system page
1 File Space Header
1 Extent descriptor page
0 BLOB page
0 Compressed BLOB page
0 Other type of page
===============================================
Content of ibdata1 file : When you have innodb_file_per_table enabled, the tables are stored in their own tablespace but the shared tablespace is still used to store other InnoDB’s internal data:
a) Table Data Pages (Information on .ibd files)
b) Table Index Pages (Information on .ibd files)
c) Data Dictionary
d) MVCC Control Data
i) Undo Space
ii) Rollback Segments
e) Double Write Buffer (Pages Written in the Background to avoid OS caching)
f) Insert Buffer (Changes to Secondary Indexes/Change buffer)
ibdata1 files grows and keeps on growing: This is the most common reason, a pretty old transaction created few days/long ago. The status is ACTIVE, that means InnoDB has created a snapshot of the data so it needs to maintain old pages in undo to be able to provide a consistent view of the database since that transaction was started. If your database is heavily write loaded that means lots of undo pages are being stored.
If you don’t find any long-running transaction you can also monitor another variable from the INNODB STATUS, the “History list length.” It shows the number of pending purge operations. In this case the problem is usually caused because the purge thread (or master thread in older versions) is not capable to process undo records with the same speed as they come in.
Reclaim space back from ibdata1: No, it is not possible at least in an easy and fast way. InnoDB tablespaces never shrink.
When you delete some rows, the pages are marked as deleted to reuse later but the space is never recovered. The only way is to start the database with fresh ibdata1. To do that you would need to take a full logical backup with mysqldump. Then stop MySQL and remove all the databases, ib_logfile* and ibdata* files. When you start MySQL again it will create a new fresh shared tablespace. Then, recover the logical dump.
When the ibdata1 file is growing too fast within MySQL it is usually caused by a long running transaction that we have forgotten about. Try to solve the problem as fast as possible (commiting or killing a transaction) because you won’t be able to recover the wasted disk space without the painfully slow mysqldump process.
Check the content of ibdata1: Details been provided since 5.7 onwards
./5.7.10/bin/innochecksum --page-type-summary --verbose ibdata1
File::ibdata1
================PAGE TYPE SUMMARY==============
#PAGE_COUNT PAGE_TYPE
===============================================
5545 Index page
20100 Undo log page
7 Inode page
2743 Insert buffer free list page
733 Freshly allocated page
2 Insert buffer bitmap
179 System page
1 Transaction system page
1 File Space Header
1 Extent descriptor page
0 BLOB page
0 Compressed BLOB page
0 Other type of page
===============================================
No comments:
Post a Comment