tidb ddl问题

🪐 TiDB 技术问题
1

【 TiDB 使用环境】生产环境 or 测试环境 or POC
【 TiDB 版本】
v4.0.14
【遇到的问题】
现象：
执行了add index 的 ddl（job id 1502）操作后。再执行其他的ddl就阻塞了。9:25分执行了ADMIN CANCEL DDL JOBS 1502;之后，其他的ddl马上就执行完了。
过程描述：
job 1502 的开始时间为2022-04-26 09:50:22 ，+8 后的北京时间应该是2022-04-26 17:50:22 ，执行ADMIN CANCEL DDL JOBS 1502;的时间是2022-04-26 21:25:00。这期间其他的ddl均不能执行（一执行就会卡住不动）。直到执行ADMIN CANCEL DDL JOBS 1502；后。其他阻塞的ddl一下都执行成功了。
问题：
一、 add index 不是在线的吗？为什么会出现阻塞的现象？
二、 ddl 的队列深度是多少，最多可以有多少ddl并发？

【复现路径】做过哪些操作出现的问题
【问题现象及影响】

【附件】

请提供各个组件的 version 信息，如 cdc/tikv，可通过执行 cdc version/tikv-server --version 获取。

MySQL [(none)]> admin show ddl jobs;
+--------+----------------+---------------------------+----------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------+
| JOB_ID | DB_NAME        | TABLE_NAME                | JOB_TYPE       | SCHEMA_STATE         | SCHEMA_ID | TABLE_ID | ROW_COUNT | START_TIME          | END_TIME            | STATE   |
+--------+----------------+---------------------------+----------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------+
|   1502 | aaaa_saas      | aaaa_outfit_skc           | add index      | write reorganization |       137 |     1210 | 328089828 | 2022-04-26 09:50:22 | NULL                | running |
|   1503 | aaaa_da_bbbb | bbbb_cccc       | add index      | none                 |       135 |     1131 |         0 | 2022-04-26 09:58:57 | NULL                | none    |
|   1504 | aaaa_da_bbbb | bbbb_cccc       | add index      | none                 |       135 |     1131 |         0 | 2022-04-26 10:25:32 | NULL                | none    |
|   1505 | aaaa_da_bbbb | bbbb_cccc       | add index      | none                 |       135 |     1131 |         0 | 2022-04-26 10:30:42 | NULL                | none    |
|   1506 | aaaa_da_bbbb | bbbb_cccc       | add index      | none                 |       135 |     1131 |         0 | 2022-04-26 10:38:14 | NULL                | none    |
|   1511 | aaaa_da_bbbb | bbbb_cccc_copy1 | add index      | none                 |       135 |     1507 |         0 | 2022-04-26 10:40:55 | NULL                | none    |
|   1512 | aaaa_da_bbbb | bbbb_cccc_copy1 | add index      | none                 |       135 |     1507 |         0 | 2022-04-26 10:42:44 | NULL                | none    |
|   1513 | aaaa_da_bbbb | bbbb_cccc_copy1 | drop table     | none                 |       135 |     1507 |         0 | 2022-04-26 10:45:41 | NULL                | none    |
|   1514 | aaaa_da_bbbb | bbbb_cccc_copy2 | drop table     | none                 |       135 |     1509 |         0 | 2022-04-26 11:02:47 | NULL                | none    |
|   1515 | aaaa_da_bbbb | bbbb_cccc_copy1 | add index      | none                 |       135 |     1507 |         0 | 2022-04-26 11:16:43 | NULL                | none    |
|   1517 | aaaa_da_bbbb |                           | create table   | none                 |       135 |     1516 |         0 | 2022-04-26 11:22:09 | NULL                | none    |
|   1519 | aaaa_da_bbbb |                           | create table   | none                 |       135 |     1518 |         0 | 2022-04-26 11:27:06 | NULL                | none    |
|   1521 | aaaa_da_bbbb |                           | create table   | none                 |       135 |     1520 |         0 | 2022-04-26 11:33:16 | NULL                | none    |
|   1523 | aaaa_da_bbbb |                           | create table   | none                 |       135 |     1522 |         0 | 2022-04-26 11:38:43 | NULL                | none    |
|   1525 | aaaa_da_bbbb |                           | create table   | none                 |       135 |     1524 |         0 | 2022-04-26 11:44:02 | NULL                | none    |
|   1527 | aaaa_saas      |                           | create table   | none                 |       137 |     1526 |         0 | 2022-04-26 11:45:29 | NULL                | none    |
|   1529 | aaaa_saas      |                           | create table   | none                 |       137 |     1528 |         0 | 2022-04-26 11:59:36 | NULL                | none    |
|   1531 | aaaa_saas      |                           | create table   | none                 |       137 |     1530 |         0 | 2022-04-26 12:06:01 | NULL                | none    |
|   1533 | aaaa_saas      |                           | create table   | none                 |       137 |     1532 |         0 | 2022-04-26 12:06:48 | NULL                | none    |
|   1535 | aaaa_saas      |                           | create table   | none                 |       137 |     1534 |         0 | 2022-04-26 12:10:15 | NULL                | none    |
|   1537 | test           |                           | create table   | none                 |         1 |     1536 |         0 | 2022-04-26 12:12:57 | NULL                | none    |
|   1539 | test           |                           | create table   | none                 |         1 |     1538 |         0 | 2022-04-26 12:20:08 | NULL                | none    |
|   1541 | aaaa_saas      |                           | create table   | none                 |       137 |     1540 |         0 | 2022-04-26 12:35:25 | NULL                | none    |
|   1543 | aaaa_saas      |                           | create table   | none                 |       137 |     1542 |         0 | 2022-04-26 12:35:54 | NULL                | none    |
|   1545 | aaaa_saas      |                           | create table   | none                 |       137 |     1544 |         0 | 2022-04-26 12:37:09 | NULL                | none    |
|   1547 | aaaa_saas      |                           | create table   | none                 |       137 |     1546 |         0 | 2022-04-26 12:38:13 | NULL                | none    |
|   1549 | test           |                           | create table   | none                 |         1 |     1548 |         0 | 2022-04-26 12:39:14 | NULL                | none    |
|   1551 | aaaa_saas      |                           | create table   | none                 |       137 |     1550 |         0 | 2022-04-26 12:44:02 | NULL                | none    |
|   1553 | test           |                           | create table   | none                 |         1 |     1552 |         0 | 2022-04-26 12:47:11 | NULL                | none    |
|   1555 | test           |                           | create table   | none                 |         1 |     1554 |         0 | 2022-04-26 12:49:18 | NULL                | none    |
|   1557 | test           |                           | create table   | none                 |         1 |     1556 |         0 | 2022-04-26 12:51:10 | NULL                | none    |
|   1559 | test           |                           | create table   | none                 |         1 |     1558 |         0 | 2022-04-26 12:57:08 | NULL                | none    |
|   1561 | peter          |                           | create schema  | none                 |      1560 |        0 |         0 | 2022-04-26 12:58:12 | NULL                | none    |
|   1563 | zytest         |                           | create schema  | none                 |      1562 |        0 |         0 | 2022-04-26 13:01:03 | NULL                | none    |
|   1565 | zytest         |                           | create schema  | none                 |      1564 |        0 |         0 | 2022-04-26 13:03:22 | NULL                | none    |
|   1567 | zytest         |                           | create schema  | none                 |      1566 |        0 |         0 | 2022-04-26 13:12:32 | NULL                | none    |
|   1569 | test           |                           | create table   | none                 |         1 |     1568 |         0 | 2022-04-26 13:12:52 | NULL                | none    |
|   1510 | aaaa_da_bbbb | bbbb_cccc_copy2 | create table   | public               |       135 |     1509 |         0 | 2022-04-26 10:40:20 | 2022-04-26 10:40:21 | synced  |
|   1508 | aaaa_da_bbbb | bbbb_cccc_copy1 | create table   | public               |       135 |     1507 |         0 | 2022-04-26 10:40:01 | 2022-04-26 10:40:01 | synced  |
|   1501 | aaaa_saas      | aaaa_outfit_sic           | drop index     | none                 |       137 |     1210 |         0 | 2022-04-26 09:50:21 | 2022-04-26 09:50:22 | synced  |
|   1500 | aaaa_saas      | aaaa_dddd       | add index      | public               |       137 |     1496 |         0 | 2022-04-26 09:45:06 | 2022-04-26 09:45:07 | synced  |
|   1499 | aaaa_saas      | aaaa_dddd       | add index      | public               |       137 |     1496 |         0 | 2022-04-26 09:45:06 | 2022-04-26 09:45:06 | synced  |
|   1498 | aaaa_saas      | aaaa_dddd       | add index      | public               |       137 |     1496 |         0 | 2022-04-26 09:45:05 | 2022-04-26 09:45:05 | synced  |
|   1497 | aaaa_saas      | aaaa_dddd       | create table   | public               |       137 |     1496 |         0 | 2022-04-26 09:44:35 | 2022-04-26 09:44:35 | synced  |
|   1495 | aaaa_da_bbbb | bbbb_tenant_order       | truncate table | public               |       135 |     1466 |         0 | 2022-04-26 02:00:50 | 2022-04-26 02:00:50 | synced  |
|   1493 | aaaa_saas      | aaaa_eeee         | drop column    | none                 |       137 |      633 |         0 | 2022-04-25 07:30:24 | 2022-04-25 07:30:24 | synced  |
|   1492 | aaaa_saas      | aaaa_eeee         | add column     | public               |       137 |      633 |         0 | 2022-04-25 07:30:11 | 2022-04-25 07:30:12 | synced  |
+--------+----------------+---------------------------+----------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------+
47 rows in set (0.05 sec)

