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_SCHEMA: drupal
     TABLE_NAME: cache_form
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2681
    DATA_LENGTH: 12391088 <b>==>11M</b>
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 177152
      DATA_FREE: 0
    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

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?


  • Just a comment change 1204->1024 to do the right math wrong: select sum(length(data))/1024/**1204** from cache_form; right select sum(length(data))/1024/**1024** from cache_form; anyway, thanks for the idea to check table size.
    – user39492
    Jun 4 '14 at 8:57

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.

  • Rolando :really a nice answer.+1 from me.. Dec 27 '11 at 4:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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