Backup Locks 機能とバックアップ時の挙動について

2020年1月10日

この記事は最終更新から4年以上経過しています。内容が古くなっている可能性があります。

はじめに

Percona Server には Backup Locks という機能が存在しています。今回はこの機能および MySQL での実装方法について調査をおこないました。

概要

LOCK TABLES FOR BACKUP uses a new MDL lock type to block updates to non-transactional tables and DDL statements for all tables. If there is an active LOCK TABLES FOR BACKUP lock then all DDL statements and all updates to MyISAM, CSV, MEMORY, ARCHIVE, TokuDB, and MyRocks tables will be blocked in the Waiting for backup lock status, visible in PERFORMANCE_SCHEMA or PROCESSLIST.

この機能は Percona Server 5.6.16 から実装されており、非トランザクションテーブルへの DML や全てのテーブルへの DDL の実行にロックを取得します。これにより、それまで FLUSH TABLES WITH READ LOCK(以後 FTWRL) を実行することによってロックされていた DML を、以下のように並列実行できるようになりました。

[session1]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)

[session2]> INSERT INTO test.t1 VALUES(1);
(...ロック待ち...)
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

[session1]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

[session1]> LOCK TABLES FOR BACKUP;
Query OK, 0 rows affected (0.01 sec)

[session2]> INSERT INTO test.t1 VALUES(1);
Query OK, 1 row affected (0.02 sec)

[session1]> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.02 sec)

[session2]> INSERT INTO test.t1 VALUES(1);

(...ロック待ち...)

^C -- query aborted

ERROR 1317 (70100): Query execution was interrupted

[session1]> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

[session1]> LOCK TABLES FOR BACKUP;

Query OK, 0 rows affected (0.01 sec)

[session2]> INSERT INTO test.t1 VALUES(1);

Query OK, 1 row affected (0.02 sec)

そのため、この機能は Percona Xtrabackup(以下 Xtrabackup) や Percona Server で拡張された mysqldump の lock-for-backup オプションを使用する際に、FTWRL の代替として実行されています。なお、バックアップ取得先のサーバーが Backup locks をサポートしていない場合、FTWRL が実行されます。
また、LOCK TABLES FOR BACKUP 実行中に同じ接続で FTWRL を実行することはできないようです。

mysql> LOCK TABLES FOR BACKUP;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;
ERROR 7000 (HY000): Can't execute the query because you have a conflicting backup lock

mysql> LOCK TABLES FOR BACKUP;

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;

ERROR 7000 (HY000): Can't execute the query because you have a conflicting backup lock

このロックを解除する際には、UNLOCK TABLES を使用します。

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

1 2	mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

MySQL における実装

MySQL においては 8.0 から、同様の機能として LOCK INSTANCE FOR BACKUP 構文がサポートされるようになっています。

引用 : 13.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements

LOCK INSTANCE FOR BACKUP acquires an instance-level backup lock that permits DML during an online backup while preventing operations that could result in an inconsistent snapshot.

こちらも同様に、ファイルの作成や削除をおこなうような DDL の実行をロックし、DML についてはロックしません。なお、MySQL では LOCK INSTANCE 中でも FTWRL を実行することができます。また、ロックを解除する際には UNLOCK INSTANCE を使用します。

mysql> UNLOCK INSTANCE;
Query OK, 0 rows affected (0.00 sec)

1 2	mysql> UNLOCK INSTANCE; Query OK, 0 rows affected (0.00 sec)

MariaDB における実装

MariaDB においても、10.4.2 から BACKUP LOCK 機能が追加されており、同様に DDL の実行をロックすることができます。
また、ロックを解除する際には BACKUP UNLOCK を実行します。

引用 : BACKUP LOCK

BACKUP LOCK blocks a table from DDL statements. This is mainly intended to be used by tools like mariabackup that need to ensure there are no DDLs on a table while the table files are opened. For example, for an Aria table that stores data in 3 files with extensions .frm, .MAI and .MAD. Normal read/write operations can continue as normal.

なお、MariaDB には BACKUP STAGE という機能も存在しており、Mariabackup の実行時には、こちらの機能が使用されているようです。

バックアップでの使用ケース

上記の機能について、実際に物理バックアップツール(Xtrabackup、MySQL Enterprise Backup(以下 MEB)、Mariabackup)を使用して確認してみました。比較したバージョンは以下の通りです。
なお、公開されている rpm パッケージを使用してインストールをおこなっているため、他の MariaDB 関連パッケージと依存関係のある Mariabackup については MariaDB Server でのみ検証しています。

MySQL(Percona Server) 8.0.18
MySQL(Percona Server) 5.7.28
MariaDB Server 10.4.11 / 10.3.21

確認するのは、バックアップツール実行時に、すべてのInnoDB / XtraDBデータおよびログのバックアップを完了した後に実施される、MyISAM およびその他の InnoDB 以外のテーブルや .frm ファイルなどのバックアップ取得時の挙動となります。

※Xtrabackup の挙動に関する概要についてはこちら : How Percona XtraBackup Works

そのため、デフォルトの設定の他に、以下の内容を変更しています。一般クエリログを有効にしているので、どのようなコマンドでロックを取得しているかを比較していきたいと思います。

一般クエリログを有効(general_log = ON)
以下のような MyISAM(Aria) テーブルを作成

   mysql> SHOW CREATE TABLE test.t1\G
   *************************** 1. row ***************************
          Table: t1
   Create Table: CREATE TABLE <code>t1</code> (
id</code> int(11) DEFAULT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
   1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE test.t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE <code>t1</code> (

id</code> int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

結果

結果をまとめたところ、以下のようになりました。
なお、バージョンをまたいだバックアップの実施(例:MySQL 8.0.18 に対して MEB 4.1.3 を実行する、など)は全てエラーとなって実行できなかったため、結果から省いています。

結果として、概ね想定通りの挙動となりました。以下に、今回わかったことを記載していきます。

MEB 8.0 や Mariabackup 10.4 では、FTWRL の代わりに新しいロックが使用されている
Xtrabackup は Percona Server であれば Backup Locks 機能を使用するが、それ以外の場合は MySQL 8.0 などで別の代替手段(LOCK INSTANCE FOR BACKUP)を持っていても FTWRL を実行してしまう
Xtrabackup では、MyISAM テーブルが存在していなければ FTWRL の実行を回避できる
MariaBackup では BACKUP STAGE が優先され、BACKUP LOCK は使用されていない

まとめ

ここまで、FTWRL に代わるバックアップ用のロックについて調査してきました。Percona Server は 5.6 の頃から存在していた機能ですが、MySQL や MariaDB もようやく追いついてきた印象を受けます。
また、これらのコマンドは手動でも実行できるので、今まで FTWRL を使用していた場合、一考する価値があるかもしれません。