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]
[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!
Recent Comments