The Geek Diary

Beginners Guide to Implementing Table Maintenance in MySQL

by

Table maintenance operations are useful for identifying and correcting database problems such as the following:
– Tables that become damaged as a result of a server crash.
– Slow query processing on tables.

Many tools are available for performing table maintenance:

  • MySQL Workbench
  • MySQL Enterprise Monitor
  • SQL (DML) maintenance statements
  • Utilities: mysqlcheck and myisamchk
  • Server auto-recovery

SQL for Table Maintenance Operations

There are multiple SQL statements for performing table maintenance:

  • ANALYZE TABLE: Updating index statistics
  • CHECK TABLE: Properly checking integrity
  • CHECKSUM TABLE: Properly checking integrity
  • REPAIR TABLE: Repairs
  • OPTIMIZE TABLE: Optimization

Each statement takes one or more table names and optional keywords. Below is an example of a maintenance statement and output:

mysql> CHECK TABLE world_innodb.City; 
+-------------------+-------+----------+----------+ 
| Table             | Op    | Msg_type | Msg_text | 
+-------------------+-------+----------+----------+
| world_innodb.City | check | status   | OK       | 
+-------------------+-------+----------+----------+

After performing the requested operation, the server returns information about the result of the operation to the client. The information takes the form of a result set with four columns:

  • Table: Indicates the table for which the operation was performed
  • Op: Names the operation (check, repair, analyze, or optimize)
  • Msg_type: Provides an indicator of success or failure
  • Msg_text: Provides extra information

ANALYZE TABLE Statement

MySQL uses the stored key distribution statistics to decide the order in which the optimizer joins tables when you perform a join on something other than a constant. In addition, key distributions determine which indexes MySQL uses for a specific table within a query. You can execute the ANALYZE TABLE statement to analyze and store the statistics, or you can configure InnoDB to gather the statistics automatically after a number of data changes or when you query table or index metadata.

ANALYZE TABLE characteristics:

  • During the analysis, MySQL locks the table with a read lock for InnoDB and MyISAM.
  • This statement is equivalent to using ‘mysqlcheck –analyze’.
  • Requires SELECT and INSERT privileges for the table
  • Supports partitioned tables. You can also use ALTER TABLE…ANALYZE PARTITION to check one or more partitions.

Example of a good ANALYZE TABLE result:

mysql> ANALYZE LOCAL TABLE Country; 
+----------------------+--------+----------+----------+ 
| Table                | Op     | Msg_type | Msg_text | 
+----------------------+--------+----------+----------+ 
| world_innodb.Country | analyze| status   | OK       | 
+----------------------+--------+----------+----------+

If the table has not changed since running the last ANALYZE TABLE statement, MySQL does not analyze the table. By default, MySQL writes ANALYZE TABLE statements to the binary log and replicates them to replication slaves. Suppress logging with the optional NO_WRITE_TO_BINLOG keyword or its alias, LOCAL.

You can control how MySQL gathers and stores key distribution statistics with the following options:

  • innodb_stats_persistent: When this option is ON, MySQL enables the STATS_PERSISTENT setting on newly created tables. You can also set STATS_PERSISTENT on tables when using the CREATE TABLE or ALTER TABLE statements. By default, MySQL does not persist key distribution statistics to disk, so they must be generated at times such as server restart. MySQL stores key distribution statistics on disk for tables with STATS_PERSISTENT enabled so that it does not need to generate statistics as frequently for those tables. This allows the optimizer to create more consistent query plans over time.
  • innodb_stats_persistent_sample_pages: MySQL recalculates statistics by reading a sample of a STATS_PERSISTENT table’s index pages, rather than the entire table. By default, it reads a sample of 20 pages. Increase this number to improve the quality of generated statistics and query plans. Decrease this number to reduce the I/O cost of generating statistics.
  • innodb_stats_transient_sample_pages: This option controls the number of index pages sampled on tables that do not have the STATS_PERSISTENT setting.

The following options control how MySQL gathers statistics automatically.

  • innodb_stats_auto_recalc: With this option enabled, MySQL generates statistics automatically for a STATS_PERSISTENT table when 10% of its rows have changed since the last recalculation.
  • innodb_stats_on_metadata: Enable this option to update statistics when you execute metadata statements such as SHOW TABLE STATUS, or when querying INFORMATION_SCHEMA.TABLES. By default, this option is disabled.

CHECK TABLE Statement

The CHECK Table statement checks the integrity of table structure and contents for errors. CHECK TABLE characteristics:

  • For MyISAM tables, the key statistics are updated as well.
  • Can also check views for problems, such as tables that are referenced in the view definition that no longer exist.
  • Supports partitioned tables. You can also use ALTER TABLE…CHECK PARTITION to check one or more partitions.

With FOR UPGRADE, the server checks each table to determine whether the table structure is compatible with the current version of MySQL. Incompatibilities might occur because the storage format for a data type has changed or because its sort order has changed. If there is a possible incompatibility, the server runs a full check on the table. If the full check succeeds, the server marks the table’s .frm file with the current MySQL version number. Marking the .frm file ensures that future checks for the table with the same version of the server are fast.

Use FOR UPGRADE with the InnoDB, MyISAM, and ARCHIVE storage engines. Use QUICK with InnoDB and MyISAM tables. MyISAM supports other options.

Example of a good CHECK TABLE result:

