Innodb needs an instant checksum.
Posted by:
yun chen
Date: February 21, 2017 01:28AM
Our business has a heavy use of mysql, from master-slave, HA group , to self sharded-routed mysql cluster, mysql group replication(dev and test). An eager requirement is in shrink, scale or rebalance, redo cases, how can we make a quick check about data consistency.
The original "checksum table xxx" is full-table and very slow.
So I am wondering the possibility of some thing like 'checksum table xxx instant'.
Below is an immature idea:
<0> some conf item like 'instant-checksum = true' in my.cnf. requirements: gtid, innodb.
<1> first mysql startup phase:
a new table mysql.checksums (id, table_name, schema_name, gtid_set, value, created_at, updated_at)
fill mysql.checksums with 'checksum table xxx' concurrently, it may be very slow.
<2> transaction goes on phase:
DDL case:
create table: insert mysql.checksums
drop table: delete mysql.checksum or set value to NULL
alter table column: update value with 'checksum table xxx', it may be slow.
alter table column type/column name, alter table character set: may be no need to do anything.
DML case:
insert: add row-crc of this row without carry.
update: add after-row-crc and decrease before-row-crc without carry
delete: decrease before-row-crc without carry.
I think there will be a performance decrease , but it's acceptable in my case, at least.
I also think this feature is very useful to make new feature like 'group replication' popular, for people can check consistency without pain.