Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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 rep -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 tabale 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.

share|improve this answer
    
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 '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 4 '13 at 20:05
    
My conjecture: The table information_schema.STATISTICS could possibly be more stabilized by ` innodb_stats_on_metadata`. –  RolandoMySQLDBA Jan 4 '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 4 '13 at 22:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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