Friday, August 11, 2006

mysqld restarted: MyISAM tables crashing frequently

When MyISAM tables start crashing somewhat frequently, you can be sure that's not a good sign.

The first step in investigating the issue is to analyze the MySQL error log file (usually host-name.err). There you may find messages indicating that either the slave thread was killed, if the replication was enabled, or that mysqld was restarted just before the corruption happened.

If the reason for corruption was that the slave thread was killed, you can avoid it by ensuring that you issue "STOP SLAVE" first before shutting down MySQL.

On the other hand, if the reaason for MyISAM corruption was the "mysqld restarted" messages that you are seeing in the error log files, then you need to find out whether the current parent process of mysqld is the same as when MySQL was last restarted manually/properly.


If the parent process is new then you may need to investigate whether there is a cron job or a monitoring script that is automatically restarting MySQL leading to corruption. In the event that there is such a script, you should modify it so it stops the slave threads explicitly by issuing "STOP SLAVE".

If the parent process is the same as when MySQL was restarted manually then another explanation is that some query is causing mysqld to fail which is in turn causing "mysqld restarted" messages to appear in the log file.

Here's what you can do to troubleshoot and investigate instances like this:

1. Check all MyISAM tables not just the ones that have been crashing on db9. It may be the case that there is some table that is corrupted but not being reported by MySQL and queries on that table are triggering a crash.

2. Enable full query logging to investigate and find out which exact query is causing MySQL to fail and producing the "mysqld restarted" messages. The drawback is that you will need ample disk space for each and every query executed on the server to be logged.

3. Enable automatic recovery by using "myisam-recover" server startup option (this will not help us troubleshoot the crashes, however it will repair the tables automatically if they were marked as 'not closed properly' or 'crashed'). If we enable automatic recovery, we need to decide whether MySQL should keep a backup of the original corrupted data file (can use a lot of disk space).

The ideal configuration for automatic recovery is to use BACKUP with FORCE options which will cause MySQL to forcefully repair the corrupted table (even if the repair would cause some rows to be lost). MySQL will however keep a backup of the corrupted table if this option is used.

Anything else that I missed that can be done to investigate and troubleshoot frequent MyISAM corruptions?

3 comments:

Anonymous said...

Hello,

we encountered similar problems on a SUN Server running Solaris 9 (32bit), Mysql 4.1.16.

We solved the problem by enabling the "checksum" flag for almost all tables. The speed decrease was marginal.

D.

Anonymous said...

There are many types of MyISAM corruption based on their cause. First there are ones which come from MySQL bugs themselves, not crashes. Similar could be further corruption based on previous corruption or MySQL format changes with version upgrade.

If you get corruption and this is reason of the crash this could be MySQL problem or hardware/OS problem. Bad memory is quite frequent cause of corruption for example.

Now if it is MySQL problem you may get problems which correspond to particular query (good as easy to track and fix) or general memory corruption case when you can see random queries crashing.

Anyway looking at your logs as well as on output of check table command for currupted table is good way to start.

Anonymous said...

Hello Error what ?

mysql 4016 0.1 2.2 107776 70008 ? S 01:46 0:02 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=my
mysql 4044 0.0 2.2 107776 70008 ? S 01:46 0:02 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=my
mysql 4046 0.1 2.2 107776 70008 ? S 01:46 0:03 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=my