Lihat status eksekusi pernyataan DDL dan status MDL - PolarDB

PolarDB menyediakan fitur Skema Performa Polar untuk memantau status eksekusi pernyataan DDL dan kunci metadata (MDL). Fitur ini lebih ringan, menggunakan lebih sedikit sumber daya memori, serta memiliki overhead performa yang lebih rendah dibandingkan dengan Performance Schema dari MySQL. Topik ini menjelaskan cara menggunakan fitur ini untuk melihat status eksekusi pernyataan DDL dan MDL.

Versi yang didukung

Kluster PolarDB for MySQL Anda harus menjalankan salah satu versi mesin database berikut:

Kluster PolarDB for MySQL 5.7 (versi revisi 5.7.1.0.35 atau lebih baru).
Kluster PolarDB for MySQL 8.0.1 (versi revisi 8.0.1.1.21 atau lebih baru).
Kluster PolarDB for MySQL 8.0.2 (versi revisi 8.0.2.2.8 atau lebih baru).

Untuk informasi tentang cara melihat versi mesin database kluster, lihat Kueri Versi Mesin.

Masalah

Pernyataan DDL dieksekusi pada node utama kluster. Oleh karena itu, Anda harus menanyakan status eksekusi pernyataan DDL pada node utama.

Jika terhubung ke kluster menggunakan titik akhir utama, Anda dapat langsung menanyakan status eksekusi pernyataan DDL pada node utama. Untuk informasi tentang cara menanyakan titik akhir utama, lihat Lihat Titik Akhir dan Nomor Port.
Jika terhubung ke kluster menggunakan titik akhir kluster, Anda dapat menggunakan petunjuk dalam pernyataan SQL untuk menanyakan status eksekusi pernyataan DDL pada node utama. Untuk informasi lebih lanjut, lihat Pembagian Baca/Tulis.

Setelah mengaktifkan fitur Performance Schema, fitur Skema Performa Polar secara otomatis dinonaktifkan.

Prosedur

Catatan

Metode yang diperkenalkan dalam topik ini hanya berlaku untuk tabel yang menggunakan Mesin Penyimpanan InnoDB.

Aktifkan fitur Skema Performa Polar.

Untuk mengaktifkan fitur Skema Performa Polar, atur parameter loose_polar_performance_schema ke ON di Konsol PolarDB. Parameter ini hanya berlaku setelah Anda memulai ulang kluster. Untuk informasi lebih lanjut, lihat Konfigurasikan Parameter Kluster dan Node.

Tabel berikut menjelaskan parameter fitur Skema Performa Polar.

Parameter	Deskripsi
loose_polar_performance_schema	Menentukan apakah akan mengaktifkan fitur Skema Performa Polar. Nilai valid: ON OFF
loose_polar_performance_schema_enable_row_locks	Menentukan apakah Skema Performa Polar mengumpulkan dan mengekspos data pemantauan kunci tingkat baris. OFF (default) ON Catatan Parameter ini hanya tersedia di PolarDB for MySQL 8.0.1 dan 8.0.2.
performance_schema_max_thread_instances	Jumlah maksimum thread yang dilacak oleh fitur Skema Performa Polar. Nilai valid: -1 hingga 65536. Nilai -1 menentukan bahwa jumlah MDL disesuaikan secara otomatis untuk beradaptasi dengan perubahan bisnis. Catatan Parameter ini telah dioptimalkan. Kami sarankan Anda tidak mengubahnya.
performance_schema_max_metadata_locks	Jumlah maksimum MDL yang dipantau oleh fitur Skema Performa Polar. Nilai valid: -1 hingga 1048576. Nilai -1 menentukan bahwa jumlah MDL disesuaikan secara otomatis untuk beradaptasi dengan perubahan bisnis. Catatan Parameter ini telah dioptimalkan. Kami sarankan Anda tidak mengubahnya.

Setelah kluster dimulai, Anda dapat mengeksekusi pernyataan berikut untuk memeriksa apakah fitur Skema Performa Polar diaktifkan:

SHOW VARIABLES LIKE 'polar_performance_schema';

Hasil serupa dikembalikan:

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| polar_performance_schema | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

Lihat status eksekusi pernyataan DDL dan status MDL.

Lihat status eksekusi acara saat ini dari tabel performance_schema.events_stages_current. Contoh pernyataan:

SELECT THREAD_ID,EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 as PROGRESS FROM performance_schema.events_stages_current;

Contoh hasil:

+-----------+----------+------------------------------------------------------+----------------+----------------+----------+
| 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)

