Part 1 of this series spells out how to install and run MySQLTuner
, a script which recommends MySQL configuration changes. The goal is to optimize database performance and stability. This post describes how to interpret and use MySQLTuner
output, specifically in the area of database defragmentation.
Proceed with caution
A word of caution is warranted before I plunge into the details of implementing MySQLTuner's
suggestions. MySQLTuner
does not excuse you from learning the basic database optimization principles and following industry best practices. Following a MySQLTuner
recommendation without researching and understanding its ramifications is a gamble that may end up worsening your database performance and reliability.
Optimizing MySQL
configuration is not a trivial matter, and must be done in a controlled manner. You should change only one MySQL
configuration variable at a time. After every change, monitor the system to verify that the expected outcome is achieved without any negative side effect.
General comments
MySQLTuner
is a PERL script which you can invoke like this:
$ perl mysqltuner.pl
The following 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 often tempted to bypass the first several sections of the report on database metrics, and head straight to the Recommendations
section. But, the metrics provide the crucial context for the recommendations that follow, and should be read carefully.
Storage engine statistics
The Storage engine statistics
section of the report summarizes the total number and size of InnoDB
and MyISAM
tables in your database.
In the above example, 18 InnoDB
and 4 MyISAM
tables were detected. But the report does not identify the tables. If you want to list 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 list all MyISAM
tables, replace InnoDB
with MyISAM
in the above command.
The key actionable statistic in this section is the total number of fragmented tables (20 in the example). Fragmentation occurs during normal database operations when records are inserted and deleted, leaving behind 'gaps' in the database.
MySQLTuner
does not report the size of the 'gaps' or unused space in the fragmented tables. You can find out by running the following MySQL
statement.
mysql> select ENGINE, TABLE_NAME, \ DATA_LENGTH, INDEX_LENGTH, DATA_FREE, \ DATA_FREE / (INDEX_LENGTH + DATA_LENGTH) as frag_ratio \ from information_schema.tables \ where DATA_FREE > 0 order by 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 and INDEX_LENGTH variables contain respectively the size of the data and the index for a table. DATA_FREE is the size of the unused space in a table. The fragmentation ratio is the amount of unused space to the sum of the used data and index space.
If your tables are large, you can round up the output length variables to megabytes (MB) by using the following SQL statement:
mysql> select ENGINE, TABLE_NAME, \ round(DATA_LENGTH /1024 / 1024) as data_length, \ round(INDEX_LENGTH /1024 /1024) as index_length, \ round(DATA_FREE / 1024 /1024) 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;
Database Defragmentation
If you scroll down to the Recommendations
section of the report, you will see that the first general recommendation is 'Run OPTIMIZE TABLE
to defragment tables for better performance'. You may execute the OPTIMIZE TABLE
SQL statement for each of the 22 tables. Alternatively, you can run the mysqlcheck
command as follows:
$ mysqlcheck -Aos --auto-repair -u root -p
Notes:
Optimizing a table will lock it up. In other words, no update to the table is allowed while the operation is being performed. For a large production table, the substantial downtime is something that the database administrator should consider before deciding to optimize a table.
Optimizing a table does not necessarily reclaim its free space. This is especially true for
InnoDB
tables. Prior toMySQL
version 5.6, allInnoDB
tables are by default stored in a single file. This behavior is controlled by theMySQL
configuration variableinnodb_file_per_table
. OptimizingInnoDB
tables stored together in a single file may inadvertently produce the undesirable effect of increasing the file size.InnoDB
tables fragment differently than the legacyMyISAM
tables.mysqlcheck
optimizes anInnoDB
table by recreating it. For eachInnoDB
table that it optimizes,mysqlcheck
generates the following informational message: 'Note : Table does not support optimize, doing recreate + analyze instead'. You can safely ignore those messages.The
mysqld
server process must be running formysqlcheck
to execute.-A
(--all-databases
)With
-A
specified, all tables of all databases are optimized.If you want to defragment only a specific table of a specific database, customize the following command.
$ mysqlcheck -os <database> <table> -u root -p
-o
(--optimize
)This option specifies that the optimize operation is to be performed.
-s
(--silent
)-s
enables silent mode: only error messages are displayed.--auto-repair
If
MySQLTuner
finds a target table which is corrupted, it will try to repair it.
What's next?
Part 3 of this series continues the discussion on MySQLTuner
output, specifically about the management of database memory footprint.
2 comments:
To the extent running "enhance table" you can begin MySQL like this mysql - u username - p and from that point you can execute orders. Commonly the first order I utilize is show databases; however you should discover a MySQL instructional exercise first.
Ref : thesis writing service
- Your website is so cool. I am impressed by the info that you have on this site. I will be wanting ahead for additional of your respective posts. Stick with it!
Post a Comment