5

I'm interested in this for InnoDB mostly, but also for MyISAM.

I looked around in information_schema and did not see this data anywhere.

I'm using MySQL 5.5.16.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Ike Walker
  • 754
  • 1
  • 6
  • 15

1 Answers1

3

There is no immediate control data to tell you that, but there are a few mechanisms you can setup.

MECHANISM #1

If you have binary logging enabled, simply do a grep -i "analyze table" against all the binary logs using the output from mysqlbinlog.

MECHANISM #2

If you have the general log enabled, simply do a grep -i "analyze table" against the general log file and locate the timestamp just about the command.

MECHANISM #3

You should schedule a cronjob that runs ANALYZE TABLE against all tables that have high-write, high-update, high-delete volume. That way, there is no guess work.

MECHANISM #4

Try setting innodb_stats_on_metadata to have a measure of predictability as to when an InnoDB table needs ANALYZE TABLE. (See my Mar 26, 2012 post : When are InnoDB table index statistics updated? )

CAVEAT

In the past, I have often stated that running ANALYZE TABLE table against InnoDB is useless.

Hopefully, MECHANISM #4 is probably what you need.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Thanks for the detailed response. I set sql_log_bin=0 when I run analyze to prevent it from blocking other replicated statements, so I can't do #1. And I don't typically use the general log, so #2 is out. I already run analyze on a scheduled basis a la #3, I'm just looking for a way to avoid running it if it's been run recently. – Ike Walker Jan 04 '13 at 20:05
  • As for #4, I disable innodb_stats_on_metadata in order to improve the performance of information_schema. – Ike Walker Jan 04 '13 at 20:05
  • My conjecture: The table information_schema.STATISTICS could possibly be more stabilized by innodb_stats_on_metadata. – RolandoMySQLDBA Jan 04 '13 at 21:00
  • Yeah, I'm just not willing to take the performance hit of using innodb_stats_on_metadata. I'll accept your answer now as it's clear that there is no easy way to get this data form MySQL. – Ike Walker Jan 04 '13 at 22:22
  • 2
    I just ran an ANALYZE TABLE on an InnoDB table and it boosted performance tremendously, so I disagree with you - definitely not useless in all cases. – Shane N Mar 05 '15 at 17:46
  • @ShaneN I am glad things improved for you. Most people don't run ANALYZE TABLE on a regular basis. Most will run it once, let hundreds of thousands of INSERTs, UPDATEs, and DELETEs go by, and forget to run ANALYZE TABLE again. This will make the MySQL Query Optimizer take bad guesses on EXPLAIN plans. If you crontab your ANALYZE TABLE, then it is useful (as I stated in MECHANISM #3). – RolandoMySQLDBA Mar 05 '15 at 17:51
  • Thanks for the clarification. In this case, we do have a table with high writes and deletes. Other than monitoring query performance, how do I know when I need to run ANALYZE? I don't want to run it if we don't need to since it locks table while computing. – Shane N Mar 05 '15 at 19:14
  • @ShaneN There is not concrete method to enumerate when. Basically, you have three(3) ways to know when: 1) The performance of your SELECTs go from hero to zero and needs another boost, 2) Your have heavy INSERTs, DELETEs, and UPDATEs that come the same time everyday or every week (such as regular traffic spikes), 3) some overnight ETL jobs. – RolandoMySQLDBA Mar 05 '15 at 22:09