Detectar (e corrigir) fragmentação no MySQL

Prefiro chamar este tipo de Fragmentação simplesmente de “espaço livre”, que possui as seguintes características:
– Não é um problema de desempenho. É um problema administrativo, de espaço;
– Se você vai utilizar este espaço com futuros INSERTs, isto não é um problema;
– Se você não vai utilizar este espaço com futuros INSERTs, isto é um problema se você precisa de espaço;
– Se você vai utilizar este espaço com futuros INSERTs, mas está precisando de espaço agora, a saída é executar o OPTIMIZE (que em tabelas InnoDB, faz a recriação delas, e também executa o ANALYZE).

Este script utiliza as estatísticas da tabelas (armazenadas no banco information_schema) para verificar as que estão com mais de 10% de espaço livre.

mysql> SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND DATA_FREE*100/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE) > 10 AND NOT ENGINE='MEMORY';
+-----------------------------------------------+------------+
| CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME) | DATA_FREE  |
+-----------------------------------------------+------------+
| zabbix.alerts                                 |  404750336 |
| zabbix.events                                 |  181403648 |
| zabbix.history_log                            | 1111490560 |
| zabbix.history_text                           | 6597640192 |
+-----------------------------------------------+------------+
4 rows in set (0.20 sec)

mysql> OPTIMIZE TABLE zabbix.alerts;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| zabbix.alerts | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.alerts | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 30.71 sec)

mysql> OPTIMIZE TABLE zabbix.events;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| zabbix.events | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.events | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 3.69 sec)

mysql> OPTIMIZE TABLE zabbix.history_log;
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table              | Op       | Msg_type | Msg_text                                                          |
+--------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history_log | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history_log | optimize | status   | OK                                                                |
+--------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (37.77 sec)

mysql> OPTIMIZE TABLE zabbix.history_text;
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table               | Op       | Msg_type | Msg_text                                                          |
+---------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history_text | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history_text | optimize | status   | OK                                                                |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (27 min 34.07 sec)

mysql> SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND DATA_FREE*100/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE) > 10 AND NOT ENGINE='MEMORY';
Empty set (0.11 sec)

mysql>

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

This site uses Akismet to reduce spam. Learn how your comment data is processed.