メモリ1GBなWordPressサーバに特化したMySQL5.6セッティング

メモリ1GBなLinuxサーバでWordPressを稼働させるには、どの機能（サーバ）を利用しメモリを割り当てるのか取捨選択と割り当てバランスが重要です。稼働一年半のノウハウを結集した自サーバMySQLセッティングを紹介します。

サーバ要件・セッティング条件

サーバのメモリは1GB。OSはCentOS6系、サーバアプリはyumでインストール可能なもの、yumは外部レポジトリも利用して可能な限り最新バージョンを使用。スワップをなるべく発生させないようにメモリ使用率は60%程度に抑えます。
これらの要件で選定した2016/9時点のサーバアプリとバージョンは以下の通り。なお以前利用していたclamavは熟考の結果、今は利用していません。

・CentOS6.8
・Apache2.2-PreFork
・PHP7
・MySQL5.6-InnoDB (MySQL5.7は検証時にWordPressが動作しなかったので保留)

メモリ割り当て配分

CentOS6系のOS標準で利用するメモリ量は100MB程度。Apache+PHPには450MB、MySQLには100MBを確保します。なおPHPは外部レポジトリを利用したPHP7がお勧め。デフォルトのPHP5.3より高速化だけでなく省メモリも実現できるので、同時実行可能なプロセス数を増やすことができます。

2016/9/18時点のmy.cnf設定

apachebenchで計測した速度と負荷をかけた後のmysqltunerレコメンド値、muninで視覚化した各種情報、mysql-slow.logの中身を見て調整したmy.cnfです。

/etc/my.cnf[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-storage-engine=InnoDB
character-set-server=utf8
bind-address = 127.0.0.1

# MySQLに接続するクライアントとの文字コード認証を省略
skip-character-set-client-handshake
# MySQLに接続するクライアントの名前解決を回避し、待ち時間を発生させない設定
skip-name-resolve

## MySQL5.6で追加された設定
# TIMESTAMPデフォルト値の指定
explicit_defaults_for_timestamp = 1
# パフォーマンススキーマを無効化
performance_schema = 0
table_definition_cache = 1024
# MySQLに接続するクライアントの名前解決キャッシュ量を減らし、メモリを少しでも開放する
host_cache_size = 2

##innoDB
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_data_file_path = ibdata1:64M:autoextend
innodb_log_file_size = 16M
innodb_buffer_pool_size = 16M
innodb_autoextend_increment = 16
innodb_log_buffer_size = 4M
# ログ記録タイミングをクエリ毎から1回/秒に変更することでIO処理を減らし、DBの高速化を図る
innodb_flush_log_at_trx_commit = 2
skip_innodb_doublewrite
# IOPSを指定。HDD単体は200．RAID10なら400~。SSDなら2000以上可能。
innodb_io_capacity = 1000
# MySQL5.6から追加
# デフォルトはinnodb_io_capacity*2が適用されるため、不要にIOを圧迫しないようinnodb_io_capacityと同じ値を明示的に値を指定
innodb_io_capacity_max = 1000
# InnoDBのメモリ管理をシステム(OS標準)に指定
innodb_use_sys_malloc=1
# ロックモード指定。2=「インターリーブ」。これはもっとも高速で、もっとも拡張性の高いロックモード。シングル構成でWordPress用ならデメリットなし。
innodb_autoinc_lock_mode = 2

## MyISAM
# InnoDBならMyISAMへの割り当ては最低限で良い。
key_buffer_size = 512k

## Memory
# WordPressはTemporary tablesチューニングの効果が弱く多めに盛っても効果なし。
# よってデフォルトと同じ値を指定
tmp_table_size = 16M
# 基本的にtmp_table_sizeと同じ値を指定
max_heap_table_size = 16M

## Connection Management
# Apache-Preforkで設定可能なConnection数+3程度を指定。
max_connections = 18
## max_connections/3が適正値とのこと
thread_cache_size = 6
# WordPressの場合テーブル数が一定なので思い切って少なくする
table_open_cache = 256
# デフォルトはtable_open_cache*2 + max_connections + 10（条件により異なる）
open_files_limit =540

## Memory Allocation per Connection
# MyISAM用の設定のため、mysqltunerでレコメンドが出ない程度に最小限に設定
read_buffer_size = 256k
read_rnd_buffer_size = 512k
join_buffer_size = 256K
sort_buffer_size = 1M

# クエリ結果のキャッシュサイズ。
# simplicityテーマはランダム要素がありクエリキャッシュの効果がほぼ0なので無効化。確保する場合はたっぷりと。
query_cache_size=0
max_allowed_packet = 1M
wait_timeout = 30

#slow_query_log
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow.log
# デフォルトは1=1秒。WordPressで1秒かかる処理はほとんどないため、0.5程度に設定している。
long_query_time=0.5

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# デフォルトのタイムゾーンはOS依存。明示的に設定する場合はここで指定。
timezone = UTC

[client]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

セッティング後のメモリ使用状況など

下記グラフは13,000PV/月なWordPressサイトの上記セッティングでのメモリ使用量。mysqldが緑、httpdが青、OSのCommitサイズがオレンジ線です。
201608-201609_httpd-and-mysqld

OS全体のメモリ使用量は以下の通り。実メモリ使用サイズが約300MB、commitサイズが650MB程度です。これにclamavを追加しても+300MBなので基本的にスワップなし。またはApacheのプロセス数を10程度追加可能なはずです。
201608-201609_memory-month

最後にmysqltunerの値も貼っておきます。

# mysqltuner
-------- Performance Metrics -------------------------------------------------
[--] Up for: 6d 1h 58m 36s (718K q [1.367 qps], 30K conn, TX: 2B, RX: 273M)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 44.5M global + 2.2M per thread (18 max threads)
[OK] Maximum possible memory usage: 85.0M (8% of installed RAM)
[OK] Slow queries: 0% (19/718K)
[OK] Highest usage of available connections: 22% (4/18)
[OK] Key buffer size / total MyISAM indexes: 512.0K/96.0K
[!!] Key buffer hit rate: 73.7% (342 cached / 90 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 134K sorts)
[!!] Temporary tables created on disk: 41% (68K on disk / 163K total)
[OK] Thread cache hit rate: 99% (4 created / 30K connections)
[!!] Table cache hit rate: 8% (49 open / 548 opened)
[OK] Open file limit used: 9% (49/540)
[OK] Table locks acquired immediately: 100% (662K immediate / 662K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 256)