Fastest way to check if InnoDB table has changed

Question

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?

Answer 1

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()).

Answer 2

I think I've found the solution. For some time I was looking at Percona Server to replace my MySQL servers, and now i think there is a good reason for this.

Percona server introduces many new INFORMATION_SCHEMA tables like INNODB_TABLE_STATS, which isn't available in standard MySQL server. When you do:

SELECT rows, modified FROM information_schema.innodb_table_stats WHERE table_schema='db' AND table_name='table'

You get actual row count and a counter. The Official documentation says the following about this field:

If the value of modified column exceeds “rows / 16” or 2000000000, the statistics recalculation is done when innodb_stats_auto_update == 1. We can estimate the oldness of the statistics by this value.

So this counter wraps every once in a while, but you can make a checksum of the number of rows and the counter, and then with every modification of the table you get a unique checksum. E.g.:

SELECT MD5(CONCAT(rows,'_',modified)) AS checksum FROM information_schema.innodb_table_stats WHERE table_schema='db' AND table_name='table';

I was going do upgrade my servers to Percona server anyway so this bounding is not an issue for me. Managing hundreds of triggers and adding fields to tables is a major pain for this application, because it's very late in development.

This is the PHP function I've come up with to make sure that tables can be checksummed whatever engine and server is used:

function checksum_table($input_tables){
    if(!$input_tables) return false; // Sanity check
    $tables = (is_array($input_tables)) ? $input_tables : array($input_tables); // Make $tables always an array
    $where = "";
    $checksum = "";
    $found_tables = array();
    $tables_indexed = array();
    foreach($tables as $table_name){
        $tables_indexed[$table_name] = true; // Indexed array for faster searching
        if(strstr($table_name,".")){ // If we are passing db.table_name
            $table_name_split = explode(".",$table_name);
            $where .= "(table_schema='".$table_name_split[0]."' AND table_name='".$table_name_split[1]."') OR ";
        }else{
            $where .= "(table_schema=DATABASE() AND table_name='".$table_name."') OR ";
        }
    }
    if($where != ""){ // Sanity check
        $where = substr($where,0,-4); // Remove the last "OR"
        $get_chksum = mysql_query("SELECT table_schema, table_name, rows, modified FROM information_schema.innodb_table_stats WHERE ".$where);
        while($row = mysql_fetch_assoc($get_chksum)){
            if($tables_indexed[$row[table_name]]){ // Not entirely foolproof, but saves some queries like "SELECT DATABASE()" to find out the current database
                $found_tables[$row[table_name]] = true;
            }elseif($tables_indexed[$row[table_schema].".".$row[table_name]]){
                $found_tables[$row[table_schema].".".$row[table_name]] = true;
            }
            $checksum .= "_".$row[rows]."_".$row[modified]."_";
        }
    }

    foreach($tables as $table_name){
        if(!$found_tables[$table_name]){ // Table is not found in information_schema.innodb_table_stats (Probably not InnoDB table or not using Percona Server)
            $get_chksum = mysql_query("CHECKSUM TABLE ".$table_name); // Checksuming the old-fashioned way
            $chksum = mysql_fetch_assoc($get_chksum);
            $checksum .= "_".$chksum[Checksum]."_";
        }
    }

    $checksum = sprintf("%s",crc32($checksum)); // Using crc32 because it's faster than md5(). Must be returned as string to prevent PHPs signed integer problems.

    return $checksum;
}

You can use it like this:

// checksum a signle table in the current db
$checksum = checksum_table("test_table");

// checksum a signle table in db other than the current
$checksum = checksum_table("other_db.test_table");

// checksum multiple tables at once. It's faster when using Percona server, because all tables are checksummed via one select.
$checksum = checksum_table(array("test_table, "other_db.test_table")); 

I hope this saves some trouble to other people having the same problem.

Answer 3

You should update to Mysql v5.6+ at that version innodb also has support for checksum table. http://dev.mysql.com/doc/refman/5.6/en/checksum-table.html

other then that, the ideal solution would be if your client wasn't polling for result's constantly, but instead you where pushing new and changed data when and if it was available. It would be faster and less load would be on the server. if you are using web based gui, you should look into APE http://ape-project.org/ or other similar projects.

Answer 4

If you're mostly adding to a table, you can hook on AUTO_INCREMENT as a measure of updatetness.

SELECT `AUTO_INCREMENT` FROM `information_schema`.`tables` 
WHERE `table_schema` = DATABASE() AND `table_name` = 'YOUR_TABLE';

But I'd prefer to refer to an otside source like a counter in Memcached which you will increment every time you change something in the database.

Answer 5

You could try to do the following:

SELECT rows_changed
FROM information_schema.table_statistics
WHERE table_schema = 'mydb' AND table_name='mytable';

This returns a number that increases with each table update, keeping track of it will allow to to detect change.

Important note: the value is changed immediately after an UPDATE, not after COMMIT. So you may not see the changes if the modifications were made inside another transaction that didn't finish.

Answer 6

This answer has nothing to do with mysql database versions or types, I wanted to know if update statements were making changes AND to do this in my php code..

1. Created a dummy table with one record and one field which I would query to get the value of mysql's current_timestamp.

2. To the data table being updated, added a timestamp field and used the mysql option "ON UPDATE CURRENT_TIMESTAMP"

3. Compared #1 and #2

This will not work 100% of the time but for my application it was a simple and great solution. Hope this helps somebody

Answer 7

In case you want to check if a view has changed, you can check the most recent change among the tables involved in the view:

select max(t.update_time) last_involved_table_update 
from view_table_usage vtu 
join tables t on (t.table_name=vtu.table_name and t.table_schema=vtu.table_schema)  
where vtu.view_name='name_of_view' and vtu.view_schema='schema_name_of_view';

Note that there may be some false positives, e.g. in case a column of a table(included in the view) has been updated without the column itself being a part of the view. In that case the view will show as updated in the above query.