MySQL [(none)]> show processlist;
+------+-------------+--------------+----------------+---------+------+-------+------------------------------------------------------------------------------------------------------+
| Id   | User        | Host         | db             | Command | Time | State | Info                                                                                                 |
+------+-------------+--------------+----------------+---------+------+-------+------------------------------------------------------------------------------------------------------+
|    6 | aaaa_saas   | 10.23.0.1 | aaaa_saas      | Sleep   |   17 | 2     | NULL                                                                                                 |
|   30 | aaaa_saas   | 10.23.0.2  | aaaa_saas      | Query   |    0 | 2     |                             SELECT position_2, id
                            FROM aaaa_outfit_detai |
|   13 | aaaa_saas   | 10.23.0.3 | aaaa_da_bbbb | Sleep   |  488 | 2     | NULL                                                                                                 |
|   27 | aaaa_saas   | 10.23.0.4 | aaaa_da_bbbb | Sleep   |  509 | 2     | NULL                                                                                                 |
|    5 | ucloudadmin | 10.23.0.5 | NULL           | Sleep   |  602 | 2     | NULL                                                                                                 |
|   31 | ucloudadmin | 10.25.0.6 | test           | Query   |  547 | 2     | CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `r_id` INT AUTO_INCREMENT,
   `r_title` VARCHA |
