5

I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true.

After I run the query delete from abc.test, I want to calculate the database size of abc. Here is the query I use:

SELECT
    table_schema "name",
    sum( IF(engine = "MyISAM", data_length + index_length -  data_free,
    data_length + index_length)) "size"
FROM information_schema.TABLES
where table_schema like "abc";

The strange thing is that I find the database size doesn't decrease at all, however the data in "test" is gone.

I've done this kind of test many times, this strange behavior happens sometimes.

I'm using percona mysql server 5.5.29-rel29.4.

Can anybody tell me what is wrong?

Update:

Actually, I use another thread to check the database size periodically.

2
  • Questions: 1) When you ran delete from abc.testl, did /var/lib/mysql/abc/test1.ibd shrink or stay the same size? 2) Did /var/lib/mysql/ibdata1 grow ? 3) Did your information_schema query show that the used space decreased or stay the same number ? Mar 7, 2013 at 15:14
  • 1) the same size 2) no 3) stay the same
    – Eagle
    Mar 8, 2013 at 2:10

3 Answers 3

3

You need to rebuild the table in order to shrink the ibd file. This command will do it:

optimize table abc.test;
2
  • I know the ibd file won't shrink. I'm trying to get the database size by querying against information_schema. The query shows that sometimes the database size decreases, sometimes not.
    – Eagle
    Mar 8, 2013 at 2:15
  • Those values in information_schema are just estimates. The actual size of the table is the size of the ibd file.
    – Ike Walker
    Mar 8, 2013 at 16:25
2

I can tell you why /var/lib/mysql/abc/test1.ibd did not shrink.

The query delete from abc.testl; is a DML transaction. You simply deleted all the row data. If you want to reclaim the physical space of the .ibd file, you need to use DDL instead of DML. I have answered a question of this nature back on Jan 17, 2012 : Problem with InnoDB "per table" file sizes.

Try the following DDL statement:

TRUNCATE TABLE abc.testl;

If the table has no foreign keys, you can do this:

CREATE TABLE abc.test2 LIKE abc.test1;
DROP TABLE abc.test1;
ALTER TABLE abc.test2 RENAME abc.test1;

Here is what you can do right after a full delete of all rows:

DELETE FROM abc.testl;
ALTER TABLE abc.test1 ENGINE=InnoDB;

With regard to checking space, please see my posts with queries that can give you sizes grouped by storage engines and rolled up in summary:

Give it a Try !!!

1

For addtion, when you delete some rows in table. Table will be fragmented. Remove the fragment will make your table is shinked. Optimize table like Ike Walker said will help.

Optimization table makes downtime for mysqld.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.