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.
Comments
Post a Comment