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.

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.

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.

Unfortunately, this is a performance killer. Checksum is made up by hashing all the rows one by one. From the docs: "This row-by-row calculation is what you get with the EXTENDED clause, with InnoDB and all other storage engines other than MyISAM, and with MyISAM tables not created with the CHECKSUM=1 clause" :-(
–
lserniJun 10 '14 at 8:14

Are you sure you can't go wrong w/ the idb moddate? A change could be just living in the buffer pool in memory and not gotten flushed to disk yet.
–
atxdbaDec 22 '11 at 2:28

1

Thanks for the answer, but as i said, update_time in information_schema.tables is NULL for InnoDB tables. Also i'm not sure that innodb_max_dirty_pages_pct=0 is a good idea, because it will sacrifice performance... I was thinking about a solution with triggers, to insert a random value at a reference table for each of the watched tables, but then i'll need 3 triggers per table only for this...
–
JacketDec 22 '11 at 6:08

Also selecting from information_schema.tables is kind of slow too... i takes about 300ms to check one table. For comparison doing a "CHECKSUM TABLE" on a MyISAM table with millions of rows with Live Checksum enabled is taking less than a milisecond.
–
JacketDec 22 '11 at 6:17

1

+1 for the file system check, so long as the buffer flushing is regular enough (roughly once per second is the default), then this time stamp will be pretty accurate, and probably good enough for most cases...
–
Dave RixDec 22 '11 at 21:31

1

Maybe it's OK for a local database, but i have multiple remote slaves, so this is not working...
–
JacketDec 27 '11 at 9:20

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:

// 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.