PolarDB allows you to use the Polar performance schema feature to monitor the execution status of DDL statements and metadata locks. The Polar performance schema feature is a lightweight status monitoring feature. Compare with the Performance Schema feature provided by MySQL, this feature uses fewer memory resources and incurs lower performance overheads. This topic describes how to use the Polar performance schema feature to view the execution status of DDL statements and metadata locks.
Prerequisites
The cluster must be of PolarDB for MySQL 8.0.1 and the revision version must be 8.0.1.1.21 or later. For more information about how to check the version, see Query the engine version.
Precautions
DDL statements are always executed on the primary node of a cluster. Therefore, you must query the execution status of DDL statements on the primary node.
- If you are connected to the cluster by using the primary endpoint, you can directly query the execution status of DDL statements on the primary node. For information about how to query the primary endpoint, see View an endpoint.
- If you are connected to the cluster by using a cluster endpoint, you can use hints in SQL statements to query the execution status of DDL statements on the primary node. For more information, see Hints.
After you enable the performance schema feature, the Polar performance schema feature is automatically disabled.
Procedure
Note The method introduced in this topic is suitable only for tables that use the InnoDB storage engine.
- Enable the Polar performance schema feature.
You must set the loose_polar_performance_schema parameter to
ONfor your cluster in the console. The feature takes effect after you restart the cluster. For more information, see Specify cluster and node parameters.The following table describes the parameters for the Polar performance schema feature.Parameter Description loose_polar_performance_schema Specifies whether to enable the Polar performance schema feature. Valid values: - ON: The system enables the Polar performance schema feature.
- OFF: The system disables the Polar performance schema feature.
performance_schema_max_thread_instances The maximum number of threads used by the Polar performance schema feature for monitoring. Valid values: -1 to 65536. A value of -1 indicates that the number of threads is automatically adjusted to adapt to business changes. Note This parameter is tuned. We recommend that you do not modify this parameter.performance_schema_max_metadata_locks The maximum number of metadata locks used by the Polar performance schema feature for monitoring. Valid values: -1 to 1048576. A value of -1 indicates that the number of metadata locks is automatically adjusted to adapt to business changes. Note This parameter is tuned. We recommend that you do not modify this parameter.After your cluster is started, you can execute the following statement to specify whether to enable the Polar performance schema feature:
SHOW VARIABLES LIKE 'polar_performance_schema';A similar result is returned:
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | polar_performance_schema | ON | +--------------------------+-------+ 1 row in set (0.00 sec) - View the execution status of a DDL statement and metadata locks.
- In the process where a DDL statement is executed, you can execute the following statement to view the
performance_schema.events_stages_currenttable. Then, you can obtain the status of the DDL statement.
A similar result is returned:SELECT THREAD_ID,EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 as PROGRESS FROM performance_schema.events_stages_current;+-----------+----------+------------------------------------------------------+----------------+----------------+----------+ | THREAD_ID | EVENT_ID | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | PROGRESS | +-----------+----------+------------------------------------------------------+----------------+----------------+----------+ | 3057989 | 13 | stage/innodb/alter table (read PK and internal sort) | 56634 | 330135 | 17.1548 | +-----------+----------+------------------------------------------------------+----------------+----------------+----------+ 1 row in set (0.00 sec)You can use theperformance_schema.threadsandinformation_schema.PROCESSLISTtables and executed the following statement to view the SQL statement corresponding to the current event:
A similar result is returned:SELECT esc.THREAD_ID, esc.EVENT_NAME, esc.WORK_COMPLETED, esc.WORK_ESTIMATED, pl.INFO FROM performance_schema.events_stages_current esc LEFT JOIN performance_schema.threads th ON esc.thread_id = th.thread_id LEFT JOIN information_schema.PROCESSLIST pl ON th.PROCESSLIST_ID = pl.ID;+-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+ | THREAD_ID | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | INFO | +-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+ | 3057989 | stage/innodb/alter table (read PK and internal sort) | 77034 | 330519 | ALTER TABLE test.test ALGORITHM=INPLACE, ADD testA VARCHAR(20) NOT NULL DEFAULT 'testA' | +-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) - You can execute the following statement to view the
performance_schema.metadata_lockstable. Then, you can obtain the metadata lock status of the current cluster.
A similar result is returned:SELECT * FROM performance_schema.metadata_locks;
You can use the+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | GLOBAL | NULL | NULL | NULL | 139949462878336 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:3103 | 3055785 | 1 | | TABLE | test | test | NULL | 139931318980224 | SHARED_WRITE | TRANSACTION | GRANTED | sql_parse.cc:6479 | 3055785 | 1 | | COMMIT | NULL | NULL | NULL | 139931318980480 | INTENTION_EXCLUSIVE | EXPLICIT | GRANTED | handler.cc:1669 | 3055785 | 1 | | TABLE | performance_schema | metadata_locks | NULL | 139934227366144 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6479 | 3057612 | 1 | | GLOBAL | NULL | NULL | NULL | 139934216849664 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5519 | 3057989 | 13 | | SCHEMA | test | NULL | NULL | 139934216849408 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5506 | 3057989 | 13 | | TABLE | test | test | NULL | 139934216848640 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:6479 | 3057989 | 13 | | BACKUP LOCK | NULL | NULL | NULL | 139934216849280 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5526 | 3057989 | 13 | | TABLESPACE | NULL | test/test | NULL | 139934216848384 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:815 | 3057989 | 13 | | TABLE | test | #sql-17d9_2ea89a | NULL | 139934216848896 | EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:15054 | 3057989 | 13 | | GLOBAL | NULL | NULL | NULL | 139934216850176 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:416 | 3057989 | 13 | | TABLESPACE | NULL | test/test | NULL | 139934216849920 | EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:397 | 3057989 | 13 | +-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ 12 rows in set (0.00 sec)OWNER_THREAD_IDfield and execute the following statement to view information about the threads that hold metadata locks in theperformance_schema.threadstable:select * from performance_schema.threads where THREAD_ID = "OWNER_THREAD_ID in performance_schema.metadata_locks table ";
- In the process where a DDL statement is executed, you can execute the following statement to view the
Best practices
- Waiting for table metadata lock
In practice, the common cause of DDL blocking is that the metadata lock cannot be acquired. In this case, the DDL statement is in the
Waiting for table metadata lockstate. You can use themetadata_locktable to quickly discover the causes for DDL blocking.Example:
Execute theSHOW PROCESSLISTstatement to query the execution status of theALTER TABLE t1 ADD column d varchar(10),algorithm = inplacestatement. A similar result is returned:/*force_node='pi-bp10k7631d6k3****'*/ show processlist; +-----------+-----------------+-----------------------+--------------------+----------------+---------+---------------------------------+-------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----------+-----------------+-----------------------+--------------------+----------------+---------+---------------------------------+-------------------------------------------------------------+ | 98 | event_scheduler | localhost | NULL | Daemon | 1306586 | Waiting on empty queue | NULL | | 109 | replicator | 11.111.XX.XX:62549 | NULL | Polar Log Dump | 1 | Reading log from innodb | NULL | | 113 | replicator | 11.111.XX.XX:62560 | NULL | Polar Log Ack | 1 | Receiving from client | NULL | | 133 | replicator | 10.13.64.70:42712 | NULL | Polar Log Dump | 1 | Reading log from innodb | NULL | | 138 | replicator | 10.13.64.70:42723 | NULL | Polar Log Ack | 0 | Receiving from client | NULL | | 369 | aurora | 10.111.211.209:33334 | NULL | Sleep | 0 | | NULL | | 370 | aurora | 10.111.211.209:33336 | NULL | RDS Push LSN | 1306413 | starting | NULL | | 372 | aurora | 10.111.204.224:37010 | NULL | Sleep | 0 | | NULL | | 373 | aurora | 10.111.204.224:37019 | NULL | RDS Push LSN | 1306413 | starting | NULL | | 3064011 | root | 127.0.0.1:59703 | NULL | Sleep | 716 | | NULL | | 3064013 | root | 127.0.0.1:59710 | NULL | Sleep | 25 | | NULL | | 3064015 | root | 127.0.0.1:59713 | NULL | Sleep | 55 | | NULL | | 3064018 | root | 127.0.0.1:59716 | NULL | Sleep | 1 | | NULL | | 3067041 | zyg_root | 172.17.XX.XX:48594 | test | Query | 22 | Waiting for table metadata lock | alter table t1 add column d varchar(10),algorithm = inplace | | 3067443 | zyg_root | 172.17.XX.XX:48602 | test | Sleep | 27 | | NULL | | 3069716 | aurora | 100.104.XX.XX:33017 | information_schema | Sleep | 30 | | NULL | | 3069859 | aurora | 100.104.XX.XX:41872 | information_schema | Sleep | 30 | | NULL | | 3069925 | aurora | 10.111.204.224:20916 | NULL | Sleep | 2 | | NULL | | 3069932 | aurora | 10.111.211.209:51263 | NULL | Sleep | 2 | | NULL | | 270526156 | zyg_root | 172.17.28.253:46272 | test | Query | 0 | starting | /*force_node='pi-bp10k7631d6k3****'*/ show processlist | +-----------+-----------------+-----------------------+--------------------+----------------+---------+---------------------------------+-------------------------------------------------------------+ 20 rows in set (0.00 sec)The DDL statement is in the
Waiting for table metadata lockstate.You can view the metadata lock status of the DDL statement by querying theperformance_schema.metadata_lockstable. A similar result is returned:/*force_node='pi-bp10k7631d6k3****'*/ select * from performance_schema.metadata_locks; +-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+ | TABLE | performance_schema | metadata_locks | NULL | 139742994307712 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:7688 | 3810041 | 1 | | TABLE | test | t1 | NULL | 139742992122240 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:7688 | 3810574 | 1 | | GLOBAL | NULL | NULL | NULL | 139742992172544 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5637 | 3810086 | 3 | | SCHEMA | test | NULL | NULL | 139742993150592 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5624 | 3810086 | 3 | | TABLE | test | t1 | NULL | 139742993150848 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:7688 | 3810086 | 3 | | BACKUP LOCK | NULL | NULL | NULL | 139742993844096 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5644 | 3810086 | 3 | | TABLESPACE | NULL | test/t1 | NULL | 139742991805696 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:815 | 3810086 | 3 | | TABLE | test | #sql-1b34_2ecca1 | NULL | 139742992091136 | EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:15532 | 3810086 | 3 | | TABLE | test | t1 | NULL | 140266021234688 | EXCLUSIVE | TRANSACTION | PENDING | mdl.cc:4124 | 3810086 | 3 | +-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+ 9 rows in set (0.00 sec)The3810574thread holds theSHARED_READlock of thetest/t1table. As a result, the3810086thread cannot acquire theEXCLUSIVElock. You can use theperformance_schema.threadstable to obtain the details of the thread. A similar result is returned:/*force_node='pi-bp10k7631d6k3****'*/ select * from performance_schema.threads where THREAD_ID in (3810086,3810574)\G *************************** 1. row *************************** THREAD_ID: 3810086 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 3067041 PROCESSLIST_USER: zyg_root PROCESSLIST_HOST: 172.17.28.253 PROCESSLIST_DB: test PROCESSLIST_COMMAND: Query PROCESSLIST_TIME: 41 PROCESSLIST_STATE: Waiting for table metadata lock PROCESSLIST_INFO: alter table t1 add column d varchar(10),algorithm = inplace PARENT_THREAD_ID: NULL ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: TCP/IP THREAD_OS_ID: 64852 RESOURCE_GROUP: NULL *************************** 2. row *************************** THREAD_ID: 3810574 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 3067443 PROCESSLIST_USER: zyg_root PROCESSLIST_HOST: 172.17.28.253 PROCESSLIST_DB: test PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 46 PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: NULL ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: TCP/IP THREAD_OS_ID: 65845 RESOURCE_GROUP: NULL 2 rows in set (0.01 sec)The
3810086thread is for the blocked DDL statement, while the3810574thread is for a slow query. The3810574thread holds the lock. Therefore, theALTER TABLE t1 ADD column d varchar(10),algorithm = inplacestatement cannot acquire the metadata lock and is blocked. Depending on actual business requirements, you can wait for the transaction to be committed or execute theKILL processlist_idstatement to terminate the transaction before you execute theALTER TABLE t1 ADD column d varchar(10),algorithm = inplacestatement again. - Wait for syncing with replicas
PolarDB uses the cluster architecture. Therefore, when you execute a DDL statement on the primary node, you must wait for all read-only nodes to release the corresponding metadata locks. If you find that the DDL statement is in the
Wait for syncing with replicasstate when you execute theSHOW PROCESSLISTstatement, a read-only node holds the metadata lock of the table. You can quickly query the thread that holds the metadata lock on the read-only node as set forth in this section.Example:
Execute theSHOW PROCESSLISTstatement on the primary node of the cluster to query the execution status of theALTER TABLE t1 ADD column d varchar(10),algorithm = inplacestatement. A similar result is returned:/*force_node='pi-bp10k7631d6k3****'*/ show processlist; +-----------+-----------------+-----------------------+--------------------+----------------+---------+--------------------------------+-------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----------+-----------------+-----------------------+--------------------+----------------+---------+--------------------------------+-------------------------------------------------------------+ | 98 | event_scheduler | localhost | NULL | Daemon | 1307512 | Waiting on empty queue | NULL | | 109 | replicator | 11.111.XX.XX:62549 | NULL | Polar Log Dump | 1 | Reading log from innodb | NULL | | 113 | replicator | 11.111.XX.XX:62560 | NULL | Polar Log Ack | 1 | Receiving from client | NULL | | 133 | replicator | 10.13.64.70:42712 | NULL | Polar Log Dump | 1 | Reading log from innodb | NULL | | 138 | replicator | 10.13.64.70:42723 | NULL | Polar Log Ack | 0 | Receiving from client | NULL | | 369 | aurora | 10.111.211.209:33334 | NULL | Sleep | 0 | | NULL | | 370 | aurora | 10.111.211.209:33336 | NULL | RDS Push LSN | 1307339 | starting | NULL | | 372 | aurora | 10.111.204.224:37010 | NULL | Sleep | 0 | | NULL | | 373 | aurora | 10.111.204.224:37019 | NULL | RDS Push LSN | 1307339 | starting | NULL | | 3064011 | root | 127.0.0.1:59703 | NULL | Sleep | 742 | | NULL | | 3064013 | root | 127.0.0.1:59710 | NULL | Sleep | 21 | | NULL | | 3064015 | root | 127.0.0.1:59713 | NULL | Sleep | 21 | | NULL | | 3064018 | root | 127.0.0.1:59716 | NULL | Sleep | 1 | | NULL | | 3067041 | zyg_root | 172.17.28.253:48594 | test | Query | 6 | Wait for syncing with replicas | alter table t1 add column d varchar(10),algorithm = inplace | | 3067443 | zyg_root | 172.17.28.253:48602 | test | Sleep | 751 | | NULL | | 3071863 | aurora | 100.104.XX.XX:32615 | information_schema | Sleep | 56 | | NULL | | 3072000 | aurora | 100.104.XX.XX:41585 | information_schema | Sleep | 56 | | NULL | | 3072126 | aurora | 10.111.204.224:47050 | NULL | Sleep | 2 | | NULL | | 3072127 | aurora | 10.111.211.209:41026 | NULL | Sleep | 1 | | NULL | | 270526156 | zyg_root | 172.17.28.253:46272 | test | Sleep | 362 | | NULL | | 270530026 | zyg_root | 172.17.28.253:46390 | test | Query | 0 | starting | /*force_node='pi-bp10k7631d6k3****'*/ show processlist | +-----------+-----------------+-----------------------+--------------------+----------------+---------+--------------------------------+-------------------------------------------------------------+ 21 rows in set (0.00 sec)The DDL statement is in the
Wait for syncing with replicasstate.You can use theperformance_schema.metadata_lockstable and the hint to query the status of the metadata lock on the specified read-only node. A similar result is returned:/*force_node='pi-bp186ko4o21wl****'*/ select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+ | TABLE | test | t1 | NULL | 139394298895872 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:7688 | 3513381 | 1 | | TABLE | test | t1 | NULL | 139394298602240 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:7688 | 3519277 | 1 | | TABLE | test | t1 | NULL | 139917548369664 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:7688 | 3519279 | 1 | | TABLE | test | t1 | NULL | 139394296661888 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:7688 | 3519278 | 1 | | TABLE | test | t1 | NULL | 139394297595520 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:7688 | 3519276 | 1 | | SCHEMA | test | NULL | NULL | 139464322084864 | INTENTION_EXCLUSIVE | EXPLICIT | GRANTED | sql_table.cc:17404 | 57 | 1 | | TABLE | test | t1 | NULL | 139464322084992 | EXCLUSIVE | EXPLICIT | PENDING | sql_table.cc:17410 | 57 | 1 | | TABLE | performance_schema | metadata_locks | NULL | 139394296038784 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:7688 | 3518506 | 1 | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+ 8 rows in set (0.00 sec)The3513381,3519277,3519279,3519278, and3519276threads on the read-only node hold theSHARED_READlock of thetest/t1table. You can use theperformance_schema.threadstable to obtain details of those threads. A similar result is returned:/*force_node='pi-bp186ko4o21wl****'*/ select * from performance_schema.threads where THREAD_ID in (3519278,3513381,3519279,3519276,3519277)\G *************************** 1. row *************************** THREAD_ID: 3513381 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 538961413 PROCESSLIST_USER: zyg_root PROCESSLIST_HOST: 172.17.28.253 PROCESSLIST_DB: test PROCESSLIST_COMMAND: Connect PROCESSLIST_TIME: 103 PROCESSLIST_STATE: User sleep PROCESSLIST_INFO: select *,sleep(60) from t1 PARENT_THREAD_ID: NULL ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: TCP/IP THREAD_OS_ID: 63826 RESOURCE_GROUP: NULL *************************** 2. row *************************** THREAD_ID: 3519276 NAME: thread/sql/parallel_worker TYPE: FOREGROUND PROCESSLIST_ID: 1855915 PROCESSLIST_USER: zyg_root PROCESSLIST_HOST: 172.17.28.253 PROCESSLIST_DB: test PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 103 PROCESSLIST_STATE: Sending data PROCESSLIST_INFO: select *,sleep(60) from t1 PARENT_THREAD_ID: 3513381 ROLE: NULL INSTRUMENTED: NO HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 7117 RESOURCE_GROUP: NULL *************************** 3. row *************************** THREAD_ID: 3519277 NAME: thread/sql/parallel_worker TYPE: FOREGROUND PROCESSLIST_ID: 1855917 PROCESSLIST_USER: zyg_root PROCESSLIST_HOST: 172.17.28.253 PROCESSLIST_DB: test PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 103 PROCESSLIST_STATE: Sending data PROCESSLIST_INFO: select *,sleep(60) from t1 PARENT_THREAD_ID: 3513381 ROLE: NULL INSTRUMENTED: NO HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 7116 RESOURCE_GROUP: NULL *************************** 4. row *************************** THREAD_ID: 3519278 NAME: thread/sql/parallel_worker TYPE: FOREGROUND PROCESSLIST_ID: 1855916 PROCESSLIST_USER: zyg_root PROCESSLIST_HOST: 172.17.28.253 PROCESSLIST_DB: test PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 103 PROCESSLIST_STATE: Sending data PROCESSLIST_INFO: select *,sleep(60) from t1 PARENT_THREAD_ID: 3513381 ROLE: NULL INSTRUMENTED: NO HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 7119 RESOURCE_GROUP: NULL *************************** 5. row *************************** THREAD_ID: 3519279 NAME: thread/sql/parallel_worker TYPE: FOREGROUND PROCESSLIST_ID: 1855918 PROCESSLIST_USER: zyg_root PROCESSLIST_HOST: 172.17.28.253 PROCESSLIST_DB: test PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 103 PROCESSLIST_STATE: Sending data PROCESSLIST_INFO: select *,sleep(60) from t1 PARENT_THREAD_ID: 3513381 ROLE: NULL INSTRUMENTED: NO HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 7118 RESOURCE_GROUP: NULL 5 rows in set (0.00 sec)The query operations on the read-only node hold the lock and do not release the lock for a long period. Multiple
parallel_workerthreads concurrently hold the metadata lock because the parallel query feature is enabled. Depending on actual business requirements, you can wait for the transaction to be committed or execute theKILL processlist_idstatement to terminate the transaction before you execute theALTER TABLE t1 ADD column d varchar(10),algorithm = inplacestatement again.