mysql> CHECK TABLE Country; 
+----------------------+-------+----------+----------+ 
| Table                | Op    | Msg_type | Msg_text | 
+----------------------+-------+----------+----------+ 
| world_innodb.Country | check | status   | OK       | 
+----------------------+-------+----------+----------+

If the output from CHECK TABLE indicates that a table has problems, repair the table. For example, you could use the CHECK TABLE statement to detect hardware problems (such as faulty memory or bad disk sectors) before repairing a table. The Msg_text output column is normally OK. If you do not get either OK or Table is already up to date, run a repair of the table. If the table is marked as corrupted or not closed properly but CHECK TABLE does not find any problems in the table, it marks the table as OK.

CHECKSUM TABLE Statement

The CHECKSUM TABLE statement reports a table checksum. It is used to verify that the contents of a table are the same before and after a backup, rollback, or other operation.

CHECKSUM TABLE characteristics:

  • CHECHSUM TABLE requires the SELECT privilege for the table.
  • For a nonexistent table, CHECKSUM TABLE returns NULL and generates a warning.
  • If the EXTENDED option is used, the entire table is read row by row, and the checksum is calculated.
  • If the QUICK option is used, a live table checksum is reported if it is available; NULL is reported otherwise. This is very fast.It also enable live checksum on MyISAM tables by specifying the CHECKSUM=1 table option when you create the table.
  • If neither QUICK nor EXTENDED is specified, MySQL assumes EXTENDED except for MyISAM tables with CHECKSUM=1.

The checksum value depends on the table row format. If the row format changes, the checksum also changes. For example, the storage format for VARCHAR changed after MySQL 4.1, so if you upgrade a 4.1 table to a later version, the checksum value changes if the table contains VARCHAR fields.

Note: If the checksums for two tables are different, it is likely that the tables differ in some way. However, since the hashing function used by CHECKSUM TABLE is not guaranteed to be collision-free, there is a very remote possibility that two non-identical tables can produce the same checksum.

OPTIMIZE TABLE Statement

OPTIMIZE TABLE statement cleans up a table by defragmenting it. It defragments the table by rebuilding it and freeing unused space. It locks the table during optimization and updates index statistics. It is most beneficial on a permanent, fully populated table.

OPTIMIZE TABLE Characteristics:

  • Defragmenting involves reclaiming unused space caused by deletes and updates, and coalescing records that have become split and stored non-contiguously.
  • Requires SELECT and INSERT privileges for the table
  • Supports partitioned tables. You can also use ALTER TABLE…OPTIMIZE PARTITION to check one or more partitions.

For example, you can use the OPTIMIZE TABLE statement to rebuild a FULLTEXT index in InnoDB, after modifying a substantial number of rows. With InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. InnoDB is not subject to fragmentation in the same way as other storage engines, so you do not need to use OPTIMIZE TABLE very often.

Use OPTIMIZE TABLE on a table that uses the ARCHIVE storage engine to compress the table. The number of rows in ARCHIVE tables reported by SHOW TABLE STATUS is always accurate. An .ARN file may appear during optimization operations.

The following OPTIMIZE TABLE statement optimizes two fully populated tables in the mysql database:

mysql> OPTIMIZE TABLE mysql.help_relation, mysql.help_topic; 
+---------------------+----------+----------+----------+
| Table               | Op       | Msg_type | Msg_text | 
+---------------------+----------+----------+----------+
| mysql.help_relation | optimize | status   | OK       | 
| mysql.help_topic    | optimize | status   | OK       | 
+---------------------+----------+----------+----------+ 
2 rows in set (0.00 sec)

With MyISAM tables, use the OPTIMIZE TABLE statement after a large part of a table has been deleted or if many changes have been made to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list, and subsequent INSERT operations reuse old row positions. OPTIMIZE TABLE works best when you use it on a table that is fully populated and does not change much. The benefits of optimization diminish if the data changes a lot, and you have to optimize often.

REPAIR TABLE Statement

REPAIR TABLE characteristics:

  • QUICK option: Tries to repair only the index file and not the data file. This type of repair is like that done by myisamchk –recover –quick.
  • EXTENDED option: MySQL creates the index row by row instead of creating one index at a time with sorting. This type of repair is like that done by myisamchk –safe-recover.
  • The USE_FRM option cannot be used on a partitioned table.
  • Requires SELECT and INSERT privileges for the table
  • Supports partitioned tables. You can also use ALTER TABLE…REPAIR PARTITION to check one or more partitions.

It is best to make a backup of a table before performing a table repair operation; under some circumstances, the operation might cause data loss. Possible causes include (but are not limited to) file system errors. If the server crashes during a REPAIR TABLE operation, you should immediately execute another REPAIR TABLE after restarting before performing any other operations to avoid further corruption. If you frequently need to use REPAIR TABLE to recover from corrupt tables, try to find the underlying reason to prevent such corruption and eliminate the need to use REPAIR TABLE.

Example of a REPAIR TABLE statement:

mysql> REPAIR TABLE mysql.help_relation; 
+---------------------+--------+----------+----------+ 
| Table               | Op     | Msg_type | Msg_text | 
+---------------------+--------+----------+----------+ 
| mysql.help_relation | repair | status   | OK       | 
+---------------------+--------+----------+----------+ 
1 row in set (0.00 sec)

You May Also Like