DanBrother的部落格

check_table_fragmentation.sql
>>
MySQL > source check_table_fragmentation.sql
SELECT table_schema, table_name, CONCAT(ROUND((data_free / 1024 / 1024),2),'MB') as data_free
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema','sys')
AND ENGINE LIKE 'InnoDB' AND data_free > 100 * 1024 * 1024;

+------------------+-----------------------------------+------------+
| table_schema     | table_name                        | data_free  |
+------------------+-----------------------------------+------------+
| test_usr        | test_frag_table                        | 15002.00MB |


# Performance Table Defragmentation Using Percona's toolket - pt-online-schema-change

nohup  pt-online-schema-change -u$dbuser  --socket=$socket_file --critical-load="Threads_running=100" \
--set-vars innodb_lock_wait_timeout=60 \
--execute --alter "ENGINE=InnoDB;" --charset=utf8 --nocheck-replication-filters --recursion-method=processlist D=test_usr,t=test_frag_table 2>&1 &