2020-08-20

AWS Aurora MySQL v5.6 から v5.7 へのアップグレードをちょっとだけ考えてみた

Aurora

Aurora MySQL v5.6 のサポート期限がいつかわからない

現在、Auroraでは、MySQL v5.6 (Engine 1.x) とv5.7互換 (Engine 2.x) のデータベースが利用できます。

Oracleが開発しているオリジナルのMySQL v5.6(以降 Vanilla MySQLと記載) は、2021/02 にサポートが切れます。 Aurora MySQL は Amazonが独自に開発している互換DBです。Vanilla MySQLのサポート期限はAuroraには関係ないのですが、互換DBであることを踏まえると、「同じようなタイミングでサポート切れになる可能性もあるのでは・・・」と心配になってしまいます。

例えば、RHEL6互換のAmazon LinuxはRHEL6のサポート期限(2020/11末)と近しいタイミングでサポートが終了になっているようです。

aws.amazon.com

一方で、Aurora のMySQLの最新バージョン(8.0) へのキャッチアップは、まだされていません(RDS MySQLはMySQL v8.0利用可)。MySQL v8.0は2年以上前（2018/04）にリリースされています。

Amazon が Aurora を常に最新のMySQLにキャッチアップさせ続ける気があるのかどうか、よくわからないです。 v8.0へはキャッチアップせず、v5.6/v5.7 互換のバージョンをずっと継続していくのかもしれません。

ようするに、Aurora v5.6がいつまでサポートされるのか、いつかはサポートが切れるのか、よくわかりませんでした。

補足しておくと、Aurora には LTS(長期サポート)リリースのバージョンがあり、LTSに指定されたバージョンのリリースから最低でも１年はサポートが継続されると明記されています。ある日突然サポートが打ち切られるということはないでしょう。 docs.aws.amazon.com

メリットが限られているアップグレード

MySQL v5.7 の新機能のうち、いくつかは Aurora の制限により使えないとされています。これらは、「使えない」というよりは、Aurora 独自に解決策がすでにあるため、「使う必要がない」と言えるものが多くあります。例えば、「起動時の InnoDB バッファープールのロード」は同じ役割をはたす機能が Aurora v5.6の時点で既に実装されています。

docs.aws.amazon.com

グループのレプリケーションプラグイン
ページサイズの増加
起動時の InnoDB バッファープールのロード
InnoDB フルテキストパーサープラグイン
マルチソースレプリケーション
オンラインバッファープールのサイズ変更

Auroraのアーキテクチャや制限を踏まえると、v5.7にアップグレードして得られるメリットは主に以下でしょうか。オンプレでのアップグレード体験と比較すると、ちょっと物足りなさを感じますね。。。(それだけ、Auroraが既に十分、賢いとも言える)

JSON型
Generated Column （生成列）
VARCHARの長さの拡張がオンラインDDLで対応可能

アップグレード方法

とはいえ、いつアップグレードすることになっても良いよう、方法を検討してみました。

インプレース

~~DBを停止し、現在利用しているサーバをそのままアップグレードする方法です。現時点ではAuroraではサポートされていないようです。~~

サポートされました！

dev.classmethod.jp

ダンプ・インポート

mysqldumpでデータを抜いて、v5.7へインポートする。シンプルでわかりやすいやり方ですが、データ量が多いとすごく時間がかかります。

スナップショット復元

Aurora MySQL v5.6 のスナップショットから v5.7 の新規DBインスタンスを作成することができます。 DBへの書き込みを停止し→スナップショットの取得→新規DBインスタンスの作成→アプリからの接続先変更という流れになります。

移行先を事前に作成しておけないので、当日、間接的な部分も含めて完了させないといけない点がデメリットでしょうか。例えば、監視設定とかセキュリティまわりなど。

v5.7のスナップショットからv5.6を作成すること(ダウングレード)はできないようです。万が一の切り戻し時には他の手段を取る必要があります。

レプリケーション

v5.6/v5.7 のAuroraクラスタ間でレプリケーションをしておく。データを事前に連携しておくので、停止時間が短く済みます。事前にv5.7側に参照を向けておいて実績を作れるというメリットもありますが、v5.6→v5.7間で普段発生しないレプリケーション遅延が発生する可能性があります。

まとめ

Amazonさん、Auroraのライフサイクルポリシーをハッキリしてほしいです（見落としてたらごめん）
アップグレード方法はスナップショットかレプリケーションが良さそうだが、実際に手を動かしてもうちょっと考えたい

2020-08-15

MySQL Shell dumpInstance でバックアップ中はDDL が待機させられる

MySQL

MySQL Sever Blog に　MySQL Shell 8.0.21 の Dump / Import 機能についてエントリーが投稿されています。並列化により非常に高速に Dump / Import 出来ることなどが示されてます。非常に強力なツールであることが感じられます。

MySQL Shell Dump & Load part 1: Demo! | MySQL Server Blog

とりあえず動かしてみる

\help util.dumpInstance で説明が見れます。 threads や maxRate オプションでパフォーマンスの調整ができそうです。

$ mysqlsh root@localhost
mysqlsh > \help util.dumpInstance
NAME
      dumpInstance - Dumps the whole database to files in the output directory.

SYNTAX
      util.dumpInstance(outputUrl[, options])

WHERE
      outputUrl: Target directory to store the dump files.
      options: Dictionary with the dump options.