|   14 | aaaa_saas   | 10.23.0.7  | aaaa_da_bbbb | Sleep   |  514 | 2     | NULL                                                                                                 |
|    7 | ucloudadmin | 10.23.0.8  | NULL           | Sleep   |  601 | 2     | NULL                                                                                                 |
|   29 | aaaa_saas   | 10.23.0.9 | aaaa_saas      | Sleep   |  452 | 2     | NULL                                                                                                 |
|   20 | aaaa_saas   | 10.23.0.10  | aaaa_da_bbbb | Sleep   |  490 | 2     | NULL                                                                                                 |
|   25 | aaaa_saas   | 10.23.0.11 | aaaa_saas      | Sleep   |  429 | 2     | NULL                                                                                                 |
|   47 | aaaa_saas   | 10.23.0.12 | aaaa_saas      | Query   |    2 | 2     | select id, outfit_id
    from aaaa_outfit_skc
    where
    channel_id = 20011024
    AND tenant_id  |
|   26 | aaaa_saas   | 10.23.0.13 | aaaa_saas      | Sleep   |   27 | 2     | NULL                                                                                                 |
|   37 | ucloudadmin | 10.25.0.14 | NULL           | Query   |    0 | 2     | show processlist                                                                                     |
|   40 | aaaa_saas   | 10.23.0.15 | aaaa_da_bbbb | Sleep   |  504 | 2     | NULL                                                                                                 |
|   12 | aaaa_saas   | 10.23.0.16 | aaaa_saas      | Sleep   |  285 | 2     | NULL                                                                                                 |
|    9 | aaaa_saas   | 10.23.0.17  | aaaa_da_bbbb | Sleep   |  478 | 2     | NULL                                                                                                 |
|   23 | ucloudadmin | 10.25.0.18 | test           | Query   |  568 | 2     | create database zytest                                                                               |
|   48 | aaaa_saas   | 10.23.0.19  | aaaa_da_bbbb | Sleep   |  424 | 2     | NULL                                                                                                 |
|   15 | aaaa_saas   | 10.23.0.20 | aaaa_da_bbbb | Sleep   |  490 | 2     | NULL                                                                                                 |
|   39 | aaaa_saas   | 10.23.0.21 | aaaa_da_bbbb | Sleep   |  481 | 2     | NULL                                                                                                 |
|   18 | aaaa_saas   | 10.23.0.22  | aaaa_saas      | Sleep   |   46 | 2     | NULL                                                                                                 |
|   21 | aaaa_saas   | 10.23.0.23  | aaaa_da_bbbb | Sleep   |  482 | 2     | NULL                                                                                                 |
|   22 | aaaa_saas   | 10.23.0.24  | aaaa_saas      | Sleep   |    9 | 2     | NULL                                                                                                 |
|   17 | aaaa_saas   | 10.23.0.25  | aaaa_saas      | Sleep   |   46 | 2     | NULL                                                                                                 |
|    8 | ucloudadmin | 10.23.0.26 | NULL           | Sleep   |  599 | 2     | NULL                                                                                                 |
|   19 | aaaa_saas   | 10.23.0.27 | aaaa_da_bbbb | Sleep   |  479 | 2     | NULL                                                                                                 |
|   24 | aaaa_saas   | 10.23.0.28 | aaaa_da_bbbb | Sleep   |  471 | 2     | NULL                                                                                                 |
|   10 | aaaa_saas   | 10.23.0.29  | aaaa_da_bbbb | Sleep   |  494 | 2     | NULL                                                                                                 |
|   28 | aaaa_saas   | 10.23.0.30 | aaaa_saas      | Sleep   |   17 | 2     | NULL                                                                                                 |
|   11 | aaaa_saas   | 10.23.0.31 | aaaa_da_bbbb | Sleep   |  508 | 2     | NULL                                                                                                 |
|   16 | aaaa_saas   | 10.23.0.32 | aaaa_da_bbbb | Sleep   |  482 | 2     | NULL                                                                                                 |
|   32 | aaaa_saas   | 10.23.0.33  | aaaa_da_bbbb | Sleep   |  492 | 2     | NULL                                                                                                 |
+------+-------------+--------------+----------------+---------+------+-------+------------------------------------------------------------------------------------------------------+
33 rows in set (0.00 sec)
  • 9:25分
