このページについて

新規サービスでMySQLを使っていてノウハウが溜まってきたので、MySQLをSpring Bootアプリケーションで本番運用するときやってみたことを整理しました。
折角なのでMySQL Advent Calendar 2020 に参加致します。

作者について

アプリケーションエンジニアです。主な言語はJavaですが、 PHPやJavaScriptも使います。
近年はAzure全般やMySQLのDBAに巻き込まれ、アプリケーションとミドルウェアと言われる部分を横断的に取り組む担当者になりつつあります。MySQLはまだまだ初心者と自負しています。

この記事で取り扱うこと

• MySQLを使う上でSpring Bootアプリケーションでの一般的な事項
• アプリケーション運用の上で最低限必要なMySQLサーバの設定、運用面の考慮
• 組織やプロジェクト、要件にあまり依存しないこと
  • 技術選定なので多少組織依存しているかもしれませんが、比較的一般的なことを選んでいます

『他にこんなことがあるよ』があれば是非教えてください。

この記事で取り扱わないこと

• MySQLのファイルセット、IO、ネットワークに関わる部分
• 利用技術の詳細
• 環境記載以外での動作や考慮事項の詳細 (AWS RDSなど)
• テーブル設計の話
• 高負荷サービスでの対応
  • Max 50QPS, 現在 60万レコードくらいの 閑古鳥 比較的低負荷なサービスを開発しています

環境

本記事を書いた時点で利用していた環境およびフレームワーク類のバージョンです。

• Azure Database for MySQL (MySQL 5.7)
• Javaのバージョン 11 (ローカルは AdoptOpenJDK, ローカル以外は java-buildpack に委譲)
• Spring Boot (2.2系, 2.3系, 2.4系で確認)
  • コネクションプーリングは デフォルトのままHikariCpを使っています
  • データアクセスはMyBatisです
• Azure上でのアプリケーション稼働はコンテナを使っています
• 開発者PCはWindows 10とMac OS Mojaveが混在です

本編はここから

MySQLを選定した理由

本番運用の考慮はまず選定からです。要件が緩やかなので下記の理由で気軽に決めました。

• データ移行が必要なため他システム(AWS RDS for MySQLでMySQL5.6)と統一した
• 周辺に有識者も経験者がおり、開発知識がある

開発編

アプリケーションの仕様・方式を決める

MySQLのサーバ設計、サーバパラメータの設計にも必要なため、最低限必要と判断したことを洗い出して順番に決めていきました。

なかなか業務ユーザに理解されにくい部分もありますが、寿司ビール や『びょういんとびよういん』など交渉ネタを絞り出して決めました。