DESCRIPTION
      The outputUrl specifies where the dump is going to be stored.

      By default, a local directory is used, and in this case outputUrl can be
      prefixed with file:// scheme. If a relative path is given, the absolute
      path is computed as relative to the current working directory. If the
      output directory does not exist but its parent does, it is created. If
      the output directory exists, it must be empty. All directories created
      during the dump will have the following access rights (on operating
      systems which support them): rwxr-x---. All files created during the dump
      will have the following access rights (on operating systems which support
      them): rw-r-----.

      The following options are supported:
      - excludeSchemas: list of strings (default: empty) - list of schemas to
        be excluded from the dump.
      - excludeTables: list of strings (default: empty) - List of tables to be
        excluded from the dump in the format of schema.table.
      - users: bool (default: true) - Include users, roles and grants in the
        dump file.
      - events: bool (default: true) - Include events from each dumped schema.
      - routines: bool (default: true) - Include functions and stored
        procedures for each dumped schema.
      - triggers: bool (default: true) - Include triggers for each dumped
        table.
      - tzUtc: bool (default: true) - Convert TMESTAMP data to UTC.
      - consistent: bool (default: true) - Enable or disable consistent data
        dumps.
      - ddlOnly: bool (default: false) - Only dump Data Definition Language
        (DDL) from the database.
      - dataOnly: bool (default: false) - Only dump data from the database.
      - dryRun: bool (default: false) - Print information about what would be
        dumped, but do not dump anything.
      - ocimds: bool (default: false) - Enable checks for compatibility with
        MySQL Database Service (MDS)
      - compatibility: list of strings (default: empty) - Apply MySQL Database
        Service compatibility modifications when writing dump files. Supported
        values: "force_innodb", "strip_definers", "strip_restricted_grants",
        "strip_role_admin", "strip_tablespaces".
      - chunking: bool (default: true) - Enable chunking of the tables.
      - bytesPerChunk: string (default: "32M") - Sets average estimated number
        of bytes to be written to each chunk file, enables chunking.
      - threads: int (default: 4) - Use N threads to dump data chunks from the
        server.
      - maxRate: string (default: "0") - Limit data read throughput to maximum
            - maxRate: string (default: "0") - Limit data read throughput to maximum
        rate, measured in bytes per second per thread. Use maxRate="0" to set
        no limit.
      - showProgress: bool (default: true if stdout is a TTY device, false
        otherwise) - Enable or disable dump progress information.
      - compression: string (default: "zstd") - Compression used when writing
        the data dump files, one of: "none", "gzip", "zstd".
      - defaultCharacterSet: string (default: "utf8mb4") - Character set used
        for the dump.
      - osBucketName: string (default: not set) - Use specified OCI bucket for
        the location of the dump.
      - osNamespace: string (default: not set) - Specify the OCI namespace
        (tenancy name) where the OCI bucket is located.
      - ociConfigFile: string (default: not set) - Use the specified OCI
        configuration file instead of the one in the default location.
      - ociProfile: string (default: not set) - Use the specified OCI profile
        instead of the default one.
<snip>

引数に出力先のディレクトリを指定して実行します。

$ mysqlsh root@localhost
mysqlsh> util.dumpInstance("/tmp/dumpdir")

dump時はどんなSQLが流れるのか

一般クエリログを有効にし、ダンプ処理中に流れるSQLを観測してみました。テスト用に小さなテーブル（lockt）を１つ用意してます。

mysql> SHOW TABLES FROM test;
+----------------+
| Tables_in_test |
+----------------+
| lockt          |
+----------------+
1 row in set (0.01 sec)

一般クエリログに出力されたログの Id 列に注目すると、コネクションIDが17〜22の5本のコネクションにより並列にバックアップ処理が行われていることがわかります。

$ tail -n +3 general.log  | awk '{ print $2 }' | sort | uniq
17
18
19
20
21
22
Id

コネクションごとにログを分解するとこんな感じ。

コネクションID 17: ポート番号確認

$ grep '17 ' general.log
2020-08-15T04:03:37.955055Z    17 Query select schema()
2020-08-15T04:04:03.138587Z    17 Query SELECT @@GLOBAL.port
2020-08-15T04:04:04.708654Z    17 Quit

コネクションID 18: FLUSH TABLE WITH READ LOCK と LOCK INSTANCE FOR BACKUP でロックする

$ grep '18 ' general.log
2020-08-15T04:04:03.156406Z    18 Connect   root@localhost on  using SSL/TLS
2020-08-15T04:04:03.156714Z    18 Query SET SQL_MODE = ''
2020-08-15T04:04:03.156883Z    18 Query SET NAMES 'utf8mb4'
2020-08-15T04:04:03.157033Z    18 Query SET SESSION net_write_timeout = 300
2020-08-15T04:04:03.157150Z    18 Query SET TIME_ZONE = '+00:00'
2020-08-15T04:04:03.157338Z    18 Query SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'ndbinfo', 'performance_schema', 'sys')
2020-08-15T04:04:03.159080Z    18 Query SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test' AND TABLE_TYPE = 'BASE TABLE'
2020-08-15T04:04:03.160512Z    18 Query SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'test' AND TABLE_TYPE = 'VIEW'
2020-08-15T04:04:03.161561Z    18 Query SELECT TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'lockt'
2020-08-15T04:04:03.162400Z    18 Query SELECT @@GLOBAL.HOSTNAME
2020-08-15T04:04:03.162565Z    18 Query SELECT @@GLOBAL.VERSION
2020-08-15T04:04:03.162733Z    18 Query SELECT @@GLOBAL.GTID_EXECUTED
2020-08-15T04:04:03.163244Z    18 Query select * from (select distinct grantee, substr(grantee, 2, length(grantee)-locate('@', reverse(grantee))-2) as User, substr(grantee, length(grantee)-locate('@', reverse(grantee))+3, locate('@', reverse(grantee))-3) as host from information_schema.user_privileges order by user, host) as Users where Users.User not like 'mysql.infoschema' AND Users.User not like 'mysql.session' AND Users.User not like 'mysql.sys'
2020-08-15T04:04:03.164371Z    18 Query USE `test`
2020-08-15T04:04:03.164583Z    18 Query show events
2020-08-15T04:04:03.165572Z    18 Query USE `test`
2020-08-15T04:04:03.165755Z    18 Query SHOW FUNCTION STATUS WHERE Db = 'test'
2020-08-15T04:04:03.166877Z    18 Query USE `test`
2020-08-15T04:04:03.167059Z    18 Query SHOW PROCEDURE STATUS WHERE Db = 'test'
2020-08-15T04:04:03.168525Z    18 Query FLUSH TABLES WITH READ LOCK
2020-08-15T04:04:03.209360Z    18 Query /*!80000 LOCK INSTANCE FOR BACKUP */
2020-08-15T04:04:03.209530Z    18 Query UNLOCK TABLES
2020-08-15T04:04:03.210561Z    18 Query select * from (select distinct grantee, substr(grantee, 2, length(grantee)-locate('@', reverse(grantee))-2) as User, substr(grantee, length(grantee)-locate('@', reverse(grantee))+3, locate('@', reverse(grantee))-3) as host from information_schema.user_privileges order by user, host) as Users where Users.User not like 'mysql.infoschema' AND Users.User not like 'mysql.session' AND Users.User not like 'mysql.sys'
2020-08-15T04:04:03.211197Z    18 Query SHOW CREATE USER 'root'@'localhost'
2020-08-15T04:04:03.211977Z    18 Query SHOW GRANTS FOR 'root'@'localhost'
2020-08-15T04:04:03.212358Z    18 Query SELECT INDEX_NAME, COLUMN_NAME FROM information_schema.statistics WHERE NON_UNIQUE = 0 AND SEQ_IN_INDEX = 1 AND TABLE_SCHEMA = 'test' AND TABLE_NAME = 'lockt'
2020-08-15T04:04:03.240608Z    18 Quit

