4

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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
8
  • 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 4, 2013 at 20:05
  • As for #4, I disable innodb_stats_on_metadata in order to improve the performance of information_schema.
    – Ike Walker
    Jan 4, 2013 at 20:05
  • My conjecture: The table information_schema.STATISTICS could possibly be more stabilized by ` innodb_stats_on_metadata`.
    – RolandoMySQLDBA
    Jan 4, 2013 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 4, 2013 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 5, 2015 at 17:46

Your Answer

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

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