MySQL徹底入門 第4版を読んで

運用に必要な知識

MySQLのプロセスアーキテクチャ

  • MySQLはシングルプロセス・マルチスレッドモデルのデータベース。
    • psコマンドのauxや-elfなどのオプションではmysqldのプロセスは1つしか見えない。
    • 内部的には複数のスレッドを使い分けている
  • スレッドは、フォアグランドスレッドとバックグランドスレッドに分けられる
    • フォアグランドスレッドは外部のクライアントと1対1で紐づく
      • 1つの接続が1つのスレッドになり、接続が切断されるまでの同一のスレッドが処理をする
    • 現在のコネクション（= スレッド）の情報はperformance_schemaのthreadsテーブルまたはSHOW PROCESSLISTで確認できる
      • 実行時のオーバーヘッドや情報の粒度などからperformance_schema.threadsテーブルの方が推奨されている

MySQLのパラメータ

  • MySQLにはたくさんのパラメータ（設定可能項目）があり、分類ごとにいくつかの特性を持っている。
    • オンラインで変更が可能なパラメータと設定変更にMySQLの再起動が必要なパラメータがある。
  • my.cnfの読み込み順序
    • /etc/my.cnf
    • /etc/mysql/my.cnf
    • SYSCONFDIR/my.cnf
    • $MYSQL_HOME/my.cnf
    • -default-extra-file
    • $HOME/my.cnf
    • $HOME/.mylogin.cnf
    • DATADIR/mysqld-auto.cnf
    • コマンドラインオプションで与えられた引数
  • オンラインで変更したパラメータを永続化するにはSET PERSISTステートメントが使える
    • SET PERSIST/SET PERSIST_ONLYで変更されたパラメータはDATADIR/mysqld-auto.cnfにJSONの形式で記録される
      • このファイルは管理者が直接編集することを想定していない。
      • mysqld-auto.cnfに記録されたパラメータの設定を削除するには、RESET PERSISTステートメントを利用する
      • 各パラメータがどこで設定されたかはperformance_schema.variables_infoテーブルで確認できる

MySQLのバージョン情報

MySQLのバージョンは、SELECT VERSION(); や SHOW VARIABLES LIKE 'version'; などで調べることができる。

運用設計

バージョン選定

MySQL 8.0系列は同一リリース系列内でも非互換性があるため、バージョンの選定は慎重にされなくてはならない。 クライアントやライブラリのバージョンはMySQLサーバと同じかそれ以上であることが必須。MySQL 8.0系列ではバージョン番号の信仰に伴って新機能が追加されてしまうため、 その機能やその周辺に新規にバグが内在する可能性がある。

現時点で確実に進められることは次の通り。

  • 導入時点での最新バージョンを選び動作確認をする
  • 導入以後のアップグレードに備えてSQLレベルでの非互換性を検出できるように回帰テストを作成する
  • アップグレードをする際にはクライアントのバージョンを先にあげる

バックアップ設計

  • バックアップの間隔
    • バックアップにはフルバックアップと増分バックアップがあり、両方揃って初めて任意の時点のデータを復元できる
    • フルバックアップの頻度
    • 増分バックアップの頻度
      • レプリケーション構成を組んでいる場合、間違いなくマスターではバイナリログが出力されているが、これをマスターのストレージ以外の場所に保管しておくことが必要。
        • マスターだけにバイナリログが保管されている（増分バックアップを取得しない）場合、MySQLが稼働しているマシンが完全に壊れ、ストレージからMySQLのデータはおろかバイナリログすら取り出せなくなってしまうと最後のフルバックアップの時点までしか復元できない
    • リストアにかかる時間の見積もり
      • フルバックアップの頻度を決める上で重要なのが「フルバックアップをリストアするのにかかる時間」と「一定期間のバイナリログを適用するのにかかる時間」。これらをできるだけ正しく見積もることは大切。
      • フルバックアップのリストア時間はデータの総容量に依存し、バイナリログの適用はバイナリログの量（= データが更新される頻度）に依存する
    • バックアップの運用計画に関するパラメータ
      • 増分バックアップの計画に大切なパラメータとしてbinlog_expire_logs_secondsという値がある。
        • これは、現在のバイナリログがスイッチした時点で過去の古いバイナリログを削除するためのパラメータ。
      • バイナリログがスイッチするタイミングは、max_binlog_sizeというパラメータで決定される。
        • 追記されていったバイナリログのサイズがこのパラメータで設定されて値よりも大きくなると、バイナリログのスイッチが発生する。
      • log_slave_updates
        • デフォルトはONで、スレーブはマスターからレプリケーションで受け取った更新差分を自身（= スレーブ）のバイナリログに出力する。これによりマスターとスレーブで二重にバイナリログが保管され、バイナリログの喪失への耐性が上がる。