コネクションID 19: カラム名やトリガーの数などメタデータの収集

$ grep '19 ' general.log
2020-08-15T04:04:03.206269Z    19 Connect   root@localhost on  using SSL/TLS
2020-08-15T04:04:03.207182Z    19 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2020-08-15T04:04:03.207402Z    19 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2020-08-15T04:04:03.207531Z    19 Query SET SQL_MODE = ''
2020-08-15T04:04:03.207673Z    19 Query SET NAMES 'utf8mb4'
2020-08-15T04:04:03.207802Z    19 Query SET SESSION net_write_timeout = 300
2020-08-15T04:04:03.208020Z    19 Query SET TIME_ZONE = '+00:00'
2020-08-15T04:04:03.215139Z    19 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2020-08-15T04:04:03.233438Z    19 Query USE `test`
2020-08-15T04:04:03.233704Z    19 Query show table status like 'lockt'
2020-08-15T04:04:03.235205Z    19 Query SET SQL_QUOTE_SHOW_CREATE=1
2020-08-15T04:04:03.235319Z    19 Query SET SESSION character_set_results = 'binary'
2020-08-15T04:04:03.235398Z    19 Query show create table `lockt`
2020-08-15T04:04:03.235584Z    19 Query SET SESSION character_set_results = 'utf8mb4'
2020-08-15T04:04:03.235685Z    19 Query show fields from `lockt`
2020-08-15T04:04:03.236751Z    19 Query select count(TRIGGER_NAME) from information_schema.triggers where TRIGGER_SCHEMA = 'test' and EVENT_OBJECT_TABLE = 'lockt'
2020-08-15T04:04:03.240935Z    19 Quit

コネクションID 20: SELECTでデータを抜き出す

SELECT SQL_NO_CACHE `pk`,`name`,`favorite`,`addr` FROM `test`.`lockt` WHERE `pk` BETWEEN 1 AND 4 OR `pk` IS NULL ORDER BY `pk` /* mysqlsh dumpInstance, dumping table `test`.`lockt`, chunk ID: 0 */

と BETWEEN を使って、データを分割して並列でバックアップする作りになっていることが読み取れます（今回はデータが小さすぎるので、データのダンプは並列されずこの１つのSELECTのみで終わってます）。

$ grep '20 ' general.log
2020-08-15T04:04:03.205716Z    20 Connect   root@localhost on  using SSL/TLS
2020-08-15T04:04:03.206984Z    20 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2020-08-15T04:04:03.207611Z    20 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2020-08-15T04:04:03.207739Z    20 Query SET SQL_MODE = ''
2020-08-15T04:04:03.207950Z    20 Query SET NAMES 'utf8mb4'
2020-08-15T04:04:03.208088Z    20 Query SET SESSION net_write_timeout = 300
2020-08-15T04:04:03.208156Z    20 Query SET TIME_ZONE = '+00:00'
2020-08-15T04:04:03.219989Z    20 Query SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE EXTRA <> 'VIRTUAL GENERATED' AND EXTRA <> 'STORED GENERATED' AND TABLE_SCHEMA = 'test' AND TABLE_NAME = 'lockt' ORDER BY ORDINAL_POSITION
2020-08-15T04:04:03.221376Z    20 Query USE `test`
2020-08-15T04:04:03.222424Z    20 Query SHOW TRIGGERS LIKE 'lockt'
2020-08-15T04:04:03.224205Z    20 Query SET SESSION character_set_results = 'binary'
2020-08-15T04:04:03.224505Z    20 Query SELECT COLUMN_NAME,                 JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')               FROM information_schema.COLUMN_STATISTICS               WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'lockt'
2020-08-15T04:04:03.224985Z    20 Query SET SESSION character_set_results = 'utf8mb4'
2020-08-15T04:04:03.225329Z    20 Query SELECT SQL_NO_CACHE MIN(`pk`), MAX(`pk`) FROM `test`.`lockt`
2020-08-15T04:04:03.227138Z    20 Query SELECT AVG_ROW_LENGTH FROM information_schema.tables WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'lockt'
2020-08-15T04:04:03.230184Z    20 Query SELECT SQL_NO_CACHE `pk`,`name`,`favorite`,`addr` FROM `test`.`lockt` WHERE `pk` BETWEEN 1 AND 4 OR `pk` IS NULL ORDER BY `pk` /* mysqlsh dumpInstance, dumping table `test`.`lockt`, chunk ID: 0 */
2020-08-15T04:04:03.231534Z    20 Quit

