Mysql – Data impact on analyzing thesql table stats

MySQLstatistics

I have a MySQL database that appears to be getting slower over time. After some googling, I determine that the database probably needs to be analyzed. This is because innodb_stats_on_metadata is set as OFF, and I am not aware of any regularly scheduled analysis job running.

Here are a few of my problems:

  1. How do I know for sure this db needs to be analyzed? It seems that MySQL doesn't store when the last time "stat" was updated, and this DB didn't have logging turned on.
  2. This DB constantly have inserts/updates ongoing. I know during the analysis, nothing can write to the DB. What will happen to those write requests? will they get queued, or they will be thrown away?
  3. Will mysqlcheck have any chance of breaking the DB? if so, how do I recover the data? This is a production database that I can't risk breaking…

Any help would be appreciated.

Best Answer

If you are using the InnoDB engine for your tables #1 is relevant. If you are using MyISAM, #3 is relevant. But...

You should "never" have to run ANALYZE TABLE. (There are exceptions, but they are so rare, that I will assume that the problem is elsewhere.)

Possible cause #1: Your data has grown to be bigger than the cache (buffer_pool). How big is the data? What is the value of innodb_buffer_pool_size? Are you using InnoDB? How much RAM?

Possible cause #2: Some queries have less than perfect indexes. Set long_query_time=1; turn on the slowlog; wait a day; look in the slowlog to see what is slowest; come back for advice.