Lihat pernyataan SQL acara saat ini dari tabel performance_schema.threads dan information_schema.PROCESSLIST. Contoh pernyataan:

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;

Contoh hasil:

+-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
| 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)

Lihat informasi tentang MDL di kluster dari tabel performance_schema.metadata_locks. Contoh pernyataan:

SELECT * FROM performance_schema.metadata_locks;

Contoh hasil:

+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| 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)

Lihat informasi tentang thread yang memegang MDL dari tabel performance_schema.threads berdasarkan nilai OWNER_THREAD_ID.

SELECT * FROM performance_schema.threads WHERE THREAD_ID = "OWNER_THREAD_ID dalam tabel performance_schema.metadata_locks";

Praktik terbaik

Waiting for table metadata lock

Dalam banyak kasus, operasi DDL diblokir karena gagal mendapatkan MDL. Ketika ini terjadi, operasi DDL masuk ke status Waiting for table metadata lock. Anda dapat menanyakan tabel metadata_lock untuk dengan cepat mengidentifikasi alasan pemblokiran operasi DDL.

Contoh perintah:

Eksekusi pernyataan SHOW PROCESSLIST untuk menanyakan status eksekusi pernyataan ALTER TABLE t1 ADD COLUMN d VARCHAR(10), ALGORITHM = INPLACE. Contoh hasil:

/*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)

Hasilnya menunjukkan bahwa operasi DDL berada dalam status Waiting for table metadata lock.

Tanyakan tabel performance_schema.metadata_locks untuk melihat status MDL operasi DDL. Contoh hasil:

/*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)

Hasilnya menunjukkan bahwa thread 3810574 memegang kunci SHARED_READ pada tabel test/t1. Akibatnya, thread 3810086 tidak dapat memperoleh kunci EXCLUSIVE. Tanyakan tabel performance_schema.threads untuk melihat detail thread. Contoh hasil:

/*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)

Hasilnya menunjukkan bahwa operasi DDL yang diblokir berjalan pada thread 3810086, dan query lambat berjalan pada thread 3810574. Thread 3810574 memegang kunci. Akibatnya, pernyataan alter table t1 add column d varchar(10),algorithm = inplace gagal mendapatkan MDL dan diblokir. Tunggu hingga pernyataan tersebut dikomit atau gunakan pernyataan KILL processlist_id untuk menghentikan pernyataan tersebut. Kemudian, eksekusi ulang pernyataan ALTER TABLE t1 ADD COLUMN d VARCHAR(10), ALGORITHM = INPLACE sesuai dengan kebutuhan bisnis Anda.

Wait for syncing with replicas

PolarDB menggunakan arsitektur kluster. Saat melakukan operasi DDL pada node utama, Anda harus menunggu semua node baca-tulis melepaskan MDL yang sesuai. Jika operasi DDL berada dalam status Wait for syncing with replicas dalam output pernyataan SHOW PROCESSLIST, sebuah node baca-tulis memegang MDL yang sesuai. Anda dapat dengan cepat mengidentifikasi thread pada node baca-tulis yang memegang MDL berdasarkan informasi sebelumnya.

Contoh perintah:

Eksekusi pernyataan SHOW PROCESSLIST pada node utama kluster untuk menanyakan status eksekusi pernyataan ALTER TABLE t1 ADD COLUMN d VARCHAR(10), ALGORITHM = INPLACE. Contoh hasil:

/*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)

Hasilnya menunjukkan bahwa operasi DDL berada dalam status Wait for syncing with replicas.

Tanyakan status MDL pada node baca-tulis tertentu dari tabel performance_schema.metadata_locks menggunakan petunjuk. Contoh hasil:

/*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)

Hasilnya menunjukkan bahwa thread 3513381, 3519277, 3519279, 3519278, dan 3519276 pada node baca-tulis memegang kunci SHARED_READ pada tabel test/t1. Tanyakan informasi detail thread dari tabel performance_schema.threads. Contoh hasil:

/*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)

Operasi query pada node baca-tulis memegang kunci untuk waktu yang lama dan tidak melepaskannya. Beberapa thread parallel_worker secara bersamaan memegang MDL karena fitur query paralel diaktifkan. Tunggu hingga pernyataan tersebut dikomit atau gunakan pernyataan KILL processlist_id untuk menghentikan proses. Kemudian, eksekusi ulang pernyataan ALTER TABLE t1 ADD COLUMN d VARCHAR(10), ALGORITHM = INPLACE sesuai dengan kebutuhan bisnis Anda.

Hubungi kami

Jika Anda memiliki pertanyaan tentang operasi DDL, hubungi kami.