MySQL Maintenance Tasks for InnoDB with MySQL 5.1

MySQL Maintenance Tasks for InnoDB with MySQL 5.1

by LAURENCE posted on JULY 22, 2011

From time to time, MySQL 5.1 databases need a little house keeping.  We found our production DB had a hard time running a simple join query between two tables with about 400k rows.  It was taking between 30 and 100 seconds to run.  On QA however, it was taking 58 milliseconds. The columns involved were already indexed.  Thankfully it wasn’t impacting our users, but it still bugged me. The solution was simple, just run some cleanup commands. After the cleanup, on the live server the same query took just 4.8 milliseconds – that’s more like it!

Summary of solution:

1. Backup database
2. Check
3. Optimize
4. Analyze

$ mysqldump -u root -p --create-options --routines --triggers dbname > ./db.dmp
# note these cause LOCKS, so be careful on your production server!
$ mysqlcheck -u root -p --check --databases dbname
$ mysqlcheck -u root -p --optimize --databases dbname
$ mysqlcheck -u root -p --analyze --databases dbname

 

Complete details about each step:

1) First make database backup with mysqldump:
Don’t forget the argument –routines if you have stored procedures or functions and –triggers if using triggers:

$ mysqldump -u root -p --create-options --routines --triggers dbname > ./db.dmp
# copy to another server
$ scp ./db.dmp user@somehost:~/

For bonus points, actually restore the database on another system to make sure you have a valid backup.

This step may be impractical if the database is huge. In that case you are probably already using replication and have a backup system worked out.

2) Check:
Checks table for integrity errors.
http://dev.mysql.com/doc/refman/5.1/en/check-table.html

To check a single table:

mysql> CHECK TABLE {table name};

To check all tables in a database, from command line:

$ mysqlcheck -u root -p --check --databases dbname

This seems like a really smart thing to do on a regular basis.

3) Optimize:

Like a defrag operation, the optimize tables command reclaims unused space. At least, that is what it does for MyISAM. With InnoDB it basically runs an ALTER TABLE statement that changes nothing but tells MySQL to rebuild the table and its indexes.
http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

To optimize a single table:

mysql> OPTIMIZE TABLE {table name};

To optimize all tables in a database, from command line:

$ mysqlcheck -u root -p --optimize --databases dbname

If you get “Table does not support optimize, doing recreate + analyze instead”, that is normal for InnoDB.

4) Analyze:

Analyze rebuilds and optimizes the performance of indexes, specially it rebuilds the key distribution. If you have a slow running query but indexes are in place, it might be time to run this. A read lock goes into effect while this is running. If you have only InnoDB tables, this is already taken care of by Optimize.

http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html

To analyze a single table:

mysql> ANALYZE TABLE {table name};

To analyze all tables in a database, from command line:

$ mysqlcheck -u root -p --analyze --databases dbname

With InnoDB and ANALYZE TABLE, there are some oddities. In particular, the number of samples the analyzer takes can vary (configuration option is innodb_stats_sample_pages). The default is low, and this means running analyze tables repeatedly will produce slightly different results.

Read here for more information:

출처 : http://www.laurencegellert.com/2011/07/mysql-maintenance-tasks-for-innodb-with-mysql-5-1/