モニタリング計画

MySQLが機能を提供しているかどうかを確認する「死活監視」と、中長期的にリソースの利用状況がどうなっていくかを確認する「リソース監視」の2つが必要。

  • 死活監視
    • MySQLの機能を提供するプロセスはmysqld。これが起動していない場合、MySQLはその機能を提供できない
    • mysqld_safeというプロセスが起動している場合もあるが、これはmysqldプロセスの起動を補助するラッパースクリプトなのでmysqldではない
    • MySQLにバンドルされているmysqladminコマンドにはpingサブコマンドがある
      • 「MySQLプロトコルでmysqldに対してアクセスし、応答があれば成功、なければ失敗」と判定する
    • 「MySQLとしては異常はないが、MySQLに原因がある」ような状況を発見するには、アプリケーションログをしっかり監視するのが有効
    • 死活監視の内容
      • MySQLプロトコルでの接続が可能か
      • SHW PROCESSLIST上で長時間実行され続けているクエリがないか
      • 現在の接続数とmax_connectionsを比較して、100%に近づいていないか
      • スレーブであれば、SHOW SLAVE STATUSのSeconds_Behind_Master（= 更新がマスターに実行されてからスレーブの反映までかかっている時間）が大きくなっていないか
      • 各テーブルのAUTO_INCREMENT属性カラムが、データ型の最大値近くまで払い出されていないか
      • Uptimeが小さすぎないか
  • リソース監視
    • 直接的なエラー以外の、レスポンス速度低下の原因究明や性能限界の計測に役立つ
    • 「問題発生以前」、「問題発生中」、「問題終息後」の３時点のリソースを比較するためにも継続的なデータの蓄積は必須
  • MySQLの各種ログ
    • エラーログ
      • MySQLサーバはプロセスに発生した異常をエラーログに出力する
    • スロークエリログ
      • mysqldがSQLを処理するのにかかった時間を記録するログ
      • クエリの処理にかかった時間はMySQLサーバを利用する環境にとって非常に重要
      • できる限り有効にしておくのがよい
      • my.cnfにslow_query_logオプションを記載する or サーバ変数のslow_query_logをONに設定
    • ジェネラルログ
      • 一般クエリログとも呼ばれ、mysqldが受け取ったすべてのクエリを記録するためのログ
      • アプリケーションのデバッグ用途に用いるためのログであり、恒常的にONにしておくようなログではない
    • ログファイルのローテーション
      • MySQLサーバはそれ単体ではログローテーションの仕組みを持たない
    • その他のログと名のつくもの
      • MySQLサーバが内部動作を記録するためのログであり、テキストとして開くことはないが、削除しないようにしないといけない
        • InnoDB ログ
        • バイナリログ
        • リレーログ

実際の運用作業

パラメータチューニング

すこしくらいパラメータを変更したところでパフォーマンスが劇的によくなることはない。事前に綿密な計算のもと、パラメータを算出するよりも運用中に効果を測定しながら継続的にパラメータの最適値に近づけていくことが大切。

  • 事前に計算しておくべきパラメータ
    • innodb_buffer_pool_sizeは重要なオプション
      • InnoDBのすべてのテーブルで利用されるバッファプール（メモリ上に配置）のサイズを指定する
      • SELECTのときにキャッシュとして使われるのみならず、InnoDBのすべての操作を一度バッファプールを経由して処理されるため、あらゆるDMLやDDLの速度に影響を及ぼす。
      • 物理メモリが小さい（例えば4GB以下）ときは50%程度、十分大きい（例えば12GB以上）ときは80%などと単純な割合ではなく、InnoDBバッファプール以外にどれくらいメモリを残しておくべきかから算出するのがよい
      • innodb_buffer_pool_sizeはオンラインで変更できる（MySQL 5.6とそれ以前は再起動が必要だった）

クエリチューニング

