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.