コネクションID 21: ファンクションやプロシージャを抜き出す

$ grep '21 ' general.log
/usr/sbin/mysqld, Version: 8.0.21 (MySQL Community Server - GPL). started with:
2020-08-15T04:04:03.205102Z    21 Connect   root@localhost on  using SSL/TLS
2020-08-15T04:04:03.206485Z    21 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2020-08-15T04:04:03.206782Z    21 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2020-08-15T04:04:03.207065Z    21 Query SET SQL_MODE = ''
2020-08-15T04:04:03.207263Z    21 Query SET NAMES 'utf8mb4'
2020-08-15T04:04:03.207343Z    21 Query SET SESSION net_write_timeout = 300
2020-08-15T04:04:03.207467Z    21 Query SET TIME_ZONE = '+00:00'
2020-08-15T04:04:03.214462Z    21 Query SHOW CREATE DATABASE IF NOT EXISTS test
2020-08-15T04:04:03.221139Z    21 Query USE `test`
2020-08-15T04:04:03.221476Z    21 Query show events
2020-08-15T04:04:03.223192Z    21 Query LOCK TABLES mysql.proc READ
2020-08-15T04:04:03.224379Z    21 Query use `test`
2020-08-15T04:04:03.224863Z    21 Query select @@collation_database
2020-08-15T04:04:03.225075Z    21 Query SET SESSION character_set_results = 'binary'
2020-08-15T04:04:03.225726Z    21 Query USE `test`
2020-08-15T04:04:03.225833Z    21 Query SHOW FUNCTION STATUS WHERE Db = 'test'
2020-08-15T04:04:03.229322Z    21 Query USE `test`
2020-08-15T04:04:03.229487Z    21 Query SHOW PROCEDURE STATUS WHERE Db = 'test'
2020-08-15T04:04:03.231929Z    21 Query SET SESSION character_set_results = 'utf8mb4'
2020-08-15T04:04:03.232081Z    21 Query UNLOCK TABLES
2020-08-15T04:04:03.233234Z    21 Quit

コネクションID 22: 何もしてない（おそらくデータ量が少なかったため）

$ grep '22 ' general.log
2020-08-15T04:04:03.208791Z    22 Connect   root@localhost on  using SSL/TLS
2020-08-15T04:04:03.208917Z    22 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2020-08-15T04:04:03.208987Z    22 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2020-08-15T04:04:03.209066Z    22 Query SET SQL_MODE = ''
2020-08-15T04:04:03.209126Z    22 Query SET NAMES 'utf8mb4'
2020-08-15T04:04:03.209195Z    22 Query SET SESSION net_write_timeout = 300
2020-08-15T04:04:03.209252Z    22 Query SET TIME_ZONE = '+00:00'
2020-08-15T04:04:03.229120Z    22 Quit

どうやって一貫性を保ったバックアップを実現しているのか

\help util.dumpInstance で表示される説明には consistent オプションの記載があります（デフォルトは true）。dumpInstance では一貫性を持ったバックアップ*1が取れるようです。複数のコネクション（スレッド）が一貫性を持ったバックアップを取るためにどのように連携しているのか確認してみました。

$ egrep 'Time|FLUSH|UNLOCK|LOCK|SELECT .* FROM `test`.`lockt`|START' general.log  | grep -v '21 '
Time                 Id Command    Argument
2020-08-15T04:04:03.168525Z        18 Query     FLUSH TABLES WITH READ LOCK

2020-08-15T04:04:03.207402Z        19 Query     START TRANSACTION WITH CONSISTENT SNAPSHOT
2020-08-15T04:04:03.207611Z        20 Query     START TRANSACTION WITH CONSISTENT SNAPSHOT
2020-08-15T04:04:03.208987Z        22 Query     START TRANSACTION WITH CONSISTENT SNAPSHOT

2020-08-15T04:04:03.209360Z        18 Query     /*!80000 LOCK INSTANCE FOR BACKUP */
2020-08-15T04:04:03.209530Z        18 Query     UNLOCK TABLES

2020-08-15T04:04:03.225329Z        20 Query     SELECT SQL_NO_CACHE MIN(`pk`), MAX(`pk`) FROM `test`.`lockt`
2020-08-15T04:04:03.230184Z        20 Query     SELECT SQL_NO_CACHE `pk`,`name`,`favorite`,`addr` FROM `test`.`lockt` WHERE `pk` BETWEEN 1 AND 4 OR `pk` IS NULL ORDER BY `pk` /* mysqlsh dumpInstance, dumping table `test`.`lockt`, chunk ID: 0 */

まず、FLUSH TABLES WITH READ LOCK (FTWRL) を使って、データベース全体の静止点を設けています（コネクションID: 18）。各ダンプスレッド（コネクションID: 19-22）が、START TRANSACTION WITH CONSISTENT SNAPSHOT を実行し、静止点からトランザクションを開始します。各ダンプスレッドがトランザクションを開始し、静止点からSELECTを実行する準備ができたら、FTWRLの役割は終わりです。UNLOCK TABLESで、全体ロックを開放します。

FTWRL から UNLOCK TABLES するまでの間、データベースに書き込むことはできませんが、通常は一瞬で終わります。バックアップ中にデータベースの読み書きができるように工夫されてますね。

LOCK INSTANCE FOR BACKUP

見慣れない LOCK INSTANCE FOR BACKUP が出てきました。これは、MySQL 8.0 から登場した機能です。バックアップ中にテーブル定義が変更されてしまうと、バックアップが失敗してしまうため、DDLの実行をブロックしてくれます。 LOCK INSTANCE FOR BACKUP されているデータベースに対して、DDLを実行すると Waiting for backup lock で待たされます。

