MySQL 8.0 への移行が完了しました～さようなら全ての MySQL 5.7～ - Cybozu Inside Out

こんにちは。クラウド運用チームの飯塚です。

私たちは cybozu.com 本番環境の MySQL を昨年末から順次 8.0 系へアップグレードしており、前回の定期メンテナンスにおいて全てのインスタンスのアップグレードを完了しました。この記事では、私たちが MySQL 8.0 への移行に取り組んだ理由と必要になった対応について紹介します。

なぜ MySQL 8.0 へ移行したのか
- GTID-based レプリケーションにおける制限の緩和
- 再起動時に AUTO_INCREMENT のカウンタが巻き戻る問題の解消
実際に対応が必要だった MySQL 8.0 の変更点
その他対応が必要だった仕様変更
おわりに

なぜ MySQL 8.0 へ移行したのか

MySQL は当社が提供しているクラウドサービスの多くがプライマリデータストアとして利用しており、この記事の執筆時点で400を超える数のインスタンスが本番環境で運用されています。私たちはこれら全てを MySQL 8.0 へ移行しました。

一般的にデータベースのアップグレードは事前の検証やコードの修正に大きな労力が必要であり、敬遠されることも多い作業です。コストとの兼ね合いから EOL の直前まで後回しにされるような事例も珍しくありません。一方、MySQL 8.0 ではさまざまな改善が行われており、そのいくつかはアップグレードのためのコストをかけるのに値するものであると判断しました。特に、以下の点は当社のビジネスにおいて大きな価値があるものだと考えています。

GTID-based レプリケーションにおける制限が緩和された
MySQL の再起動時に AUTO_INCREMENT のカウンタが巻き戻る問題が解消された

これらについて解説します。

GTID-based レプリケーションにおける制限の緩和

以下の記事でも紹介したように、当社が提供しているサービスでは長い間レプリケーションを利用していませんでした。

blog.cybozu.io

そのため、レプリケーション構成へ移行するための調査を始めてみるといくつか問題が見つかりました。GTID を使ったレプリケーションには CREATE/DROP TEMPORARY TABLE によるテンポラリテーブルの操作や CREATE TABLE ... SELECT ステートメントが利用できないといった制限がある一方で、当社のサービスの中には CREATE TEMPORARY TABLE に強く依存した処理がいくつかあり、このままではレプリケーション構成へ移行することが難しいということがわかりました。

これらの CREATE TEMPORARY TABLE に関する制限は MySQL 8.0.13 で、CREATE TABLE ... SELECT に関する制限は MySQL 8.0.21 で取り除かれ、GTID を使ったレプリケーションでもこれらのステートメントが利用できるようになりました。

MySQL 8.0 Reference Manual :: 17.1.3.7 Restrictions on Replication with GTIDs

MySQL 8.0 へのアップグレードが完了したことによって GTID を使ったレプリケーションへの対応も完了したことになるため、当社のサービスの可用性を高めるための取り組みを大きく進展させることができたと考えています。

再起動時に AUTO_INCREMENT のカウンタが巻き戻る問題の解消

MySQL に遥か昔から存在する有名な問題として、再起動時に AUTO_INCREMENT のカウンタが巻き戻るというものがありました (Bug #199)。この問題はあまりにも長い間存在していたため MySQL の残念な仕様のひとつと考えられることも多いものでしたが、MySQL 8.0 になって修正されました。

MySQL 8.0 Reference Manual :: 15.6.1.6 AUTO_INCREMENT Handling in InnoDB

この問題の例を以下に示します。t1 というテーブルに AUTO_INCREMENT によって採番された 1, 2, 3 という値を入れた後、3 のレコードを削除します。

CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT);
BEGIN;
INSERT INTO t1 () VALUES (), (), ();
DELETE FROM t1 WHERE c1 = 3;
COMMIT;

このとき、3 が削除されたとしても次に採番される値は4になります。

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

しかしながら、MySQL を再起動すると MySQL 5.7 では次に採番される値が巻き戻って3になります。これは再起動時に次に採番される値を AUTO_INCREMENT が設定されているカラムの最大値+1で初期化しているためです。

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

この問題が修正されたことで大規模なテーブルを安全にマイグレーションしたい場合に対応できる事例が増えました。大規模なテーブルの安全なマイグレーションに関する取り組みについては以下の記事もご覧ください。

blog.cybozu.io

実際に対応が必要だった MySQL 8.0 の変更点

MySQL 8.0 では互換性のない変更もかなり含まれます。私たちは MySQL 8.0.0 から MySQL 8.0.20 までのリリースノートにはすべて目を通し、対応が必要かどうか確認していました。