MySQLのパフォーマンス問題の多くはクエリ単位に分解して考えることがベター。単一のクエリが遅い場合はもちろん、複数のクエリがロックやリソースの競合を起こしている場合でも、個々のクエリのスピードをあげてやることで全体としてのパフォーマンを改善できる パラメータのチューニングがせいぜい数倍程度にしかパフォーマンスの問題を改善できないのに対し、正しくチューニングされたクエリは数百倍から数千倍のパフォーマンスが出る。

  • インデックスの仕組み
    • 最もコストパフォーマンスが高いのは「インデックスを使わせること」
    • INSERT/UPDATE/DELETEといった更新処理はインデックスも同時にメンテナンスしなければならないため、更新の負荷はインデックスがない場合よりも大きくなる
    • 特定のクエリに対してベストなインデックスであっても、クエリの形が変わると同じインデックスがベストとは限らない
    • 複数カラムからなるインデックスはカラムの順番で効率が変わることがある、インデックスの大部分を操作しなければならないアクセスパターンは効率がよくない
  • インデックスによるWHERE句のチューニング
    • カードをめくって目視が1番コストの高い動作になるため、可能な限りこれを避けるようにインデックスを追加していく
    • どのくらいカードをめくって目視が発生する見積もりであるかは、実行したいクエリの先頭にEXPLAINキーワードをつけることで確認できる
      • rowsがカードをめくって目視する見積もりを表しており、filteredがカードをめくってチェックしたうち、何%程度が実際にマッチする行である見積もりを表している
      • ExtraのUsing whereがインデックスだけではWHERE句を解決しきれないので目視のフィルタリングが必要であることを表している
    • 不要なインデックスを作ってしまうことを重要視するよりも、不要になったインデックスを安全に検出して削除することを継続指定実践していいくのがよい
  • WHERE句のチューニングによる改善すべきクエリの特定
    • WHERE句にインデックスが使えずに非効率になっているクエリの特定は容易
      • 特定にはスロークエリログまたはperformance_schemaが使える
        • 実行時にフィルタがかけられる前者の方が便利
        • 後者はWHERE句などの定数をノーマライズしたダイジェストと呼ばれる単位で蓄積していくため、「リソースに余裕がある何の問題もないが、トラフィックがバーストすると問題として顕在化する」ようなクエリを探すのに便利
        • スロークエリログの Rows_examined / Rows_sent の値が1 に近ければ近いほどWHERE句の効率が良く、大きければ大きいほど非効率
  • インデックスによる ORDER BY ... LIMIT のチューニング
    • MySQLのインデックスはWHERE句による絞り込み以外に、ORDER BY句に対する効率化にも利用可能
  • ORDER BY ... LIMIT のための「優先度付きキュー」によるソート
    • 「優先度付きキュー」はMySQL 5.6とそれ以降で追加された比較的新しいORDER BY がLIMIT句とともに使われている場合の最適化方式
    • ソートバッファを優先度付きキューとして扱うことでORDER BY ... LIMITを省メモリで効率的に処理する最適化が働く。
      • この最適が行われたとしても「ソートに必要なカラムの値をすべて読み取らなければならない」という点は変わらない
      • この最適化はオプティマイザが自動で選択するので、「チューニング」ではなく「そういうもの」と知っていればよい
  • ORDER BY ... LIMIT のチューニングによる改善すべきクエリの特定
    • ORDER BY句をインデックスで解決できているか、いなかを判断するためにもスロークエリログまたはperformance_schemaを使う
  • クエリの書き換えによるチューニング
    • アプリケーション側の発行するクエリを変えることによって高速化する
    • ループ構造による繰り返しクエリ
      • N+1問題
        • Nの数が十分に小さいあいだはアプリケーション側でも遅いと感じられることがなく、データの増加にしたがってNが大きくなったときに初めて顕在化する
        • JOINを使ってSQLレベルで処理できる。アプリケーションとMySQLの通信は1回だけになり、ネットワーク負荷とCPU負荷を下げられる
    • 大量データフェッチへの対処
      • 大量データのフェッチによってI/Oを枯渇させる
      • アプリケーション側でフェッチする範囲を狭めてループで複数回実行することが負荷の軽減につながる
    • LIMIT句とOFFSET句
      • ORDER BY句とLIMIT句を併用することで、ソートに優先度付きキューを使った最適化やORDER BY狙いのキーを使った追加ソートを不要とする方法がある。
      • OFFSET句がカーソル操作をする対象は生成済みの結果セットに対してのみ
      • 巨大な1つのクエリを分割するときにLIMIT句とOFFSET句を併用した場合、個々のクエリで削減できるのはネットワーク転送量だけ
      • LIMIT句とORDER BY句の組み合わせは、パフォーマンス上の問題を引き起こしやすく、ORDER BY句の考慮漏れなどで正しい結果セットを得られなくなる可能性もあるため、注意が必要
    • ALTER TABLE実行時のTIPS
      • MySQLは以前のバージョンからオンラインALTER TABLEと呼ばれる機能を備えている
        • オンラインの意味はALTER TABLE中に読み書きが可能という意味
      • オンラインALTER TABLEがサポートされているかどうかを確認する
        • LOCK句はALTER TABLE中のDMLに対してロックを指定するための構文で
        • LOCK=NONEがALTER TABLE中のDMLを制限しない、すなわちオンラインALTER TABLEを表す
        • LOCK=SHAREDは、ALTER TABLE中には読み取りDMLのみ許可する（= 読み取り専用の）ロックを置くということで、、旧来と同じ読み取り専用のALTER TABLEになる
      • ALTER TABLEとレプリケーション遅延
        • マスターで1時間かかったクエリはスレーブでも1時間かかること、マルチスレッドスレープを有効にしてもALTER TABLEは追い越せない
          • マスターでALTER TABLEに要した時間と同じだけスレーブでへの反映が遅延する
        • レプリケーションの遅延が許容できない場合、オンラインALTER TABLEであってもアプリケーションを停止してからメンテナンスとしてALTER TABLEを実行する必要がある
      • 安全なインデックス削除のための不可視インデックス
        • インデックスを削除するDROP INDEXオペレーションは比較的高速に実行できるが、ADD INDEXオペレーションはそれなりに重い処理になるため、切り戻しが必要となった場合に時間がかかることがある
        • このような事態を避けるための「不可視インデックス」
          • 不可視とは、テーブル定義及びデータ構造上には存在するものの、オプティマイザから決して選択されない（= 見えない）ことを指している
          • 通常のインデックスと不可視インデックスの切り替えは一瞬で済むため、インデックスが削除されたあとに影響が出ないかを確認でき、万一影響が出たとしても切り戻しも一瞬でできる

