遅いクエリがあるのでMySQL、MariaDBのメモリ設定を疑ってチューニングした話

2019/7/27 MySQL

仕事でとあるWordPressのサイトの管理画面の記事編集の画面が表示されるのがすごく遅いので調べて欲しいということで原因を調査してみた。
実際に管理画面に入って投稿済みの記事を編集画面で見ると表示にすごく時間がかかる。

ひとまず、その会社さんのテスト環境でプラグインを全部停止してみて再度、編集画面の表示がどうか確認したがやはり遅い。
その線は消えたので、次に
そのサイト自体かなりの容量を使っているサイトでDBが2GBくらいあるのでそのへんが原因かなと思いMySQLを疑ってみた。
MySQLにログインしたあと、問題となる編集画面を表示させ、同時にSHOW FULL PROCESSLISTを実行。

SELECT DISTINCT meta_key FROM wp_postmeta WHERE meta_key NOT BETWEEN '_' AND '_z' HAVING meta_key NOT LIKE '\\_%' ORDER BY meta_key LIMIT 30;

30秒以上実行されるクエリがあったのでこれが原因であることがわかった。
メモリの設定が怪しいと思い
my.cnfで色々設定していたのは知っていたのでmy.cnfでメモリの設定がどうなっているか確認したところ
innodb_buffer_pool_sizeのサイズが24Mくらいしか設定されてなかったので
実メモリの80%を割り当てるのがいいと言われているがかなりのメモリを積んでいるサーバーだったのでひとまず4Gで設定

innodb_buffer_pool_size = 4G

そのほかにも

SHOW STATUS LIKE '%connect%';

で

Max_used_connections     | 108

を確認

max_connectionsが2000とかに設定されていたのでこれを150もあれば大丈夫かもしれないがメモリが潤沢にあるので
300に修正

max_connections = 300

max_connectionsは下記コマンドで確認可能

SHOW GLOBAL VARIABLES LIKE 'max_connections';

あとはこまごまとした設定を
https://sawara.me/mysql/1428/
を参考に設定していきmysqlを再起動させ、編集画面を表示させたらすぐに表示されるようになりました。

一応、max_connectionsはむやみやたらに増やすとその他のメモリの設定によってはかなりやばくなるので注意が必要。
グローバルバッファ xGB + (スレッドバッファ yMB × コネクション数)
の合計値がメモリの80%を越えないように設定しないとまずい。