You are here
UNDO logs in InnoDB system tablespace ibdata1
We see sometimes at customers that they have very big InnoDB system tablespace files (ibdata1
) although they have set innodb_file_per_table = 1
.
So we want to know what else is stored in the InnoDB system tablespace file ibdata1
to see what we can do against this unexpected growth.
First let us check the size of the ibdata1
file:
# ll ibdata1 -rw-rw---- 1 mysql mysql 109064486912 Dez 5 19:10 ibdata1
The InnoDB system tablespace is about 101.6 Gibyte in size. This is exactly 6'656'768 InnoDB blocks of 16 kibyte block size.
So next we want to analyse the InnoDB system tablespace ibdata1
file. For this we can use the tool innochecksum
:
# innochecksum --page-type-summary ibdata1 Error: Unable to lock file:: ibdata1 fcntl: Resource temporarily unavailable
But... the tool innochecksum
throughs an error. It seems like it is not allowed to analyse the InnoDB system tablespace with a running database. So then let us stop the database first and try it again. Now we get a useful output:
# innochecksum --page-type-summary ibdata1 File::ibdata1 ================PAGE TYPE SUMMARY============== #PAGE_COUNT PAGE_TYPE =============================================== 349391 Index page 5.25% 6076813 Undo log page 91.29% 18349 Inode page 0.28% 174659 Insert buffer free list page 2.62% 36639 Freshly allocated page 0.55% 405 Insert buffer bitmap 0.01% 98 System page 1 Transaction system page 1 File Space Header 404 Extent descriptor page 0.01% 0 BLOB page 8 Compressed BLOB page 0 Other type of page ------------------------------------------------------- 6656768 Pages total 100.00% =============================================== Additional information: Undo page type: 3428 insert, 6073385 update, 0 other Undo page state: 1 active, 67 cached, 249 to_free, 1581634 to_purge, 0 prepared, 4494862 other
So we can see that about 91% (about 92 Gibyte) of the InnoDB system tablespace ibdata1
blocks are used by InnoDB UNDO log pages. To avoid growing of ibdata1
you have to create a database instance with separate InnoDB UNDO tablespaces: Undo Tablespaces.
- Shinguz's blog
- Log in or register to post comments