WordPressのチューニング

Tags:
  • vegeta
  • mysqltuner
  • mackerel

変更ポイント

カーネルパラメータ net.core.somaxconn

vegetaでrate=100(同時接続数)をセットしたところ以下がNginxのerror.logに出力された．

connect() to unix:/run/php/php7.2-fpm.sock failed
 (11: Resource temporarily unavailable) while connecting to upstream,
  client: 163.215.6.1, server: ja.tak-cslab.org, request: "GET / HTTP/1.1", upstream: "fastcgi://unix:/run/php/php7.2-fpm.sock:", host: "ja.tak-cslab.org", referrer: "http://ja.tak-cslab.org/"

調べたところkernelパラメータのnet.core.somaxconnが少ない場合に出るらしい．確認してみたところ，128だったので増やした．

$ sysctl net.core.somaxconn
net.core.somaxconn = 128

え、サーバ増やしてませんか？ソケット設定したその後に！？ - Qiita

1024に増やしてみる．

/etc/sysctl.conf

net.core.somaxconn=1024

適用する

sudo sysctl -p

PHP-FPMのパラメータ listen.backlog

Nginx+PHP-FPMでチューニング - つるながの綴り方

/etc/php/7.2/fpm/pool.d/www.conf

listen.backlog = -1

再起動

sudo systemctl restart php7.2-fpm

nginxのコネクション

/etc/nginx/nginx.conf

events {
    worker_connections 65535;
    multi_accept on;
    use epoll;
}

MySQLのチューニング

mysqltunerの実行結果

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)

設定ファイルを作成

さくらVPS512で、Yahoo!砲食らっても WordPress を平常運転させるための設定 - dogmap.jp

/etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam_recover_options  = BACKUP
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size   = 100M
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

query_cache_size        = 128M
query_cache_limit       = 2M
query_cache_min_res_unit= 4k
query_cache_type        = 1
tmp_table_size          = 64M
max_heap_table_size     = 32M
innodb_log_file_size    = 16M
innodb_buffer_pool_instances = 1
long_query_time         = 1
slow_query_log          = 1
slow_query_log_file=/var/log/mysql/mysql-slow.sql

[embedded]

[mariadb]

[mariadb-10.1]

MySQLパフォーマンスチューニング -クエリキャッシュ適用状況の確認- - Qiita

PHP-FPM のチューニング

/etc/php/7.2/fpm/pool.d/www.conf

[www]
user = www-data
group = www-data
listen = /run/php/php7.2-fpm.sock
listen.owner = www-data
listen.group = www-data
listen.backlog = -1
pm = dynamic
pm.max_children = 7
pm.start_servers = 2
pm.min_spare_servers = 1
pm.max_spare_servers = 3
; pm = static
; pm.max_children = 5
; pm.start_servers = 5
; pm.min_spare_servers = 5
; pm.max_spare_servers = 35
; slowlog = /var/log/php7.2-fpm-slow.log
; request_slowlog_timeout = 1s

[PHP] PHP-FPMのチューニングをするときに考えたことと行ったこと - Qiita

プラグインの導入

001 Prime Strategy Translate Acceleratorを導入した．

https://www.atmarkit.co.jp/ait/articles/1602/25/news017_3.html

ベンチマークには Apache Benchmarkを使用した．

ab -n 300 -c 30 https://ja.tak-cslab.org/

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        2   13  17.8      7     125
Processing:     1 1056 933.6   1679    2341
Waiting:        1 1056 933.7   1679    2341
Total:          5 1069 928.0   1682    2343

Percentage of the requests served within a certain time (ms)
  50%   1682
  66%   1848
  75%   1909
  80%   1955
  90%   2064
  95%   2188
  98%   2252
  99%   2312
 100%   2343 (longest request)

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:       31   68  70.1     49     309
Processing:     4   21  25.3     17     243
Waiting:        4   20  22.0     17     243
Total:         54   90  72.8     66     319

Percentage of the requests served within a certain time (ms)
  50%     66
  66%     68
  75%     70
  80%     78
  90%    172
  95%    313
  98%    313
  99%    313
 100%    319 (longest request)

locationの順序変更，breakの導入，ファイル存在確認の導入

WordPress用Nginxの設定を修正した．

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:       21   47   7.0     48      62
Processing:     8   18   9.5     14      43
Waiting:        8   18   9.5     14      43
Total:         53   66  10.5     62      96

Percentage of the requests served within a certain time (ms)
  50%     62
  66%     64
  75%     66
  80%     67
  90%     88
  95%     92
  98%     95
  99%     96
 100%     96 (longest request)