MySQL .ibd file is too big

Question

I got a memory issue while in linux. When i checked memory allocation by using df -h i found a path /dev/sda1 and it is used 100%. When i dig more i found that /var/lib/mysql file ate almost all memory. Finally i encountered with one table with the extention .ibd has almost 20GB. When i hit sudo du -a | sort -nr | head command, i finally found actual table. it was like this:

20937708        ./var/lib/mysql/databasename/tablename.ibd

I don't know what is going on. Please help me out. My server stopped almost due to the storage issue.

Answer 1

It appears that *.ibd files (like the file ibdata1) by default never shrink, they can only grow. They are created if you have

innodb_file_per_table

configuration set (which is the default on ubuntu). If that is not set then you'll see the same behavior from the ibdata file which is basically all tables rolled into one. Apparently if you have the "all rolled into one" option then there's no easy workaround besides dumping all tables and reloading them from disk. But you don't so you're good there.

With the .ibd files if you want to reclaim them apparently there's an option optimize table tablename which basically locks the table and rewrites it, and reclaims all the disk space.

Another command that is mentioned (and I assume does the same thing as optimize is ALTER TABLE ... ENGINE=InnoDB;)

See also here.

Somewhat related, even with innodb_file_per_table turned on, the idbdata1 file can grow "a bit" (and never shrinks) because it still includes rollbacks for "your largest transaction to date". Go figure.

Answer 2

This is one of the pain points of mysql. Our server has only 100G space and mysql is now occupying almost 80% of it.

We were able to solve it by purging old records and then optimizing the table

optimize table <db.tablename>

If mysql is unable to optimize it, it will analyze and recreate it automatically for you.

Be warned that the table is locked during the duration of this command which can be a lengthy process.

Answer 3

The first thing you want to do is ascertain the size of the table from 2 perspectives

• Operating System
• INFORMATION_SCHEMA

Please run the following

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=databasename
TB=tablename
SQL="SELECT data_length+index_length FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND table_name='${TB}'"
TBLSIZE_OPER=`ls -l /var/lib/mysql/${DB}/${TB}.ibd` 
TBLSIZE_INFO=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBLSIZE_FRAG = TBLSIZE_OPER - TBLSIZE_INFO ))
(( TBLSIZE_FRAG_GB = TBLSIZE_FRAG / 1073741824 ))
echo ${TBLSIZE_FRAG} ${TBLSIZE_FRAG_GB}

This will tell you how many bytes and gigabytes of wasted space you have

If you feel this is a lot of fragmentation, then simply run the following

USE databasename
ALTER TABLE tablename ENGINE=InnoDB;

This will create a temp table with no fragmewntation and then rename the temp table.

I wrote about this almost a year ago : How to monitor mysql percentage disk utilization?

Answer 4

Above answers are the solutions however you can also explore below options with caution.

1. Check if you have any unnecessary files. if you have delete and create some free space. Also you can check for older backups or something if you missed or forgot to delete which you might not need anymore.

2. If you have enabled log_bin and you are sure you don't need them, you can purge those binary logs. Before purging binary logs make sure the slave(s) is in sync before.

   Note: You have to be "REALLY" sure before deleting binary logs. Once you delete you will lose the ability to do point in time recovery using previous backup(s).

Answer 5

While the solution by @RolandoMySQLDBA works but it sometimes raises the Duplicate entry ... for key ... error:

USE databasename
ALTER TABLE tablename ENGINE=InnoDB;

In this case, use this instead:

OPTIMIZE TABLE `databasename`.`tablename`;

Which locks the table from any writes.