こんにちは。crowdworks.jp SREチームの田中（kangaechu）です。
crowdworks.jpでは、2023年8月にAWS RDS MySQL 5.7から8.0へのアップデートが完了しました（ようやく！）。 今回はMySQL 8.0へのアップデートの手順と対応が必要な変更点について書いていきます。
MySQL 8.0にアップデートした理由
MySQL 8.0にアップデートした理由はAWS RDS MySQLのEOL対応のためです。 AWS RDS MySQL 5.7のEOLは2023年10月（のちに2023年12月に変更されました）であり、8.0へのアップデートが必要でした。 crowdworks.jpで使用している他のMySQLデータベースは8.0へのバージョンアップを完了していました。 しかしcrowdworks.jpのマスタデータベースは30億行を保持し、1日に約4億クエリを処理しているため、バージョンアップには慎重になる必要がありました。 そのため、他のデータベースのバージョンアップで経験を積んだ後に実施する計画としていました。
また、crowdworks.jpのマスタデータベースは長期間使われています。以下はサービス開始からのMySQLの状況をまとめたものです。
- 2012/3 crowdworks.jp サービス開始（さくらインターネットのVPSを使用）
- 2013/3 AWSに移行
- 2014/3 MySQL 5.5→5.6 アップデート
- 2021/3 MySQL 5.6→5.7 アップデート
- 2023/8 MySQL 5.7→8.0 アップデート
歴史的経緯のあるデータベースのため、他のMySQLバージョンアップでは発生しないような問題が発生する可能性があったので、バージョンアップ作業を慎重に進めていきました。
変更概要
AWS MySQL RDSのアップデート （MySQL 5.7.41 → 8.0.33）
手順
MySQL 8.0への移行作業は2023/6-2023/8の2ヶ月半程度で実施しました。 その間にどのような作業を行なっていたのかを紹介します。
変更内容の把握・影響調査
まずはバージョンアップによる変更点を読みました。 スプレッドシートに変更点を転記し、影響の有無について記入しました。 また、各環境でのパラメータグループのデフォルト値の変更についてもここで確認を行いました。
ここで注意すべき点は2点です。
- MySQL 8.0はセマンティックバージョニングではない
2023/8現在、MySQLは8.0.0から8.0.34までのバージョンがあります。 セマンティックバージョニングで見ると、パッチバージョンが上がっているように見えるのですが、バージョン間で破壊的変更やデフォルト値の変更が入っていることに気をつけましょう。 - 英語ドキュメントを読もう
日本語ドキュメントを読みたくなりますが、日本語版には全ての変更点が記載されていません。 英語版のドキュメントを使用し、日本語版を使用する場合は参考程度にとどめましょう。
移行方法の検討
AWS RDSの移行方法として、 インプレースのアップグレードとAmazon RDS Blue/Green Deployments が使用できます。MySQL DB エンジンのアップグレードにもある通り、Blue/Green Deploymentsはアップグレードにかかる停止時間を最小化できるためおすすめです。しかし、今回のアップグレードはインプレースでのアップグレードを選択しました。
以前クラウドワークスのエンジニアブログ記事MySQLの約30億レコードをRedshiftにDMSでニアリアルタイム同期したで紹介した通り、crowdworks.jpのマスタデータベースとRedshiftはニアリアルタイムで同期しており、その同期方法としてDMSを選択しています。 マスタデータベースはDMS用のRDSインスタンスにレプリケーションします。DMSはDMS用のRDSインスタンスをソースエンドポイント、Redshiftをターゲットエンドポイントとして継続的レプリケーションを行なっています。
ここで問題となるのは、DMSがMySQLのバイナリログを使用していることです。 DMSがどこまでレプリケーションを行なったかの管理はバイナリログのポジションを使用しています。 Blue/Green Deploymentsを使用すると、ソース・レプリカインスタンスをグループとして入れ替えます。 Greenとして作成されたRDSインスタンスはバージョンアップ作業により、バイナリログのポジションが異なる可能性があります。 そのため、Blue/Green の切り替えを行なった場合、DMSはポジションを見失い、全ロードを実施する可能性があります。 それにより、Redshiftにレプリケーションが追いつくまでRedshiftを使用した業務が止まることになります。 Redshiftには30億レコード程度のデータを同期しているため、同期が完了するまでに半日から1日程度かかります。 長時間の停止は許容できないため、インプレースのアップグレードを選択しました。
開発環境のアップデート
ローカル環境や検証環境を含め、順番にMySQL 8.0へのアップグレードを実施しました。
ここでハマったのは環境間でテーブル定義が異なることでした。 （こんな事象は他環境で発生しないと思うので読み飛ばしても大丈夫です）
crowdworks.jpの開発環境は任意のブランチをデプロイできる仕組みとなっています。 そのため、開発環境でデータベースのマイグレーションを含む修正をデプロイすると、データベースの状態がproduction環境と異なる状態が発生します。 そのため、開発環境では全てのテーブルを再作成し、本番環境を同等の状態を作り直しました。
また、ローカル環境でも同様の事象が発生しました。 crowdworks.jpのローカル環境を自分のPCに構築した人には移行手順として、MySQL 5.7でアンロードしたデータをMySQL 8.0でロードするよう依頼をしたのですが、一部ユーザはテーブル定義の不整合によりロードに失敗していました。 アンロードしたデータを修正してもらうことで対応を行いました。
本番環境のアップデート
移行計画の作成
開発環境のアップデートにより検証手順や時間を見積もれたので、まずは移行計画を作成しました。 このようなフォーマットで移行計画を記述し、レビューを行いました。
# 作業概要 ## 背景 ## 変更概要 ## 対象 ## 日時 # エンドユーザから見たサービス影響範囲 ## 想定されるダウンタイム ## バージョンアップによる影響 # 作業の全体的な流れ ## 事前作業 ## 当日作業 # 想定されるリスク
アップデート手順の作成
移行計画のレビューが完了したので、アップデートの具体的な手順を作成しました。
アップデート
レプリカインスタンスは事前にアップデートし、マスタデータベースは深夜メンテナンスでアップデートしました。
対応が必要だった変更点
アップデート後に一部クエリのパフォーマンスが悪化
アップデート後に一部のクエリのレスポンスタイムが悪化しました。 （データベースサーバのCPU使用率が100%に張り付き、生きた心地がしませんでした）
実行計画を確認したところ、 Extra: に Backward index scan とありました。また、使用するインデックスに以下の違いがありました
- 変更前：フィルタするためのインデックスを選択
- 変更後：ソートするためのインデックスを選択
MySQL 8.0では降順(Descending)インデックスの機能が追加されました。またオプティマイザ自体にも変更が入っています。
MySQL 8.0へのアップデートに伴い、実行計画が変わったと判断しました。
そのため、optimizer_switch='prefer_ordering_index=off' を設定し、オプティマイザの動作を変更したところ、レスポンスタイムの悪化が解消されました。
パラメータの設定について一つ注意点があります。optimizer_switch パラメータは以下の3通りの設定方法が考えられます。
- GLOBAL変数
- RDS パラメータグループ
- SESSION変数
しかし、実際に設定できるのは3. のSESSION変数のみです。1. GLOBAL変数は管理者ユーザであってもSUPER権限が付いていないため、エラーとなります。
MySQL > SET GLOBAL optimizer_switch='prefer_ordering_index=off'; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
また、2. のRDSパラメータグループでoptimizer_switch パラメータを設定する場合、パラメータに設定できるキー名に制限があり、 prefer_ordering_index を指定するとエラーになりました。
第135回 MySQL 8.0で追加されたoptimizer_switchのフラグについて | gihyo.jp
crowdworks.jpではRailsを使用しているため、config/database.yml にvariablesとして設定しました
default: &default adapter: mysql2 variables: optimizer_switch: 'prefer_ordering_index=off'
行フォーマットが古い場合、1行に8,126バイトを超えるレコードを保存できない
InnoDBでは、デフォルトの1レコードのサイズは8,126バイトです。
ActiveRecordでは、カラムのデータ型としてstringを使用すると、MySQLでは VARCHAR(255) のカラムとして扱います。
文字コードセットにutf8mb4を使用している場合、255 * 4 = 1,020バイトとなります。
そのため、8,126/1,020 = 8 なので、8個のstring型を持つモデルはエラーになるのでは？と思ってしまいますが、そうではありません。
MySQLは可変長カラム値 (VARCHAR、VARBINARY、BLOB、TEXT)を保存する場合、行フォーマット（innodb_default_row_format）によって保存の仕方が異なります。
REDUNDANT/COMPACT: 最初の768バイトをレコード（Bツリーノード内のインデックスレコード）に保存、超えたものは別の場所（オーバーフローページ）に保存DYNAMIC/COMPRESSED: 20バイトのポインタをレコードに保存、実体は別の場所に保存
innodb_default_row_format=DYNAMIC がデフォルトになったのはMySQL 5.7から。MySQL 5.5から使用しているcrowdworks.jp のテーブルの行フォーマットは COMPACT を使用しています。
行フォーマットが COMPACT の場合、8,126/768 = 10.5 であり、 VARCHAR(255) が10個を超えると保存に失敗する可能性がありました。
MySQL 8.0からは1行に8,126バイトを超えるテーブルを作成すると、
- アップデート時にワーニング
- 新規作成時にエラー
となります。
crowdworks.jp ではそのようなテーブルが存在しました。
そのため、これらのテーブルについてMySQL 8.0アップデート前に ALTER TABLE example ROW_FORMAT=DYNAMIC; を実行し、行フォーマットを DYNAMIC に変更しました。
（全テーブルの行フォーマットを DYNAMICに変換したかったのですが、約1日かかることが判明したため、アップデート作業と別に実施する予定です）
BINARY演算子の削除
BINARY演算子は文字セットや照合順序に関わらず、バイナリでの比較などを行うために使用します。
SELECT * FROM CITY WHERE Name = BINARY 'Kabul'; のように使う感じですね。
BINARY演算子は8.0.27で削除されました。
こんな演算子は使ってないだろうと思いながらコード検索をしたところ、1箇所だけ使用していました。
現在は削除しても影響がなさそうだったので、コード自体を修正しました。
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.27 (2021-10-19, General Availability)
特定の文字列をTIMESTAMP型にパースするとエラー
外部サービスから受信した 2006-01-02T15:04:05.000+0000 のようなタイムスタンプっぽい文字列をMySQLでTIMESTAMP型にパースしたところ、MySQL 8.0ではエラーになりました。
2006-01-02T15:04:05.000+00:00 では問題ありませんでした。
MySQL8.0.19でTIMESTAMP型へのパース処理が変更されたことによる影響です。
一度RubyでTime型に変換し、それを文字列型に変換するようにコードを修正しました。
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.19 (2020-01-13, General Availability)
予約語に rank が追加
列名などで使われることがありそうな rank という単語がMySQL 8.0から予約語に追加されました。
ActiveRecordを使っていれば、テーブル名や列名はバックティックを前後に付与してくれるため、特に問題はありません。
SQL文に列名として rank が使われていたところがあったので修正しました。
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.2 (2017-07-17, Development Milestone)
これからやりたいこと
Amazon RDS Blue/Green Deployments を使えるようにしたい
Amazon RDS Blue/Green Deploymentsは切り替えにかかる時間が短い・Green側への変更が可能などと便利なため、積極的に使っていきたいです。 行フォーマットの変更、utf8mb4への移行など使いたい場面は多くあります。 今回の移行ではEOLまでの時間が迫っていたためBlue/Green Deploymentsの使用を断念しましたが、環境を整えて変更しやすい構成への移行を行っていきたいと考えています。
Aurora化
crowdworks.jpの一部データベースではAuroraを使用していますが、マスタデータベースはRDSを使用しています。 パフォーマンス・可用性・耐障害性の観点からAuroraへの移行を検討しています。
また、AuroraとRedshiftとのゼロ ETL 統合（パブリックプレビュー）を使用することにより、DMSでのレプリケーションを廃止することも可能です。 シンプルで運用負荷の少ない構成を目指したいと考えています。
まとめ
crowdworks.jp ではマスタデータベースをMySQL 5.7から8.0へ移行しました。 移行に際して必要な対応がいくつかありましたが、適宜対応を行い、特に問題なく稼働しています。 今後もcrowdworks.jpのサービスを安心・安定してご利用いただけるよう、改善を進めていきます。
We're hiring!
クラウドワークスはたのしいよ！みんなはいってね！