MySQL [(none)]> ADMIN CANCEL DDL JOBS 1502;
+--------+------------+
| JOB_ID | RESULT     |
+--------+------------+
| 1502   | successful |
+--------+------------+
1 row in set (0.11 sec)

MySQL [(none)]> admin show ddl jobs;ERROR 1317 (70100): Query execution was interrupted
MySQL [test]> Bye
ERROR 1007 (HY000): Can't create database 'zytest'; database exists
MySQL [test]> Bye
ERROR 1007 (HY000): Can't create database 'zytest'; database exists
MySQL [test]> Bye

+--------+----------------+------------+---------------+--------------+-----------+----------+-----------+---------------------+---------------------+-----------+
| JOB_ID | DB_NAME        | TABLE_NAME | JOB_TYPE      | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | START_TIME          | END_TIME            | STATE     |
+--------+----------------+------------+---------------+--------------+-----------+----------+-----------+---------------------+---------------------+-----------+
|   1570 | aaaa_da_bbbb |            | add index     | none         |       135 |     1507 |         0 | 2022-04-26 13:23:44 | 2022-04-26 13:25:00 | cancelled |
|   1569 | test           |            | create table  | none         |         1 |     1568 |         0 | 2022-04-26 13:12:52 | 2022-04-26 13:25:01 | cancelled |
|   1567 | zytest         |            | create schema | none         |      1566 |        0 |         0 | 2022-04-26 13:12:32 | 2022-04-26 13:25:01 | cancelled |
|   1565 | zytest         |            | create schema | none         |      1564 |        0 |         0 | 2022-04-26 13:03:22 | 2022-04-26 13:25:00 | cancelled |
|   1563 | zytest         |            | create schema | public       |      1562 |        0 |         0 | 2022-04-26 13:01:03 | 2022-04-26 13:25:00 | synced    |
|   1561 | peter          |            | create schema | public       |      1560 |        0 |         0 | 2022-04-26 12:58:12 | 2022-04-26 13:25:00 | synced    |
|   1559 | test           |            | create table  | none         |         1 |     1558 |         0 | 2022-04-26 12:57:08 | 2022-04-26 13:25:00 | cancelled |
|   1557 | test           |            | create table  | none         |         1 |     1556 |         0 | 2022-04-26 12:51:10 | 2022-04-26 13:25:00 | cancelled |
|   1555 | test           |            | create table  | none         |         1 |     1554 |         0 | 2022-04-26 12:49:18 | 2022-04-26 13:25:00 | cancelled |
|   1553 | test           |            | create table  | none         |         1 |     1552 |         0 | 2022-04-26 12:47:11 | 2022-04-26 13:25:00 | cancelled |
+--------+----------------+------------+---------------+--------------+-----------+----------+-----------+---------------------+---------------------+-----------+
10 rows in set (0.01 sec)

