MySQL .ibd file is too big

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.

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.

StackExchange Database Administrators Q#142653, answer score: 6