3

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.

2 Answers 2

1

If you disable innodb_stats_on_metadata, InnoDB still updates the stats periodically:

  • On first opening the table after a MySQL Server restart.
  • When the table changes 1/16 of the rows.
  • When the table performs over 2 billion row updates.
  • When a client executes the SQL statement ANALYZE TABLE <tablename>.

In addition, if InnoDB persistent stats are enabled (they are enabled by default), the stats are updated after at least 10% of the rows in the table are updated. So InnoDB keeps a running count of row updates, and if this is greater than 10% of the size of the table, it triggers a stats update. However, there's a bug that the counter itself is not persistent, so if your mysqld restarts, it resets to 0.

This was an issue in my company, for tables that are very large (hundreds of millions of rows). There's a significant likelihood that the mysqld instance will be restarted for upgrades or configuration changes before 10% of the rows are updated. So basically the stats are never recalculated!

We solved this by running a service on a schedule once a day that executes ANALYZE TABLE on a random subset of tables. So each table gets re-analyzed once per ten days on average. That's pretty low-impact and it is sufficient to keep the stats up to date, at least well enough for the query optimizer to make good choices.

0

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.

2
  • Why should I never run ANALYZE TABLE? I thought if innodb_stats_on_metadata is set to OFF, I am supposed to analyze the tables regularly? And Yes I am using InnoDB. Jan 28, 2016 at 12:46
  • I think the 10% rule still kicks in -- after the table has grown by 10%, the stats will be recalculated.
    – Rick James
    Jan 28, 2016 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.