1

After running mysqltuner on my Magento db it says me 

[!!] Total fragmented tables: 1203

In suggestion it says 

 Run OPTIMIZE TABLE to defragment tables for better performance

I just made a db backup and I was wondering which is the best way to defragment Magneto db.

I saw on some forums that i can't use OPTIMIZE option because is an InnoDB.

My question is:

1) Which is the best command to optimize Magento InnoDB ?

I can't do it one by one if they are 1203 tables. 2)Can I do optimization on all database?

user2478262
  • 59
  • 1
  • 9

1 Answers1

0

Bogus! That tool always complains about fragmented tables. Virtually all tables in the universe are "fragmented" to some level. Virtually no MySQL system suffers from fragmentation.

I have studied literally thousands of slow queries. Only two could be traced to fragmentation. The rest were solved by indexes, reformulation of queries, redesign of the schema, etc. Not defragmentation.

Most of the other output from that tool is reasonably good. Did anything else warrant a [!!]?

Is the system running slowly?

The best tool for diagnosing slow MySQL is the slowlog. Turn it on, set long_query_time to 2 (or less), wait a day, run pt-query-digest, show us the top 1 or 2 slow queries. With luck, we can give you a fix that will significantly speed up the system.

And if you want a more thorough analysis of the tunables, provide me with SHOW GLOBAL STATUS (after being up at least a day), SHOW VARIABLES and how much RAM you have.

Rick James
  • 135,179
  • 13
  • 127
  • 222