Thanks for your reply sterin.
Unfortunately the application is very late in development and there is no single wrapper around database calls, so it will be very hard to search for all queries that modify table data and add this additional statement, otherwise the idea is good, and it will be fast, but as i said this will be a great amount of effort for now.
I am currently researching another solution, but I must do some extensive performance testing first. The solution is to have one persistent daemon, which will periodically do “CHECKSUM TABLE xxx” on all tables marked with “CHECKSUM=1” and will cache the checksums in a reference table like the one you proposed. I will report my progress, but any other ideas will be greatly appreciated.
UPDATE:
This method proved to be pretty heavy on the database. Here are some graphs from the tests (there are from an actual production server):
The spike in the load (it’s actually a gap on the MySQL graphs) at about 12:10 is because i accidentally spawned 100+ threads before I managed to kill the daemon :roll:.
In the first run I’ve forgot to add sleep() at the end of the test loop :eek: and went for lunch :roll: You can see what’s the impact on the database. The second run was with 3 second sleep between the test loops. The daemon works with 2 threads, and it’s doing an average (measured for 10 seconds) of 17 table checksums per second with sleep(3), and 30 without sleep. It’s finishing a loop in about 2 seconds. There are 70 tables to be checksummed with an average of 50 000 rows (max 670 000).
There’s an option here to use one slave for this, but this will make slaves critical for the application and this is not what i want right now…
So i’m still looking for other options.