AUTOVICE

TECH BLOG

MySQLが頻繁に突然停止するので設定を見直す

2020-09-07

はじめに

仕事で作成させていただいたWebアプリで使っているMySQLが頻繁に突然停止しているという現象が続いていました。Webアプリへのアクセスに時間がかかることも気になっていました。

少なくとも稼働当初はMySQLが突然停止するなんてことはなかったはずですし、Webアプリへのアクセス時間もいたって普通だったと記憶しています。

件のVPSはRailsアプリとWordPressの両方を稼働させているので、リソースが足りなくなってメモリリークを起こしているのかと思っていました。そうだとすると、VPSの性能を上げるかRailsアプリとWordPressでVPSを分けるしかありませんが、現環境でできることはないかと思い、設定周りを調べてみることにしました。

環境

  • CentOS Linux release 7.7.1908 (Core)
  • mysql Ver 14.14 Distrib 5.7.28
  • PHP 7.2.25

原因を探る

まずは突然停止する原因を探るために、VPSでCPUやメモリの使用率を調べてみました。

topコマンドを実行したあと、Shift+mでメモリ使用率を確認します。

$ top
top - 13:44:23 up 278 days,  2:25,  2 users,  load average: 0.01, 0.10, 0.09
Tasks: 122 total,   2 running, 120 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.0 sy,  0.0 ni, 99.7 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :   498796 total,     6404 free,   430668 used,    61724 buff/cache
KiB Swap:  2097148 total,   113572 free,  1983576 used.    54348 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                                                       
23688 nginx     20   0  448916  43884   2648 S  0.0  8.8  30:03.44 php-fpm                                                                                                       
23707 nginx     20   0  451032  43136    356 S  0.0  8.6  29:58.56 php-fpm                                                                                                       
23689 nginx     20   0  448488  42940    284 S  0.0  8.6  29:49.35 php-fpm                                                                                                       
23708 nginx     20   0  449152  42872    328 S  0.0  8.6  30:02.65 php-fpm                                                                                                       
23713 nginx     20   0  450944  42700    288 S  0.0  8.6  29:47.66 php-fpm                                                                                                       
23712 nginx     20   0  448888  42412    296 S  0.0  8.5  29:46.59 php-fpm                                                                                                       
23717 nginx     20   0  450912  38696    288 S  0.0  7.8  29:43.92 php-fpm                                                                                                       
23718 nginx     20   0  449088  36432    216 S  0.0  7.3  29:59.10 php-fpm                                                                                                       
31275 mysql     20   0 1156332   8900    824 S  0.0  1.8   1:15.04 mysqld                                                                                                        
23716 nginx     20   0  448948   1968    212 S  0.0  0.4  29:51.01 php-fpm                                                                                                       
 1841 user      20   0  116692   1832    268 S  0.0  0.4   0:00.04 bash                                                                                                          
    1 root      20   0  191188   1800    796 S  0.0  0.4  23:59.56 systemd                                                                                                       
 1838 root      20   0  158872   1660    328 S  0.0  0.3   0:00.02 sshd                                                                                                          

てっきりMySQLのメモリ使用率がバカ高いのかと思ったら、php-fpmが大量に存在していてひとつひとつがそれなりにメモリを食ってました。

php-fpmはサーバー上でPHPを動かすためのもので、現環境ではWordPressを稼働させるために入れています。

なんとなく原因がわかった気がするので対処していきましょう。

対処

MySQL

MySQLのメモリ周りのことを調べると、table_definition_cacheperformance_schemaという設定のことが出てきます。

  • table_definition_cache:テーブル定義を入れるためのキャッシュ領域
  • performance_schema:サーバーのチューニングに役立てる情報を提供する(らしい)

現在の設定値はどうなっているか確認してみます。

$ mysql -uroot -p

mysql> show variables like 'table_definition_cache';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| table_definition_cache | 1400  |
+------------------------+-------+
1 row in set (0.02 sec)

mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

table_definition_cacheのデフォルト値はバージョンによって異なるようです。最小値の400で十分だと判断しました。

MySQLをWordPressで使っている場合、performance_schemaは不要らしいのでOFFに変更します。

$ sudo vi /etc/my.cnf

[mysql.d]
# 以下を追記
table_definition_cache = 400
performance_schema = 0

MySQLを再起動します。

$ sudo service mysqld restart

もう一度メモリ使用率を確認してみます(抜粋)。

$ top
  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
 2184 mysql     20   0  988316  39292   8620 S  0.0  7.9   0:00.23 mysqld                                                                                                        

劇的にというわけではありませんが、わずかながら変化はありました。

PHP-FPM

そもそもPHP-FPMは再起動を行わないとプロセスが肥大化していくそうです。なので先述のように大量のプロセスが残存していたんですね。

