Async metadata lock replication

Prerequisites

• For PolarDB for MySQL 5.6 and 5.7 clusters, all revision versions support this feature.

• For PolarDB for MySQL 8.0 clusters, the revision version must be 8.0.1.1.10 or later. To check the cluster version, see Query the engine version.

Overview

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 MDLs, which causes read-only nodes to wait for a long time before they can obtain the MDL information for synchronization. Before the MDL information is synchronized, 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 allows read-only nodes to continue parsing and applying redo logs even while waiting for MDL information to be synchronized.

Usage

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

• You can run the following SQL statement on a read-only node to obtain MDL synchronization information for the target read-only node:

  SELECT * FROM INFORMATION_SCHEMA.INNODB_LOG_MDL_SLOT;

  Note

  • PolarDB clusters must run one of the following versions for you to have permission to run the preceding SQL statement:

    • PolarDB for MySQL 8.0.1 with revision version 8.0.1.1.24 or later

    • PolarDB for MySQL 5.7 with revision version 5.7.1.0.20 or later

    • PolarDB for MySQL 5.6 with revision version 5.6.1.0.33 or later

    • If your cluster version does not meet the preceding requirements, we recommend that you upgrade your cluster. For more information, see Upgrade the version.

  • For more information about how to force a query to run on a specified 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 table shows the details of the MDL information that is being synchronized. The slot_name column shows information about the related data tables. The slot_state column shows the current MDL synchronization status, which can be one of the following:

  • SLOT_NONE: The initialization state.

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

  • SLOT_ACQUIRING: The system has assigned worker thread resources, 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 an MDL release request and is waiting for the scheduling system to assign a worker thread to process the request.

• You can run the following SQL statement to obtain the status of the worker threads that are used to synchronize MDL requests on a read-only node:

  SELECT * FROM INFORMATION_SCHEMA.INNODB_LOG_MDL_THREAD;

  Note

  • PolarDB clusters must run one of the following versions for you to have permission to run the preceding SQL statement:

    • PolarDB for MySQL 8.0.1 with revision version 8.0.1.1.24 or later

    • PolarDB for MySQL 5.7 with revision version 5.7.1.0.20 or later

    • PolarDB for MySQL 5.6 with revision version 5.6.1.0.33 or later

    • If your cluster version does not meet the preceding requirements, we recommend that you upgrade your cluster. For more information, see Upgrade the version.

  • For more information about how to force a query to run on a specified 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 whose status is SLOT_ACQUIRING, the INNODB_LOG_MDL_THREAD table contains a corresponding worker thread that processes the request. In this case, the value of thr_state is not free. This indicates that an MDL wait is likely to occur 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.