For the MySQL database to work quickly and without interruptions, it is necessary to perform maintenance jobs from time to time. This mostly just means running a few commands, but let’s address first things first.

Why Do You Need to Do Anything?

A database works quickly due to a complex storage system that can degrade over time. Also, data is stored on physical media that can eventually wear out.

OPTIMIZE

The DELETE or UPDATE commands do not delete data, but instead they mark it as deleted. This leads to table fragmentation. A high level of data fragmentation has two negative consequences:

1. The size of the database files is disproportionately large with respect to the data
2. General database slowdown

The OPTIMIZE command overwrites database files, eliminating fragmentation effects.

When to Run OPTIMIZE

Typically, you should run OPTIMIZE after a bulk data update or deletion.

Data fragmentation itself is not a problem. The voids that form in the database will be overwritten over time. 

Using the following SQL query, you can understand the degree of fragmentation of tables:

SELECT ENGINE,
  concat(TABLE_SCHEMA, '.', TABLE_NAME) as Table_Name,
  round(DATA_LENGTH/1024/1024, 2) as Data_Length,
  round(INDEX_LENGTH/1024/1024, 2) as Index_Length,
  round(DATA_FREE/1024/1024, 2) as Data_Free,
  (data_free/(index_length+data_length)) as Frag_Ratio
FROM information_schema.tables
WHERE DATA_FREE > 0
ORDER BY frag_ratio DESC;

The Frag_Ratio column will show the fragmentation percentage. If it is less than 5%, then nothing needs to be done. Even 20% fragmentation may not affect query performance. However, if you notice a drop in performance and the percentage of data fragmentation is high, you should run OPTIMIZE. 

Please note that the execution of OPTIMIZE completely blocks access to the table. For a database in production, this is usually equal to stopping the entire database. Therefore, run this command only during scheduled maintenance. 

OPTIMIZE Use Cases

You can run this command either for a specific table using an SQL statement or for the entire database using a standalone utility.

Running OPTIMIZE on a specific table:

mysql>OPTIMIZE TABLE sakila.actor

For all tables in a separate schema: 

mysqlcheck --user=root --password=secret-pass  --optimize sakila

For all schemas:

mysqlcheck --user=root --password=secret-pass  --optimize --all-databases

ANALYZE

MySQL relies on data distribution statistics to build an optimal plan for query execution. Statistics are stored in the INFORMATION_SCHEMA STATISTICS table. If this data is outdated, MySQL may use a less optimal way to create a query.

When to Run ANALYZE

You can run ANALYZE from time to time if your MySQL database has fixed minimum load hours. 

If the innodb_stats_auto_recalc flag is set, then after changing 10% of the records, the automatic recalculation of statistics will start. The counter counting down 10% is not stored in RAM and is reset when the server is restarted. It can lead to outdated statistics. 

The ANALYZE command places a read lock on the table for which it is being executed. Therefore, running this command at the time of minimum load on the server will avoid blocking tables during high loads.

ANALYZE Use Cases

You can run this command either for a specific table using the SQL statement or for the entire database using a command line utility.

Running for a single table:

ANALYZE TABLE sakila.actor;

Running for a specific schema:

mysqlcheck --user=root --password=secret-pass  --analyze sakila

Running for all schemas on the server:

mysqlcheck --user=root --password=secret-pass  --all-databases

CHECK

For various reasons, the data in the database can get corrupted. But in general, this can happen either due to hardware errors or due to software errors. More details about the problems that may arise are described in the official documentation.

MySQL has a standard CHECK command to find most of the problems and fix them. 

The CHECK command has several levels: 

FOR UPGRADE	Do a very quick check if the storage format for the table has changed so that one needs to do a REPAIR. This is only needed when one upgrades between major versions of MariaDB or MySQL. This is usually done by running mysql_upgrade.
FAST	Only check tables that have not been closed properly or are marked as corrupt. This is only supported by the MyISAM and Aria engines. For other engines, the table is checked normally.
CHANGED	Check only tables that have changed since last REPAIR/CHECK. Only supported by the MyISAM and Aria engines. For other engines the table is checked normally.
QUICK	Do a fast check. For the MyISAM and Aria engines this means we skip checking the delete link chain, which may take some time.
MEDIUM	Scan the data files also. Checks integrity between data and index files with checksums. In most cases this should find all possible errors.
EXTENDED	Does a full check to verify every possible error. For MyISAM and Aria we verify for each row that all its keys exist and point to the row. This may take a long time on big tables!

 When to Run CHECK?

With the FAST or CHANGED option, you can run it from time to time, for example, every midnight. With the EXTENDED option, you can run it during routine maintenance.

Running CHECK on large tables may block other flows. Depending on the option selected (QUICK/EXTENDED), the duration varies greatly. When using the EXTENDED option, the time it takes to find errors for large tables can be hours. However, when using the FAST or CHANGED options, they execute quickly and have almost no performance impact.

Thus, the quick check can be run automatically once a day, during minimum server load. A full check of the data can take a lot of time. Because of this, some active queries to the database may time out. You should run a full check during routine maintenance, notifying customers of the downtime. Or if the database is part of a cluster, then just put the database in single-user mode.

And of course, you need to run the check if you get any abnormal errors while working with the database which may indicate data corruption.

CHECK Use Cases

Running a check on a specific table with the extended option:

mysql>CHECK TABLE sakila.actor EXTENDED

Running a quick check on the entire database with an autocorrection option:

mysqlcheck --user=root --password=secret-pass  --auto-repair --fast sakila

Running a full check on the entire DBMS:

mysqlcheck --user=root --password=secret-pass  --extended --auto-repair --all-databases;

What and When to Run

ANALYZE and CHECK FAST can be set up to run automatically during nightly database maintenance.

Run OPTIMIZE and CHECK EXTENDED when data locking is not a problem, for example, during routine maintenance. 

If a large database size is an issue, you can also consider running OPTIMIZE on individual tables after a bulk data update or deletion.

How to Automate?

All the commands described above are SQL queries that can be run using the mysqlcheck utility from the console.

In Linux, you can schedule regular nightly maintenance using cron. 

Enter:

crontab -e

and then add the following string:

00 00 * * * mysqlcheck --user=root --password=secret-pass  --extended --auto-repair --all-databases && mysqlcheck --user=root --password=secret-pass --analyze --all-databases;

In Windows, this can be done using windows schedule.

 Press win+r and enter taskschd.msc.

 Select Create Task.

 From the Triggers tab, add an execution schedule.

 From the Action tab, add the following data into the program field, specify psql in the field:

mysqlcheck --user=root --password=secret-pass  --extended --auto-repair --all-databases && mysqlcheck --user=root --password=secret-pass --analyze --all-databases;

How to Automate Maintenance Jobs with SqlBak

The SqlBak service is focused primarily on the regular creation of backups. However, it also allows you to automate database maintenance.

Compared to automation through standard operating system utilities, SqlBak has several advantages:

• Email notifications
• Job history
• Running .sh or .bat scripts

To automate MySQL maintenance using SqlBak follow these steps:

Install a thin client on the server and connect the database (connection details are available here).

Create a maintenance job.

Add two BAT scripts:

mysqlcheck --user=root --password=secret-pass --analyze --all-databases;

and

mysqlcheck --user=root --password=secret-pass  --extended --auto-repair --all-databases

Specify your email (optional) to receive job execution updates.

Start the job.