チューニンガソン5の復習 MySQL 5.5 チート編＆勉強会のお知らせ
ゼロスタートの@zakiさんを中心に企画、運営されているチューニンガソンというイベントがあります。指定された環境を用いてOSやサーバ周りのチューニングを行い、性能を競うというものです。2013年1月に行われた第5回ではMySQLがテーマだったということで、本日はこれの復習をしていこうと思います。
- 【レポート】いろいろチューニングしてパフォーマンスを競うバトルイベント！「Tuningathon」第5弾！ #tuningathon : ゼロスタートの広報ブログ
- 2013年最初のチューニンガソンはMySQL！――第5弾！ いろいろチューニングしてパフォーマンスを競うバトルイベント「チューニンガソン」レポート：レポート｜gihyo.jp … 技術評論社
- Tuningathon#5 - Togetter
しばらく真面目にチューニングしていたのですが思いのほか難しかったので、今回はちょっとズルをして優勝タイムを出すところまでご紹介します。
計測条件
Dropboxでレギュレーションが公開されています。
また、イベントで用いられたものと同じAmazon Machine Imageが公開されています。
しかし従量課金のm1.xlargeで作業していると、正直私は焦りを隠せません。落ち着いて作業をするために、まずは自宅のPCへ引っ越しをしました。
- データベースのダンプファイル tuningathon5.sql.gz (116MB)
- MySQLの設定ファイル my.cnf
- 計測プログラムの改変版 tgbench_mysql_2.py
計測プログラムのオリジナル版には、結果を集計サーバにPOSTする機能があります。しかしもう集計サーバはありませんので、改変版ではPOST機能を取り除いてあります。またSQLとログを少し見やすく整形しています。@zakiさんに改変版の頒布許可をいただきました。どうもありがとうございます。
データベースはMediaWikiのもので、Wikipedia日本語版が格納されています。
CREATE TABLE `page` ( `page_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `page_namespace` int(11) NOT NULL, `page_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `page_restrictions` tinyblob NOT NULL, `page_counter` bigint(20) unsigned NOT NULL DEFAULT '0', `page_is_redirect` tinyint(3) unsigned NOT NULL DEFAULT '0', `page_is_new` tinyint(3) unsigned NOT NULL DEFAULT '0', `page_random` double unsigned NOT NULL, `page_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `page_latest` int(10) unsigned NOT NULL, `page_len` int(10) unsigned NOT NULL, PRIMARY KEY (`page_id`), UNIQUE KEY `name_title` (`page_namespace`,`page_title`), KEY `page_random` (`page_random`), KEY `page_len` (`page_len`), KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`) ) ENGINE=InnoDB AUTO_INCREMENT=2704666 DEFAULT CHARSET=utf8; CREATE TABLE `revision` ( `rev_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `rev_page` int(10) unsigned NOT NULL, `rev_text_id` int(10) unsigned NOT NULL, `rev_comment` tinyblob NOT NULL, `rev_user` int(10) unsigned NOT NULL DEFAULT '0', `rev_user_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `rev_timestamp` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `rev_minor_edit` tinyint(3) unsigned NOT NULL DEFAULT '0', `rev_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0', `rev_len` int(10) unsigned DEFAULT NULL, `rev_parent_id` int(10) unsigned DEFAULT NULL, `rev_sha1` varbinary(32) NOT NULL DEFAULT '', PRIMARY KEY (`rev_id`), UNIQUE KEY `rev_page_id` (`rev_page`,`rev_id`), KEY `rev_timestamp` (`rev_timestamp`), KEY `page_timestamp` (`rev_page`,`rev_timestamp`), KEY `user_timestamp` (`rev_user`,`rev_timestamp`), KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`), KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`) ) ENGINE=InnoDB AUTO_INCREMENT=45658130 DEFAULT CHARSET=utf8 MAX_ROWS=10000000 AVG_ROW_LENGTH=1024;
mysql> SELECT * -> FROM information_schema.tables -> WHERE table_schema = 'wikipedia' -> AND table_name IN ('page', 'revision')\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: wikipedia TABLE_NAME: page TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Compact TABLE_ROWS: 1670757 AVG_ROW_LENGTH: 98 DATA_LENGTH: 164265984 (156.7MB) MAX_DATA_LENGTH: 0 INDEX_LENGTH: 207159296 (197.6MB) DATA_FREE: 85983232 AUTO_INCREMENT: 2704666 CREATE_TIME: 2013-03-03 20:54:20 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8_general_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: wikipedia TABLE_NAME: revision TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Compact TABLE_ROWS: 1599526 AVG_ROW_LENGTH: 136 DATA_LENGTH: 217808896 (207.7MB) MAX_DATA_LENGTH: 0 INDEX_LENGTH: 431144960 (411.2MB) DATA_FREE: 85983232 AUTO_INCREMENT: 45658130 CREATE_TIME: 2013-03-03 20:55:53 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8_general_ci CHECKSUM: NULL CREATE_OPTIONS: max_rows=10000000 avg_row_length=1024 TABLE_COMMENT:
MySQLの設定ファイルはRed Hat Enterprise Linuxバンドル版のmy.cnfを基本として、キャラクタセットのみ追加指定した状態です。チューニングはまったく施されていません。
[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 socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character-set-server=utf8 [mysql] default-character-set=utf8
計測プログラムが実行するクエリは以下の6種類です。これらが1回ずつ順番に実行されます。
RESET /* Q0 */ QUERY CACHE; SELECT /* Q1 */ COUNT(*) FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 AND r.rev_user = 0; SELECT /* Q2 */ COUNT(*) FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 AND r.rev_user = 0 AND p.page_touched > DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y0101000000'); SELECT /* Q3 */ p.page_id FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 AND r.rev_user = 0 ORDER BY p.page_touched DESC LIMIT 10; SELECT /* Q4 */ r.rev_user, COUNT(*) AS c FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 GROUP BY r.rev_user ORDER BY c DESC; SELECT /* Q5 */ SUBSTRING(r.rev_timestamp, 1, 6), COUNT(*) AS c FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 GROUP BY SUBSTRING(r.rev_timestamp, 1, 6) ORDER BY c DESC;
ベースライン
手元のPCを使用して、まずはそのまま実行してみます。
- CPU：Intel Core i5-2400S (Quad-Core、2.50GHz、Max 3.30GHz)
- Host OS：Scientific Linux 6.3 x86_64
- Guest OS：Scientific Linux 6.3 x86_64
- Guest CPU Allocation：2 Cores
- Guest Memory Allocation：2 GB
$ python tgbench_mysql_2.py 127.0.0.1 Q0:0.00018 Q1:5.97537 Q2:4.66229 Q3:4.59017 Q4:14.48054 Q5:14.30720 Total:43.97575
1回目は44.0秒、2回目は41.1秒となりました。2回目はOSのページキャッシュ、InnoDBのバッファプールにデータがキャッシュされるため性能が向上します。クエリの単体性能を計測する際は、2回ずつ実施することをおすすめします。
当日は140秒程度の結果が出ていたそうで、m1.xlargeに比べるとCore i5-2400Sの方が3倍程度速いということになります。m1.xlargeはXeon E5645でSPECint_base2006は36.4、一方Core i5-2400SのSPECint_base2006は36.0とCPUコアあたりの性能にはほとんど違いがないのですが、EC2の方で何らかの制限が設けられているのだと考えられます。
チューニングその1：InnoDBバッファプールの拡大
計測プログラムが実行するクエリはpageテーブルとrevisionテーブルのみにアクセスします。pageテーブルのサイズは354.2MB、revisionテーブルのサイズは618.9MBということで、まずはこれらのテーブルがすべて収まるだけのメモリを割り当てることがチューニングの第一歩になるかと思います。
[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 socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 innodb_buffer_pool_size=1G
8.8秒になりました。m1.xlargeの場合は、これで17秒程度になったようです。
チューニングその2：RESET QUERY CACHEの回避
クエリ0番にRESET QUERY CACHEがあるのを見てしまうと、やはりこれを回避したいと思うのが人情ではないでしょうか。本番で成功した方はいなかったのですが、回避の可能性には数名の方が気づいていたようです。
MySQL Proxyとは、MySQLの通信プロトコルを理解しMySQLクライアントとMySQLサーバとの通信を横取りしてさまざまな操作を行うサーバプログラムです。Luaスクリプトによって処理内容を定義し、ロードバランサとして使用したりクエリのプロファイリングを行ったりすることが想定されています。ライセンスはGPLv2です。なお長い間アルファ版のまま開発が進んでいないため、本番環境で使用することはおすすめしません。
-- tuningathon5_q0.lua -- Q0: Ignoring 'RESET QUERY CACHE' function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local query = string.sub(packet, 2) if string.find(query, "^%s*RESET /%* Q0 %*/ QUERY CACHE%s*$") then print("[HIT Q0]") proxy.queries:reset() proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "FLUSH /* Q0_rewrite */ QUERY CACHE") return proxy.PROXY_SEND_QUERY end end end
このLuaスクリプトで、RESET QUERY CACHEをFLUSH QUERY CACHEに書き換えてしまいます。RESET QUERY CACHEはクエリキャッシュを削除するコマンド、一方FLUSH QUERY CACHEはクエリキャッシュをデフラグするだけのコマンドです。これら二つのコマンドは処理が成功した際の戻りのパケットが同一ですので、クエリを書き換えていることはそう簡単には発覚しないはずです。
[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 socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 innodb_buffer_pool_size=1G port=3307 query_cache_size=1M
MySQLサーバのTCP待ち受けポートを変更し、クエリキャッシュの設定を施しておきます。今回のクエリに対しては、クエリキャッシュは1MBあれば足ります。MySQL Proxyを以下のオプションで起動し、MySQLクライアントから送られてくるクエリをMySQL Proxyで中継してMySQLサーバに渡すようにします。
$ /PATH/TO/mysql-proxy --proxy-address=:3306 \ --proxy-backend-addresses=:3307 \ --proxy-lua-script=tuningathon5_q0.lua
1.0秒になりました。優勝タイムは6.2秒とのことで、環境の違いを加味してもこの時点で上回ることができたのではないかと思います。
チューニングその3：クエリ1番の修正
RESET QUERY CACHEを回避することでクエリ3〜5番は速くなったのですが、実はクエリ1番と2番が速くなっていません。
mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 107 | +---------------+-------+ 1 row in set (0.00 sec) (計測プログラム実行) mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 110 | +---------------+-------+ 1 row in set (0.00 sec)
計測プログラムの前後でクエリキャッシュの状態を確認すると、3回しかクエリキャッシュにヒットしていないことが分かります。これはクエリ3〜5番の分です。なぜクエリ1番と2番はクエリキャッシュにヒットしないのでしょうか。まずクエリ1番から確認していきましょう。
mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 110 | +---------------+-------+ 1 row in set (0.00 sec) mysql> SELECT /* Q1 */ COUNT(*) -> FROM page p -> JOIN revision r -> ON p.page_id = r.rev_page -> WHERE p.page_is_redirect = 0 -> AND p.page_namespace = 0 -> AND r.rev_user = 0; +----------+ | COUNT(*) | +----------+ | 273537 | +----------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 111 | +---------------+-------+ 1 row in set (0.00 sec)
クエリ1番を手打ちすると、きちんとクエリキャッシュにヒットしていることが分かります。原因を突き止めることはなかなか難しいと思いますが、これは、残念ながらMySQLの不具合です。
AUTOCOMMITがOFFの場合に、トランザクション開始前の最初のクエリがクエリキャッシュにヒットしないというものです。今も直っていません。PythonのMySQLdbモジュールはPython Database API Specification v2.0にもとづきデフォルトでAUTOCOMMITがOFFになっており、この不具合を必ず踏みます。ワークアラウンドとして「START TRANSACTIONで明示的にトランザクションを開始しておく」という方法がありますので、先ほどのLuaスクリプトを以下のように修正します。
-- tuningathon5_q0q1.lua -- Q0: Ignoring 'RESET QUERY CACHE' -- Q1: Workaround for Bug#42197 function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local query = string.sub(packet, 2) if string.find(query, "^%s*RESET /%* Q0 %*/ QUERY CACHE%s*$") then print("[HIT Q0]") proxy.queries:reset() proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "FLUSH /* Q0_rewrite */ QUERY CACHE") proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "START /* Q1_workaround */ TRANSACTION", { resultset_is_needed = true }) return proxy.PROXY_SEND_QUERY end end end function read_query_result(inj) if inj.id == 2 then return proxy.PROXY_IGNORE_RESULT end end
クエリ0番を実行した直後にSTART TRANSACTIONを実行します。その際START TRANSACTIONの結果をそのままクライアントに返すと計測プログラムが誤動作してしまいますので、read_query_result()ファンクションで結果を握りつぶしています。
0.56秒になりました。グラフでオレンジ色の部分がなくなったことが分かると思います。
チューニングその4：クエリ2番の修正
クエリ2番がクエリキャッシュにヒットしない理由は明快で、NOW()ファンクションを使用しているためです。
SELECT /* Q2 */ COUNT(*) FROM page p JOIN revision r ON p.page_id = r.rev_page WHERE p.page_is_redirect = 0 AND p.page_namespace = 0 AND r.rev_user = 0 AND p.page_touched > DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y0101000000');
マニュアルに、クエリキャッシュにヒットしなくなるファンクションの一覧が記載されています。
このクエリをどうやって直すかですが、あらためてDATE_FORMAT()ファンクションのところをよく見ると、出力結果が年月日のうち年にしか依存していないことが分かると思います。ここの部分は2013年1月1日〜12月31日までは「20120101000000」という文字列を、2014年1月1日〜12月31日までは「20130101000000」という文字列を返します。このくらいでしたらLuaスクリプトで同じ文字列を返すようにできます。
-- tuningathon5_q0q1q2.lua -- Q0: Ignoring 'RESET QUERY CACHE' -- Q1: Workaround for Bug#42197 -- Q2: Avoiding NOW() function function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local query = string.sub(packet, 2) if string.find(query, "^%s*RESET /%* Q0 %*/ QUERY CACHE%s*$") then print("[HIT Q0]") proxy.queries:reset() proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "FLUSH /* Q0_rewrite */ QUERY CACHE") proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "START /* Q1_workaround */ TRANSACTION", { resultset_is_needed = true }) return proxy.PROXY_SEND_QUERY elseif string.find(query, "^%s*SELECT /%* Q2 %*/ COUNT%(%*%)%s*" .. "FROM page p%s*" .. "JOIN revision r%s*ON p%.page_id = r%.rev_page%s*" .. "WHERE p%.page_is_redirect = 0%s*" .. "AND p%.page_namespace = 0%s*" .. "AND r%.rev_user = 0%s*" .. "AND p%.page_touched > DATE_FORMAT%(NOW%(%) %- INTERVAL 1 YEAR, '%%Y0101000000'%)%s*$") then print("[HIT Q2]") proxy.queries:reset() proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "SELECT /* Q2_rewrite */ COUNT(*) " .. "FROM page p " .. "JOIN revision r ON p.page_id = r.rev_page " .. "WHERE p.page_is_redirect = 0 " .. "AND p.page_namespace = 0 " .. "AND r.rev_user = 0 " .. "AND p.page_touched > '" .. os.date('%Y') - 1 .. "0101000000'") return proxy.PROXY_SEND_QUERY end end end function read_query_result(inj) if inj.id == 2 then return proxy.PROXY_IGNORE_RESULT end end
0.067秒になりました。このグラフでは何も見えなくなりました。拡大してみましょう。
最終的にクエリ4番が処理時間の大半を占めるようになりました。これは、クエリ4番の結果セットが39,434レコードと最も大きいためです。
勉強会のお知らせ
MySQL Proxyを使用してクエリキャッシュを無理やり効かせるという今回のチューニング方針ですが、MySQL Proxyがアルファ版であること、クエリキャッシュはCPUスケーラビリティが悪く最近は使われない傾向であることを考慮すると、実際の業務に応用することは難しくあまり筋が良いチューニング方針だとは言えません。
本エントリ執筆時点でまだ上手くできていないのですが、クエリキャッシュをOFFに戻して、ついでにMySQLを5.6にバージョンアップしてどこまで改善できるのかを調べています。調査結果について、今月久しぶりに開催されるMySQLユーザ会のイベントでご報告したいと思います。
- MyNA(日本MySQLユーザ会)会 2013年3月 : ATND
- 日時：2013年03月15日(金) 18:30〜21:00
- 場所：日本オラクル 青山センター