f:id:cybozuinsideout:20210520171542p:plain — リリースノートの内容を精査するタスクの管理には kintone を使いました

その中にはコードの修正が必要になったものもいくつかあります。特に影響が大きかったものを抜粋して紹介します。

utf8mb4 の照合順序のデフォルト値の変更

文字コードとして utf8mb4 を使っている場合の照合順序のデフォルト値が utf8mb4_general_ci から utf8mb4_0900_ai_ci に変更されました。

当初は utf8mb4_general_ci を明示的に指定するだけで対応できるかと考えていたのですが、mysqldump で MySQL 5.7 のテーブルを抜き出して MySQL 8.0 にリストアしようとした場合には問題が起こることが分かりました。MySQL 5.7 での utf8mb4 のデフォルトの照合順序は utf8mb4_general_ci であるため、たとえテーブル作成時に明示的に utf8mb4_general_ci を指定していたとしても mysqldump で抜き出したときには utf8mb4_general_ci の指定が消えてしまい、そのまま MySQL 8.0 でリストアすると utf8mb4_0900_ai_ci に変更されてしまいます。

CREATE TABLE t1 (
  c1 VARCHAR (140) NOT NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

$ mysqldump --compact d1
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `c1` varchar(140) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
$

当社が提供している SaaS 製品は申し込みがある度にテナントごとのテーブルが作成される仕組みであり、本番環境で日常的にテーブルが新規作成されるため、CREATE TABLE するための SQL ファイルを準備しておく必要があります。mysqldump を使ってこのファイルを作成している場合には元となる MySQL を 8.0 にアップグレードしておくという対応が必要になりましたが、移行途中の時期には混乱がありました。

ところで MySQL 8.0 では default_collation_for_utf8mb4 の設定によってデフォルトの照合順序を utf8mb4_general_ci に戻すことができます。しかしながら私たちの用途ではあまり使い勝手が良くなく、この方法は採用できませんでした。

まず、default_collation_for_utf8mb4 はオンラインで変更可能なパラメータですが、驚くべきことに my.cnf に設定を書くことができません。この挙動は不具合ではないかということでバグ報告が行われていますが仕様であるとして Close されています。

Bug #96335 MySQL doesn't start when I set default_collation_for_utf8mb4 in my.cnf

my.cnf に設定を書くことができない問題の回避策として MySQL 8.0 の新機能である SET PERSIST ステートメントを使う方法があります。これは MySQL のシェルだけを使って設定項目を永続化（再起動後にも有効に）することができる機能です。my.cnf では default_collation_for_utf8mb4 を設定することができませんが、SET PERSIST では設定することができ、再起動後も設定を引き継ぐことができます。

ただし、この回避策にも注意点があります。永続化された設定はテキストファイルとして書き出されますが /etc/mysql のような設定ファイル用のディレクトリではなく /var/lib/mysql のような データディレクトリに 書き出されます。これは私たちがこれまで利用してきた設定ファイルを自動でデプロイする仕組みと相性が良くなく、SET PERSIST の利用は見送られました。

SQL_CALC_FOUND_ROWS と FOUND_ROWS() が deprecated に

MySQL 8.0.17 で SQL_CALC_FOUND_ROWS と FOUND_ROWS() が deprecated になりました。この機能は LIMIT 句を付けたクエリについて LIMIT 句を付けなかった場合の行数が得られる機能で、ページャーの実装において全体の件数を取得したい場合に使われることがあります。たとえば、全体で10行あるテーブル t1 について以下のようなクエリを実行すると先頭の5行のレコードに加えて FOUND_ROWS() の結果として 10 が得られます。

BEGIN;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 5;
SELECT FOUND_ROWS();

置き換え先としては SQL_CALC_FOUND_ROWS を付けない SELECT を実行した後に、同様の条件で LIMIT を付けない SELECT COUNT(*) を実行する方法が紹介されています。

BEGIN;
SELECT * FROM t1 LIMIT 5;
SELECT COUNT(*) FROM t1;

COUNT(*) を使う方法は SQL_CALC_FOUND_ROWS を使う方法と比較していくつかの最適化が有効になり高速になるとされています。トランザクション分離レベルに REPEATABLE-READ 以上を用いている場合は COUNT(*) に単純に置き換えることができます。しかしながら、トランザクション分離レベルが READ-COMMITTED 以下の場合は単純に置き換えることができません。これは、SQL_CALC_FOUND_ROWS を用いた件数取得は別のトランザクションによる件数変更の影響を受けない一方で、COUNT(*) を用いた件数取得はファジーリードやファントムリードの影響を受けて SELECT ... LIMIT N の実行時と結果が異なる場合があるためです。

Connector/J のメタデータ取得処理の性能低下

Connector/J にはテーブルのカラム名などのメタデータを取得する機能があります。この処理は MySQL 8.0.3 未満であれば SHOW FULL COLUMNS を使い、MySQL 8.0.3 以上であれば INFORMATION_SCHEMA.COLUMNS を使うような使い分けで実装されています。使い分けの理由は MySQL 8.0 では information_schema のほうが効率的であるためと説明されています。

このメタデータ取得の処理において MySQL 8.0.21 未満のバージョンでは以下のようなパフォーマンス問題が報告されており、MySQL 5.7 と比べて SHOW FULL COLUMNS, SHOW FIELDS, SHOW TABLES などのパフォーマンスが低下している状況でした。

Bug #98750 SHOW FIELDS command regression on MySQL 8.0

この問題により、Connector/J のメタデータ取得機能を利用しているアプリケーションについて10%を超える性能低下が検出されました。最終的に私たちは MySQL 8.0.21 未満のバージョンを利用することにしたため、メタデータの取得を回避するようにコードの修正が必要でした。

sys.innodb_lock_waits の罠

MySQL でロック競合の状態を解析しようとしたとき、以下のブログの記事で紹介されているクエリを参考にしたことがある方も多いのではないでしょうか？

sh2.hatenablog.jp

MySQL 8.0 ではこのクエリで使われている information_schema のテーブルの一部が廃止され、似たような機能を持つテーブルが performance_schema に追加されました。概ね、information_schema.innodb_lock_waits を performance_schema.data_lock_waits に、information_schema.innodb_locks を performance_schema.data_locks に置き換えることになります。

当社ではロックに関わる問題を検出して性能改善に役立てるため、ロック解析のクエリを定期実行してログに記録しています。しかしながら、information_schema のテーブルの drop-in replacement として performance_schema のテーブルを使うことには注意が必要です。私たちは以下のようなパフォーマンス問題を発見して不具合として報告しました。

Bug #100537 Performance degradation caused by monitoring sys.innodb_lock_waits in MySQL 8.0

報告したテストケースでは sys.innodb_lock_waits（ロック解析に使われるテーブルの違いを吸収して透過的に扱うことができる sys スキーマのビュー）を出力するクエリを1秒間隔で実行するモニターを設定したうえで、ロック待ちが多数発生するようなトランザクションを実行しています。MySQL のバージョンとモニターの有無によって実行時間に次のような違いが現れました。

MySQL のバージョン	モニター無効	モニター有効
MySQL 5.7	12.853 秒	13.680 秒
MySQL 8.0	12.629 秒	110.889 秒

モニター有効化による性能の低下は MySQL 5.7 では10%未満であり、モニターから得られる情報の価値を考慮すると許容できるものでした。一方、MySQL 8.0 ではモニター有効化によって処理時間が10倍近くになっており、許容することができない性能低下が発生してしまいました。

この問題は根本的な対処方法が見つけられておらず、モニタリング用のクエリの実行間隔を伸ばしたり採取する情報を減らすことで対応しています。もし同様の使い方をしていて困っている方がいればぜひ Bug #100537 の Affects Me を押していただけると幸いです。

外部キー制約をもつテーブルの DDL 操作によるメタデータロックの発生

MySQL 8.0.3 の 新機能 として外部キー制約をもつテーブルの DDL 操作をする際に参照先のテーブルのメタデータロックを獲得するようになりました (WL#6049)。この挙動については以下の記事でも紹介されています。

yoku0825.blogspot.com

私たちは以下のような利用パターンで MySQL 5.7 との挙動の違いに悩まされました。既に t1 というテーブルが存在していてサービスを運用している状況で、外部キー制約で t1 を参照する新しいテーブル t2 を作成することを考えます。

まずは親テーブルを作成します。

CREATE TABLE t1 (c1 INT PRIMARY KEY);

t1 を SELECT するだけのトランザクションを作っておきます。このとき t1 に対するメタデータロックが獲得されます。

BEGIN;
SELECT * FROM t1;

別のトランザクションで新しいテーブル t2 を作成します。このテーブルは外部キー制約で t1 を参照します。MySQL 8.0 ではこのとき t1 のメタデータロックを獲得するようになったため、先ほどの t1 を SELECT するだけのトランザクションによって CREATE TABLE がブロックされます。

CREATE TABLE t2 (c1 INT, c2 INT, FOREIGN KEY (c1) REFERENCES t1 (c1));

*************************** 3. row ***************************
     Id: 2227
   User: root
   Host: localhost
     db: d1
Command: Query
   Time: 10
  State: Waiting for table metadata lock
   Info: CREATE TABLE t2 (c1 INT, c2 INT, FOREIGN KEY (c1) REFERENCES t1 (c1))

この挙動は日次の集計処理など比較的長時間の SELECT を実行するトランザクションと、オンラインで実施可能であるはずのテーブル追加が同時に行えない（設定によってはタイムアウトする）ことを意味します。しかしながら、この挙動は不具合ではなく整合性を保つための望ましい変更であると思われるので、運用で回避するしかありません。リリースの頻度が増えてきた場合にはこの制限によって生じる問題が顕在化してくる恐れもあるため、上手な付き合い方を模索していきたいと考えています。

トランザクション分離レベルを設定する変数名の変更

MySQL 8.0 になり、変数名が変わったものがあります。私たちを悩ませたのはトランザクション分離レベルを表す tx_isolation が transaction_isolation に変わったことでした。transaction_isolation は MySQL 5.7.20 で tx_isolation のエイリアスとして導入され、tx_isolation は MySQL 8.0.3 で削除されました。すなわち MySQL 5.7.20 未満から MySQL 8.0.3 以上にアップグレードしようとした場合には中間的な移行措置なしで変数名を切り替える必要があります。

私たちは MySQL に接続する際のデータソース名 (DSN) のオプションとして以下のように tx_isolation を指定している箇所があり、どうすれば円滑に変数名を切り替えることができるか悩んでいました。

user@tcp(172.16.0.1)/?tx_isolation=%27READ-COMMITTED%27

MySQL のバージョンによって変数名を変えるような分岐を作りたくなりますが、利用しているコネクターのライブラリによってはより良い方法が使える場合があります。たとえば Go の database/sql なら BeginTx の引数でトランザクション分離レベルを指定することができます。こちらは変数名の変更の影響を受けません。そもそもトランザクション分離レベルはトランザクション単位で個別に設定されるものであるため、こちらの使い方のほうがコネクション単位でグローバルに設定するよりも望ましいかもしれません。

その他対応が必要だった仕様変更

上記以外にも以下の変更についてはコードの修正が必要となりました。リリースノートの焼き直しとなってしまうため詳細な解説は省きます。

UNION 句と FOR UPDATE を使う場合に括弧が追加で必要

Bug #99561 で報告されたものです。MySQL 8.0 で SQL のパーサーが書き直されたことによる影響です。

Implicit Account Creation の廃止

MySQL 5.7 まで使うことができた、GRANT ステートメントでユーザーが作成される仕様です。MySQL 8.0.11 で削除されました。

information_schema のカラム名が大文字固定に

MySQL 5.7 以前では information_schema を SELECT するとき、ヘッダに書かれたカラム名の大文字・小文字が SELECT 句に書いたものに追従するという不思議な仕様がありました。

mysql> SELECT table_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'd1';
+------------+
| table_NAME |
+------------+
| t1         |
+------------+
1 row in set (0.00 sec)

この仕様は MySQL 8.0 で削除され、information_schema のカラム名は大文字に固定されました。

EXPLAIN ステートメントの EXTENDED キーワードの廃止

EXTENDED を付けなくても常に EXTENDED 相当の情報が表示されるようになったためです。MySQL 8.0.12 で利用できなくなりました。

GROUP BY a ASC の廃止

GROUP BY a ASC と書くと GROUP BY a ORDER BY a ASC と書いたのと同じ結果が得られるという仕様です。MySQL 8.0.13 で削除されました。コードの変更量だけを考えれば、MySQL 8.0 の対応に必要なコードの修正の中でこの仕様変更に起因する修正が最も多かったチームもあるようです。

おわりに

この記事では当社のサービスで利用している MySQL を 8.0 系へアップグレードした理由と必要になった対応について紹介しました。一般的に言われているように事前の検証やコードの修正には労力が必要でしたが、cybozu.com が MySQL 8.0 になったことで機能追加や制限の緩和によるさまざまな恩恵を受けることができるようになりました。この成果はサービスの安定性の向上やメンテナンス時間の短縮といった形でお客さまに還元していきたいと思います。

MySQL 8.0 系では継続的デリバリーモデルによってマイナーバージョンアップでも機能が追加されたり仕様が変更されたりすることがあるため扱いにくいという声もよく聞かれます。しかしながら、追加された機能には魅力的なものも多く、そのいくつかはアップグレードする動機になり得るものです。この記事の中に MySQL 8.0 系へのアップグレードを検討されている方のお役に立てるものがあれば幸いです。

最後になりましたが MySQL コミュニティのみなさまが共有して下さっている導入事例や不具合情報がなければ MySQL 8.0 へのアップグレードを完遂することはできませんでした。世界中の MySQL コミュニティのみなさまに感謝申し上げます。