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

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
Rolando :really a nice answer.+1 from me.. –  Abdul Manaf Dec 27 '11 at 4:34
add comment

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.