PolarDB supports the Async Metadata Lock Replication feature to improve the execution efficiency of DDL operations. This topic describes the Async Metadata Lock Replication feature.

Prerequisites

  • For PolarDB for MySQL 5.6 and 5.7 clusters, all the revision versions support this feature.
  • The revision version for a PolarDB for MySQL 8.0 cluster is 8.0.1.1.10 or later. For more information about how to determine the cluster version, see Query the kernel version number.

Feature description

When you perform data definition language (DDL) operations on a database, you must synchronize metadata lock (MDL) information between the primary node and read-only nodes to make data definitions consistent. However, DDL operations on the primary node often occupy MDLs. As a result, read-only nodes can obtain MDL Information for synchronization after they wait for the information for a long time. Before the MDL information is synchronized, read-only nodes no longer parse redo logs. This seriously affects the overall efficiency of DDL operations.

PolarDB decouples MDL synchronization from redo log parsing by using the Async Metadata Lock Replication feature. This way, read-only nodes can still parse and apply redo logs even when the read-only nodes wait for metadata lock information that is to be synchronized.

Usage method

  • By default, this feature is enabled. No additional operation is required.
  • You can execute the following SQL statement on a read-only node to retrieve the information that is related to MDL synchronization from the read-only node:
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOG_MDL_SLOT;
    Note For information about how to forcibly specify that a query command is run on a 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 |
    +---------+------------+-------------------+----------+-------------------+
    This table lists the details of the MDL Information that is being synchronized. In this table, the slot_name column shows the relevant data table information and the slot_state column shows the information about the current MDL synchronization status, including:
    • SLOT_NONE: the initialization state.
    • SLOT_RESERVED: The read-only node has received the request for retrieving the MDL information and is waiting for a worker thread that is assigned by the scheduling system to process this request.
    • SLOT_ACQUIRING: The system has allocated 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 other connections, MDL synchronization remains in this state.
    • SLOT_LOCKED: The metadata lock on the read-only node has been retrieved and held.
    • SLOT_RELEASING: The read-only node has received an MDL release request and is waiting for a worker thread that is assigned by the scheduling system to process the request.
  • You can execute the following SQL statement to retrieve the status information about the worker thread that is used by the read-only node to process the MDL synchronization request:
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOG_MDL_THREAD;

    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 an MDL synchronization request whose state is SLOT_ACQUIRING exists in the INNODB_LOG_MDL_SLOT table, a corresponding worker thread is available in the INNODB_LOG_MDL_THREAD table to process the request. This indicates that the value of the thr_state parameter is not free. This case shows that the read-only node waits for the MDL at a high probability. You can check whether an MDL is occupied based on whether the value of the thr_state parameter is free.