PHP-FPMの設定で考慮すべきは以下の3つです。

  • pm:プロセス数の制御(dynamicstatic
  • pm.max_children:プロセスの最大起動数(502
  • pm.max_requests:プロセスを再起動する処理リクエスト数(変更なし)
$ sudo vi /etc/php-fpm.d/www.cnf

pm = static
pm.max_children = 2
pm.max_requests = 500

PHP-FPMを再起動します。

$ sudo service php-fpm restart

もう一度メモリ使用率を確認してみます。

$ top
  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                                                       
 2024 nginx     20   0  436020  46908   6764 S  0.0  9.4   0:01.27 php-fpm                                                                                                       
 2025 nginx     20   0  352424  44100   4128 S  0.0  8.8   0:01.64 php-fpm                                                                                                       
 2184 mysql     20   0  988316  39292   8620 S  0.0  7.9   0:00.23 mysqld                                                                                                        
15424 user      20   0  696296  38560   3084 S  0.0  7.7   5:00.91 ruby                                                                                                          
15425 user      20   0  696428  31460   2900 S  0.0  6.3   5:05.64 ruby                                                                                                          
 2023 root      20   0  314748  10540   6568 S  0.0  2.1   0:00.08 php-fpm                                                                                                       
16310 nginx     20   0  125024   2968   1676 S  0.0  0.6   0:17.25 nginx                                                                                                         
    1 root      20   0  191188   2764   1568 S  0.0  0.6  23:59.64 systemd                                                                                                       
 1942 root      20   0  116680   2764   1108 S  0.0  0.6   0:00.12 bash                                                                                                          
 2215 root      20   0  161888   2148   1544 R  0.4  0.4   0:00.03 top                                                                                                           
  637 root      20   0  550136   1960   1120 S  0.0  0.4  17:56.73 NetworkManager                                                                                                
 1841 user      20   0  116692   1796    228 S  0.0  0.4   0:00.04 bash                                                                                                          
 1840 user      20   0  158872   1744    380 S  0.0  0.3   0:00.16 sshd 

PHP-FPMのプロセス数は減りました(再起動したからだと思いますが)。

RailsアプリとWordPressにアクセスしてみると、処理時間がかなり短縮されているように感じました。

MySQLが突然停止する事象については経過を観察する必要があるので、後日また確認して追記したいと思います。

追記

設定変更から一週間が経ったので経過を追記します。

設定を変更したことによってMySQLが突然停止することはなくなりました。また、Webアプリへのアクセス時間も問題が起こっていた頃と比べてだいぶ早くなった気がします。

最後にtopコマンドの結果を見てみます。

top - 16:25:56 up 285 days,  5:07,  1 user,  load average: 0.00, 0.01, 0.05
Tasks:  84 total,   2 running,  82 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.3 sy,  0.0 ni, 99.7 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :   498796 total,    13792 free,   347276 used,   137728 buff/cache
KiB Swap:  2097148 total,  1852836 free,   244312 used.   119544 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                                                        
15425 user      20   0  696428  82984   3152 S  0.0 16.6   5:16.84 ruby                                                                                                           
15424 user      20   0  696296  80736   3024 S  0.0 16.2   5:12.14 ruby                                                                                                           
 3114 nginx     20   0  452956  60456   5400 S  0.0 12.1  14:06.64 php-fpm                                                                                                        
 3113 nginx     20   0  450764  58340   5388 S  0.0 11.7  14:10.82 php-fpm                                                                                                        
 2346 mysql     20   0 1012016  27008   3880 S  0.0  5.4   6:49.69 mysqld                                                                                                         
15418 user      20   0  680540  11848    464 S  0.0  2.4   1:57.21 ruby                                                                                                           
18061 root      20   0  158868   5680   4340 S  0.0  1.1   0:00.01 sshd                                                                                                           
18065 user      20   0  116692   3268   1708 S  0.0  0.7   0:00.04 bash                                                                                                           
  637 root      20   0  550136   3232   2116 S  0.0  0.6  18:16.95 NetworkManager                                                                                                 
 4200 nginx     20   0  124708   3204   1604 S  0.0  0.6   0:15.19 nginx                                                                                                          
 1221 root      20   0  247336   2636   2068 S  0.0  0.5  31:17.44 rsyslogd                                                                                                       
    1 root      20   0  191188   2608   1500 S  0.0  0.5  24:25.78 systemd                                                                                                        
18064 user      20   0  158868   2424   1088 S  0.0  0.5   0:00.04 sshd                                                                                                           
18158 user      20   0  161888   2208   1604 R  0.0  0.4   0:01.06 top                                                                                                            
  344 root      20   0   35384   2108   1968 S  0.0  0.4   5:19.78 systemd-journal                                                                                                
 3112 root      20   0  314748   1896    108 S  0.3  0.4   0:36.32 php-fpm                                                                                                        
  529 dbus      20   0   60328   1108    752 S  0.0  0.2  15:31.16 dbus-daemon                                                                                                    
  503 polkitd   20   0  718692   1072    432 S  0.0  0.2   2:08.54 polkitd                                                                                                        
  504 root      20   0   26512    980    804 S  0.0  0.2   6:48.35 systemd-logind                                                                                                 
 1715 root      20   0  102896    892    664 S  0.0  0.2   0:00.62 dhclient                                                                                                       
  941 root      20   0  574204    672    188 S  0.0  0.1  61:44.03 tuned                                                                                                          
 4199 root      20   0  121644    616     28 S  0.0  0.1   0:00.00 nginx                                                                                                          
  534 chrony    20   0  117804    560    420 S  0.0  0.1   0:55.51 chronyd                                                                                                        
  514 rpc       20   0   69340    484    336 S  0.0  0.1   1:12.58 rpcbind                                                                                                        
 1227 root      20   0  126320    444    336 S  0.0  0.1   5:44.70 crond  

mysqldは相変わらずですが、php-fpmは設定変更(常駐プロセス数の抑制)してからの状態を維持しています。このことから、今回の事象はphp-fpmが原因だったということでしょう。