Async metadata lock replication

Prerequisites

• This feature is supported on all revision versions of PolarDB for MySQL 5.6 and 5.7 clusters.

• For PolarDB for MySQL 8.0 clusters, the revision version must be 8.0.1.1.10 or later. For more information, see Query the version number.

Function introduction

When you execute DDL operations on a database, metadata lock (MDL) information must be synchronized between the primary node and read-only nodes to ensure data definition consistency. However, DDL operations on the primary node often hold an MDL. This causes read-only nodes to wait for a long time to acquire the MDL for synchronization. Before the MDL is successfully synchronized, the read-only nodes stop parsing Redo Logs. This severely affects the overall efficiency of DDL operations.

With the async metadata lock replication feature, PolarDB decouples MDL synchronization from Redo Log parsing. This enables read-only nodes to continue parsing and applying Redo Logs even while waiting for the MDL to be synchronized.

Usage

• This feature is enabled by default. No additional action is required.

• On a read-only node, you can run the following SQL statement to retrieve information about MDL synchronization:

  SELECT * FROM INFORMATION_SCHEMA.INNODB_LOG_MDL_SLOT;

  Note

  • To run the preceding SQL statement, your PolarDB cluster must be one of the following versions:

    • PolarDB for MySQL 8.0.1 with a revision version of 8.0.1.1.24 or later

    • PolarDB for MySQL 5.7 with a revision version of 5.7.1.0.20 or later

    • PolarDB for MySQL 5.6 with a revision version of 5.6.1.0.33 or later

    • If your cluster version does not meet the preceding requirements, you must upgrade the cluster. For more information, see Manually upgrade a cluster version.

  • For more information about how to force a query to run on a specific node, see Hint syntax.

  The following result is returned:

  +---------+------------+-------------------+----------+-------------------+
  | slot_id | slot_state | slot_name         | slot_lsn | thread_id         |
  +---------+------------+-------------------+----------+-------------------+
  |       0 | SLOT_NONE  | no targeted table |        0 | no running thread |
  |       1 | SLOT_NONE  | no targeted table |        0 | no running thread |
  |       2 | SLOT_NONE  | no targeted table |        0 | no running thread |
  |       3 | SLOT_NONE  | no targeted table |        0 | no running thread |
  |       4 | SLOT_NONE  | no targeted table |        0 | no running thread |
  +---------+------------+-------------------+----------+-------------------+

  The output shows the details of the MDL information that is being synchronized. The slot_name column displays information about the related data tables. The slot_state column displays the current MDL synchronization status. The status can be one of the following:

  • SLOT_NONE: The initialization state.

  • SLOT_RESERVED: The read-only node has received a request to acquire an MDL and is waiting for the scheduling system to assign a worker thread to process the request.

  • SLOT_ACQUIRING: The system has assigned a worker thread, and the read-only node is sending the MDL request.

    Note

    If the MDL required by the read-only node is held by another connection, the MDL synchronization status remains in this state.

  • SLOT_LOCKED: The MDL has been acquired and is held on the read-only node.

  • SLOT_RELEASING: The read-only node has received a request to release the MDL and is waiting for the scheduling system to assign a worker thread to process the request.

• You can run the following SQL statement to retrieve the status of the worker threads used for MDL request synchronization on a read-only node:

  SELECT * FROM INFORMATION_SCHEMA.INNODB_LOG_MDL_THREAD;

  Note

  • To run the preceding SQL statement, your PolarDB cluster must be one of the following versions:

    • PolarDB for MySQL 8.0.1 with a revision version of 8.0.1.1.24 or later

    • PolarDB for MySQL 5.7 with a revision version of 5.7.1.0.20 or later

    • PolarDB for MySQL 5.6 with a revision version of 5.6.1.0.33 or later

    • If your cluster version does not meet the preceding requirements, you must upgrade the cluster. For more information, see Manually upgrade a cluster version.

  • For more information about how to force a query to run on a specific node, see Hint syntax.

  The following result is returned:

  +-----------+-----------+------------------+-------------------+----------+
  | thread_id | thr_state | slot_state       | slot_name         | slot_lsn |
  +-----------+-----------+------------------+-------------------+----------+
  |         0 | free      | not in acquiring | no targeted table |        0 |
  |         1 | free      | not in acquiring | no targeted table |        0 |
  |         2 | free      | not in acquiring | no targeted table |        0 |
  |         3 | free      | not in acquiring | no targeted table |        0 |
  +-----------+-----------+------------------+-------------------+----------+

  If the INNODB_LOG_MDL_SLOT table contains an MDL synchronization request with the SLOT_ACQUIRING status, a corresponding worker thread exists in the INNODB_LOG_MDL_THREAD table to process the request. In this case, the value of thr_state is not free. This indicates that an MDL wait may be occurring on the read-only node. You can check whether the value of thr_state is free to troubleshoot MDL blocking issues.

Contact us

If you have any questions about DDL operations, you can search for the DingTalk group number and join the group for consultation. You can directly @ the experts in the group with your questions. The group also has a PolarDB for MySQL assistant online 24/7 to answer your questions. DingTalk group number: 15375044501.