1. TOPTOP
  2. Webサービス
  3. MySQL

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

mysql_tuning_for_pagespeed_insights_0

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

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

mysql_tuning_for_pagespeed_insights_1

この「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

mysql_tuning_for_pagespeed_insights_2

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       |
+-------------------------+---------+

参考となるページ

なお、各SHOWクエリの意味については、上記のページが参考になります。おそらく自分が説明するよりも、公式ドキュメントやQiitaのページに書かれていることの方が正確を期することができるでしょう。