MySQLサーバーをどのようにチューニングしますか。buffer pool、slow query log、接続について説明してください。

Question

IT Interview Practice · Accepted Answer

サーバーのチューニングはループに従います。**計測し、ボトルネックを見つけ、1つだけ直し、再計測する。** 最も効果の高い3つの領域は、InnoDB buffer pool、slow query log、そして接続の扱いです。

## InnoDB buffer pool — 最重要のつまみ

buffer poolは、データとインデックスのページをRAMにキャッシュします。ワーキングセットがそこに収まれば、読み取りはメモリ速度になり、ディスクI/Oはほぼ消えます。専用DBサーバーではRAMの約50〜75%に設定します。

```sql
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;  -- 12 GB

-- Health check: hit rate should be ~99%+
SHOW ENGINE INNODB STATUS\G
--   Buffer pool hit rate 999 / 1000     <- good; near 1000/1000 = mostly RAM
```

## slow query log — 直すべき対象を見つける

```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- log anything over 500 ms
-- Then aggregate with pt-query-digest / mysqldumpslow to rank the worst queries,
-- and EXPLAIN each one. Most wins are a missing index, not a server setting.
```

## 接続

接続はそれぞれメモリとスレッドを消費します。リークを覆い隠すために`max_connections`を極端に高く設定してはいけません。数千もの接続が殺到するとサーバーがスラッシングします。アプリ側で**connection pool**（またはProxySQLのようなプロキシ）を使い、小さく再利用される接続の集合を保ち、`Threads_connected`と`Threads_running`を監視してください。

```sql
SHOW GLOBAL STATUS LIKE 'Threads_running';   -- how many are actually doing work
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```

## 攻略の順序

まず上位の遅いクエリ（インデックス）を直します。たいていどんな設定変更よりも効果があります。次にbuffer poolを適切なサイズにします。そのうえで初めて`max_connections`、`innodb_log_file_size`、フラッシュ設定を調整します。一度に1つの変数だけ変え、再計測してください。

## なぜ重要なのか

「MySQLが遅い」という事象のほとんどは、インデックスのない少数のクエリか、ワーキングセットを保持するには小さすぎるbuffer poolであって、風変わりなチューニングではありません。シニアエンジニアはまずslow logとbuffer poolのヒット率に手を伸ばし、やみくもに設定つまみをひねる代わりに、本当のボトルネックを直します。

InnoDB buffer pool — 最重要のつまみ

slow query log — 直すべき対象を見つける

接続

攻略の順序

なぜ重要なのか

関連する質問