TiCDC MySQL Sink Write Timeout on Downstream TiDB (Full-Table Scan Induced)

[asddongmen]

Description

In recent production environment deployments for multiple clients, we have identified a critical issue that leads to write timeout errors in TiCDC:

• When the downstream TiDB cluster does not have the ANALYZE statement executed on target tables, TiCDC may trigger full-table scans when writing DELETE or UPDATE statements to the MySQL sink.
• Full-table scans drastically increase the execution time of these SQL operations, which frequently exceeds the current default write timeout threshold and results in synchronization failures.

This issue serves as a documentation note for the TiCDC-to-TiDB write timeout problem, and outlines actionable solutions.

Solution

To address the timeout issue before the default configuration is adjusted, the following temporary solutions are available:

1. Execute ANALYZE on downstream TiDB tables
   Run the ANALYZE TABLE command on the affected downstream tables to update table statistics. This helps the TiDB optimizer generate efficient execution plans (e.g., index-based scans) instead of full-table scans, thus reducing SQL execution latency.
   Example:

   ANALYZE TABLE `target_db`.`target_table`;

2. Adjust the configuration of the TiCDC MySQL sink write timeout parameter to a higher value that can accommodate the extended execution time of SQL operations caused by full-table scans.
   Example:

   --sink-uri = mysql://127.0.0.1:3306/?write-timeout=30m

3. Optimize DELETE/UPDATE SQL statements to avoid full-table scans
   Refine the filtering conditions in TiCDC-generated DELETE/UPDATE statements to ensure they hit available indexes on downstream tables. For instance, add constraints on indexed fields (e.g., primary keys, unique keys) to eliminate unnecessary full-table traversals.

Impact

• Positive: Reduces timeout errors in production environments where downstream TiDB tables lack up-to-date statistics; improves TiCDC synchronization reliability and user experience.

Keywords

TiCDC, MySQL Sink, Write Timeout, TiDB, Full-Table Scan, ANALYZE