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.
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 headerSo, 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
mysqldump --no-data database1 > database1.sql
mysqldump --no-data database2 > database2.sql- I stopped the mysql server
/etc/init.d/mysql stop- I renamed mysql dir (/data/db/mysql in my case)
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/- I did some clean up
rm -vf /data/db/mysql/mysql/innodb_{index,table}_stats.*
rm -vf /data/db/mysql/mysql/gtid_slave_pos.*- Last few things before starting mysql
mkdir -vp /data/db/mysql/tmp
chown mysql:mysql /data/ -R- I started mysql and checked tables for MySQL upgrade (this is really necessary in order to recreate innodb_{index,table}_stats tables).
/etc/init.d/mysql start
mysql_upgrade -uuser -ppassword --force
/etc/init.d/mysql restart- No errors in mysql log, so I imported the dumps I created before
mysql database1 < database1.sql
mysql database2 < database2.sql- I discarded the tables space for every single table
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;- I moved the data files (the .ibd files) and imported the table space for every single table
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;Good luck!
Excellent. Period. !
Thank you!
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
Installing using RubyGems
To install as root:
sudo gem install innodb_ruby
To install as a user:
gem install –user-install innodb_ruby
Please read more info here: https://github.com/jeremycole/innodb_ruby/wiki