# TiCDC Syncpoint Feature Documentation​ - Part II ## Continually Checksumming TiDB Source/Replica Work in progress! > Author: Morgan Tocker ## Motivations When using replication between database systems it is possible that the data could drift over time. This problem is not new (in MySQL it is common to run `pt-table-checksum` in production) and the exact causes of data drift can vary between bugs and user errors. With TiCDC being less mature, replica drift (a polite way of saying data corruption) is particularly risky. And indeed a quick check of the TiDB 5.4 release notes shows that there are 3 data corruption bugs fixed in the next patch version, making consistency checking a blocking issue for deployment. Further, it looks like TiCDC does not seem to stop if it can’t replicate certain tables. By default it ignores tables without primary keys. This behavior is somewhat surprising. Neither MySQL nor TiDB (TiCDC) support built-in replica consistency checking, but with MySQL the third party tools are at least well established. We need to establish which tool to use for TiDB. ## Goals - Identify a TiDB to TiDB optimized consistency checking tool - Continuously check source/replica consistency in a production environment with low impact (aim: >2TB can safely be checked per hour, or ~50TB/day) - Low impact = non blocking. We should optimistically check for consistency and retry chunks (with a small backoff) until they match, or a threshold is exceeded. ## Non-Goals - MySQL to TiDB consistency checking (for this we already have cloner). - In order to achieve high throughput consistency checking, we need to make sure the generated queries for consistency checking specifically target the TiDB Optimizer, TiKV coprocessor and TiDB coprocessor cache. - We are not looking to create a new tool. We will patch existing tools where we can to support an ‘optimized TiDB to TiDB’ algorithm. ## Investigated Options ### ADMIN CHECKSUM TABLE This is a built-in command in TiDB which is used by TiDB-Lightning (fast import tool) to verify that data is consistent after bulk importing. Good: - Has coprocessor support - Supports tidb_snapshot, so an online comparison can be made without blocking Bad: - Checks both clustered index and secondary keys (we don’t require secondary key support for this use case) - Aligning the tidb_snapshot between source and target looks to be difficult without modifying TiCDC. I.e. the TSO used on the replica is not preserved from the source, and it uses eventual consistency. The order of changes needs to be exactly the same as the source for the checksum to pass. > Conclusion: Rejected for now. ### Pt-table-checksum Good: - Long history of usage Bad: - Written in Perl. Don’t want to get in the business of modifying it to optimize for TiDB. - Written around SBR. We want to operate slightly differently to the original intention with retrying chunks until consistency passes. > Conclusion: Great inspiration for usage, but must be rejected. ### Cloner Good: - Developed by Jon Tirsen at Square/Block. - Retrying of checksums overlaps with the intended workflow of continually checksumming. Bad: - Might not cover as wide of a range of scenarios as more established tools (I ran into this issue while testing a clone of a tpcc benchmark replicated). - Also doesn’t cover the case that replica doesn’t yet have a table which exists on source (i.e. the table might be on its way but delayed by replica lag). - Needs some customization to be TiDB efficient. Some of the basic work is done in the ‘--use-crc32-checksum’ checksum mode. > Conclusion: front runner based on being the only tool which retries checksums continually until a match. ### Sync-diff-inspector This is the option that was suggested by PingCAP in [Support case #754](https://support.pingcap.com/hc/en-us/requests/754). Good: - Supported by PingCAP - Generates co-processor efficient statements (verified) - Can generate a SQL file to repair inconsistencies (not a requirement for now, but nice touch) Bad: - JSON/BINARY/BLOB/BIT “and other types of data” are not supported. Wait what? (See also [pingcap/tidb-tools #350](https://github.com/pingcap/tidb-tools/issues/350) ) - Also doesn’t cover the case that replica doesn’t yet have a table which exists on source (might be on its way but delayed by replication delay). - Configuration file/usage seems really weird, but it is workable. - It doesn’t support the workflow of continuously checking TiCDC, and retrying chunks until consistent. See [DM issue #1097](https://github.com/pingcap/dm/issues/1097). - This makes it no better than ADMIN CHECKSUM TABLE when comparing TiDB to TiDB. i.e. Currently sync_diff_inspector requires a tidb_snapshot which won’t work due to eventual consistency. > Conclusion: I would love to use this tool, but not supporting continuous checking is a deal breaker. We can’t pause clusters for it. The statements it generates use the following form: ```{.sql .copyable} tidb> explain analyze SELECT COUNT(*) as CNT, BIT_XOR(CAST(CRC32(CONCAT_WS(',', `id`, `b`, `c`, `d`, CONCAT(ISNULL(`id`), ISNULL(`b`), ISNULL(`c`), ISNULL(`d`))))AS UNSIGNED)) as CHECKSUM FROM `checkdb`.`t1` WHERE ((((`id` > '270741')) AND ((`id` <= '322481'))) AND (TRUE)); ``` ```bash +-----------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +-----------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+ | StreamAgg_17 | 1.00 | 1 | root | | time:607.8µs, loops:2 | funcs:count(Column#9)->Column#5, funcs:bit_xor(Column#10)->Column#6 | 776 Bytes | N/A | | └─TableReader_18 | 1.00 | 3 | root | | time:602.6µs, loops:2, cop_task: {num: 3, max: 514µs, min: 479.8µs, avg: 498.4µs, p95: 514µs, rpc_num: 3, rpc_time: 1.41ms, copr_cache_hit_ratio: 1.00} | data:StreamAgg_9 | 548 Bytes | N/A | | └─StreamAgg_9 | 1.00 | 3 | cop[tikv] | | tikv_task:{proc max:92ms, min:32ms, p80:92ms, p95:92ms, iters:53, tasks:3} | funcs:count(1)->Column#9, funcs:bit_xor(cast(crc32(concat_ws(",", cast(checkdb.t1.id, var_string(20)), checkdb.t1.b, checkdb.t1.c, checkdb.t1.d, concat("0", cast(isnull(checkdb.t1.b), var_string(20)), cast(isnull(checkdb.t1.c), var_string(20)), cast(isnull(checkdb.t1.d), var_string(20))))), bigint(22) UNSIGNED BINARY))->Column#10 | N/A | N/A | | └─TableRangeScan_16 | 75597.65 | 51740 | cop[tikv] | table:t1 | tikv_task:{proc max:52ms, min:12ms, p80:52ms, p95:52ms, iters:53, tasks:3} | range:(270741,322481], keep order:false | N/A | N/A | +-----------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+ 4 rows in set (0.00 sec) ``` This statement looks great! Note how this works: It contacts the fields, then concats nullability for the fields making it null safe. The crc32 is only called once on the concat’ed result (I think this handling might be better than cloner). This is similar to `pt-table-checksum`. I don’t understand why the limitation on certain data types. Once we figure out retrying, it will be efficient with the coprocessor cache. ### Proposed Solution PingCAP advised of an experimental feature which will be released next month. It is available in 6.1.0 so I was able to verify it works as expected. Here are my tests: ```{.bash .copyable} tiup playground --pd 1 --kv 3 --db 1 --ticdc 1 --tiflash 0 tiup cdc cli changefeed create --pd="http://127.0.0.1:2379" --sink-uri="mysql://root@192.168.86.38:4000/" --changefeed-id="simple-replication-task" --sort-engine="unified" --sync-interval=“10m0s” --sync-point tiup bench tpcc prepare --warehouses 500 ``` ```bash /* wait 10+ minutes */ ``` On the replica: ```{.sql .copyable} mysql> select * from tidb_cdc.syncpoint_v1 order by primary_ts desc LIMIT 1; +---------------------------------+--------------------+--------------------+ | cf | primary_ts | secondary_ts | +---------------------------------+--------------------+--------------------+ | default_simple-replication-task | 434584588129402880 | 434584589215465473 | +---------------------------------+--------------------+--------------------+ 1 row in set (0.00 sec) ``` These TS values can be inserted into sync_diff_inspector’s config: ```{.bash .copyavle} cat sync_diff_inspector/config/config.toml ``` ```bash # Diff Configuration. ######################### Global config ######################### # how many goroutines are created to check data check-thread-count = 4 # set false if just want compare data by checksum, will skip select data when checksum is not equal. # set true if want compare all different rows, will slow down the total compare time. export-fix-sql = true # ignore check table's data check-struct-only = false ######################### Databases config ######################### [data-sources] [data-sources.mysql1] host = "127.0.0.1" port = 4000 user = "root" password = "" snapshot = "434584588129402880" # mysql doesn't has snapshot config [data-sources.tidb0] host = "192.168.86.38" port = 4000 user = "root" password = "" # remove comment if use tidb's snapshot data # snapshot = "2016-10-08 16:45:26" # snapshot = "386902609362944000" snapshot = "434584589215465473" ######################### Task config ######################### # Required [task] # 1 fix sql: fix-target-TIDB1.sql # 2 log: sync-diff.log # 3 summary: summary.txt # 4 checkpoint: a dir output-dir = "/tmp/output/config" source-instances = ["mysql1"] target-instance = "tidb0" # tables need to check. *Include `schema` and `table`. Use `.` to split* target-check-tables = ["test.stock"] # extra table config target-configs= ["config1"] # Optional [table-configs] [table-configs.config1] # tables need to use this specified config. # if use this config. target-tables should be a subset of #target-check-tables target-tables = ["schema*.table*", "test2.t2"] range = "age > 10 AND age < 20" index-fields = [""] ignore-columns = ["",""] chunk-size = 0 collation = "" ``` The sync_diff can then be run: ```{.bash .copyable} rm -rf /tmp/output/config && ./bin/sync_diff_inspector --config sync_diff_inspector/config/config.toml ``` ```bash A total of 1 tables need to be compared Comparing the table structure of ``test`.`stock`` ... equivalent Comparing the table data of ``test`.`stock`` ... equivalent _____________________________________________________________________________ Progress [============================================================>] 100% 0/0 A total of 1 table have been compared and all are equal. You can view the comparision details through '/tmp/output/config/sync_diff.log' ``` This outputs a summary file such as the following: ```bash Summary Source Database host = "127.0.0.1" port = 4000 user = "root" snapshot = "434584588129402880" Target Databases host = "192.168.86.38" port = 4000 user = "root" snapshot = "434584589215465473" Comparison Result The table structure and data in following tables are equivalent +----------------+----------+-----------+ | TABLE | UPCOUNT | DOWNCOUNT | +----------------+----------+-----------+ | `test`.`stock` | 23519007 | 23519007 | +----------------+----------+-----------+ Time Cost: 20.516066125s Average Speed: 381.021569MB/s ``` This is 1.24TB/hr, which is in the ballpark of Square/Block expectations (2TB/hr). This test was performed on 2 desktops which hosted all TiKV/TiDB nodes. So performance does not appear to be an issue. ### Further reading [TiCDC Syncpoint Feature Documentation​ - Part I](https://support.pingcap.com/hc/en-us/articles/7506296966551)