ユーザ管理

  • グローバル権限はNG
    • MySQL 8.0.16以降では partial_revokes オプションによってブラックリスト的に制御できるようになったが、過大な権限は危険
    • 現実的な権限制御
      • データベース単位で原則の権限を付与（GRANT）
      • 必要に応じてテーブル単位で拒否（REVOKE/partial_revokes オプションが必要）

MySQLのさまざまな状態をみる

STATUSコマンド

  • 動作中のMySQLサーバーや、接続に関する基本的な情報を表示
  • Current user
    • 接続ユーザ
  • Threads
    • 現在MySQLサーバーに接続中のスレッド数
  • Question
    • MySQLサーバーが起動してから発生したクエリーの数
  • Slow queries
    • MySQLサーバーが起動してから発生したスロークエリー数
  • Opens
    • MySQLサーバーが起動してからオープンされたクテーブルの数
  • Open tables
    • 現在オープンされているテーブルの数

SHOWコマンド

  • データベースオブジェクトの情報やさまざまな状態を表示する
  • SHOW DATABASES
    • 現在接続中のユーザーがアクセス可能なデータベースの一覧を表示
  • SHOW TABLES
    • カレントデータベース内に存在するテーブル及びビューの一覧を表示
  • SHOW CREATE TABLE table_name
    • 指定したテーブルのCREATE文を返す
  • SHOW COLUMNS FROM table_name
    • 指定したテーブルまたはビューを構成するカラムの情報を表示
    • DESC table_name でも同じ結果を表示

サーバーや接続の動作オプションを見る

SHOW GLOBAL VARIABLES, SHOW SESSION VARIABLES（SHOW VARIABLES）

MySQLシステム変数の値を確認したいときには、SHOW VARIABLESを使用する。 セッション変数は@@SESSION、グローバル変数は@@GLOBALそれぞれの要素として確認できる。

動作中の状態を見る

  • SHOW STATUSコマンド
    • MySQLサーバーの実行状態に関する値を表示。メモリやディスクの使用状態、接続数に関する情報などが含まれている。
  • SHOW PROCESSLIST
    • その時点で稼働中のプロセスを一覧表示

発生したエラーや警告を見る

