Linuxサーバー上でMySQLをセルフホスト運用していると、インストール直後のデフォルト設定のまま運用し続けることで、メモリの大半がDBキャッシュに活かされていない状態になります。設定ファイルを一切変更していない場合、InnoDBのバッファプールはデフォルトで128MBしか割り当てられていません。
この記事では、RHEL 9系／Rocky Linux 9およびUbuntu 24.04 LTSを想定環境として、my.cnfの主要パラメータの意味と設定手順を解説します。InnoDBバッファプールサイズの調整を中心に、接続数・タイムアウト・バイナリログ保持期間の設定まで、順を追って説明します。MariaDB 10.x系でも設定項目はほぼ共通です。
この記事のポイント
・InnoDBバッファプールサイズはRAMの50～70%を目安に設定する
・SHOW VARIABLES / SHOW STATUSで現状を数値で把握してから変更する
・max_connections超過時は「Too many connections」エラーが発生する
・変更前にmysqld --validate-configで構文を検証してから再起動する
でも安心してください。プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
デフォルトのmy.cnfで何が足りないのか
MySQLをパッケージインストールした直後のmy.cnfは、汎用的な最低設定が入っているだけです。特に問題になりやすいのがinnodb_buffer_pool_sizeの小ささです。このパラメータはInnoDBがデータとインデックスをメモリにキャッシュするための領域を指定するもので、デフォルト値は128MBに設定されています。サーバーに16GBのRAMが搭載されていても、デフォルトのままでは128MBしかDBキャッシュに使われません。残りのメモリはDBには利用されず、テーブルデータをディスクから何度も読み込み続ける状態になります。これがI/O増加とクエリ遅延の主因です。
デフォルト設定のままでよく起きる症状は以下のとおりです。
・クエリの応答時間が負荷と共に悪化する
・
iostatでディスクI/O待ちが常態化する・接続数が多い時間帯に「Too many connections」エラーが出始める
・スロークエリログに同じクエリが繰り返し記録される
これらを解消するには、サーバーのRAM搭載量や用途に合わせてmy.cnfを明示的に調整する必要があります。
チューニング前に現状を数値で把握する
my.cnfを変更する前に、SHOW VARIABLESとSHOW STATUSで現在の設定値と稼働状況を確認します。数値を把握せずに設定を変えると、症状を改善するどころか悪化させる場合もあります。まず現状を記録してから作業に入ることが重要です。1. 設定値をSHOW VARIABLESで確認する
# MySQL/MariaDB にログイン mysql -u root -p # バッファプール・接続数などの設定値を確認 SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'; SHOW VARIABLES LIKE 'max_connections'; SHOW VARIABLES LIKE 'wait_timeout'; SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
+--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +--------------------------+-----------+ +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+
134217728は128MBです。8GBや16GB以上のRAMを積んだサーバーには明らかに小さすぎる値です。この数値を確認してから、次の手順でキャッシュヒット率を調べます。2. バッファプールのヒット率をSHOW STATUSで確認する
# バッファプールのキャッシュヒット率を確認 SHOW STATUS LIKE 'Innodb_buffer_pool_read%'; # 接続数の現状を確認 SHOW STATUS LIKE 'Max_used_connections'; SHOW STATUS LIKE 'Threads_connected';
+----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_reads | 48291 | | Innodb_buffer_pool_read_requests | 51284 | +----------------------------------+-------+
Innodb_buffer_pool_readsはディスクから実際に読み込んだ回数、Innodb_buffer_pool_read_requestsはキャッシュへのアクセス要求数です。ヒット率の目安は次の計算式で確認できます。キャッシュヒット率（%）＝（1 − Innodb_buffer_pool_reads ÷ Innodb_buffer_pool_read_requests）× 100
上の例だと 1 − 48291 ÷ 51284 ≒ 5.8%（ヒット率）です。本来は99%以上が健全な状態で、5.8%はほぼキャッシュが機能していない状態を意味します。バッファプール拡張の優先度が高いことがわかります。
InnoDBバッファプールサイズを設定する
DBパフォーマンス改善の中で最も効果が高い変更がinnodb_buffer_pool_sizeの拡張です。一般的な目安はサーバーRAMの50～70%です。DBサーバー専用機なら70%まで割り当てられますが、WebサーバーやアプリケーションサーバーとDBが同居している場合は50%程度に留め、OS側のメモリを確保します。計算の参考（RAMが16GBの場合）
・DBサーバー専用: 16GB × 70% ≒ 11GB →
innodb_buffer_pool_size = 11G・WebとDB同居: 16GB × 50% ≒ 8GB →
innodb_buffer_pool_size = 8G1. my.cnfファイルの場所を確認する
設定ファイルの場所はディストリビューションによって異なります。・RHEL 9 / Rocky Linux 9:
/etc/my.cnf・Ubuntu 24.04 LTS（MySQL 8.x）:
/etc/mysql/mysql.conf.d/mysqld.cnf・MariaDB on Ubuntu:
/etc/mysql/mariadb.conf.d/50-server.cnf# どのファイルが実際に読み込まれているかを確認 mysql --help --verbose 2>/dev/null | grep -A1 'Default options'
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/conf.d/*.cnf ~/.my.cnf
2. my.cnfにパラメータを追記する
# /etc/my.cnf（RHEL 9 / Rocky Linux 9の場合） [mysqld] # InnoDBバッファプールサイズ（RAMの50～70%を目安に設定） innodb_buffer_pool_size = 8G # バッファプールインスタンス数（合計サイズが1GB以上なら8が推奨） innodb_buffer_pool_instances = 8
innodb_buffer_pool_instancesはバッファプールを内部で分割する数です。合計サイズが1GB以上の場合は8に設定すると、内部ロックの競合が減り、並列アクセス時の応答が改善します。MySQL 8.0ではデフォルトが自動設定になっていますが、明示的に8と書いておくほうが意図が伝わりやすく管理しやすいです。接続数とタイムアウトを調整する
Webアプリから多数の同時接続が来る構成では、max_connectionsのデフォルト値（151）では「Too many connections」エラーが発生することがあります。ただし、単純に増やすだけでは各接続に必要なスレッドメモリも比例して増加するため、他のパラメータとのバランスが重要です。変更前に
Max_used_connections（稼働開始以降の最大同時接続数）を確認し、実際に上限に近づいているかを検証してから設定値を決めます。1. 接続数の上限を見直す
# これまでの最大接続数を確認 SHOW STATUS LIKE 'Max_used_connections'; # 現在の接続数を確認 SHOW STATUS LIKE 'Threads_connected';
Max_used_connectionsが151に近い値を示している場合は、max_connectionsを引き上げる必要があります。設定の目安として、最大同時接続数の1.5倍程度を上限に設定するのが一般的です。2. タイムアウト値を設定する
# /etc/my.cnf [mysqld] # 最大同時接続数（Max_used_connectionsを基に設定） max_connections = 300 # スリープ接続のタイムアウト（秒） # デフォルトは28800秒（8時間） wait_timeout = 600 interactive_timeout = 600
wait_timeoutはスリープ状態（クエリを実行していない状態）の接続が切断されるまでの時間です。デフォルトの28800秒（8時間）は長すぎるため、WebアプリがDBコネクションプールを使っている構成では、ゾンビ接続がmax_connectionsの枠を長時間占有する原因になります。600秒（10分）程度に短縮することで、不要なコネクションが早めに解放されます。MySQLのパラメータ設定を体系的に学びたい場合は、Linux実践セミナーでも実サーバーを使った設定確認の手順を扱っています。
バイナリログの保持期間を管理する
バイナリログ（binlog）は更新系クエリの記録をファイルに蓄積します。保持期間を設定しないと、ディスクが枯渇するまでログが積み重なり続けます。ポイントインタイムリカバリやレプリケーションを利用している場合は削除できませんが、不要なログが7日以上溜まっている場合は保持期間を設定します。MySQL 8.0以降はbinlog_expire_logs_seconds（秒単位）が推奨パラメータです。MySQL 5.7以前やMariaDB 10.x系では
expire_logs_days（日単位）を使います。# /etc/my.cnf [mysqld] # MySQL 8.0以降: バイナリログを7日間保持（604800秒） binlog_expire_logs_seconds = 604800 # MariaDB / MySQL 5.7以前の場合（日単位、コメントアウトして使用） # expire_logs_days = 7
# バイナリログのファイル一覧を確認 SHOW BINARY LOGS;
+------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000041 | 2097152 | No | | mysql-bin.000042 | 1048576 | No | | mysql-bin.000043 | 524288 | No | +------------------+-----------+-----------+
skip-log-binをmy.cnfに追記して無効化できます。ただし、無効化するとポイントインタイムリカバリができなくなるため、バックアップ戦略を見直した上で判断してください。設定変更後の確認と再起動手順
my.cnfを変更したら、MySQLを再起動する前に設定ファイルの構文チェックを必ず行います。パラメータ名のタイプミスや不正な値が含まれていると、再起動後にMySQLが起動しなくなります。1. 設定ファイルの構文を検証する
# MySQL 8.0以降: 設定ファイルの構文を検証 mysqld --validate-config # エラーがなければ出力なしで終了する echo "終了コード: $?"
2024-01-15T10:30:00.000000Z 0 [ERROR] [MY-000067] [Server] unknown variable 'innoodb_buffer_pool_size=8G'.
2. サービスを再起動して設定を反映する
# RHEL 9 / Rocky Linux 9 の場合 sudo systemctl restart mysqld # Ubuntu 24.04 LTS（MySQL 8.x）の場合 sudo systemctl restart mysql # MariaDB の場合（RHEL系/Ubuntu共通） sudo systemctl restart mariadb
3. 設定値が反映されたか確認する
# 再起動後に設定値が反映されているか確認 mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';" mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"
4. エラーログで起動成否を確認する
# MySQLエラーログの末尾を確認 sudo tail -30 /var/log/mysqld.log # RHEL 9 / Rocky Linux sudo tail -30 /var/log/mysql/error.log # Ubuntu 24.04 LTS
[ERROR]が含まれる行があれば、パラメータ名とメッセージを手がかりにmy.cnfを修正して再起動します。ready for connectionsのメッセージが出ていれば正常起動しています。よくある設定ミスとエラーの対処法
「InnoDB: Requested too large」で起動しない
innodb_buffer_pool_sizeをサーバーの物理RAMを超えて設定した場合、MySQLが起動できずこのエラーが発生します。エラーログでRequested too largeのメッセージを確認し、サイズを物理RAM内に収まる値に修正してから再起動します。確認コマンド（サーバーの総メモリ量を確認する）
free -h
「Too many connections」が解消されない
max_connectionsを引き上げてもエラーが続く場合、アプリ側の接続プールが接続を正常にクローズしていない可能性があります。SHOW PROCESSLISTでスリープ状態の接続数を確認します。# スリープ状態の接続一覧と滞留時間を確認 SELECT user, host, db, command, time FROM information_schema.processlist WHERE command = 'Sleep' ORDER BY time DESC LIMIT 20;
wait_timeoutを短縮することで自動切断されるようになります。「Variable is a read only variable」（動的変更を試みた場合）
SET GLOBAL innodb_buffer_pool_size = ...で動的に変更を試みた際にこのエラーが出ることがあります。innodb_buffer_pool_sizeはMySQL 8.0.3以降で動的変更に対応していますが、サイズを大きく変更する場合や一部のMySQL/MariaDBバージョンでは変更が受け付けられません。動的変更が使えない場合は、my.cnfを変更してサービスを再起動する方法で設定します。本記事のまとめ
MySQLのmy.cnfで効果が高い主要パラメータをまとめます。| パラメータ | デフォルト値 | 推奨設定の目安 |
|---|---|---|
| innodb_buffer_pool_size | 128MB | RAMの50～70%（DBサーバー専用なら70%） |
| innodb_buffer_pool_instances | 自動 | 合計1GB以上なら8 |
| max_connections | 151 | Max_used_connectionsの1.5倍程度 |
| wait_timeout | 28800秒（8時間） | 600秒（Webアプリ接続プール運用時） |
| interactive_timeout | 28800秒（8時間） | wait_timeoutと同じ値に揃える |
| binlog_expire_logs_seconds | 2592000秒（30日） | 604800秒（7日）など運用に応じて |
＞＞ Linuxサーバー構築セミナーの詳細と日程はこちら
3,100名以上が実践した「型」を無料で公開中
プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
その「型」を図解60Pにまとめた入門マニュアルを、完全無料でプレゼントしています。
姓・名・メールの3つだけ／30秒／解除は3秒 ／ 詳細はこちら
- 前のページへ：PostgreSQLの論理レプリケーション（PUBLICATION・SUBSCRIPTION）をLinuxで設定する手順｜テーブル単位の非同期複製と動作確認
- この記事の属するカテゴリ：データーベース管理へ戻る
無料メルマガで学習を続ける
Linuxの実践スキルをメールで毎週お届け。
登録は30秒、解除もいつでも可。
登録無料・いつでも解除できます