my.cnfのチューニング設計｜innodb_buffer_pool_sizeとmax_connectionsを実メモリから決める

この記事のポイント

・innodb_buffer_pool_sizeは「空きRAMの70～80%」が実務のセオリー
・max_connectionsはper-threadバッファと掛け算で上限メモリを算出して決める
・MySQL 8.0ではquery_cache_sizeは廃止。MariaDBとの違いに注意
・SHOW STATUSのヒット率でバッファプールの効果を定量的に確認できる

# MySQLが参照する設定ファイルの順序を確認する
$ mysql --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

# MariaDBの場合
$ mariadbd --help --verbose 2>&1 | grep "Default options" -A 1

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /etc/my.cnf.d/mariadb-server.cnf ~/.my.cnf

# /etc/my.cnf の基本構成例
[mysqld]
# 基本設定
user                 = mysql
datadir              = /var/lib/mysql
socket               = /var/lib/mysql/mysql.sock
pid_file             = /var/run/mysqld/mysqld.pid

# 文字コード（MySQL 8.0ではutf8mb4がデフォルト）
character-set-server = utf8mb4
collation-server     = utf8mb4_general_ci

# ネットワーク
bind-address         = 127.0.0.1
port                 = 3306

# エラーログ
log_error            = /var/log/mysqld.log

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

# RHEL/CentOS/Rocky Linux の場合
$ sudo systemctl restart mysqld

# Ubuntu/Debian の場合
$ sudo systemctl restart mysql

# MariaDB の場合（どちらのOSでも）
$ sudo systemctl restart mariadb

# 起動状態の確認
$ sudo systemctl status mysqld

# 現在のinnodb_buffer_pool_sizeを確認する
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

# 134217728 バイト = 128MB（MySQL 5.7以降のデフォルト値）
# RAM 8GBのサーバーでこの値のままでは明らかに設定不足

# RAM 8GB、Webアプリと同居する構成の設定例
[mysqld]
# InnoDBバッファプール（RAMの約50%）
innodb_buffer_pool_size = 4G

# コメントアウト例: RAM別の参考値
# RAM 16GBの専用DBサーバーの場合
# innodb_buffer_pool_size = 12G
# RAM 4GBの小規模サーバーの場合
# innodb_buffer_pool_size = 2G

# innodb_buffer_pool_size が 4G の場合: 4インスタンス（各1GB）
[mysqld]
innodb_buffer_pool_size      = 4G
innodb_buffer_pool_instances = 4

# innodb_buffer_pool_size が 12G の場合: 8インスタンス（各1.5GB）
# innodb_buffer_pool_size      = 12G
# innodb_buffer_pool_instances = 8

# 確認コマンド
# mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

# 最大メモリ使用量の概算計算式
# 必要メモリ = innodb_buffer_pool_size + (per-thread_buffers x max_connections)
# per-thread_buffers = sort_buffer_size + read_buffer_size + read_rnd_buffer_size
#                    + join_buffer_size + thread_stack
#                    = 256KB + 128KB + 256KB + 256KB + 1024KB
#                    = 約 2MB（デフォルト設定の場合）

# RAM 8GB、innodb_buffer_pool_size=4GB の場合
# 残りRAM = 8GB - 4GB = 4GB（OS・その他プロセスを除くと実質2GB程度）
# 安全なmax_connections上限 = 2GB / 2MB = 1000
# ただし現実的な上限は 200～300 程度（安全マージンを確保する）

# 過去の最大同時接続数を確認する（サーバー起動後の累積最大値）
mysql> SHOW GLOBAL STATUS LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 47    |
+----------------------+-------+

# 現在の接続数を確認する（リアルタイム）
mysql> SHOW GLOBAL STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 12    |
+-------------------+-------+

# max_connections の目安 = 実績値の1.5～2倍
# 上の例では47の1.5倍 = 71 → 余裕を見て100程度が適切

# RAM 8GB、Webアプリと同居する構成の設定例
[mysqld]
innodb_buffer_pool_size = 4G
max_connections         = 200

# per-threadバッファをデフォルト値で明示する（意図を記録する）
sort_buffer_size        = 256K
read_buffer_size        = 128K
read_rnd_buffer_size    = 256K
join_buffer_size        = 256K
thread_stack            = 1M

# 接続タイムアウト設定
wait_timeout            = 600
interactive_timeout     = 600

# MySQL 5.7 の設定例
[mysqld]
innodb_log_file_size   = 256M   # buffer_pool_size(4G)の約6%～25%
innodb_log_buffer_size = 16M

# MySQL 8.0.30以降: innodb_redo_log_capacity で総容量を指定する
# innodb_redo_log_capacity = 2G
# innodb_log_file_size は廃止（設定しない）

# MariaDB 10.x: MySQL 5.7と同様の変数名が使える
# innodb_log_file_size = 256M

# 現在値を確認する
# mysql> SHOW VARIABLES LIKE 'innodb_log_file_size';

# スロークエリログの設定
[mysqld]
slow_query_log                = ON
slow_query_log_file           = /var/log/mysql/slow.log
long_query_time               = 1
# long_query_time: 1秒以上かかったクエリを記録する
# 0に設定すると全クエリを記録（チューニング調査時のみ推奨）
log_queries_not_using_indexes = ON
# インデックスを使っていないクエリも記録する

