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.
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 ?