mysql> SHOW PROCESSLIST;
+----+-----------------+<snip>-------------------------+------------------+
| Id | User            |<snip> State                   | Info             |
+----+-----------------+<snip>-------------------------+------------------+
|  5 | event_scheduler |<snip> Waiting on empty queue  | NULL             |
| 36 | root            |<snip> starting                | SHOW PROCESSLIST |
| 39 | root            |<snip>                         | NULL             |
| 40 | root            |<snip> Waiting for backup lock | truncate test.t2 |
+----+-----------------+<snip>-------------------------+------------------+
4 rows in set (0.00 sec)

DDLが待機している状態で、対象テーブルにDMLを実行することは可能でした。

mysql> INSERT INTO test.t2 VALUES();
Query OK, 1 row affected (0.01 sec)

metadata lock のようにDDLに続く後続のクエリが待機してしまう事はなさそうです（よかった！）。

まとめ

MySQL Shell の dump / load Utility は並列バックアップなど強力なツールっぽい
FLUSH TABLES WITH READ LOCK と LOCK INSTANCE FOR BACKUP を使って、一貫性の持ったバックアップを確実に取れるように工夫されてる
dump 中は DDLが待機させられるので注意

*1:すべてのテーブルで同じ時点のデータがダンプされているバックアップを「一貫性を持ったバックアップ」と呼んでいます

2020-08-14

MySQL のロック範囲は実行計画で変わるという話

MySQL

最近、ANDPADでデータベース周りの技術顧問をさせて頂いています。ANDPADのエンジニアの皆さんから「データベースのロックまわりを詳しく知りたい！」というお話を受けて、先日、ロック周りの社内勉強会を開催しました。

SQLでは一般的なプログラミング言語と違って、ロックの制御を明示的に記述しません。ロックは暗黙的に（自動的に）データベースが必要なロックを獲得します。データベースのロックが　わかりにくい・むずかしい　と言われることが多いのはこういった背景があると思います。

MySQL のロック範囲は実行計画で変わる

更新対象の行がロックされるのは予測が付く方が多いと思います。しかし、MySQL（InnoDB）では更新対象でなくても行がロックされることがあります。

このようなサンプルデータを使って説明します。

