MySQL when Optimize Fails

MySQL – When an Optimize Fails

When using the MyISAM engine, you may need to run an “optimize” to regain space and increase performance.  What do you need to know BEFORE you start the “optimize” command.

The first thing to know is the “optimize” creates a Temporary file in the database datadir, (see mr.TMD).  The problem is there is only 16GB of free space with the mr.MYD and mr.MYI are almost 40GB.  There is one more thing you need to know.  MySQL uses tmpdir at the end of the “optimize” which can be a problem if there is not enough space.

SERVER1:/db/mysql/data/stats>ls -lt kh*                                             

                      -rw-rw---- 1 pm pmapp 18163175424 Mar 23 20:00 mr.MYI                                                             -rw-rw---- 1 pm pmapp 2973237248 Mar 23 20:00 mr.TMD                                                             -rw-rw---- 1 pm pmapp 28696182932 Mar 23 15:03 mr.MYD                                                           -rw-rw---- 1 pm pmapp 9086 Nov 25 2008 mr.frm                                                          

SERVER1:/db/mysql/data/stats>df -h .       

                                                  

                      Filesystem Size Used Avail Use% Mounted on                                                                 

                      /dev/sdb1 252G 224G 16G 94% /pmdata                                                                       

                      

SERVER1:/db/mysql/data/stats>  

Once the file system fills up, or if the “optimize” is killed, the table will become corrupt.

mysql> select count(*) from mr;                                                                  

                      ERROR 144 (HY000): Table './stats/mr' is marked as crashed and last (automatic?) repair failed   PM      

mysql>

Ok, now what can you do?  First, try a quick repair:

mysql> check table mr;                                                                      

                      +------------+-------+----------+----------------------------------------------------------------+      

                      | Table | Op | Msg_type | Msg_text |                                                                       

                      +------------+-------+----------+----------------------------------------------------------------+         

                      | stats.mr | check | warning | Table is marked as crashed and last repair failed |                       

                      | stats.mr | check | warning | 1 client is using or hasn't closed the table properly |                   

                      | stats.mr | check | warning | Size of indexfile is: 18163175424 Should be: 1957414912 |                 

                      | stats.mr | check | warning | Size of datafile is: 28696182932 Should be: 18056641136 |                 

                      | stats.mr | check | error | Record-count is not ok; is 291268514 Should be: 50272289 |                  

                      | stats.mr | check | warning | Found 10427444792 deleted space. Should be 0 |                            

                      | stats.mr | check | warning | Found 168940044 deleted blocks Should be: 0 |                             

                      | stats.mr | check | warning | Found 465138001 parts Should be: 50272289 parts |                         

                      | stats.mr | check | error | Corrupt |                                                                   

                      +------------+-------+----------+----------------------------------------------------------------+         

                      9 rows in set (6 min 21.41 sec)                                                                            

                                                                                                                                 

Nope, we received an error that the table is Corrupt.  I asked the System Administrator to add 50GB to /pmdata.  But they couldn’t.  Next step, shutdown the database and move mr.MYD and mr.MYI to a filesystem larger than 50GB, and run a myisamchk against the mr.MYI file.  Luckily /pmlog had 135GB free!

root@hostname:/gpm/prod/db/mysql/data/stats>df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda2              16G  8.3G  6.7G  56% /

/dev/sda1             107M   25M   77M  24% /boot

none                   32G     0   32G   0% /dev/shm

/dev/sdb3              30G  835M   28G   3% /pm

/dev/sdb1             252G  222G   18G  93% /pmdata

/dev/sdb2             205G   60G  135G  31% /pmlog

SERVER1:myisamchk --force --fast --update-state /pmlog/mysql/stats/khmr.MYI                                                              

                      Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200              

                      Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295             

                      Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295            

                      Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295             

Checking MyISAM file: /gpmlog/mysql/stats/mr.MYI  

                                                       

                      Data records: 50272289 Deleted blocks: 0                                                                   

                      myisamchk: warning: Table is marked as crashed and last repair failed                                      

                      myisamchk: warning: 1 client is using or hasn't closed the table properly                                  

                      - check file-size                                                                                          

                      myisamchk: warning: Size of indexfile is: 18163175424 Should be: 1957414912                                

                      - check record delete-chain                                                                                

                      - check key delete-chain                                                                                   

                      - check index reference                                                                                    

                      - check data record references index: 1    

SERVER1:/>df -h .                                                                            

                      Filesystem Size Used Avail Use% Mounted on                                                               

                      /dev/sda2 16G 15G 0 100% /                                                                                 

                      SERVER1:/> 

Data records: 50272289                                                                       

                      - Fixing index 1                                                                              

                      myisamchk: Disk is full writing '/tmp/ST4GHvP9' (Errcode: 28). Waiting for someone to free                 

                      space... Retry in 60 secs                                                                                  

                                                    

The /tmp filesystem filled up.  I didn’t see that coming, lost 2 hours.  Next I had to add the tempdir to the command as not to fill up the / filesystem.  The following syntax repaired the corrupted table.

myisamchk --force --safe-recover

--update-state /gpmlog/mysql/stats/mr.MYD --tmpdir=/pmlog/mysql/stats/

I then copied the mr.MYD and mr.MYI back to the original destination.  The myisamchk reduced the size of both files for a total of 15GB.  I started mysql, and ran a select count(*) mr; which now returned rows.