Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I determine when an InnoDB table was last changed?

Tags:

mysql

innodb

I've had success in the past storing the (heavily) processed results of a database query in memcached, using the last update time of the underlying tables(s) as part of the cache key. For MyISAM tables, that last changed time is available in SHOW TABLE STATUS. Unfortunately, that's usually NULL for InnoDB tables.

In MySQL 4.1, the ctime for an InnoDB in its SHOW TABLE STATUS line was usually its actual last update time, but that doesn't seem to be true for MySQL 5.1.

There is a DATETIME field in the table, but it only shows when a row has been modified - it cannot show the deletion time of a row that's not there anymore! So, I really cannot use MAX(update_time).

Here's the really tricky part. I have a number of replicas that I do reads from. Can I figure out the state of the table that doesn't rely on when the changes have actually been applied?

My conclusion after working on this for a while is that it's not going to be possible to get this information as cheaply as I'd like. I'm probably going to cache data until the time that I expect the table to change (it's updated once a day), and let the query cache help out where it can.

like image 516
David M Avatar asked May 07 '10 00:05

David M


1 Answers

I Have solution from documentation mysql https://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html There step to make sure stats innodb persistent active :

  1. Check innodb stats persistent setting with
SHOW VARIABLES like 'innodb_stats_persistent';

# Result should be ON.
  1. Check Last edited table from mysql.innodb_table_stats
SELECT *
FROM mysql.innodb_table_stats
like image 171
Pamungkas Jayuda Avatar answered Sep 17 '22 13:09

Pamungkas Jayuda