第5弾! Tuningathon(チューニンガソン)で優勝した時にやったこと

こんな経験はそうそう無いと思うので、忘れない内にメモしておきます。

※記載されている内容は、基本的に個人的な見解に基づくものであり、間違っているかもしれません。ご了承ください。

まずは下準備から。

 

MySQLのバージョンを確認します。

# mysql --version

バージョンは5.5.29でした。

最新安定版なので、とりあえずこのままで行こうと思いました。

途中で行き詰まった時に5.6を試してみようかと少し考えましたが、

同時実行性が求められる内容ならともかく、今回のような単発の遅いSQLが劇的に早くなるとは思えなかったので、

結局最後までこのまま行きました。

 

環境を壊した場合は、インスタンスを初期化してもらうとして、

データやテーブルを戻したい時の念のためmysqldumpでバックアップをとります。

 # mysqldump -uroot wikipedia > /tmp/wikipedia01.dump

 

テスト用のスクリプトを確認します。

RESET QUERY CACHE;

というクエリが先頭にあったので、

「クエリキャッシュは使えない」ということだけとりあえず把握しました。

 

サーバのスペック(特に大事なメモリ容量)を確認します。

# cat /proc/meminfo

なんと、メモリ容量15GB!!dumpファイルが1GBもなかったので、

メモリは潤沢すぎるほどに載っているということがわかりました。

 

テーブルの中身を見たり、クエリを流すのを楽にできるように、

phpMyAdminをインストールします。

# yum install php54 php54-mysqlnd

↑依存関係でApacheも入ります。

phpMyAdminは、手元にあった3.5.1を入れました。

最新版はコチラ

以外にもこれが後々の最後の決め手に気がつく要因の一つとなりました。

詳しくは後述します。

 

ここからが、本題のチューニング!

 

MySQLのチューニングと聞いた瞬間、真っ先にやろうとしたのが、

「ストレージエンジンをMEMORY(HEAP)エンジン」に変えることです。

・・・ですが、

mysql > ALTER TABLE テーブル名 ENGINE = MEMORY;

を実行しようとした所、「BLOBまたはTEXT型のカラムがあるため変更できません」と怒られてしまいました・・・

MyISAMにしようかとも少しだけ思いましたが、

「MySQL5.5はInnoDBが劇的に進化していて、MyISAMと同じくらいか、クエリによってはむしろ早い」

という噂を聞いたことがあったので、慣れ親しんだInnoDBのままで行くことにしました。

 

ストレージそのものをオンメモリ化できないということで、

メモリ関連の設定ファイルの調整に入ります。

設定ファイルの内容↓

[mysqld] 
# Settings user and group are ignored when systemd is used. 
# If you need to run mysqld under different user or group, 
# customize your systemd unit file for mysqld according to the 
# instructions in http://fedoraproject.org/wiki/Systemd 
datadir=/var/lib/mysql 
#tmpdir=/dev/shm
socket=/var/lib/mysql/mysql.sock 
# Disabling symbolic-links is recommended to prevent assorted security risks 
symbolic-links=0 
character-set-server=utf8 
skip-name-resolve
innodb_buffer_pool_size = 4G
innodb_log_buffer_size  = 4M 
innodb_additional_mem_pool_size = 128M 
thread_cache_size = 10 
innodb_log_file_size = 256M 

sort_buffer_size = 512M 
join_buffer_size = 128M 
read_buffer_size = 512M 
read_rnd_buffer_size = 512M 
max_allowed_packet = 16M 

table_open_cache = 2048 
table_cache = 2048 
max_sort_length = 12 

max_heap_table_size = 2G 
tmp_table_size = 2G 

[mysqld_safe] 
log-error=/var/log/mysqld.log 
pid-file=/var/run/mysqld/mysqld.pid 
character-set-server=utf8

 

割り当て量は、メモリが盛大に乗っているサーバだったため、

だいぶ適当です。

 

ポイントは以下のあたりかと思います。

それにしても割り当て過ぎな面がありますが、減らす調整をしても全く速くならなかったので途中で止めました。

skip-name-resolve コネクションオープンが遅くなるので、DNS逆引きによる名前解決を行わない
thread_cache_size スレッド(コネクション)をキャッシュして接続のオーバヘッドを低減させる。今回は最大でローカルとオフィシャルの同時2接続だけなので少なくて良いが、確保はしておく。
innodb_buffer_pool_size InnoDBの一番大事なバッファ。全データが確実にのる分確保する。
sort_buffer_size 後半二つのSQLが、集計項目でORDER BYしており、インデックスが効かないので、使用されるテーブルがまるごと乗るくらい確保。
read_buffer_size 最後のSQLが、関数を噛ました項目でGROUP BYしており、インデックスが効かないので、フルスキャンが発生していると思われるので使用されるテーブルがまるごと乗るくらい確保。
read_rnd_buffer_size 後半二つのSQLが、GROUP BYで時間が掛かっていたので使用されるテーブルがまるごと乗るくらい確保。
max_heap_table_size
tmp_table_size
後半二つのSQLが、GROUP BYを処理するための一時テーブルの作成に時間がかかっていたので、盛大に確保。

 

