DB チューニングの概念 — ロック、キャッシュ、インデックス、チェックポイント、メモリ

概要 — 何をする画面か

サービス ▸ データベース は、登録された DB コレクター（access=database）に直接接続し、ライブの負荷と構造の指標を可視化します。単に数値を並べるのではなく、主要な指標（ロック待ち、キャッシュヒット率、インデックス利用率、チェックポイント、ダーティページ）ごとに設計された教育用ホバーポップオーバーを添えることで、「なぜこの値が悪いのか／どう直すのか」を、その値を見ているその場で学べます。

本記事は、それらのポップオーバーが教える概念を要約したものです。完全な用語集はライブのポップオーバー（resources/lang/{ko,en}/dbmon.php の edu キー）に一元管理されているため、実際の状況では指標にホバーして、文脈に応じた詳細を読んでください。

画面はモニター（いま何が悪いか）、ポップオーバーは原因の教育（なぜ／どうやって）、そして実際の設定スナップショットはインスタンスパラメーター（[param] · nginx / my.cnf / sysctl）にあります。3つを合わせて読むことで、診断 → 根拠 → 対処へと進めます。

こんなときに使う

DB カードが Degraded や Down を示していて、その理由（接続の飽和、高い同時実行、クエリの滞留、スロークエリ率の上昇）を理解したいとき。
キャッシュヒット率またはインデックス利用率のスコアが低く、根本原因と改善のレバー（バッファプールのサイズ、インデックスの追加、カバリングインデックス）を知りたいとき。
行ロック待ちが急増し、どのクエリ／パターンが競合を生んでいるかを絞り込みたいとき。
メモリ管理の詳細で、ダーティページ／チェックポイントの波形が危険域に忍び寄っており、チューニングの方向性（バッファプールや redo ログの拡大）を知りたいとき。
DBA／DevOps のオンボーディング — 実データを相手に InnoDB の内部（ロック、バッファプール、redo）を学ぶとき。

画面構成

教育用ポップオーバーが付く場所:

場所	指標	ポップオーバーが教える内容
カード下部の右 · `Cache` バッジ	キャッシュヒット率（バッファプール）	算出式＋引き上げる4つの方法
カード下部の右 · `Index` バッジ	インデックス利用率	算出式＋インデックスヒットを増やす5つの方法
同時実行負荷 · `lock`	行ロックの平均待ち時間（ms）	ロック待ちが増える7つの原因（頻度順）
ヘッダー／メモリ詳細 · `Checkpoint … % of redo`	チェックポイント経過量	redo ログ圧力の意味と、上昇・下降の読み方
詳細（折りたたみ） · メモリ管理	ダーティページ · チェックポイントの時系列	LWM/MAX しきい値のオーバーレイ＋フラッシュエピソードの注記

カード本体そのものは、2つの負荷の軸を要約します。

同時実行負荷 — 接続の飽和度（リングゲージ） · Running（threads_running） · Backlog（waiting） · lock。
スループット — Total QPS のスパーク · Slow rate（slow_qps） · Slow (total)。

下部のスキーマ分析（折りたたみ）は、総容量、上位スキーマ、最大のテーブル、エンジン構成を表示します（チューニングの概念とは別で、information_schema から取得します）。

使い方

DB カードで問題の指標（赤／黄のバッジ、または高い値）を見つけます。
その指標のラベル／バッジにホバーして文脈ポップオーバーを開きます — 原因、算出式、対処方法が表示されます。
詳細を展開し、ダーティ／チェックポイントの波形としきい値ラインが入ったメモリ管理の時系列を確認します。
ポップオーバーが示す設定のレバー（例: innodb_buffer_pool_size、インデックスの追加）を対象 DB に適用するか、インスタンスパラメーターの現在値と突き合わせます。

以下は各概念の要約リファレンスです（正本はライブのポップオーバーです）。

行ロック待ち — 7つの原因

InnoDB が行ロックの獲得を待った平均時間（ms）です。高いほど競合が大きいことを意味します。頻度順:

#	原因	要点
1	トランザクションがロックを長く保持しすぎる	`BEGIN … UPDATE …` の後にコミットが遅れると（外部 API、ユーザーの思考時間、遅いクエリ）、その行を待つすべてのトランザクションが行列になります。最も多いケースです。
2	ホットな行の競合	多数のトランザクションが同じ行（カウンター、在庫、集計／ロールアップ行、シーケンス）を更新する → 直列化され、待ちが積み上がります。
3	インデックスのない UPDATE/DELETE	WHERE がインデックスを使えない場合、InnoDB は必要以上に多くの行／範囲をロックします → 無関係なトランザクションまでブロックします。実務では隠れた第1位です。
4	ギャップロック／ネクストキーロック	REPEATABLE READ では、範囲ロックが行と行の間のギャップもロックし、INSERT をブロックします。範囲 UPDATE ＋同時 INSERT のパターンです。
5	大量バッチ DML	数万行に触れる1本の UPDATE/DELETE は、多数のロックを長時間保持します。
6	遅いロック保持者	ロックを保持しているクエリが I/O や CPU で遅いと、その分だけロックを長く握り続けます — スロークエリ率の上昇とロック待ちの上昇は連動します。
7	外部キーのチェック	FK で参照するテーブルを更新すると親行に共有ロックがかかり、待ちが増えます。

この画面では、#3（インデックスのない UPDATE）がロック待ち急増の隠れた第1要因です。SHOW ENGINE INNODB STATUS の TRANSACTIONS セクションを、インデックス利用率のスコアと合わせて読んでください。

キャッシュヒット率（InnoDB バッファプール）

ほとんどの読み取りがディスクではなくメモリ（バッファプール）から返されるときに高くなります。

hit ratio = 1 − Δinnodb_buffer_pool_reads / Δinnodb_buffer_pool_read_requests

評価	しきい値
優秀	≥ 99.5%
良好	≥ 98%
普通	≥ 90%

引き上げる方法（要約）: innodb_buffer_pool_size を増やす（目安は RAM の 50–75%） · フルスキャンを減らしてバッファプールの汚染を避ける · ワーキングセットを小さくする（使わないカラムを避ける、大きな BLOB を分離する、アーカイブする） · innodb_buffer_pool_instances で競合を減らし、再起動時にバッファプールをダンプ／再読み込みする。

インデックス利用率

インデックス経由で行を見つけた読み取りと、次の行までスキャンした読み取りの比率です。

index usage = Δhandler_read_key / (Δhandler_read_key + Δhandler_read_rnd_next)

評価	しきい値
優秀	≥ 90%
良好	≥ 70%
普通	≥ 40%

引き上げる方法（要約）: EXPLAIN でフルスキャン（type=ALL）とファイルソートを見つけ、WHERE/JOIN/ORDER BY のカラムにインデックスを張る · 複合インデックスは選択性の高いカラムを先頭にして作る（最左プレフィックスの規則） · カラムに関数やキャストを使ってインデックスを無効化しない（WHERE DATE(col)=… → 範囲条件を使う） · カバリングインデックスを使う · 重複した／使われていないインデックスを削除する。

チェックポイント経過量（redo ログ圧力）

最後のチェックポイントから redo ログがどれだけ進んだか（バイト）＝まだデータファイルにフラッシュされていない変更であり、クラッシュ時に InnoDB が再適用する作業量です。ヘッダーの % of redo は、フラッシュストームの崖までの距離を示します。

状態	% of redo	意味
安全	< 50%	変更が間に合ってフラッシュされている
注意	50 – 75%	書き込みがフラッシュを上回っている。強制フラッシュが近づいている
危険	≥ 75%	非同期／同期フラッシュのしきい値付近 — 強制フラッシュによりレイテンシ／I/O ストームの恐れ

読み方: 上昇＝書き込み > フラッシュ（redo が埋まっている） · 下降＝フラッシュが追いついた（健全） · のこぎり波＝蓄積 ↔ フラッシュ、正常なリズム。75% に近づいたら、バッファプールや innodb_log_file_size の拡大を検討します。

ダーティページのフラッシュ（メモリ管理）

バッファプール内で変更されたが、まだディスクに書き出されていないページです。適正値はサーバー自身のフラッシュしきい値（innodb_max_dirty_pages_pct = MAX、innodb_max_dirty_pages_pct_lwm = LWM）です。専門家の見解では、ダーティページは LWM を下回って振動しているべきです。

