mysql table very large size although it doesn't have a lot of records

Question

I have drupal website, one of the tables is cache_form. I have a cron to

DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW());

which works fine.

The size of cache_form.myd is 4.9G although I have this:

mysql>select sum(length(data))/1024/1204 from cache_form;

11.19144630 <b>==> 11 M

mysql>check table cache_form\G
Table: drupal.cache_form
Op: check
Msg_type: status
Msg_text: OK

mysql>select cid,length(data) from cache_form order by length(data) desc limit 1;
82373 ==> 80K

select * from information_schema.tables where table_name='cache_form'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: drupal
     TABLE_NAME: cache_form
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2681
 AVG_ROW_LENGTH: 4621
    DATA_LENGTH: 12391088 <b>==>11M</b>
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 177152
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2011-07-11 19:07:38
    UPDATE_TIME: 2011-12-26 08:40:09
     CHECK_TIME: 2011-12-26 08:09:45
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 

I have tried to repair it

mysql> repair table cache_form;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| drupal.cache_form | repair | status   | OK       |
+-------------------+--------+----------+----------+

then I got the correct size!!

Any idea of what might cause cache_form.myd file to be with this size and repair table finally removed the extra space?

Thanks

Answer 1

When it comes to a MyISAM table, deleting rows does trigger a kind of garbage collection in that all the unused space is recording in a linked list.

The data length you saw is correct. Your cron job deleted every row less than UNIX_TIMESTAMP(NOW()). This will not cause the table to shrink. All the delete rows are just chained together.

THere are other options you could have chosen to shrink the table:

• Option 1 : ALTER TABLE cache_form ENGINE=MyISAM;
• Option 2 : OPTIMIZE TABLE cache_form;
• Option 3 : Manually copy to a temp table

Option 1 simply does the following

• copies data pages in the .MYD to a temporary .MYD and renames it back to cache_form.MYD.
• copies index pages in the .MYI to a temporary .MYI and renames it back to cache_form.MYI.

Naturally, Option 1 will not copy the unused space.

Option 2 performs the same operation as Option 1 and then performs ANALYZE TABLE cache_form; to compute index statistics.

Option 3 would simply be an emulation of Option 1. Here it is:

CREATE TABLE cache_form_new LIKE cache_form;
INSERT INTO cache_form_new SELECT * FROM cache_form;
ALTER TABLE cache_form RENAME cache_form_old;
ALTER TABLE cache_form_new RENAME cache_form;
DROP TABLE cache_form_old;

Whenever you did the compression this way, find out what data_length is for cache_form_new. This will give you an idea what the actual size should be.