Mysqltuner Serial Business Office Ii - Defragmenting A Mysql Database

Part 1 of this serial spells out how to install too move MySQLTuner, a script which recommends MySQL configuration changes. The destination is to optimize database performance too stability. This ship service describes how to translate too purpose MySQLTuner output, specifically inward the surface area of database defragmentation.

Proceed alongside caution

A intelligence of caution is warranted earlier I plunge into the details of implementing MySQLTuner's suggestions. MySQLTuner does non excuse yous from learning the basic database optimization principles too next manufacture best practices. Following a MySQLTuner recommendation without researching too agreement its ramifications is a adventure that may cease upward worsening your database performance too reliability.

Optimizing MySQL configuration is non a picayune matter, too must hold upward done inward a controlled manner. You should alter solely i MySQL configuration variable at a time. After every change, monitor the organisation to verify that the expected termination is achieved without whatever negative side effect.

General comments

MySQLTuner is a PERL script which yous tin invoke similar this:

$ perl mysqltuner.pl

The next is the MySQLTuner output for a low-memory VPS server running on the LAMP platform (Linux, Apache, MySQL, PHP). The VPS is dedicated for running a WordPress blog.

One is frequently tempted to bypass the rootage several sections of the study on database metrics, too caput conduct to the Recommendations section. But, the metrics render the crucial context for the recommendations that follow, too should hold upward read carefully.

Storage engine statistics

The Storage engine statistics department of the study summarizes the total publish too size of InnoDB too MyISAM tables inward your database.

In the higher upward example, eighteen InnoDB too iv MyISAM tables were detected. But the study does non position the tables. If yous desire to listing all InnoDB tables, execute the command below.

$ echo 'select concat(table_schema,".",table_name) from information_schema.tables where engine="InnoDB";'|mysql -u root -p

To listing all MyISAM tables, supplant InnoDB alongside MyISAM inward the higher upward command.

The fundamental actionable statistic inward this department is the total publish of fragmented tables (20 inward the example). Fragmentation occurs during normal database operations when records are inserted too deleted, leaving behind 'gaps' inward the database.

MySQLTuner does non study the size of the 'gaps' or unused infinite inward the fragmented tables. You tin bring out out past times running the next MySQL statement.

 mysql> pick out ENGINE, TABLE_NAME,               \             DATA_LENGTH, INDEX_LENGTH, DATA_FREE, \           DATA_FREE / (INDEX_LENGTH + DATA_LENGTH) equally frag_ratio  \        from information_schema.tables                             \        where DATA_FREE > 0 companionship past times frag_ratio desc;    +-------+-----------+-----------+------------+---------+----------+   | ENGINE| TABLE_NAME|DATA_LENGTH|INDEX_LENGTH|DATA_FREE|frag_ratio|   +-------+-----------+-----------+------------+---------+----------+   ...   | InnoDB| wp_options|   1179648 |      16384 | 11534336|  9.6438  |   ...   +-------+-----------+-----------+------------+---------+----------+   

The DATA_LENGTH too INDEX_LENGTH variables incorporate respectively the size of the information too the index for a table. DATA_FREE is the size of the unused infinite inward a table. The fragmentation ratio is the amount of unused infinite to the amount of the used information too index space.

If your tables are large, yous tin circular upward the output length variables to megabytes (MB) past times using the next SQL statement:

 mysql> pick out ENGINE, TABLE_NAME,                          \           round(DATA_LENGTH  /1024 / 1024) equally data_length, \           round(INDEX_LENGTH /1024 /1024) equally index_length, \           round(DATA_FREE / 1024 /1024) equally data_free,      \           data_free / (index_length + data_length) equally frag_ratio \        from information_schema.tables                      \        where DATA_FREE > 0 companionship past times frag_ratio desc;   

Database Defragmentation

If yous scroll downward to the Recommendations department of the report, yous volition run into that the rootage full general recommendation is 'Run OPTIMIZE TABLE to defragment tables for improve performance'. You may execute the OPTIMIZE TABLE SQL disputation for each of the 22 tables. Alternatively, yous tin move the mysqlcheck command equally follows:

$ mysqlcheck -Aos --auto-repair -u root -p

Notes:

1. Optimizing a tabular array volition lock it up. In other words, no update to the tabular array is allowed land the performance is existence performed. For a large production table, the substantial downtime is something that the database administrator should take in earlier deciding to optimize a table.

2. Optimizing a tabular array does non necessarily reclaim its costless space. This is peculiarly truthful for InnoDB tables. Prior to MySQL version 5.6, all InnoDB tables are past times default stored inward a unmarried file. This demeanour is controlled past times the MySQL configuration variable innodb_file_per_table. Optimizing InnoDB tables stored together inward a unmarried file may inadvertently create the undesirable trial of increasing the file size.

3. InnoDB tables fragment differently than the legacy MyISAM tables. mysqlcheck optimizes an InnoDB tabular array past times recreating it. For each InnoDB tabular array that it optimizes, mysqlcheck generates the next informational message: 'Note : Table does non back upward optimize, doing recreate + analyze instead'. You tin safely ignore those messages.

4. The mysqld server procedure must hold upward running for mysqlcheck to execute.

5. -A (--all-databases)

   With -A specified, all tables of all databases are optimized.

   If yous desire to defragment solely a specific tabular array of a specific database, customize the next command.

   $ mysqlcheck -os <database> <table> -u root -p

6. -o (--optimize)

   This selection specifies that the optimize performance is to hold upward performed.

7. -s (--silent)

   -s enables still mode: solely fault messages are displayed.

8. --auto-repair

   If MySQLTuner finds a target tabular array which is corrupted, it volition endeavour to repair it.

What's next?

Part 3 of this serial continues the intelligence on MySQLTuner output, specifically almost the management of database retentiveness footprint.

Share this post