Monthly Archives: August 2015

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!

Clean up whisper database

Ending up on this page means that you already know that whisper is not that disk space efficient.
Unless you have unlimited disk space, which you don't have, at some point you would want to clean it up.

This is how I am doing it:

1. Delete old metrics of ours that haven't been written to for over 90 days
[codesyntax lang="bash"]

# just to satisfy my curiosity: how much disk space I will gain
find /data/db/whisper/ -name "*wsp" -mtime +90 -exec echo -n -e {}"\0" \; | du -hc --files0-from=-
# delete the files!
find /data/db/whisper/ -type f -mtime +90 -name "*wsp" -exec rm '{}' \;

[/codesyntax]

2. Delete empty directories
[codesyntax lang="bash"]

# how many do we have?
find /data/db/whisper/ -type d -empty | wc -l
# remove them!
find /data/db/whisper/ -type d -empty -delete

[/codesyntax]