最後に実行したクエリーで発生したエラーや警告の内容を確認するには、SHOW ERRORS, SHOW WARNINGS命令が便利

レプリケーション

レプリケーションは「複数のMySQLサーバのデータを同期する」ための仕組みで、3.23 以前から20年以上にわかって提供し続けている枯れた機能

レプリケーションの仕組み

MySQLのレプリケーションの構成では「マスターとスレーブに同一のデータを用意する」「」マスターに更新SQLを実行する」「マスターからスレーブに更新SQLを転送する」「スレーブが転送された更新SQLの内容をリプレイする」ことでデータを同期する。 「まったく同じデータを持ったMySQLに対して」「まったく同じSQLを実行すれば」「実行後のデータはまったく同じになる」という「結果整合性モデル」でレプリケーションは動作する。

（非同期）レプリケーションのデータ同期

古くからあるこの方式では、「クライアントから更新SQLを受け杖ける（唯一の）サーバー」を「マスター」、「マスターから間接的に更新SQLの内容を受け取るサーバー」を「スレーブ」と呼び、その関係は非対称。 マスターにはBinlog Dumpと呼ばれるスレッドが待機しており、このスレッドがバイナリログの更新を検知してスレーブにバイナリログイベントを送信する。Binlog Dumpスレッドはマスターと接続しているスレーブに対応して1対1で常駐する。 Binlog Dumpスレッドに対応する形でスレーブには「（スレーブ）I/O」が起動しており、この2つのスレッドがTCP通信でコネクションを開いている。コネクションからバイナリログイベントが送信されてくると、それをI/Oスレッドが受信してリレーログと呼ばれるファイルに記録する。 スレーブにはさらに「（スレーブ）SQL」スレッドが常駐しており、リレーログの更新を待ち受けている。このSQLスレッドがリレーログからバイナリログイベントを取り出し、スレーブ上のデータに対してリプレイすることで、マスターとスレーブのデータが同一となる。 このリプレイ時にスレーブにはスレーブサーバー常に新たにバイナリログを記録する。

  • 非同期レプリケーションと準同期レプリケーション
    • 非同期レプリケーション
      • 「マスター上のデータとバイナリログの更新は同期を保証」「それ以降のスレーブへの電波については時間を保証しない」設定
    • 準同期レプリケーション
      • 「マスター上のデータとバイナリログ、および（最低1台の）スレーブのリレーログへの更新は同期を保証」「最初の1台以外のスレーブ、および最初のスレーブにおいてもリレーログからデータの反映は、時間を保証しない」設定
    • 非同期レプリケーションと準同期レプリケーションの違いは、（主にマスタークラッシュ時の）データの永続性にある
  • マルチソースレプリケーション
    • 「マスター多：スレーブ1」の構成のこと
      • 「ソース」はバイナリログイベントを受け付ける「元」を表す。つまり、複数マスター構成
    • 「テーブル構成は同等だがユーザーIDなどによって収容先のMySQLが分かれている」（= シャーディングされている）構成をひとつのMySQLにまとめる、などの使い方をすることも可能
  • 多段レプリケーション構成
    • スレーブがマスターから受け取りリプレイしたバイナリログイベントを、さらにスレーブ自身のバイナリログに記録することによって、「1次スレーブをマスターに見立てた2次スレーブ」を構築できる。
      • 「多段レプリケーション構成」、「カスケードレプリケーション」などと呼び、1次スレーブを「子スレーブ」「中間マスター」「中間スレーブ」、2次スレーブを「孫スレーブ」などと呼ぶ
  • グループレプリケーションのデータ同期
    • グループレプリケーションでは「クライアントからの更新SQLを受け付けるサーバー」を「プライマリー」と呼びそれ以外のサーバーを「セカンダリー」と呼びます。
    • 「プライマリーが1台のみ選出されるシングルプライマリーモード」と「すべてのサーバーがプライマリーになるマルチプライマリーモード」の両モードが存在し、とくに後者の場合はマスター/スレーブ構成とは大きく異なる。
    • 更新SQLを受け付けたプライマリーサーバーはグループコミュニケーションエンジンに更新SQLの内容を渡し、各サーバーのグループコミュニケーションエンジン同士がサーティフィケーションと呼ばれる処理を経てリレーログに書き込む。

レプリケーションはデータの複製のための機能。マスターへの更新はバイナリログから始まる形でスレーブに非同期に差分更新される。