Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 ? –  RolandoMySQLDBA Mar 7 '13 at 15:14
    
1) the same size 2) no 3) stay the same –  Eagle Mar 8 '13 at 2:10

2 Answers 2

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

optimize table abc.test;
share|improve this answer
    
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 '13 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 '13 at 16:25

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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