GNU Info

Info Node: (mysql.info)Maintenance regimen

(mysql.info)Maintenance regimen


Next: Table-info Prev: Table maintenance Up: Disaster Prevention
Enter node , (file) or (file)node

Setting Up a Table Maintenance Regimen
--------------------------------------

Starting with MySQL Version 3.23.13, you can check MyISAM tables with
the `CHECK TABLE' command. Note: CHECK TABLE.  You can repair tables
with the `REPAIR TABLE' command. Note: REPAIR TABLE.

It is a good idea to perform table checks on a regular basis rather than
waiting for problems to occur.  For maintenance purposes, you can use
`myisamchk -s' to check tables.  The `-s' option (short for `--silent')
causes `myisamchk' to run in silent mode, printing messages only when
errors occur.

It's also a good idea to check tables when the server starts up.  For
example, whenever the machine has done a reboot in the middle of an
update, you usually need to check all the tables that could have been
affected. (This is an "expected crashed table".) You could add a test to
`safe_mysqld' that runs `myisamchk' to check all tables that have been
modified during the last 24 hours if there is an old `.pid' (process
ID) file left after a reboot.  (The `.pid' file is created by `mysqld'
when it starts up and removed when it terminates normally.  The
presence of a `.pid' file at system startup time indicates that
`mysqld' terminated abnormally.)

An even better test would be to check any table whose last-modified time
is more recent than that of the `.pid' file.

You should also check your tables regularly during normal system
operation.  At MySQL AB, we run a `cron' job to check all our important
tables once a week, using a line like this in a `crontab' file:

     35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI

This prints out information about crashed tables so we can examine and
repair them when needed.

As we haven't had any unexpectedly crashed tables (tables that become
corrupted for reasons other than hardware trouble) for a couple of
years now (this is really true), once a week is more than enough for us.

We recommend that to start with, you execute `myisamchk -s' each night
on all tables that have been updated during the last 24 hours, until
you come to trust MySQL as much as we do.

Normally you don't need to maintain MySQL tables that much.  If you are
changing tables with dynamic size rows (tables with `VARCHAR', `BLOB'
or `TEXT' columns) or have tables with many deleted rows you may want
to from time to time (once a month?) defragment/reclaim space from the
tables.

You can do this by using `OPTIMIZE TABLE' on the tables in question or
if you can take the `mysqld' server down for a while do:

     isamchk -r --silent --sort-index -O sort_buffer_size=16M */*.ISM
     myisamchk -r --silent --sort-index  -O sort_buffer_size=16M */*.MYI


automatically generated by info2www version 1.2.2.9