次に、とりあえず実行計画はとらずに勘で明らかに足りていない部分にインデックスを張りました。

この時点では既存のインデックスの一部が効いていそうな部分はとりあえずノータッチとしました。
この時点で初めて計測した所、16~18秒程度だったと思います。
内訳は、
1•2番目→0.7-8秒
3番目→0.01秒
4•5番目→7-8秒
ちなみに、

GROUP BY、ORDER BY、一時テーブルの作成に時間が掛かっていると書いていますが、

これは、MySQLのプロファイリング機能の出力結果から判断しました。

この後はこの結果を持ってチューニングしています。

mysql > set profiling = 1;

mysql > SQL実行;

mysql > show profile;

以上を行うと、何に何秒(マイクロ秒まで)掛かった出力されます。

目立ったのは以下の3つでした。→全ての解説はこちら

Sorting for order ORDER BYの時間
Creating tmp table 一時テーブルの作成時間=今回ではGROUP BYの時間に含まれる
Sending Data 名前的には結果を送信する時間だが、集計関数の実行も含まれる(これは、ネットで調べて今回初めて知りました)

実行結果から、1〜3番目はそれなりに十分に早かったので、
4•5番目を中心にチューニングすることにしました。

プロファイリングの結果では、
Creating tmp tableにほとんどの時間を費やしていたので、
まず、疑ったのが「一時テーブルがディスク上に作られてしまっている」ということです。
しかしこれは間違いで、いくら手を打っても改善されず、
後になってMySQLのパフォーマンス統計情報でディスク上に一時テーブルが作られた回数(Created tmp disk tables)というのを保持しているので、確認してみたら0回になっていました…
ちなみにこの情報は、phpMyAdminで簡単に見られます。
コマンドは…忘れましたw
ここでかなりの時間をロスしてしまいました。
たしか、3時過ぎくらいまで格闘していたと思います。
ダメ元で一時データ領域をtmpfsに載せてみたりという迷走もしましたw
先にパフォーマンス統計情報を確認するべきでした…

後半二つは、他に改善方法の検討がつかなかったので、
とりあえず、前半二つを少しでも早くすることにしました。

前半二つは、Sending Dataで時間がかかっており、
「こんな結果が小さい内容でそんなはずはない」と思ってネットで調べたら、

「集計関数の集計時間も含まれる」

とあったので、さらに集計関数の高速化方法を調べた所、

「COUNT(*)はインデックスカラムの中で最もサイズの小さいものを数えるため、カラム中で最もサイズの小さいものにインデックスを貼ると少し速くなる」

とあったので、実践したら0.4秒くらいになりました。

そして、「ここから先はインデックスの最適化しか道は無い!」と思い、インデックスの最適化にとりかかります。

ひたすら実行計画を取り、
使われていないインデックスの削除
および、
使用されているインデックスの最適化
を行いました。
最適化は、複合インデックスの使われていない項目を削って、
使われると思われる項目があれば追加していく流れで行いました。

この結果、合計で9.3〜10秒程度まで速度が上がりました。
ですが、残り一時間弱。既に8秒台の方が続々と出てきており、入賞には届かない状況でした。

手詰まり感を感じつつ、より適性化できるインデックスが無いかとphpMyAdminの画面とにらめっこしていました。

ここで、奇跡的にも今回の決めてとなる「主キーの変更」に気がつきました。

phpMyAdminで、インデックスの一覧と一緒にそのインデックスにおける一意な値の数が表示されるのですが、
revisonテーブルの結合キーと主キーの一意な値の数が一緒であることに気がついたのです。

MySQLのインデックスは、クラスタインデックスという方式をとっており、
主キーと一緒に実データが格納され、主キー以外のインデックスは主キーの値を持っており、
インデックスでの検索はインデックスで参照した後に実データを取るために主キーで参照しなおすため、
主キーとインデックスでは速度に差があるということは知っていたので、

これ、主キーにしたら速くなるんじゃね?

ということで変更してみました。

結果!
後半の遅いSQLが2秒台にまで大幅に高速化され、
6.225秒(ローカルでは6.11秒まで出ました)というタイムで優勝することができました。

勢いで書いたので後ほど修正が入るかもしれません…ご了承をm(_ _)m

 

追記:

振り返りをまとめました。