How to recover the space used by UNDO_LOG in InnoDB tablespaces

The panic started when the monitoring server sent an alert about the storage of one of our MySQL servers - saying that the disk is about to get filled.

I realized that a a lot of the disk space was used by the InnoDB's shared tablespace ibdata1. But wait... I have innodb_file_per_table enabled. So, what is stored in ibdata1?

According to this serverfault post, ibdata1 contains vital InnoDB informations:

  • Table Data Pages
  • Table Index Pages
  • Data Dictionary
  • MVCC Control Data
    • Undo Space
    • Rollback Segments
  • Double Write Buffer (Pages Written in the Background to avoid OS caching)
  • Insert Buffer (Changes to Secondary Indexes)

Next step was to download the InnoDB Ruby Tools made by Jeremy Cole and to check what is being stored in the ibdata1.

[codesyntax lang="bash"]

ruby ~/innodb_ruby/bin/innodb_space -f ibdata1,data1,data2 space-page-type-summary
type                count       percent     description
UNDO_LOG            1969570     83.55       Undo log
INDEX               230460      9.78        B+Tree index
IBUF_FREE_LIST      115168      4.89        Insert buffer free list
INODE               22337       0.95        File segment inode
ALLOCATED           19424       0.82        Freshly allocated
IBUF_BITMAP         143         0.01        Insert buffer bitmap
XDES                142         0.01        Extent descriptor
SYS                 130         0.01        System internal
FSP_HDR             1           0.00        File space header
TRX_SYS             1           0.00        Transaction system header

[/codesyntax]

So, it has 1969570 UNDO_LOG pages which is almost 84% of the table space...

Now, according to some stackoverflow post shrinking or purging ibdata1 file is NOT possible without doing a logical dump/import.

Well... I managed to reclaim that space!

  • I dumped the structure of the databases

[codesyntax lang="bash"]

mysqldump --no-data database1 > database1.sql
mysqldump --no-data database2 > database2.sql

[/codesyntax]

  • I stopped the mysql server

[codesyntax lang="bash"]

/etc/init.d/mysql stop

[/codesyntax]

  • I renamed mysql dir (/data/db/mysql in my case)

[codesyntax lang="bash"]

mv /data/db/mysql /data/db/mysql.bak
mkdir -vp /data/db/mysql
cp -av /data/db/mysql.bak/mysql /data/db/mysql/
cp -av /data/db/mysql.bak/{aria_log.00000001,aria_log_control} /data/db/mysql/

[/codesyntax]

  • I did some clean up

[codesyntax lang="bash"]

rm -vf /data/db/mysql/mysql/innodb_{index,table}_stats.*
rm -vf /data/db/mysql/mysql/gtid_slave_pos.*

[/codesyntax]

  • Last few things before starting mysql

[codesyntax lang="bash"]

mkdir -vp /data/db/mysql/tmp
chown mysql:mysql /data/ -R

[/codesyntax]

  • I started mysql and checked tables for MySQL upgrade (this is really necessary in order to recreate innodb_{index,table}_stats tables).

[codesyntax lang="bash"]

/etc/init.d/mysql start
mysql_upgrade -uuser -ppassword --force
/etc/init.d/mysql restart

[/codesyntax]

  • No errors in mysql log, so I imported the dumps I created before

[codesyntax lang="bash"]

mysql database1 < database1.sql
mysql database2 < database2.sql

[/codesyntax]

  • I discarded the tables space for every single table

[codesyntax lang="bash"]

alter table database1.table1 discard tablespace;
alter table database1.table2 discard tablespace;
alter table database1.tableX discard tablespace;

alter table database2.table1 discard tablespace;
alter table database2.table2 discard tablespace;
alter table database2.tableX discard tablespace;

[/codesyntax]

  • I moved the data files (the .ibd files) and imported the table space for every single table

[codesyntax lang="bash"]

alter table database1.table1 import tablespace;
alter table database1.table2 import tablespace;
alter table database1.tableX import tablespace;

alter table database2.table1 import tablespace;
alter table database2.table2 import tablespace;
alter table database2.tableX import tablespace;

[/codesyntax]

Good luck!

  1. Excellent. Period. !

  2. Hi,

    Very detailed. I hope to leverage your post to fix similar issue I am facing.

    I am stuck at the step 1 though, can you help?

    How to I install InnoDB ruby tool? I am using ubuntu 14.04?

    Looking forward for your response.

    Regards,
    Peter

Scrie si tu o vorbulita


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.