『High Performance MySQL 第4版』を読んだ内容を章ごとにまとめています。
第5章 サーバー設定の最適化
概要
この章が言いたいことは「設定項目を片っ端からいじって最適値を探す」のではなく、ワークロードに合わせて“効く少数の設定”だけを、観測しながら安全に決めることである。
そのために、(1) 設定がどう適用されるか、(2) やってはいけないチューニングの癖、(3) 最小構成から始める作り方、(4) メモリ/I/O/並行性/安全の“軸”ごとの考え方を整理する。
第4章がOS/ハードウェアという土台を整える話だったのに対し、本章はその上でMySQL本体の設定をどう作り、どう変えるかに焦点を当てる。
MySQL設定の仕組み（どこに書くと効くか）
設定は、設定ファイル（Unix系なら /etc/my.cnf や /etc/mysql/my.cnf など）とコマンドライン引数から読み込まれる。設定ファイルはセクション（例：[mysqld]）に分かれていて、正しいセクションに書かないと効かない。また、恒久的に必要な設定はコマンドラインではなく設定ファイルに寄せる（起動時に付け忘れる事故を避ける）という前提が置かれている。
変数にはスコープがあり、グローバル（例：max_connections）とセッション（例：sort_buffer_size のようにセッションで上書きできるもの）がある。さらに join_buffer_size のように、1クエリ内のJOIN回数ぶん確保され得るものもあるため、単純に「値を上げれば速い」と考えるのは危険である。
オンライン変更については、SET GLOBAL で変えられるものは多い一方、通常は再起動で戻る。MySQL 8.0では SET PERSIST が導入され、ランタイムで変えた値をディスクに保存して次回起動にも反映できるようになった。逆に、DEFAULT は便利だが、グローバルに対して使うと「設定ファイルの値に戻す」ではなく「コンパイル時デフォルトに戻る」挙動になり得る点が注意されている。
もう1つ重要なのが「変数変更の出方」である。table_open_cache や thread_cache_size のように、変えても即時に効果が出ない（次回テーブルを開く/接続が閉じるタイミングで効く）ものがあり、read_buffer_size のように必要になった瞬間に指定サイズを一括確保するものもある。章はこの“副作用と遅延適用”を理解した上で、変更を計画せよと言っている。
やってはいけないこと（この章の強い主張）
- ベンチを回しながら設定を反復調整して“チューニング”するのは、多くの現場では費用対効果が低い。
- 「バッファプールのヒット率が低いから大きくする」などの“比率チューニング”をしない。ヒット率はワークロード依存で、適正サイズや性能を直接示さない。
- ネットのテンプレやチューニングスクリプトを鵜呑みにしない（前提が違えば簡単に逆効果になる）。
- MySQLがクラッシュ時に表示する“メモリ消費の公式”を過信しない（MySQLは厳密に上限を管理するタイプではない）。
設定ファイルを作る手順（最小構成→観測→少数変更）
まずは最小構成から始め、必要な場所（datadir / socket / pid-file など）は明示的に固定する。open_files_limit は不足すると “too many open files” になるので、章では「典型的なLinuxでは可能な限り大きく」を勧めている。
MySQL 8.0の innodb_dedicated_server は、専用DBサーバー前提で innodb_buffer_pool_size / innodb_log_file_size / innodb_log_files_in_group / innodb_flush_method を自動調整する。VMのメモリサイズが変わるようなクラウド運用でも追従しやすく、「まず妥当な初期値を作る」近道として紹介されている。
そして設定を決める材料として、SHOW GLOBAL STATUS を“差分推移”で観測し、ピーク/非ピーク双方の動きを見ることが勧められる（補助ツールとして pt-mext や pt-mysql-summary が挙げられる）。ベンチより、SLO/SLIの変化で判断せよ、という第2章の思想がここでも前提である。
メモリの軸（接続・スレッド・バッファプール）
メモリの話は「InnoDBバッファプール」と「接続ごとの消費」に分けて考えるのがこの章の流儀である。
接続数をどう扱うか（max_connections / wait_timeout / thread_cache_size）
max_connections は“接続嵐への非常ブレーキ”として位置付けられる。章ではデフォルトが 151 であること、そして例として「通常300接続程度なら 500 程度」など、通常運用＋管理用余裕で決める考え方を示している。一方で、接続が増えるほど接続ごとのメモリ消費が積み上がり得るので、上げすぎは別の破綻（メモリ枯渇）につながり得る、という含みがある。スパイクの把握には max_used_connections を見る、というのも章の具体的な助言である。
wait_timeout は、アプリ再起動などで接続がきれいに閉じず残留するケースがある、という落とし穴とセットで語られる。残留接続が max_connections を圧迫すると、新規接続が弾かれるので、接続ライフサイクルを設計するうえで無視できない。
thread_cache_size は、接続の“揺れ幅”を吸収するために使う。章は具体例として、Threads_connected が普段100〜120なら 20、500〜700なら 200 という考え方を示し、Threads_created が「1秒あたり10未満」になるのを1つの目安にする。要するに「スレッドを作り直す頻度が高いなら、波をキャッシュで吸収する」という運用に落とし込める。
接続ごとバッファは“全体で盛らない”（read_buffer_size / join_buffer_size など）
この章が特に警戒しているのが、接続（やクエリ実行）ごとに確保されるバッファを、グローバルで安易に引き上げることである。read_buffer_size は必要になった瞬間に指定サイズを一括確保するタイプで、join_buffer_size はJOIN回数ぶん確保され得る。つまり「一部の遅いクエリを直したい」つもりで全体の値を盛ると、ピーク時に想定外の総メモリ消費に倒れやすい、という構造である。章は、まずクエリ/スキーマを直し、それでも必要なら局所的に検討する、という順序を一貫して推している。
InnoDBバッファプール（innodb_buffer_pool_size と停止/起動の副作用）
innodb_buffer_pool_size は最重要変数として扱われる。バッファプールはデータ/索引だけでなく内部構造（ロック等）も保持し、書き込みの平準化にも寄与する一方で、大きすぎると停止/起動が重くなり得る。章は、計画停止ができるなら innodb_max_dirty_pages_pct を下げてダーティページを減らしてから落とす、という手段や、起動直後のコールドキャッシュ対策として innodb_buffer_pool_dump_at_shutdown / innodb_buffer_pool_load_at_startup の連携を紹介する。
また innodb_fast_shutdown については「速く落としても起動時の回復作業が増えるだけで、停止→起動の総時間が速くなるとは限らない」という趣旨で、安易な“時短”に釘を刺している。
I/Oと耐久性の軸（ログ・フラッシュ・binlog）
I/Oは、性能と耐久性（ACIDのD）のトレードオフが最も露骨に出る領域として説明される。
トランザクションログ（innodb_log_file_size / innodb_log_files_in_group / innodb_log_buffer_size）
InnoDBは「コミット時にバッファプールを丸ごとフラッシュする」のではなく、まずログに書くことでランダムI/Oをシーケンシャル寄りに変換する。したがってログサイズ（innodb_log_file_size と innodb_log_files_in_group）は書き込み性能に直結し、innodb_dedicated_server を使うと自動管理される。
innodb_log_buffer_size について、章はデフォルト 1MB を示しつつ、大きなトランザクションがある場合の目安として 1〜8MB 程度を挙げている（巨大BLOBなどが典型の文脈である）。
コミット時の耐久性（innodb_flush_log_at_trx_commit）
innodb_flush_log_at_trx_commit は、設定値によって「どれだけ失われ得るか」が明確に変わる。章の説明は次の通りである：
1（デフォルト）：毎コミットでフラッシュし、最も安全。2：毎コミットで書き出すがフラッシュは毎秒（mysqldプロセスのクラッシュには強いが、電源断等では失われ得る）。0：コミットでは何もせず毎秒（最大1秒程度の損失が起き得る）。
ここで重要なのが「書き出し」と「永続化（フラッシュ）」が別物だという点である。章は、OSやコントローラがフラッシュを“偽装”すると、単なるトランザクション喪失にとどまらず破損の原因になり得る、と強く警告する。結論として、真面目な本番ワークロードなら 1 を基本にしつつ、ハードウェア（バッテリーバックアップ付き書き込みキャッシュ＋SSD等）で性能と安全性を両立するべきだ、という立場になる。
binlogの耐久性（sync_binlog）
sync_binlog は章がかなり強く 1 を推奨する項目である。1 以外にすると、クラッシュでbinlogとトランザクション整合が崩れ、レプリケーションや復旧（特にGTID利用時）を壊し得る、というのが理由である。I/Oコストよりも「壊れたときの復旧不能」を重く見るべきだ、という判断軸が示されている。
I/O方式（innodb_flush_method）
innodb_flush_method はログ/データ両方のI/O挙動（読み取りも）に影響し、性能差が大きいので理解してから変えるべき、と位置付けられる。Unix系でバッテリーバックアップ付きRAID書き込みキャッシュがあるなら O_DIRECT が推奨される。innodb_dedicated_server を使うとここも自動で選ばれる。
容量・テーブルスペースの軸（増えたら縮まない問題）
テーブルスペースは単なる「表の保存先」ではなく、undoや各種内部構造も含む領域として説明される。innodb_data_file_path / innodb_data_home_dir で共有テーブルスペースを構成できるが、複数ファイルにしても末尾連結で埋めていくため、単純なドライブ分散にはならない。autoextendは便利でも、一度伸びたら基本縮まないため、上限設定が推奨される。縮小したいなら基本はダンプ→作り直し→リストアで、InnoDBはファイル整合に厳格なので安易なファイル操作は起動不能につながる、という強い注意が入る。
innodb_file_per_table は、テーブルごとに .ibd を持つことでDROP時の回収を容易にする一方、歴史的にDROPが遅い問題があったことも説明される（MySQL 8.0.23時点では大きく改善された、と章は述べる）。章の結論は「innodb_file_per_table を使い、共有テーブルスペースは上限設定で管理しやすくする」である。
さらに、長時間トランザクション（特に REPEATABLE READ）があると古い行バージョンが捨てられずundoが膨らみ、テーブルスペースが成長し続ける、という“容量事故”のメカニズムが説明される。兆候として SHOW ENGINE INNODB STATUS の history list length を見て、必要なら innodb_purge_threads / innodb_purge_batch_size を検討し、それでも破綻しそうなら innodb_max_purge_lag で更新系を意図的に遅くしてでもパージを追いつかせる（魅力的ではないが代替がない）という対処が提示される。
高並行の軸（詰まったときに何を触るか）
高並行で詰まるなら、章はまず「古いMySQLならアップグレード」「それでも厳しいならシャーディング」を基本線に置く。それでも必要なら innodb_thread_concurrency でInnoDBカーネル内に入れるスレッド数を制限し、まずCPUコア数程度から試す、という進め方を示す。
待ち方の調整として innodb_thread_sleep_delay（デフォルト 10000 マイクロ秒）に触れ、小さなクエリが多いとレイテンシ要因になり得ると述べる。ほかに innodb_concurrency_tickets（通常変更不要、極端に長いクエリが多い場合に検討余地）や、コミット段階の同時実行を制御する innodb_commit_concurrency が紹介される。
安全設定（運用事故を減らす）
安全設定は「性能に直接効く」よりも「事故る確率を下げる」視点でまとめられている。例えば max_connect_errors はデフォルト 100 が小さすぎて、短時間のネットワーク不調でホストがブロックされやすいので引き上げを検討する、という具体的な指摘がある。
また、DNSが認証時の罠になるとして skip_name_resolve が推奨される（有効化するならGRANTをIP/ワイルドカード/localhost に寄せる必要がある）。sql_mode は互換性に影響し得るため、ONLY_FULL_GROUP_BY などの厳格化はまず開発/ステージングで検証してから導入する、という段階導入の方針が示される。sysdate_is_now も、SYSDATE() の非決定性がレプリケーションやPITRに悪影響を与え得るため、意図がなければ決定的に寄せる選択肢として紹介される。レプリカについては read_only / super_read_only を強く推奨し、うっかり書き込みによる整合崩れを防ぐべきだ、としている。
ケース別の当てはめ（複数の負荷が同居する場合の“優先順位”）
章の考え方は “まず壊れない基準線を引いて、そこから詰まりの方向にだけ少数変更する” である。
ここでの基準線は、耐久性と復旧性を落とさないこと（innodb_flush_log_at_trx_commit と sync_binlog）と、メモリ/I/Oの土台（バッファプールとログサイズ）である。
まず「専用DBサーバーなら innodb_dedicated_server を使う」が近道で、少なくとも innodb_buffer_pool_size / innodb_log_file_size / innodb_log_files_in_group / innodb_flush_method という“よく効く4点セット”をワークロードに合わせやすくする。使えない場合でも、章の結論は「重要なのは innodb_buffer_pool_size と innodb_log_file_size」という優先順位である。
読み取りが重い（常時アクセスが多い）とき
読み取りが遅いときは、まず innodb_buffer_pool_size が適正かを疑う。バッファプールが小さいとI/Oに寄って不安定になり、逆に巨大すぎると停止/起動やウォームアップが重くなる。計画停止できる環境なら innodb_max_dirty_pages_pct を使ってダーティを減らしてから停止する、起動直後がつらいなら innodb_buffer_pool_dump_at_shutdown と innodb_buffer_pool_load_at_startup で温める、という“運用込み”の具体策が章で提示される。
一方で、read_buffer_size / sort_buffer_size / join_buffer_size のような「接続やクエリごとに増えるメモリ」は、章が最も警戒している領域である。読み取りが遅いからといってグローバルで盛るのではなく、まずクエリ/インデックスを直し、必要なら局所的に検討する、という順序に戻すのが章のスタンスである（join_buffer_size はJOIN回数ぶん確保され得る点が特に危険だ）。
書き込みが重い（更新・INSERTが多い）とき
書き込みはログが支配的になりやすいので、章は innodb_log_file_size（と innodb_log_files_in_group）を重要視する。コミット時の耐久性は innodb_flush_log_at_trx_commit に集約され、値の意味は明確である：基本は 1（デフォルト）で安全、2/0 は耐久性（D）を捨ててもよい用途に限定、という判断になる。ここで章が強調するのは「書き込み」と「永続化（フラッシュ）」は別物で、OS/コントローラのフラッシュ偽装は破損の原因にもなり得る、という点である。
binlog側は sync_binlog=1 を強く推奨する。理由は、クラッシュ時にbinlogとトランザクション整合が崩れると、レプリケーションや復旧（特にGTID）が壊れ得るからである。つまり、書き込みが重い環境ほど「速くするために耐久性を落とす誘惑」が出るが、章はまず安全側に寄せ、必要ならハードウェア（BBWC＋SSD等）で性能と安全を両立せよ、という立場である。
夜間バッチが重い（ワンショットスキャンが混ざる）とき
バッチ（例：mysqldump のような一過性スキャン）がホットデータを追い出して日中性能を悪化させる問題に対して、章は innodb_old_blocks_time を具体例で挙げる（デフォルト 0 に対し 1000ms が有効だったベンチ例）。これは「バッファプールのLRUでワンショットを冷遇する」方向の設定で、バッチが“たまに来て全部を汚す”タイプに当てはめやすい。
また、書き込み系バッチでトランザクションを長時間保持すると、undoが肥大してパージが追いつかず、テーブルスペースが増え続ける問題に直結する。章は SHOW ENGINE INNODB STATUS の history list length を見て兆候を掴み、必要なら innodb_purge_threads / innodb_purge_batch_size を検討し、それでも破綻しそうなら innodb_max_purge_lag で更新を意図的に遅らせてでも追いつかせる（魅力的ではないが代替がない）という“最悪を避ける”選択肢まで提示する。
レプリケーションがある（レプリカ運用）とき
レプリカはまず事故らせないことが重要で、章は read_only と super_read_only を強く推奨する（うっかり書き込みで整合が崩れるのを防ぐ）。さらに skip_name_resolve で認証時DNS（二重逆引き）という罠を避け、接続がDNSで詰まるタイプの障害を回避する、という“性能というより信頼性”の打ち手が整理されている。
まとめ
この章の“現場向け”結論は次の形になる。
- まずは最小構成＋観測（SLO/ステータス差分）で、設定の変更は少数に絞る。
- 専用DBなら
innodb_dedicated_serverを使い、主要4設定を自動で妥当化する。 - 自動化できないなら、最重要は
innodb_buffer_pool_sizeとinnodb_log_file_size（＋耐久性の要であるinnodb_flush_log_at_trx_commitとsync_binlog）。 - 接続ごとに増えるバッファを全体で盛らず、先にクエリ/スキーマとトランザクション設計を整える。
業務での活かし方
- まず順番を固定する：最初にクエリ/インデックス/トランザクション設計を直し、それでも残るボトルネックに対して設定を当てる。設定で最初に触る候補は、土台になる
innodb_buffer_pool_sizeとログ系（innodb_log_file_sizeなど）である。 - 接続ごとのバッファを“全体で盛らない”：
read_buffer_size/sort_buffer_size/join_buffer_sizeは、接続やクエリの実行に応じてメモリ消費が増えるため、グローバルに上げるほどピーク時の総消費が暴れやすい。必要なら「その処理のときだけ」セッション単位で上げる、という前提で考える。 - トランザクションを短く保つ：画面処理やバッチがトランザクションを長時間保持すると、undo（古い行バージョン）が回収できずに膨らみ、パージ遅延→テーブルスペース肥大→最悪ディスク枯渇につながる。兆候の確認には
SHOW ENGINE INNODB STATUSのhistory list lengthを使う。 - 「どこまでデータ損失を許すか」を先に決める：
innodb_flush_log_at_trx_commitとsync_binlogは、性能と引き換えにデータ損失や復旧困難（レプリケーション破綻など）のリスクを増やし得る設定である。したがって「障害時に最大で何秒（何件）まで失ってよいか」「失ったら業務として許容できるか」を先に合意し、その許容範囲に合わせて値を選ぶ（迷うなら章の推奨どおり安全側に倒す）。 - 「いつ効く設定か」を押さえて検証する：設定は「変えた瞬間から効く」とは限らない。
SET GLOBALは既存セッションには反映されず基本的に新規接続から効くため、接続プールを使うアプリでは“コネクションの張り替え”をしないと変化が見えないことがある。さらにthread_cache_size/table_open_cacheのように、接続のクローズや次回テーブルオープンなどのタイミングで効き始める設定もある。したがって、変更前後でピーク/非ピークのメトリクス差分を取り、反映条件（接続張り替え・待機時間）を満たした上で効果判定する。
コメント