[MySQL] max_connectionsの設定見直し

2025年1月15日

目的

max_connectionsが大きすぎるとアクセス集中が発生した場合、メモリを大量に消費してMySQLがOOM Killerによってダウンする可能性がある。
ダウンの危険がないよう適切なメモリの範囲で稼働するように調査・対応を行う。
※実際にMySQLのダウンが発生したことによる障害の事例

max_connectionsを設定する上での知識

グローバルバッファサイズ

サーバー全体で共有されるメモリ領域です。
主にキャッシュやバッファプールなど、大量のデータを効率よく処理するために使われる。

スレッドバッファサイズ

各クライアント接続（スレッド）ごとに割り当てられるメモリ領域です。
接続が増えると、その分メモリ消費が増加します。

DB構成による設定

Master と Slave のデータベースで max_connections の設定を変えるかどうかは、データベースの用途や役割によって異る

Master DB:

高めに設定: 読み書きの両方の負荷を処理するため、max_connections を適切に高めに設定する必要があります。アプリケーションのスケールに応じて、書き込みトランザクションが処理しきれるように設定してください。

Slave DB:

やや低めに設定: Slave は読み取り専用の用途が多いため、Master よりも接続数を少なく見積もることができる場合があります。ただし、読み込みが集中する場合には、Masterと同等の接続数が必要になる可能性もあるため、アクセスパターンに応じて設定します。
ただし..
Slaveデータベースが自動的にMasterに昇格する設定になっている場合、昇格後の負荷に備えて、SlaveがMasterの役割を十分に果たせるように設定しておく必要がある。
通常時の低負荷時は、Slaveとしての用途を想定して max_connections をやや低く設定してもよいですが、昇格後に備えて、高めに設定しておくことが理想です。
Slaveが昇格する可能性があるなら、Masterのmax_connectionsと同じ値に設定しておくのが安全策です。

DB情報確認

調査項目

max_connections
• MySQLサーバーが同時に処理できる最大接続数。
• デフォルトは 151（バージョンによる違いあり）。
• 多くのクライアントが接続する環境では、この値を増やす必要がある。
sort_buffer_size
• ソート操作の際に使用されるバッファサイズ。
• 大きなソート（ORDER BY など）を行う際に影響。
myisam_sort_buffer_size
• MyISAM テーブルの REPAIR TABLE, ALTER TABLE, CREATE INDEX などの際に使用されるバッファサイズ。
read_buffer_size
• MySQLが シーケンシャルリード（順次読み込み） を行う際に使用するバッファサイズ。
join_buffer_size
• インデックスを使用せずに JOIN を行う場合に使われるバッファサイズ。
read_rnd_buffer_size
• ランダムリード（ランダムアクセス時の読み込みバッファ）に使用されるメモリ量。
• ORDER BY を使用する場合に影響。

調査用SQL

接続数やメモリ使用量に関連する設定値を確認

SHOW VARIABLES WHERE Variable_name IN (
'max_connections',
'sort_buffer_size',
'myisam_sort_buffer_size',
'read_buffer_size',
'join_buffer_size',
'read_rnd_buffer_size'
);

グローバルバッファサイズ、スレッドバッファサイズ、最大接続数、合計メモリ使用量を確認

select
@@global.key_buffer_size + @@global.innodb_buffer_pool_size + @@global.innodb_log_buffer_size + @@global.net_buffer_length as global_buffer_size,
@@global.sort_buffer_size + @@global.myisam_sort_buffer_size + @@global.read_buffer_size + @@global.join_buffer_size + @@global.read_rnd_buffer_size as thread_buffer_size,
@@global.max_connections as max_connections,
@@global.key_buffer_size + @@global.innodb_buffer_pool_size + @@global.innodb_log_buffer_size + @@global.net_buffer_length + (@@global.sort_buffer_size + @@global.myisam_sort_buffer_size + @@global.read_buffer_size + @@global.join_buffer_size + @@global.read_rnd_buffer_size) * @@global.max_connections as total_memory_size;

MySQLサーバーがこれまでに使用した最大同時接続数を確認

show global status like 'Max_used_connections';

修正後

全ての設定がかなり大きな値で設定されていたため、
設定を全体的に見直してみました。
300件同時接続された場合でも1GB分余裕を持っているように設定しています。

	現在の設定	修正後の設定
read_buffer_size	2,097,152	1,048,576
global_buffer_size	4,336,926,720	8,388,608
sort_buffer_size	8,388,608	3,145,728
myisam_sort_buffer_size	8,388,608	3,145,728
join_buffer_size	8,388,608	3,145,728
現在のメモリ	8GB	8GB
read_rnd_buffer_size	16,777,216	12,582,912
Max_used_connections （起動してから現在までの最大同時接続数）	8	–
total(GB)	26.357	6.929
max_connections	500	300