MySQLのチューニング〜PageSpeed Insightsが表示する「サーバーの応答時間を短縮する」の対応準備〜 MySQLTunerとSHOWクエリの活用

2018年8月24日

Googleが提供するPageSpeed Insightsの「最適化提案」の1つとして、「サーバーの応答時間を短縮する」という項目があります。「サーバーの応答時間を短縮する」を指摘されると、実際の応答速度とともに、サーバーの応答時間を改善するための「Googleの推奨事項をお読みください」というリンクが表示されます。

サーバーの応答時間が遅くなる原因

この「Googleの推奨事項」をクリックすると、サーバーの応答時間が遅くなる原因として以下の原因が挙げられています。

速度の遅いアプリケーションロジック
遅いデータベースクエリ
遅いルーティング、フレームワーク、ライブラリ
リソースによるCPUの消費
メモリ不足

今回の記事では、特に遅いデータベースクエリについて考えます。メモリの問題はすでに片付けていて、Googleが推奨するスピードである200m秒以下にならない原因は、MySQLのチューニングに原因があると考えたからです。

MySQLTunerでMySQLを診断

MySQLのチューニングのどこに問題があるかは、MySQLTunerを使って調べます。MySQLTunerはPerlで作成されたMySQLの診断アプリケーションです。

MySQLTunerのインストール

# cd /usr/local/src/
# wget -O MySQLTuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip 
# unzip MySQLTuner.zip
# cd MySQLTuner-perl-master
# chmod +x mysqltuner.pl

まずwgetコマンドを使って、GitHubからMySQLTunerの圧縮ファイルを取得します。圧縮したファイルをunzipで解凍して、mysqltuner.plに対して書き込み権限を与えます。

MySQLの診断

# pwd
/usr/local/src/MySQLTuner-perl-master
# ./mysqltuner.pl

mysqltuner.plを実行すると、実行結果がいろいろと表示されますが、サーバーの応答時間に関して見るべき箇所はPerformance Metricsという箇所です。

-------- Performance Metrics ------------------------------------------------------
...
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 1M selects)
...
[!!] Temporary tables created on disk: 78% (163K on disk / 207K total)

改善の余地があるとされる箇所は、!!と表示されます。主にクエリキャッシュと一時（Temp）テーブルの利用について改善の余地があると診断されました。その他の項目についての診断結果に関する解釈はこちらのページが参考になります。

SHOWクエリによるパフォーマンス確認

MySQLTunerでは、MySQLのパフォーマンスについてどこを改善すべきかは教えてくれますが、実際のパフォーマンス値は分かりません。具体的な数値を知りたければ、MySQLの中に入ってSHOWクエリを実行して数値を測定します。

クエリーキャッシュの設定数値一覧

mysql> SHOW VARIABLES LIKE '%query_cache_%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

クエリーキャッシュの状態を示す一覧

mysql> SHOW GLOBAL STATUS LIKE "%QCache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031352 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 4957179 |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+