Running optimization on MyISAM Engine truncated the whole table !

0.00 avg. rating (0% score) - 0 votes

Web development is always full of surprises whether you are developing website, maintaining databases or trying to save yourself from hack attempts.Recently We encountered a strange behavior of MyISAM engine in mysql5.4+ version when We tried to optimized the table containing around 50GB of data but it truncated showing this .

mysql> select count(1) from templates;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
We lost almost 53GB data of templates which were uploaded by users. Luckily we had backup of that mysql instance, so we restored it to a previous state but it was always in mind why this happened ?
To dig this out, We created a replica of same table to local machine and tried to run OPTIMIZE TABLE again on the same.
mysql> optimize table templates;
+----------------+----------+----------+--------------------------------------+
| Table          | Op       | Msg_type | Msg_text                             |
+----------------+----------+----------+--------------------------------------+
| db12.templates | optimize | error    | myisam_sort_buffer_size is too small |
| db12.templates | optimize | status   | OK                                   |
+----------------+----------+----------+--------------------------------------+ 

wait..what? what is myisam_sort_buffer_size ? and why it is too small !

We were aware of sort_buffer_size but this one came as a new search topic, So as usual We googled about it and figured out how OPTIMIZE TABLE works. Then We came to know it uses filesort to re-create the indexes after fragmenting the space. And because the size of the buffer sort size for myisam engine was around 2MB so it was not table to sort larger amount of data present in the table and corrupted the index file present in the database named template.MYI. So since all the indexes where corrupted, it was showing zero records but the data was present in template.MYD
root@serverXXXX-pc:/usr/local/mysql/data/db12# ls -al --block-size=M
total 5
drwx------ 2 mysql mysql     1M Sep 25 17:07 ./
drwx------ 7 mysql mysql     1M Oct  2 22:16 ../
-rw-rw---- 1 mysql mysql   230M Sep 25 17:06 templates.frm
-rw-rw---- 1 mysql mysql 57344M Sep 25 17:07 templates.MYD
-rw-rw---- 1 mysql mysql     5M Sep 25 17:07 templates.MYI
So It was only indexes that were showing no data but the it was there on the disc. So after increasing the myisam_sort_buffer_size to 256MB and tried running REPAIR TABLE on the table, magic happened. All the data that was missing was recovered as repair table rebuilt the indexes using the same file sort but now the buffer size was larger enough for sorting. To update the size here is the command.
SET GLOBAL myisam_sort_buffer_size = 268435456;
It is also used to increase the speed of repair and optimization commands in mysql. The more is the value, more quickly it will run repair and optimizations. And Finally We got all the templating data back into the table with Indexes. There are much more hidden things out there to explore, hoping we will try to keep you updated.
Happy Coding!
Posted in Database

2 thoughts on “Running optimization on MyISAM Engine truncated the whole table !

Comments are closed.