Monday, August 25, 2008

Typical automated MySQL maintenance jobs

The following maintenance jobs are typically run against a MySQL database:

  • “Backup”

  • Clean-up binary logs.

  • Optimize tables

  • Purge query cache

  • Rotate binary logs

Backup

A backup is not a typical maintenance job. But it behaves more or less like one. The backup should be done regularly depending on the restore/PITR (Point in Time Recovery) requirements.

Make sure, that in the backup all the necessary files (data files, transaction log files, configuration files and binary log files) are included. To prove that the backup process is working properly a regular restore should be performed. This can ideally be combined with the set-up of new database instances for developers or testing.

Clean-up the binary logs

The binary logs can be cleaned-up in two ways:

a) Passive by MySQL itself:


# my.cnf

expire_logs_days = 7


b) Active by the customers environment:

mysql> PURGE MASTER LOGS TO 'binarylog.000999';

mysql> PURGE MASTER LOGS BEFORE '2008-07-29 22:46:26';


Make sure NO binary logs are purged which are still needed by a slave. In this situation the slave is lost and has to be set-up from scratch.

Make also sure binary logs are not removed by a file system operation (rm bin-log.*). Otherwise the database gets confused.

Optimize table

After large UPDATE or INSERT/DELETE operations or long time tables are blown up and contain a lot of unused space. This unused space can be partially reclaimed by optimizing the table again.



mysql> OPTIMIZE TABLE ;


This operation internally copies the whole table and therefore can take a long time!

Purge query cache

When there are SELECT queries with different sizes of result sets the query cache gets de-fragmented. This is shown by a lot of free space in the query cache but also a lot of not cached queries. Here it makes sense to purge the query cache from time to time.


mysql> FLUSH QUERY CACHE;


Binlog rotate

Binary logs can only be rotated by size. Sometimes you want to have them rotated by time. You can do this as follows (for example with a cron job):



mysql> FLUSH LOGS;


What other MySQL maintenance jobs are you performing (not application related) I am very interested in...

3 comments:

Orange said...

Just what I needed. I have become the maintainer of a small but previously neglected mysql server and was looking for exactly this kind of info. It serves one database which feeds an intranet application. The database/tables hadn't had any maintenance in three years so I found some errors in it initially and am now in the process of automating basic maintenance.

I've currently got a nightly job which runs in this order:
1. flush query cache.
2. check table.
3. analyze table.
4. optimize table.
5. repair table.

Steps 2 - 5 are run against all the tables of the database that serves an intranet site. I'm fairly certain that check and analyze are not all that helpful in an AUTOMATED routine unless that routine has some logic to figure out what to do with the result codes. i.e. I may only need to run repair if check/analyze find errors but right now my automated process is running repair each night regardless.

Any thoughts?

Shinguz said...

Hi knetknight

Thanks for your comment.

When your are facing time/scheduling issues I recommend you to go through the MySQL online documentation (Chapter: Table Maintenance Statements and Restrictions on InnoDB Tables) because some of these commands have no effect on some table types (InnoDB) and some of them do the work already done in a previous command.

Regards
Oli

Unknown said...

knetknight

I'm not to hot on MySQL but I would have thought 5. repair table would be avoided except as a last result?

If there is corrupted data would it not be better to restore from a backup and apply logs?