ゼロスタートの@zakiさんを中心に企画、運営されているチューニンガソンというイベントがあります。指定された環境を用いてOSやサーバ周りのチューニングを行い、性能を競うというものです。2013年1月に行われた第5回ではMySQLがテーマだったということで、本日はこれの復習をしていこうと思います。
しばらく真面目にチューニングしていたのですが思いのほか難しかったので、今回はちょっとズルをして優勝タイムを出すところまでご紹介します。
計測条件
Dropboxでレギュレーションが公開されています。
また、イベントで用いられたものと同じAmazon Machine Imageが公開されています。
しかし従量課金のm1.xlargeで作業していると、正直私は焦りを隠せません。落ち着いて作業をするために、まずは自宅のPCへ引っ越しをしました。
計測プログラムのオリジナル版には、結果を集計サーバに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 QUERY CACHE;
SELECT 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 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 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 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 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を使用して、まずはそのまま実行してみます。
$ 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です。なお長い間アルファ版のまま開発が進んでいないため、本番環境で使用することはおすすめしません。
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 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スクリプトを以下のように修正します。
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 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スクリプトで同じ文字列を返すようにできます。
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ユーザ会のイベントでご報告したいと思います。
当日は@yoku0825さんもMySQL 5.6の新機能について発表されます。ぜひ、ご参加ください。
