Blog

MySQL & phpMyAdmin – Truncate didnt free the innoDB space

Tags: innodbMySQLphpmyadminTableTruncate

Published on: April 7, 2012 by George K.

MySQL & phpMyAdmin – Truncate didnt free the innoDB space

Scenario:

Situation :In cPanel database management we have come across the issue that the truncate didn’t free the innoDB space. The issue is that we have Configured the MySQL server with innodb per table. There was a table comments having approximate 600+ MB size all because of comment spamming. I went to the table field in phpMyAdmin and clicked on “Empty” and all went good in regards to truncation.

Table has no records or zero record. But the phpmyAdmin still said

“InnoDB free: 67778911 KB” and when I checked the size by logging to the server in backend, it also showed the size.

12K /var/lib/mysql/db_gallery/comments.frm
653M /var/lib/mysql/db_gallery/comments.ibd

What could be the issue.? Won’t truncate work by actually removing the data from the .ibd file ? What is happening ?

I googled and found this link at StackOverFlow http://dba.stackexchange.com/questions/10779/problem-with-innodb-per-table-file-sizes

Went ahead to Operations tab –> Table Maintenance –> “Optimize table” and it’s fixed. It showed “InnoDB free: 0 kB” and a backend listing showed the below size history too 🙂

12K /var/lib/mysql/db_gallery/comments.frm
100K /var/lib/mysql/db_gallery/comments.ibd

Category : General, Howtos, Linux, MySQL, Troubleshooting

George K.

George K.

George started his career in web hosting and Linux technical support in the year 2004 and is with SupportSages since 2009. He has keen interest in server optimizations, custom security solutions, hacked server recovery, cyber forensic and high availability fail over system design and implementation. George loves long drives and is passionate about art and literature.

You may also read:

Comments

Add new commentSIGN IN

Let's Connect

Categories

Your Cart

Cart is empty.

Subtotal
₹0.00
APPLY
0
Send this to a friend