mysql> CREATE TABLE `lockt` (
    ->  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->  `name` varchar(255) DEFAULT NULL,
    ->  `favorite` varchar(255) DEFAULT NULL,
    ->  `addr` varchar(255) DEFAULT NULL,
    -> PRIMARY KEY (`pk`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM lockt;
+----+-------+----------+---------+
| pk | name  | favorite | addr    |
+----+-------+----------+---------+
|  1 | Taro  | Apple    | Tokyo   |
|  2 | Jiro  | Orange   | Tokyo   |
|  3 | Alice | Orange   | London  |
|  4 | John  | Pine     | NewYork |
+----+-------+----------+---------+
4 rows in set (0.00 sec)

MySQL 8.0 で登場した、FOR UPDATE SKIP LOCKED を使って、どの行がロックしたのかを確かめます。FOR UPDATE SKIP LOCKED はロックされている行を除外して、SELECTすることが出来る機能です。

-- 何もロックがない状態ではすべてのレコードが取れる
mysql> SELECT * FROM lockt FOR UPDATE SKIP LOCKED;
+----+-------+----------+---------+
| pk | name  | favorite | addr    |
+----+-------+----------+---------+
|  1 | Taro  | Apple    | Tokyo   |
|  2 | Jiro  | Orange   | Tokyo   |
|  3 | Alice | Orange   | London  |
|  4 | John  | Pine     | NewYork |
+----+-------+----------+---------+
4 rows in set (0.00 sec)

favorite = 'Orange' and addr = 'Tokyo'の pk=2 の1行のみを更新してみます。

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

mysql> UPDATE lockt SET favorite = 'Banana' WHERE favorite = 'Orange' AND addr = 'Tokyo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

上記のUPDATEがロックを取っている状態で、FOR UPDATE SKIP LOCKED で確認します。

mysql> SELECT * FROM lockt FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)

関係のない pk=1,3,4 の行も含めて、全部の行がロックされてしまいました！

インデックスを追加します

次に、favorite カラムにインデックスを追加して、ロック範囲がどう変化するか確認してみます。

mysql> CREATE INDEX idx_favorite ON lockt(favorite);
Query OK, 0 rows affected (0.02 sec)

今度は、favorite = 'Orange'の行がロックされました。まだ、関係のない pk = 3 の行がロックされてしまっています。

mysql> SELECT * FROM lockt FOR UPDATE SKIP LOCKED;
+----+------+----------+---------+
| pk | name | favorite | addr    |
+----+------+----------+---------+
|  1 | Taro | Apple    | Tokyo   |
|  4 | John | Pine     | NewYork |
+----+------+----------+---------+
2 rows in set (0.00 sec)

インデックスを favorite と addr の複合キーにします。

mysql> CREATE INDEX idx_favorite_addr ON lockt(favorite, addr);
Query OK, 0 rows affected (0.03 sec)

今度は、無事、pk = 2の行だけがロックされました！

mysql> SELECT * FROM lockt FOR UPDATE SKIP LOCKED;
+----+-------+----------+---------+
| pk | name  | favorite | addr    |
+----+-------+----------+---------+
|  1 | Taro  | Apple    | Tokyo   |
|  3 | Alice | Orange   | London  |
|  4 | John  | Pine     | NewYork |
+----+-------+----------+---------+
3 rows in set (0.00 sec)

実行計画を比べる

すべてロックされてしまったケースでは、インデックスでの絞り込みが行われてません。更新対象の行を特定するためにテーブル全体をスキャン（type=index）してしまっています。

mysql> EXPLAIN UPDATE lockt SET favorite = 'Banana' WHERE favorite = 'Orange' AND addr = 'Tokyo' \G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: lockt
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where

インデックスを追加した場合は、rangeスキャンになり、該当の行をインデックスを使って絞り込めていることがわかります。

mysql> EXPLAIN UPDATE lockt SET favorite = 'Banana' WHERE favorite = 'Orange' AND addr = 'Tokyo' \G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: lockt
   partitions: NULL
         type: range
possible_keys: idx_favorite,idx_favorite_addr
          key: idx_favorite_addr
      key_len: 2046
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using temporary
1 row in set, 1 warning (0.00 sec)

もっと詳しい内容はANDPADのTechblogで

という様に、MySQLでは実行計画がSQLのパフォーマンスだけでなくロック範囲にも影響を及ぼすことを説明しました。より詳しい内容はANDPADのTechblogに掲載されています。

tech.andpad.co.jp

2020-08-11

PHP mysqli ドライバの max_persistent 設定の使い所がよくわからなかった件

PHP MySQL

TL;DR

PHP の mysqli.max_persistent で指定できるのは、HTTPワーカ プロセスあたり のコネクション数だった
- 「全部で何個まで」っていう制限値じゃなかった

PHP mysqli.max_persistent 設定

Persistent Connections まわりのエントリーを先日書きました。

mita2db.hateblo.jp

@do_aki さんに、PHPの mysqli ドライバには以下のようなコネクション数をコントロールする設定があると教えてもらいました。

mysqli.max_persistent integer Maximum of persistent connections that can be made. Set to 0 for unlimited.

https://www.php.net/manual/en/mysqli.configuration.php#ini.mysqli.max-persistent

これを使えば、PHPからDBサーバへ貼るコネクション数をコントロールできるのかしら？

試します

mysqli.max_persistent=1 に設定して、簡単なプログラムを実行します。ちなみに、mysqli ドライバで持続的接続(Persistent Connections) を使うには、ホスト名に p: とプレフィックスを付ける必要があります。

$ cat /etc/php.d/30-mysqli.ini
mysqli.max_persistent=1

<?php

if (($mysqli = mysqli_connect("p:db01", "appuser", "password", ""))) {
  $res = mysqli_query($mysqli, "SELECT CONNECTION_ID() as msg");
  $row = mysqli_fetch_assoc($res);
  echo $row['msg']."\n";
}

?>

実行したあとに、DBサーバ側でコネクション数を数えます。

mysql> SHOW PROCESSLIST;
+--------+-------------+---------------------+------+---------+-<snip>
| Id     | User        | Host                | db   | Command | <snip>
+--------+-------------+---------------------+------+---------+-<snip>
| 554589 | appuser     | 192.168.X.X:36642   | NULL | Sleep   | <snip>
| 554590 | appuser     | 192.168.X.X:36664   | NULL | Sleep   | <snip>
| 554591 | appuser     | 192.168.X.X:36668   | NULL | Sleep   | <snip>
| 554592 | appuser     | 192.168.X.X:36670   | NULL | Sleep   | <snip>
| 554593 | appuser     | 192.168.X.X:36704   | NULL | Sleep   | <snip>

・・・接続数が絞れてない？ httpd のプロセス数と同じ数のコネクションが出来てしまっています。

プロセス毎の制限値らしい

mysqli ドライバではなく、mysql ドライバ (i のつかない方) のマニュアルには、「プロセス毎の最大値」と記載があります。 mysqli も同じ挙動だろうという推測ができますね・・・

mysql.max_persistent integer The maximum number of persistent MySQL connections per process.

<?php
$a = mysqli_connect("p:db01", "appuser", "password", "");
$b = mysqli_connect("p:db02", "appuser", "password", "");
?>

2つ接続を貼ってみると、2個目の mysqli_connect がエラーになりました。やはり、プロセス毎でした。そして、最大数に達さないように、あふれたコネクションを閉じてくれるのではなく、エラーになるんですね。。。

$ sudo tail /var/log/httpd/error_log
[Sun Aug 09 16:08:30.949737 2020] [php7:warn] [pid 19100] [client X.X.X.X:55350] 
PHP Warning:  mysqli_connect(): Too many open persistent links (1) in /var/www/html/index.php on line 10

意図せず大量にコネクションを貼ってしまうのを防ぐためのパラメータなんですかねー・・・いまいち使い所がわからん。

2020-08-02

MySQL Connection Pooling と Persistent Connections はチョット違うという話

MySQL

コネクションプーリングのメリット

コネクションプーリングは、一度確率したコネクションを使い回す仕組みです。TCP 3-way ハンドシェイクやDBの新規接続処理をスキップすることで、パフォーマンスを向上させる効果があります。

ただ、私の経験ではコネクションプーリングは「しても、しなくてもどっちでも良い」ケースがほとんどでした。接続処理以外の部分が占める時間やリソースの方が圧倒的に多いケースがほとんどではないでしょうか。

一部、アプリケーションサーバとDBサーバの距離が非常に長く、RTT（往復時間）が大きい場合に効果があった経験はあります*1。

Connection Pooling と Persistent Connections

コネクションプーリングと似た仕組みとして、持続的データベース接続（Persistent Connections）があります。コネクションプーリングと持続的データベース接続は厳密に言うと少し違うものなのですが、どちらも接続を使い回す仕組みなので、私は両方「プーリング」と呼んでしまうことが多いです。その方が、通じやすいので。。。

コネクションプーリング (Connection Pooling)

アプリケーションはコネクションプールからコネクションを取出し、使い終わったら、プールにコネクションを戻します。 DBへアクセスするタイミングでのみコネクションを利用するため、アプリケーションのスレッド数（HTTPのWokerスレッド）より少ないコネクション数で済みます。HTTP の Worker スレッド数とプールされるコネクション数は別々に管理されます。プールする接続数はドライバの設定で管理します。

f:id:mita2db:20200802161506p:plain — connection pooling

プールから払い出せるコネクションがなくなると、コネクションが返却されるのを待つか、エラーになります。

「コネクションプーリング」というとこのタイプの仕組みをイメージされる方が多いのではないでしょうか。Nodejs の mysql2 はこのタイプです。

持続的データベース接続 (Persistent Connections)

Apache (Prefork) + PHP はこのタイプです。WebサーバのWokerスレッド（プロセス）とDBのコネクションが１：１で対応します。プールが存在しないため、WebサーバのWokerスレッド数と同じ数のコネクションが張られます。

f:id:mita2db:20200712151937p:plain — persistent connection

基本的にWebサーバのWokerスレッドが存在し続ける限り、コネクションが残り続けます*2。WebサーバのWokerスレッド数を増やすと連動して、DBへのコネクション数も増えます。そのため、コネクションプーリングとは異なり、HTTPサーバのWokerスレッド数を増やす場合は、接続上限に達しないよう MySQLの max_connectionsの値も増やしておく必要があります。コネクションプーリングと比較して、コネクション数のコントロールが難しい仕組みです。

*1:あと、TLSを使っている場合も効果ありそう

*2:MySQLのwait_timeoutに達して、DB側がコネクションを切るケースはあります

2020-07-20

Amazon Aurora レプリカでは metadata lock 待ちが発生しない

Aurora

Amazon Aurora のレプリカは Vanilla MySQL のレプリケーションとは違った仕組みで実現されている。マスターとレプリカは同じディスクボリュームを参照しており、マスターでの更新はほぼ即時レプリカに反映される。

DB クラスターボリュームは DB クラスターのデータの複数のコピーで構成されます。ただし、クラスターボリュームのデータは、DB クラスターのプライマリインスタンスおよび Aurora レプリカの 1 つの論理ボリュームとして表されます。この結果、すべての Aurora レプリカは、最短のレプリカラグでクエリの結果として同じデータを返します。レプリカラグは、通常はプライマリインスタンスが更新を書き込んだ後、100 ミリ秒未満です。https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/Aurora.Replication.html

テーブル定義の変更もすぐにレプリカに反映されるのか？

仕組みから考えると、DDL (CREATE/DROP/ALTER/TRUNCATE) もレプリカに即時反映されそうだが、どうだろうか。

以下のように、マスターとレプリカでカラム数を定期的に数えるシェルを回しながら、ADD COLUMN を実行して確認してみた*1。

while [ 1 ]
do
   sleep 1
   date
   echo "COLUMN at MASTER  $(mysql -uadmin -h <マスター> -e 'desc sbtest.sbtest1' | wc -l)"
   echo "COLUMN at REPLICA $(mysql -uadmin -h <レプリカ> -e 'desc sbtest.sbtest1' | wc -l)"
done

RDS MySQL

比較をわかりやすくするため、Vanilla MySQL (RDS MySQLを利用) の挙動から。MySQL では、ALTER文がレプリカで再実行されるため、マスターでかかった時間と同じぐらい時間がかかる。そのため、時間のかかるALTER文だと大きなレプリケーション遅延が発生する。この検証ではマスターから23秒遅れでレプリカに反映されている。

Mon Jul 20 06:39:59 UTC 2020
COLUMN at MASTER  5
COLUMN at REPLICA 5
<snip>
Mon Jul 20 06:40:21 UTC 2020
COLUMN at MASTER  5
COLUMN at REPLICA 5
Mon Jul 20 06:40:22 UTC 2020
COLUMN at MASTER  6
COLUMN at REPLICA 5
<snip>
Mon Jul 20 06:40:44 UTC 2020
COLUMN at MASTER  6
COLUMN at REPLICA 5
Mon Jul 20 06:40:45 UTC 2020
COLUMN at MASTER  6
COLUMN at REPLICA 6

Aurora

一方、Aurora はほぼ同時にレプリカへのカラム追加が完了している。

$ sh check.sh
Mon Jul 20 05:50:26 UTC 2020
COLUMN at MASTER  7
COLUMN at REPLICA 7
<snip>
Mon Jul 20 05:51:14 UTC 2020
COLUMN at MASTER  7
COLUMN at REPLICA 7
Mon Jul 20 05:51:15 UTC 2020
COLUMN at MASTER  8
COLUMN at REPLICA 8

テーブル定義の変更もAuroraではすぐレプリカに反映されることが確かめられた。

ALTERの対象テーブルへ実行中のクエリがあったら？

次は、metadata lock まわりの挙動を確認してみる。

マスター

Aurora マスターの挙動は、Vanilla MySQL と変わらない。実行中のクエリがあった場合、そのクエリが終わるまで、metadata lock 獲得待ちとなり、ALTERが待機する（Waiting for table metadata lock）。そしてさらに後続のクエリも metadata lock 待ちで待機する。

mysql> SHOW PROCESSLIST;
+-----+~+---------------------------------+------------------------------------+
| Id  |~| State      | Info                                                    |
+-----+~+---------------------------------+------------------------------------+
<snip>
| 263 |~| User sleep| SELECT *, SLEEP(120) FROM sbtest.sbtest1 LIMIT 1         |
| 264 |~| Waiting for table metadata lock | alter table sbtest.sbtest1 add column (cx int default 1) |
| 265 |~| init      | SHOW PROCESSLIST                                         |
+-----+-~-+---------------------------------+----------------------------------+
7 rows in set (0.00 sec)

レプリカ

レプリカで SELECT *, sleep(240) FROM sbtest.sbtest1 LIMIT 1 を実行しながら、マスターに対して、ALTER TABLE sbtest.sbtest1 ADD COLUMN を実行してみる。

RDS MySQL

Applier Thread (SQLスレッド) が、先行するクエリが終わるまで、metadata lock 獲得待ちで待機する。マスターと同じ挙動がレプリカで再現する。

mysql> show slave status \G
*************************** 1. row ***************************
<snip>
      Slave_SQL_Running_State: Waiting for table metadata lock
           Master_Retry_Count: 86400

Aurora

マスターでALTERを打ったタイミングで、レプリカで対象テーブルに対して実行中のクエリはエラーになった。 ALTERを打ったその瞬間に実行中だったクエリがエラーになるだけで、その後はALTER実行中であっても問題なくSELECTができる。

mysql>  SELECT *, sleep(240) FROM  sbtest.sbtest1 LIMIT 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

そして、マスターでALTERが完了した直後もレプリカで実行中のクエリがエラーになった。実行中のクエリがエラーで強制終了させられるので、レプリカ側で Vanilla MySQL のように metadata lock 獲得待ちになることはなさそう。

mysql>  SELECT *, sleep(240) FROM  sbtest.sbtest1 LIMIT 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

まとめると、ALTERの開始時と完了時にレプリカで実行中のクエリがエラーになるようだ（そのおかげで、レプリカで metadata lock 獲得待ちが発生しない）。

まとめ

Aurora では、DDLはレプリカにすぐ反映される
Aurora では、レプリカでの metadata lock 待ちは気にしなくてよさそう
- しかし、ALTER実行によりレプリカでクエリが単発レベルのエラーになることはある

*1:v5.6 で試している

2020-07-18

MySQLを止めずにレプリケーションをブーストする小技

先日は、MySQLユーザ会会 2020年7月に参加しました。

今回はWebや雑誌で連載の著者の方々が、執筆に至った経緯や、執筆時に心がけていることを語る回でした。 @kk2170 さんが「過去の自分に向けて書く」とおっしゃっていたのが、（そういう視点は自分の中になかったので）すごく響きました。

mysql.connpass.com

一刻も早くレプリケーション遅延を取り戻したい！そんな場合に使える小技を紹介します。

レプリケーションを止めずに有効化・無効化できるのみを取り上げています。元に戻すのにレプリケーションを止める必要性のある方法だと、またそこでレプリケーション遅延が発生しますからね…

CPU の governor を performance に変更する

LinuxにはCPUクロックの調整機能があり、CPU governor が ondemand 設定の場合、負荷に応じて、CPUのクロック数が上下します。クロック数を調整することで、消費電力を最適化します。

特に、(マルチスレッドスレーブを使わずに) シングルスレッドでレプリケーションを行っている場合、負荷が低いとみなされ、クロック数が十分上がらないケースがあります。シングルスレッドのレプリケーションではCPU 1コアしか使われないためです。

performance に変更することで、常に最高クロック状態を維持することができます。サーバの消費電力が上がってしまうのが注意点です。

# for CPU in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor
> do
> echo -n performance > $CPU
> done

Durability を犠牲にする

InnoDB ログへの書き込み頻度を落とす

InnoDB ログ（REDOログ）への書き込み頻度を下げることで、DISK IOを少なくしパフォーマンスをブーストします。

mysql> SET GLOBAL innodb_flush_log_at_trx_commit=0;

mysql> SHOW GLOBAL VARIABLES like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
1 row in set (0.01 sec)

デフォルトは、innodb_flush_log_at_trx_commit=1であり、コミットする度に、InnoDB ログファイルへ書き込みが行われます。innodb_flush_log_at_trx_commit=0 では１秒おきに書き込みます。

InnoDB ログは mysqld がクラッシュしたり、サーバがダウンした際にデータが失われないようにする役割を担っています。innodb_flush_log_at_trx_commit=0 にすると障害時にデータが失われるリスクがあります。もし運悪く障害が発生してしまったら、レプリカを作り直すことになるでしょう。遅延が収まったら、有効に戻しましょう。

同様に、sync_binlog を無効化（0 を設定する）のも効果があります。

InnoDB ログそのものをOFFにする（MySQL 8.0.21 の新機能）

MySQL 8.0.21 からは InnoDB ログそのものを無効化することが出来るようになりました。innodb_flush_log_at_trx_commit では書き込み頻度を落とすことしか出来なかったのが、InnoDBログの存在自体を無効化できるようになりました。

ただし、innodb_flush_log_at_trx_commit=0 同様に、障害時にデータは失われてしまいます。

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG ;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+

パフォーマンス比較

以下のグラフは、デフォルトの状態と、{innodb_flush_log_at_trx_commit,sync_binlog} = {0,0} と、InnoDB ログを無効化したパターンのレプリケーションのパフォーマンスを簡単に計測した結果です*1。「150万件のINSERTをどれぐらいの秒数でレプリカが食いきったか」を計測しています。

f:id:mita2db:20200718142154p:plain

InnoDB ログを無効化した場合が圧倒的に速い！！！

まとめ

レプリ遅延で困ったら、とりあえず、InnoDB ログをOFFれ

*1:マルチスレッドスレーブを有効にし、workerを4で計測

Aurora MySQL v5.6 のサポート期限がいつかわからない

メリットが限られているアップグレード

アップグレード方法

インプレース

ダンプ・インポート

スナップショット復元

まとめ

とりあえず動かしてみる

dump時 はどんなSQLが流れるのか

どうやって一貫性を保ったバックアップを実現しているのか

LOCK INSTANCE FOR BACKUP

まとめ

MySQL のロック範囲は実行計画で変わる

インデックスを追加します

実行計画を比べる

もっと詳しい内容はANDPADのTechblogで

TL;DR

PHP mysqli.max_persistent 設定

試します

プロセス毎の制限値らしい

コネクションプーリングのメリット

Connection Pooling と Persistent Connections

コネクションプーリング (Connection Pooling)

持続的データベース接続 (Persistent Connections)

テーブル定義の変更もすぐにレプリカに反映されるのか？

ALTERの対象テーブルへ実行中のクエリがあったら？

マスター

レプリカ

まとめ

CPU の governor を performance に変更する

Durability を犠牲にする

InnoDB ログへの書き込み頻度を落とす

InnoDB ログそのものをOFFにする（MySQL 8.0.21 の新機能）

パフォーマンス比較

まとめ

dump時はどんなSQLが流れるのか