Domains
English
Cart
×
×

Knowledgebase & Downloads

The knowledgebase provides documentation written by our team. Please select a category or search for answers.

Articles

Back

How do I optimize all fragmented tables at once in MySQL?

First check for possible hints and solutions with mysqltuner.pl. There may be other settings to adjust.
If you get warning simlar to:
[!!] Total fragmented tables: ...
you should defragment the tables.

Enter mysql database with
mysql mysql
and run
SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";") FROM information_schema.tables WHERE DATA_FREE > 0 INTO OUTFILE '/tmp/optimize.sql'; SOURCE /tmp/optimize.sql;

Rerun mysqltuner.pl or a direct query
SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";") FROM information_schema.tables WHERE DATA_FREE > 0;
to see if the number of fragmented tables decreased.

For InnoDB tables you need to run
rm /tmp/optimize.sql
SELECT concat("ALTER TABLE ",table_schema,".",table_name," ENGINE=INNODB;") FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb' and DATA_FREE > 0 INTO OUTFILE '/tmp/optimize.sql'; SOURCE /tmp/optimize.sql;

Other option is to enable innodb_file_per_table in my.cnf. It would require dumping, deleting and restoring InnoDB tables though.

Related queries:
SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') GROUP BY ENGINE ORDER BY ENGINE ASC; 
SELECT DATA_FREE, DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0;


Powered by HostBill