MySQL – Fastest Way to Check if InnoDB Table Has Changed

innodbMySQLperformance

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance.

My application does about 500-1000 CHECKSUM TABLE statements per second in peak times, because the clients GUI is polling the database constantly for changes (it is a monitoring system, so must be very responsive and fast).

With MyISAM, there are live checksums that are precalculated on table modification and are VERY fast. However, there is no such thing in InnoDB. So, CHECKSUM TABLE is VERY slow.

I've hoped to be able to check the last update time of the table, Unfortunately, this is not available in InnoDB either. I'm stuck now, because tests have shown that the performance of the application drops drastically.

There are simply too many lines of code that update the tables, so implementing logic in the application to log table changes is out of the question.

Is there any fast method to detect changes in InnoDB tables?

Best Answer

For the table mydb.mytable, run this query:

SELECT update_time
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';

If you want to know what tables have changed in the last 5 minutes, run this:

SELECT table_schema,table_name,update_time
FROM information_schema.tables
WHERE update_time > (NOW() - INTERVAL 5 MINUTE);

Give it a Try !!!

UPDATE 2011-12-21 20:04 EDT

My employer (DB/Wweb hosting comany) has a client with 112,000 InnoDB tables. It is very difficult to read INFORMATION_SCHEMA.TABLES during peak hours. I have an alternate suggestion:

If you have innodb_file_per_table enabled and all the InnoDB tables are stored in .ibd files, there is a way to ascertain the time of the last update (up to the minute).

For the table mydb.mytable, do the following in the operating system:

$ cd /var/lib/mysql/mydb
$ ls -l mytable.ibd | awk '{print $4,$5}'

This timestamp is from the OS. You can't go wrong on this one.

UPDATE 2011-12-21 22:04 EDT [mysqld] innodb_max_dirty_pages_pct=0;

Add this to my.cnf, restart mysql, and all InnoDB tables will experience fast flushes from the buffer pool.

To avoid restarting, just run

mysql> SET GLOBAL innodb_max_dirty_pages_pct=0;

UPDATE 2013-06-27 07:15 EDT

When it comes to retrieving the date and time for a file, ls has the --time-style option:

$ cd /var/lib/mysql/mydb
$ ls -l --time-style="+%s" mytable.ibd | awk '{print $6}'

You can compare the timestamp of the file against UNIX_TIMESTAMP(NOW()).

Related Question