create table ddl executed too slow when using tidb as the downstream sink

[3AceShowHand]

What did you do?

run create table ddl on the upstream TiDB.

What did you expect to see?

TiCDC receive and execute ddl should be very quick

What did you see instead?

The second log is printed around 23 seconds later after the first one printed.

The root cause is that following query too slow, it may takes more than 20 seconds.

SELECT JOB_ID, JOB_TYPE, SCHEMA_STATE, SCHEMA_ID, TABLE_ID, STATE, QUERY
FROM information_schema.ddl_jobs
WHERE DB_NAME = "%s" 
    AND TABLE_NAME = "%s"
    AND JOB_TYPE LIKE "add index%%"
    AND (STATE = "running" OR STATE = "queueing")
LIMIT 1;

[2024/08/23 18:19:31.924 +08:00] [INFO] [async_ddl.go:165] ["async ddl query success"] [table=test.spmi_transfer_bill206] [duration=23.31010039s]

Versions of the cluster

Upstream TiDB cluster version (execute SELECT tidb_version(); in a MySQL client):

(paste TiDB cluster version here)

Upstream TiKV version (execute tikv-server --version):

(paste TiKV version here)

TiCDC version (execute cdc version):

(paste TiCDC version here)

[3AceShowHand]

This is introduced by #11476

[lance6716]

Maybe the root cause is pingcap/tidb#52905

We are improving the implementation of I_S tables, you can open a feature request to our team if there's no workaround.