Mysql table fragmentation cleaning, Mysql fragmentation cleaning

Mysql table fragmentation cleaning, Mysql fragmentation cleaning

Mysql has recently declined in the production environment, and some SQL statements are also slow. First, check whether the slow query log is enabled.
Show variables like 'slow _ query_log %'

Time set for viewing slow logs
Show variables like 'long _ query_time % ';

You can see that the slow query is enabled, find/-name slow-query.log to find slow logs
It is found that the SQL statements in the table are very slow but all adopt indexes, but these slow SQL statements point to one or two tables. Therefore, it is possible that the deletion operations on these tables are performed each time the backup is performed, but no fragmentation is performed.
Sort the shards and process them by the table engine.
Myisam clear fragments
Optimize table table_name,
InnoDB fragment cleaning
See this passage
If you frequently delete rows (or update rows with variable-length data types), you can end up with a lot of wasted space in your data file (s), similar to filesystem fragmentation.
If you're not using the innodb_file_per_table option, the only thing you can do about it is export and import the database, a time-and-disk-intensive procedure.
But if you are using innodb_file_per_table, you can identify and reclaim this space!
Prior to 5.1.21, the free space counter is available from the table_comment column of information_schema.tables. Here is some SQL to identify tables with at least 100 M (actually 97.65 M) of free space:
SELECT table_schema, table_name, table_comment FROM information_schema.tables WHERE engine LIKE 'innodb' AND table_comment RLIKE 'innodb free: ([0-9] {6 ,}).*';
Starting with 5.1.21, this was moved to the data_free column (a much more appropriate place ):
SELECT table_schema, table_name, data_free/1024/1024 AS data_free_MB FROM information_schema.tables WHERE engine LIKE 'innodb' AND data_free> 100*1024*1024;
You can reclaim the lost space by rebuilding the table. The best way to do this is using 'alter table' without actually changing anything:
Alter table foo ENGINE = InnoDB;
This is what MySQL does behind the scenes if you run 'optimize table' on an InnoDB table. it will result in a read lock, but not a full table lock. how long it takes is completely dependent on the amount of data in the table (but not the size of the data file ). if you have a table with a high volume of deletes or updates, you may want to run this monthly, or even weekly.
The statement is as follows:
SELECT table_schema, table_name, data_free/1024/1024 AS data_free_MB FROM information_schema.tables WHERE engine LIKE 'innodb' AND data_free> 100*1024*1024;
Two data records are returned. The two tables have data_free greater than 100 M, so alter table tablename ENGINE = InnoDB;
It is equivalent to recreating the table engine. The execution speed is normal.
Here we will mention that if the table data size and index size of a table do not match the actual table data, we also need to clear the table fragments.
Http://blog.csdn.net/u011575570/article/details/48092469 in this blog
Conclusion. You are welcome to point out anything inappropriate. Thank you.
Reference: http://pengbotao.cn/mysql-suipian-youhua.html

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.