• 文字コード、ソート順の仕様 (絵文字は使うか、取り扱う機種依存文字など
• 大文字小文字・半角全角を意識した業務運用があるが
• トランザクションレベル
• トランザクション実装 (悲観ロック、楽観ロック)
• (データ移行があったので) データ移行方式
  • 大人の事情やデータ構造上の問題で、MySQLのレプリケーションが使えませんでした。こんなこともあります

サーバ設定、パラメータ設定を決める

『アプリケーションの仕様・方式を決める』で決めた項目と、Azureの公式ドキュメントを参考にしました。
プランによって設定可能なパラメータやパラメータ上限が異なったりします。
https://docs.microsoft.com/ja-jp/azure/mysql/concepts-limits

利用者側で構成不可能なパラメータもあります。
https://docs.microsoft.com/ja-jp/azure/mysql/concepts-server-parameters#non-configurable-server-parameters

ここに記載されていないその他の変数は、既定の MySQL の標準値に設定されています　と記載があり、Azure Database for MySQLではInnoDB関連はチューニング済み…というわけでもなさそうなので注意が必要です。

意外なところだとauto_commit は 1 固定で利用者がAzure portalから変更できませんでした。
アプリケーションはSpringの @Transactional で都度トランザクションを指定し、例外発生時にロールバックするので特に支障ありませんでした。

SQLを業務ロジックと分離して管理する

SQLを業務ロジックに埋め込むと、下記のような弊害があります。

• 文字列結合処理でプレースホルダの利用を忘れ、SQLインジェクションを起こしやすい
• SQL管理のみの定数クラスができ肥大化しやすい
• SQLを複数機能で流用しにくく重複が起きやすい
• アプリケーションからMySQLサーバに発行されるSQLがわかりにくく、バグや性能劣化の原因になるSQLを作り込みやすい
• SQLチューニングしにくい
  • 実行計画が取りにくい
    • いちいちアプリケーションのログを見てSQLを特定してexplainみたいなことになる
  • ヒント句などの指定が書きにくい
    • 特定の要員以外がSQLの修正改善をしたいとき、SQLの修正箇所をどう探せば良いのかわからない
  • indexの管理が行いにくい

というわけで、MySQLに限った話ではありませんが、SQLを業務ロジックと分離して管理したくてMyBatisを導入しました。

SSL接続を利用する

パブリッククラウドなのでアプリケーションの接続ではSSLを必ず使うようにしました。
https://docs.microsoft.com/ja-jp/azure/mysql/concepts-ssl-connection-security

構成方法は調べれば色々なページが出てきますので詳細は割愛しますが、MySQL Connector/Jのバージョンに注意が必要です。
時代は令和時代ですし、bugs #93590の修正もされているので、特に問題なければ8系の最新で良いと思います。

リトライ処理を実装する

Azureでは、Reconfigurationや一時的なエラーに対応するためリトライ処理の実装が推奨されています。
https://social.technet.microsoft.com/Forums/ja-JP/412a2ca5-2e0a-4370-877e-df15dc7a082e/reconfiguration?forum=jpbidp
https://docs.microsoft.com/ja-jp/azure/mysql/concepts-connectivity

実装例は nabedgeさんの クラウド時代だからSpring-Retryフレームワーク(JSUG勉強会 登壇記) を参考にしました。

こちらのスライドではコネクション取得をリトライしていますが、Springを利用するのであれば、リトライしたいサービスクラスのメソッドに Spring Retryのアノテーションを付与してもいいかもしれません。

MySQL Connector/Jのプロパティの調整

過去のMySQL Casual Advent Calendar を参考にしました。
MySQL Connector/Jでプロパティをあれこれ変えてベンチマークその2：余計なお喋りを止める編
MySQL Connector/Jでプロパティをあれこれ変えてベンチマークその3：Prepared Statement キャッシュ編

SQL modeを設定する

Spring Bootの application.properties に kamipo TRADITIONAL を書きました。

spring.datasource.hikari.connection-init-sql=SET SESSION sql_mode='TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY'

Javaアプリケーションのプロパティとタイムアウト類を調整する

Spring Bootのプロパティ一覧、HikariCp を参考に設定します

• Javaアプリケーションの(Tomcatの)スレッド数と、コネクション数を調整する
  • MySQLサーバのmax_connections を超えないように注意
  • コンテナで動かす場合はコンテナ数、複数サーバで動かすあたりはアプリケーションサーバ台数なども考慮が必要です
• アプリケーションとしてのタイムアウトを設置する
  • HikariCPの connectionTimeout, idleTimeout, maxLifetime を設定しました
• MySQLサーバ各種タイムアウト max_execution_time なども適切な値を見つけて設定しました

タイムアウトについては、MySQL Casual Slackでyoku0825さんから助言をいただきました。
チューニングの結果、ひとまず max_execution_time の適正値を見つけて落ち着いています。
ありがとうございます。

最終的には本番相当の負荷試験で検証しましょう。

スキーマ管理にマイグレーションツールを使うか検討する

Flywayの導入も検討しましたが、アプリケーション起動時にDDL/DMLを発行してロックが競合すると対応しきれないので各所と相談の上やめました。

…本音のところ 本番環境でやらかしちゃった人 Advent Calendar 20198日目 | 進まない、マイグレーション を読んだ時点で断念しました。

プロキシ経由でMySQLに接続できるようにする

プロキシでMySQLもSSHも遮断され、開発者、運用者のPCから直接MySQLに接続できないため対応しました。

• 開発環境はPHPMyAdmin経由で接続
  • トラブル防止のため、本番環境ではPHPMyAdminは利用しないポリシーにしました
• 大規模な開発環境での作業、本番作業は踏み台のMySQL Workbenchから実施

運用編

トランザクション利用を手順化する

Azure上のMySQLでは auto_commit が 1 固定で変更できなかったので、本番作業で大量にデータを変更するときにはauto_commitしてしまわないよう作業手順を整備しました。

開発環境のMySQLにアクセスするときはPHPMyAdminを利用していますが、意図的に手でデータ変更するような環境ではないので auto_commitは許容しています。

監視する

Azure Monitorを使いました。

開発運用してみての振り返り

困ったこと・もう少し考えないといけなかったこと

非機能要件、運用要件、運用設計が不十分だった

サービス初期開発にありがちですが、仕様作りにいっぱいいっぱいで考慮できていませんでした。

おかげで本番稼働後もmain1本の構成のままです。チューニングは気軽にできるのですが、レプリカの構築までは手が回っていません。

環境によって動かないSQLがあった

MySQLサーバのパラメータsql_mode と lower_case_table_namesが環境によって統一が取れず、CIのみ動かないSQLやAzure環境のみエラーになるSQLが多発しました。

アプリケーションの動作に関わるMySQLサーバパラメータはできるだけ早めに決め、ローカル開発環境も極力本番に近づけた状態で開発できるようにした方が良かったと思います。

bugs #93590 の対策がわかりにくい

https://bugs.mysql.com/bug.php?id=93590
調べると 『SSL接続を無効にする』『MySQL Connector/Jは5系を使う』など回避策ばかりで当時は混乱しましたが、最終的には MySQL Connector/J の8.0.16を利用して解決しました。
https://github.com/mysql/mysql-connector-j/pull/32

Azureに困った

ドキュメントがわかりにくい

こんな感じで乗り切ってきました。

• RDSを利用しているページも参考にする
  • Javaアプリケーションからの利用であれば、AWSかAzureかはあまり関係ないためほぼ参考にできました
  • ただし、運用関連はあまり参考にはできないと思います。公式ドキュメントはどんなにわかりにくいと感じた場合でも必ず利用サービスのものを確認するしかありません
• 日本語訳のない英語版ドキュメントも目を通す
  • 慣れですが英語の方が意図が分かり易いと感じました
• わからないことはサポートに問い合わせる

試していませんが、Azureであればドキュメントに改訂や翻訳のPull Requestを出して貢献していくのも手かと思います。

Azure Database for MySQLでハマったこと

• 一般公開されている利用ノウハウが少なく、情報も少し古い
  • 差し支えない範囲で積極的に発信して行くのが良さそうです
• 利用者側は特定の操作設定しかできない
  • 詳細なチューニングや広範の運用コントロールをしたい場合は、PaaSのMySQLは向かないかもしれません
  • 今後フレキシブルサーバの正式版が公開されると状況は変わるかも、と感じています
  • サーバー停止できなかったのが最近 停止できるようになっていました 。PaaSなので意外なことができないと思うことがあるかもしれません
• プランごとにパラメータの上限値がある
  • 例えば max_connection を増強したいケースなどは、新しいプランでサーバを作り直してデータ移行することになりそうです
• 気がつくと機能追加されていたり、ドキュメントが変わっている
  • フレキシブルサーバー など続々と機能が増えています
  • こまめにチェックするしかありません。

MySQL 8ではなく5.7を使っている

開発者が間違ってローカルでMySQL 8をインストールして使っていたことがありました。

令和時代ですし機能改善もたくさんされているので、特別な理由がなければ 積極的にMySQL 8を使うのが良いと思います。

良かったこと

Azure Database for MySQLで良かったこと

フルマネージドで楽々運用

上記で色々困ったことを書きましたが、アプリケーションエンジニアとマネジメント中心のプロジェクトなので、OS、ハード、IO、ネットワークを利用者が全く意識しないで安定稼働できていて便利です。

現時点では Azure Database for MySQL で稀によく遭遇する DBエラー にも遭遇していませんし快適です。

バックアップも簡単にAzure portalで設定でき、 mysqldump で定期バックアップを取る必要は今のところありません。
ポイントインタイムリストアもAzure portalから簡単に行えます。
https://docs.microsoft.com/ja-jp/azure/mysql/concepts-backup#point-in-time-restore

※必ずリカバリ訓練をして検討しましょう

スキルの焦点を絞れた

アプリケーション開発者に必要なMySQLのスキルを絞ることができ、下記がしっかりできれば運用できる自信もつきました。

• MySQLのサーバパラメータ設計
• パラメータチューニング
• 負荷試験の実施
• SQL品質の担保
• SQLチューニング

(Azureに関係なく) MySQLでよかったこと

コミュニティが充実している

• MySQL Casual Slackには数回お世話になりました. yoku0825さんの素早さと豊富な知識は勉強になりました
• インターネット上に情報が豊富
• 書籍も豊富

ツールが良い

PHPMyAdminとMySQL Workbenchがなければ安定した開発運用はできませんでした。

ほかにもいろいろありますが、運用系ツールを使えていないので試してみたいです。

これからもMySQLを勉強して活用していきたいです。
最後までお読みいただきありがとうございました。