# ログディレクトリを作成してMySQLに書き込み権限を付与する
# $ sudo mkdir -p /var/log/mysql
# $ sudo chown mysql:mysql /var/log/mysql

# mysqldumpslow コマンドでスロークエリのサマリーを確認する（実行例）
# $ sudo mysqldumpslow -s t /var/log/mysql/slow.log
# Reading mysql slow query log from /var/log/mysql/slow.log
# Count: 5  Time=3.20s (16s)  Lock=0.00s (0s)  Rows=1000.0 (5000)
# SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC

# MySQL 5.7 の場合: クエリキャッシュを明示的に無効化する
[mysqld]
query_cache_type = 0
query_cache_size = 0

# MySQL 8.0 以降の場合: 上記2行を完全に削除すること
# ファイルに記述するだけでエラーになり起動しない

# MariaDB の場合: 利用する場合の設定例
# [mysqld]
# query_cache_type  = 1
# query_cache_size  = 64M
# query_cache_limit = 1M

# バージョン確認
# $ mysql --version
# mysql  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

# バッファプールのヒット率を確認する
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
+---------------------------------------+--------+
| Variable_name                         | Value  |
+---------------------------------------+--------+
| Innodb_buffer_pool_read_ahead         | 192    |
| Innodb_buffer_pool_read_ahead_evicted | 0      |
| Innodb_buffer_pool_read_ahead_rnd     | 0      |
| Innodb_buffer_pool_read_requests      | 584320 |
| Innodb_buffer_pool_reads              | 1248   |
+---------------------------------------+--------+

# ヒット率の計算式
# ヒット率(%) = (1 - reads / read_requests) x 100
# 上の例: (1 - 1248 / 584320) x 100 = 99.79%  → 問題なし

# ヒット率が95%を下回る場合 → innodb_buffer_pool_size を増やすことを検討する
# ヒット率が90%を下回る場合 → 早急にinnodb_buffer_pool_sizeを見直す必要がある

# 接続関連の主要ステータスを確認する
mysql> SHOW GLOBAL STATUS WHERE Variable_name IN (
         'Threads_connected',
         'Threads_running',
         'Max_used_connections',
         'Connection_errors_max_connections',
         'Aborted_connects'
       );
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Aborted_connects                  | 3     |
| Connection_errors_max_connections | 0     |
| Max_used_connections              | 47    |
| Threads_connected                 | 12    |
| Threads_running                   | 2     |
+-----------------------------------+-------+

# Connection_errors_max_connections が増加している場合
#   → max_connections の上限に達している → 値を増やす必要がある
# Aborted_connects が多い場合
#   → 認証失敗・タイムアウト等が発生している → ログを調査する
# Threads_running が Threads_connected に近い場合
#   → 多数のクエリが同時実行中 → ボトルネックの調査が必要

# 主要なパフォーマンス関連変数を一括確認する
mysql> SHOW GLOBAL VARIABLES WHERE Variable_name IN (
         'innodb_buffer_pool_size',
         'innodb_buffer_pool_instances',
         'max_connections',
         'innodb_log_file_size',
         'slow_query_log',
         'long_query_time'
       );
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| innodb_buffer_pool_instances | 4          |
| innodb_buffer_pool_size      | 4294967296 |
| innodb_log_file_size         | 268435456  |
| long_query_time              | 1.000000   |
| max_connections              | 200        |
| slow_query_log               | ON         |
+------------------------------+------------+
# innodb_buffer_pool_size = 4294967296 バイト = 4GB → 設定通り

# エラーログを確認する（直近の50行）
$ sudo tail -50 /var/log/mysqld.log
$ sudo tail -50 /var/log/mysql/error.log   # Ubuntu/Debian の場合

# 実際のエラー例（MySQL 8.0でquery_cache_sizeを記述した場合）
# [ERROR] [MY-011011] [Server] Failed to find valid data directory.
# [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
# または
# [ERROR] unknown variable 'query_cache_size=0'

# 起動前に設定ファイルのシンタックスチェックを行う
$ sudo mysqld --validate-config

# MariaDB の場合
$ sudo mariadbd --validate-config

# スワップの使用状況を確認する
$ free -h
              total        used        free      shared  buff/cache   available
Mem:          7.6Gi       6.9Gi       145Mi        25Mi       583Mi       382Mi
Swap:         2.0Gi       892Mi       1.1Gi

# Swap の used が増加している場合はメモリが不足している
# innodb_buffer_pool_size を10～20%削減することを検討する

# メモリをプロセス別に確認する
$ ps aux --sort=-%mem | head -10

# 動的にinnodb_buffer_pool_sizeを4GBに変更する（MySQL 5.7.5以降）
mysql> SET GLOBAL innodb_buffer_pool_size = 4294967296;
# 4294967296 = 4 x 1024 x 1024 x 1024 = 4GB

# 変更中の進捗を確認する（大きい値への変更は時間がかかる場合がある）
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name                    | Value                                              |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 240815  9:30:15. |
+----------------------------------+----------------------------------------------------+

# 変更後の確認
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 4294967296 |
+-------------------------+------------+