WordPressなんかで、MySQLを高速化することで、応答速度を向上させることができるのですが、自分の環境用に設定するための備忘録としてメモを残します。

環境

AWSとかだと負荷によってメチャクチャお金がかかるので、最近は「遅くなってもいいから料金が固定されているVPSの方が安いし安心」って思ってます（笑）

OS：Ubuntu18
メモリ：4GB
CPU：Intel系x86 64bitだったと思います…

前提となっている運用状態

主な用途はwordpressの運用です。フロントエンド側はCDNにするのでまあまあの速度で良んですが、管理画面がめっちゃ重くなるので管理画面の高速化を意識しています。

HTTPサーバとMySQLサーバが同居状態なので、ざっくりOSに1GBくらい、HTTPサーバに1GBくらい、MySQLに2GBくらい割り当てるつもりで設定を書いています。

my.cnf

場所OSにもよりますが、私の環境では /etc/mysql/my.cnf にあります。

#
# The MySQL database server configuration file.
#

[mysqld]
character-set-server = utf8
max_allowed_packet   = 16M # パケットサイズを大きくして一気にクエリを送る

# 一般的なログを保存
general_log      = 1
general_log_file = /var/log/mysql/general-query.log
log-error        = /var/log/mysql/error.log
expire_logs_days = 2

# パフォーマンスに及ぼす設定 - 主に同時接続に関連
max_connections   = 8
thread_cache_size = 32

# キャッシュ関連 - 繰り返し同じようなクエリがある場合
query_cache_size  = 134217728 # 1024バイトごとに設定 - 128M
query_cache_limit = 134217728 # 1024バイトごとに設定 - 128M
query_cache_type  = ON  # ONでquery_cacheを有効化

# innodb関連
innodb_file_per_table   = ON # テーブルを個別に分けることで更新を早くする
innodb_buffer_pool_size = 1073741824 # 出来るだけたくさん - 割り当て可能な物理メモリの8割(1G)
innodb_log_file_size    = 1048576 # buffer_pool_sizeの1/4？ - 1MB以上4G未満 - 1MB
innodb_sort_buffer_size = 67108864 # 64M 

# スロークエリの設定
slow_query_log      = ON
long_query_time     = 1 # sec
slow_query_log_file = /var/log/mysql/slow_query.log

# スレッドバッファ関連（下記合計×スレッド数のメモリが必要になるのであまり大きくしない）
sort_buffer_size     = 33554432 # ソートに全般に利用される - 32M
join_buffer_size     = 16777216 # インデックス無しJOIN - 16M
read_buffer_size     = 16777216 # インデックス無しテーブルスキャン - 16M
read_rnd_buffer_size = 8388608  # インデックス有りソート - 8M

# その他の設定
!includedir /etc/mysql/conf.d/

もっとメモリがある場合にはどうするか？

今回の設定例はMySQLに2Gを割り当てたのですが、例えばMySQLがもっとたくさん（例えば4Gとか）使える場合には以下のような方針で調整していきます。

同時接続数が多い→max_connectionsとかthread_cache_sizeを増やす（消費メモリが肥大化するので注意！）
同じようなクエリがたくさん繰り返される→query_cache_sizeとquery_cache_limitを増やす
行数が多いテーブルを頻繁にソート→sort_buffer_size、innodb_sort_buffer_size、read_rnd_buffer_size、innodb_sort_buffer_sizeなどソート関連の割り当てを増やす
JOIN句を使ったクエリが多数→キャッシュを増やしてjoin_buffer_sizeも増やす
その他まだメモリに余裕がある→innodb_buffer_pool_sizeを出来るだけ増やす

どこで詰まってるかは「スロークエリ」のログが参考になると思います。上記の設定では「/var/log/mysql/slow_query.log」に記録されるはずです。

テスト環境の場合

テスト環境の場合は、同時接続数が少ないので、max_connectionsとかthread_cache_sizeを減らして、innodb_buffer_pool_sizeやスレッドバッファ関連を増やすとスムーズかなと思います。

じゃあトータルのメモリ消費はいくつになるか？

この記事が参考になると思います。

https://qiita.com/kyrieleison/items/9f303dd046e2fb82fea3

結論としては「メモリ使用量 = グローバルバッファ + (スレッドバッファ * コネクション数)」になります。

でもやっぱり・・・

結局は「どんなクエリが多いか」「どんな利用状況か」によって増やすところ・減らすところは変わってくると思うので、理想は「サーバによる」としか言いようがないよなあ、と書いてて思いました。

もし、各数値の見積とか計算の方針がある方がいたら、教えていただけると嬉しいです。

皆さんの参考になれば幸いです。

MySQLの設定ファイル例（my.cnf）【MySQLパフォーマンスチューニング】

環境