13

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.

David M
  • 4,325
  • 2
  • 28
  • 40

5 Answers5

17

If you're not really interested when the database was changed, but want to know wether or not one database table was changed you should look into MySQL CHECKSUM TABLE

Hope this helps.

titel
  • 3,454
  • 9
  • 45
  • 54
  • Slow... 20k rows it takes 0.3 seconds, just awful – Martin Zvarík Feb 13 '19 at 22:00
  • Possibly helpful, but if you just want to check table activity and the table is being updated in a way that doesn't change the rows, the checksum will be the same. Example, the table is usually (but not always) empty, the checksum will usually be 0. – lreeder Dec 07 '21 at 22:04
12

This is MySQL bug 14374, 15438, and underlying InnoDB bug 2681.

I have two suggestions (other than patching MySQL).

  1. If you're using one table per file (innodb_file_per_table), stat the underlying file. You could write a MySQL function/extension to do this. This may lag slightly, due to database caching.
  2. You can use after update, delete, and insert triggers to keep your own metadata table with the last update times for each table you're concerned with.

I'd personally suggest the second, as its much more portable and doesn't depend on implementation details (such as innodb_file_per_table).

derobert
  • 49,731
  • 15
  • 94
  • 124
  • 2
    Keep in mind if going the 2.nd route about InnoDB triggers: "Note Currently, cascaded foreign key actions do not activate triggers." (from [13.2.2.5. FOREIGN KEY Constraints](http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html)) – Peter V. Mørch Nov 25 '11 at 16:19
1

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
lreeder
  • 12,047
  • 2
  • 56
  • 65
Pamungkas Jayuda
  • 1,194
  • 2
  • 13
  • 31
0

I would suggest adding another column to the table and let MySQL keep track of when the table was last modified, something like this:

ADD COLUMN `last_update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
crmpicco
  • 16,605
  • 26
  • 134
  • 210
  • This only shows when a specific row was updated. While you could do a "SELECT MAX(last_update) FROM table", this could add a huge amount of overhead for a table with a large number of rows just to get the most recently modified time – Matthew Kolb Feb 10 '14 at 23:25
  • and as noted in the question, doesnt help to track deleted rows. – barryhunter Jan 28 '22 at 15:55
0

Expanding on the answer provided by @Pamungkas-Jayuda,

ensure that SHOW VARIABLES like 'innodb_stats_persistent'; is ON. Then query the mysql.innodb_table_stats. You can use the following query to see the latest update of each table in each database:

SELECT database_name,table_name,last_update FROM
  mysql.innodb_table_stats a,
  (SELECT database_name AS db_last_update_name,
       max(last_update) AS db_last_update 
   FROM mysql.innodb_table_stats 
   WHERE database_name not in ( "mysql","sys")
   GROUP BY database_name )  AS b 
WHERE a.database_name = b.db_last_update_name 
  AND a.last_update = b.db_last_update ;

Output should look like:

+---------------+-----------------+---------------------+
| database_name | table_name      | last_update         |
+---------------+-----------------+---------------------+
| somedatabase1 | gg_assets_asset | 2022-09-22 17:46:38 |
| otherdatabase | yy_bundleinfo   | 2020-09-30 16:23:24 |
+---------------+-----------------+---------------------+
Otheus
  • 785
  • 10
  • 18