MySQL [(none)]> admin show ddl jobs;
+--------+----------------+------------+---------------+--------------+-----------+----------+-----------+---------------------+---------------------+-----------+
| JOB_ID | DB_NAME        | TABLE_NAME | JOB_TYPE      | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | START_TIME          | END_TIME            | STATE     |
+--------+----------------+------------+---------------+--------------+-----------+----------+-----------+---------------------+---------------------+-----------+
|   1572 | test           | runoob_tbl | drop table    | none         |         1 |     1536 |         0 | 2022-04-26 13:25:59 | 2022-04-26 13:26:00 | synced    |
|   1571 | peter          |            | drop schema   | none         |      1560 |        0 |         0 | 2022-04-26 13:25:40 | 2022-04-26 13:25:41 | synced    |
|   1570 | aaaa_da_bbbb |            | add index     | none         |       135 |     1507 |         0 | 2022-04-26 13:23:44 | 2022-04-26 13:25:00 | cancelled |
|   1569 | test           |            | create table  | none         |         1 |     1568 |         0 | 2022-04-26 13:12:52 | 2022-04-26 13:25:01 | cancelled |
|   1567 | zytest         |            | create schema | none         |      1566 |        0 |         0 | 2022-04-26 13:12:32 | 2022-04-26 13:25:01 | cancelled |
|   1565 | zytest         |            | create schema | none         |      1564 |        0 |         0 | 2022-04-26 13:03:22 | 2022-04-26 13:25:00 | cancelled |
|   1563 | zytest         |            | create schema | public       |      1562 |        0 |         0 | 2022-04-26 13:01:03 | 2022-04-26 13:25:00 | synced    |
|   1561 | peter          |            | create schema | public       |      1560 |        0 |         0 | 2022-04-26 12:58:12 | 2022-04-26 13:25:00 | synced    |
|   1559 | test           |            | create table  | none         |         1 |     1558 |         0 | 2022-04-26 12:57:08 | 2022-04-26 13:25:00 | cancelled |
|   1557 | test           |            | create table  | none         |         1 |     1556 |         0 | 2022-04-26 12:51:10 | 2022-04-26 13:25:00 | cancelled |
+--------+----------------+------------+---------------+--------------+-----------+----------+-----------+---------------------+---------------------+-----------+
10 rows in set (0.02 sec)
2

ddl有2个队列一个用于加索引，前面执行完后面才能执行。另一个队列执行其他ddl

1 个赞
3

当前 TiDB 集群中 DDL 操作是串行执行的。

2 个赞
4

串行并行可以自己调整吗？怎么看出来当前是串行的？一楼说有两个队列，那我add index那个队列没执行完应该不会影响其他的ddl吧？当时create table，create database 也都不能执行了。都阻塞住了。他们不应该是在第二个队列执行吗

5

image
image978×818 117 KB

如果ddl间有依赖也是会阻塞的，能否把你具体的操作复现下，都执行的哪些操作

2 个赞
6

分别在两个队列里的ddl只能串行， ADMIN SHOW DDL JOBS可以查看当前 DDL 作业队列中的所有结果（包括正在运行以及等待运行的任务）以及已执行完成的 DDL 作业队列中的最近十条结果。
楼上大佬说的对，加索引有单独的队列，但是对于同一个 表的 DDL 操作必须是顺序执行的。

2 个赞
7

查询一下锁

1 个赞
8

我看文档里写的，TiDB的DDL是串行的，放到队列里，依次执行，执行后放到历史执行队列里。
以下文档内容可以看
https://docs.pingcap.com/zh/tidb/stable/sql-statement-admin-show-ddl#admin-show-ddl-job-queries

9

image
image1468×772 64.6 KB

先执行1502 给aaaa_outfit_skc 添加索引,索引比较大，然后1503，1504，1505，1506是给bbbb_cccc添加索引。再然后1511，1512给bbbb_cccc_copy1添加索引。添加索引的ddl由于第一条在执行，后面的会阻塞理解了。然后1513 drop table bbbb_cccc_copy1也阻塞了。导致后面所有的ddl都阻塞了。是由于1511，1512给bbbb_cccc_copy1添加索引没添加完成，然后又drop bbbb_cccc_copy1的原因吗？

10

产生依赖关系了，同一table的ddl ,job id小的先执行，前面的执行完了后面的才能执行

1 个赞
11

好的，理解了。谢谢

12

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。