(mysql.info.gz) Maintenance schedule
Info Catalog
(mysql.info.gz) Table maintenance
(mysql.info.gz) Disaster Prevention
(mysql.info.gz) Table-info
5.7.4 Setting Up a Table Maintenance Schedule
---------------------------------------------
It is a good idea to perform table checks on a regular basis rather than
waiting for problems to occur. One way to check and repair `MyISAM'
tables is with the `CHECK TABLE' and `REPAIR TABLE' statements. These
are available starting with MySQL 3.23.16. See `CHECK TABLE'
CHECK TABLE. and `REPAIR TABLE' REPAIR TABLE.
Another way to check tables is to use `myisamchk'. For maintenance
purposes, you can use `myisamchk -s'. 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. For
example, whenever the machine has done a restart in the middle of an
update, you usually need to check all the tables that could have been
affected. (These are "expected crashed tables.") To check `MyISAM'
tables automatically, start the server with the `--myisam-recover'
option, available as of MySQL 3.23.25. If your server is too old to
support this option, you could add a test to `mysqld_safe' 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
restart. (The `.pid' file is created by `mysqld' when it starts 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 that we can examine
and repair them when needed.
Because we haven't had any unexpectedly crashed tables (tables that
become corrupted for reasons other than hardware trouble) for a couple
of years (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, MySQL tables need little maintenance. If you are changing
`MyISAM' tables with dynamic-sized rows (tables with `VARCHAR', `BLOB',
or `TEXT' columns) or have tables with many deleted rows you may want
to defragment/reclaim space from the tables from time to time (once a
month?).
You can do this by using `OPTIMIZE TABLE' on the tables in question.
Or, if you can stop the `mysqld' server for a while, change location
into the data directory and use this command while the server is
stopped:
shell> myisamchk -r -s --sort-index -O sort_buffer_size=16M */*.MYI
For `ISAM' tables, the command is similar:
shell> isamchk -r -s --sort-index -O sort_buffer_size=16M */*.ISM
Info Catalog
(mysql.info.gz) Table maintenance
(mysql.info.gz) Disaster Prevention
(mysql.info.gz) Table-info
automatically generated byinfo2html