状態	条件	意味
健全（good）	しきい値より下で振動（健全なのこぎり波）	フラッシュ中も DB が苦しんでいない
注意（warn）	低水位マークを超えている（フラッシュ前の領域）	書き込み圧力とチェックポイントの遅れを監視する
危険（crit）	MAX しきい値に到達	強制的なフラッシュ → レイテンシ急増／I/O ストーム（バッファプールや redo ログを拡大）

メモリ管理の時系列は、ダーティページの上に LWM/MAX のしきい値ラインを重ね、追い出し／フラッシュのエピソードをピン留めします。右側の指標には Pending writes と Row-lock avg も表示されます。

データソース

教育概念が参照する、実際のカウンターと保存先:

概念	元カウンター／状態	備考
ロック待ち	`SHOW GLOBAL STATUS` · `information_schema.processlist`	滞留数を算出。PROCESS 権限が必要
キャッシュヒット率	`Innodb_buffer_pool_reads` / `_read_requests`	サンプル間の差分
インデックス利用率	`Handler_read_key` / `Handler_read_rnd_next`	サンプル間の差分
チェックポイント · ダーティ	InnoDB ステータス（チェックポイント経過量 · ダーティページ · ペンディング）	メモリ管理の時系列
QPS · スロー	`Questions` · `Slow_queries`（累積）	差分 → QPS ／スロー率を導出

時系列は infr_db_metric（マイグレーション 000681）に保存されます。App\Services\Database\DbCollectorService が登録済み DB に直接接続し、SHOW GLOBAL STATUS ＋ @@max_connections ＋ processlist を読み取り、前回サンプルとの差分として派生指標を計算します。
収集は infr:collect-db コマンドで実行されます（既定では毎分スケジュール、単発で終了）。ページ進入時に最後のサンプルが古ければ、ensureFresh() が即座に単発サンプルを取得します。
画面のポーリング間隔: 30秒。接続リークを避けるため、コレクターは finally で DB::purge を実行し、あわせて PDO タイムアウトを設定します。

カード状態（degraded）のしきい値（OpsPolicy::dbThresholds() の既定値 — 運用ポリシーで調整）:

条件	既定のしきい値	理由ラベル
接続の飽和 `conn_pct` ≥	80%	接続の飽和
`threads_running` ≥	40	高い同時実行
`waiting`（滞留） ≥	5	クエリの滞留
`slow_qps` ≥	1/s	スロークエリ率

アクセス権限

閲覧（画面とポップオーバー）: demo（レベル1）以上。ポップオーバーは純粋な教育コンテンツで、追加のゲーティングや監査はありません。
DB 状態しきい値の調整: 運用ポリシーで管理 — 管理者（レベル 5/10 · infr.level:N）。
DB コレクターの登録／編集: コレクター管理者（レベル 5/10）。パスワードはコレクター内に暗号化（Crypt）して保存するか、cred_ref = env:PREFIX で .env の認証情報セットを再利用します（ハードコードは決してしません）。

トラブルシューティング・FAQ

Q. Backlog に「own connections only」と表示されます。 サーバー全体の滞留クエリを数えるには、DB アカウントに PROCESS 権限が必要です。権限がない場合、自分の接続だけが数えられます。sql/infr_db_monitor_grant.sql を参照してください（監視専用アカウントの作成を推奨します）。

Q. キャッシュ／インデックスのスコアが空欄（N/A）です。 派生指標は2つのサンプルの差分として計算されます。コレクターが起動したばかり、またはカウンターがリセットされた（再起動）場合、最初のサンプルではスコアが出ません — 次のサイクルを待ってください。

Q. カードが stale（最近のサンプルなし）と表示されます。 エコノミーモードでは、コンソールの利用者がいないときは収集がスキップされます。進入時に ensureFresh(45s) が即座にサンプルを取得しますが、スケジューラーが詰まっていると、カードは一時的に pending に降格することがあります。常時収集にするには、運用ポリシーで DB 収集モードを always に設定します。

Q. ポップオーバーの文言を翻訳／編集するには？ 教育用の用語集は1か所にあります: resources/lang/{ko,en}/dbmon.php の edu キー（一元管理）。その他のロケールは en にフォールバックします。

Q. ロック待ちの原因を実際に絞り込むには？ 対象 DB で SHOW ENGINE INNODB STATUS（TRANSACTIONS · LATEST DETECTED DEADLOCK）と information_schema.INNODB_TRX / data_locks を確認し、上の7原因の表と突き合わせます。