インフィニットループ 技術ブログ

2013年06月04日 (火)

著者 ： matsui

MySQL #MySQL #技術記事

MySQL5.6の新機能「InnoDB Memcached Plugin」の分離レベルを検証し、ソーシャルゲーム案件に使えそうか検証してみました

matsuiです。
先日6/1に行われた第四回札幌MySQL勉強会の中で、MySQL5.6の新機能「InnoDB Memcached Plugin」の分離レベルについて調べてみましたので、記事にしてみたいと思います。

InnoDB Memcached Pluginとは

「InnoDB Memcached Plugin」とは、MySQL5.6から使えるようになった、SQLを使わずMemcachedプロトコルを使ってInnoDBのデータにアクセスするためのものです。
主なメリットはその高速性にあり、Oracleでのベンチマークテストでは、SQL処理の9倍といった性能が出ているとのことです。
このあたりの速度やベンチマークについては、幾つものブログなどで検証されているようですので、今回は対象としませんでした。

分離レベルとは

それでは何を調査の対象にしたのかというと、それは分離レベルです。
分離レベルとは、トランザクションが複数個平行して行われた場合に、どのような一貫性、正確性で実行するかを4段階で定義したもので、REPEATABLE READとか、READ COMMITTEDとかのアレです。

検証内容

以下がテストケースです。3つの検証を行いました。
設定されている分離レベルはMySQLデフォルトの「REPEATABLE READ」です。
my.cnfなどの内容も、ほぼ初期値です。

  1. MySQLでBEGINした後にMemcacheからデータを更新した際、MySQL側からはどのように見えるか
  2. MySQLでBEGIN後にFOR UPDATEで行ロック、Memcache側から参照に制限がかかるか
  3. MySQLでBEGIN後にFOR UPDATEで行ロック、Memcache側からそのレコードを更新しようとしたらどうなるか

【検証用プログラム】

// 定義
$memcache_server = '192.168.3.254';
$db = 'test';
$user = $password = 'root';
// MySQLサーバにmencacheプロトコルでアクセス
$memcache = new Memcache;
$memcache->addServer($memcache_server, 11211);
format($memcache);
// MySQLサーバに普通にアクセス
$dsn = sprintf('mysql:dbname=%s;host=%s', $db, $memcache_server);
$dbh = new PDO($dsn, $user, $password);
$result = $dbh->query('SELECT @@global.tx_isolation;');
foreach ($result as $row) {
	v($row[0]);
}
$dbh->query('BEGIN;');
v('--- 更新前 ---');
readByMemcache($memcache);
readBySQL($dbh);
// テストケースを実行（コメントアウトを手動で切り替えて実行）
testCase1($memcache, $dbh);
//testCase2($memcache, $dbh);
//testCase3($memcache, $dbh);
// 値を読み出し
v('--- 更新後 ---');
readByMemcache($memcache);
readBySQL($dbh);
// ロールバック
$result = $dbh->query('ROLLBACK;');
v('--- MySQLロールバック後 ---');
readByMemcache($memcache);
readBySQL($dbh);
// テストケース1
// MySQLでBEGIN後にMemcache側で更新
function testCase1($memcache, $dbh) {
	v('# テストケース1を実行');
	$memcache->set('mkey1', 'zzz');
	v('# Memcache::set');
}
// テストケース2
// MySQLでBEGIN後に行ロック、その上でMemcache側で参照
function testCase2($memcache, $dbh) {
	v('# テストケース2を実行');
	$result_lock = $dbh->query("SELECT * FROM demo_test WHERE c1 = 'mkey2' FOR UPDATE");
	foreach ($result_lock as $row) {
		v('# 行ロックを実行: ' . $row['c1']);
	}
	$result = $dbh->exec("UPDATE demo_test SET c2 = 'yyy' WHERE c1 = 'mkey2'");
	v('# UPDATE: 影響を与えた行数: ' . $result);
}
// テストケース3
// MySQLでBEGIN後に行ロック、その上でMemcache側で更新
function testCase3($memcache, $dbh) {
	v('# テストケース3を実行');
	$result_lock = $dbh->query("SELECT * FROM demo_test WHERE c1 = 'mkey3' FOR UPDATE");
	foreach ($result_lock as $row) {
		v('# 行ロックを実行: ' . $row['c1']);
	}
	v('# Memcache::set');
	$memcache->set('mkey3', 'xxx');
}
// 初期化
function format($memcache) {
	$memcache->set('mkey1', 'abc');
	$memcache->set('mkey2', 'def');
	$memcache->set('mkey3', 'ghi');
}
// SQLでデータを読み込み
function readBySQL($dbh) {
	$result = $dbh->query("SELECT * FROM demo_test WHERE c1 IN ('mkey1', 'mkey2', 'mkey3')");
	foreach ($result as $row) {
		v('mysql: ' . $row['c2']);
	}
}
// Memcacheでデータを読み込み
function readByMemcache($memcache) {
	v('memcache: ' . $memcache->get('mkey1'));
	v('memcache: ' . $memcache->get('mkey2'));
	v('memcache: ' . $memcache->get('mkey3'));
}
// デバッグ用
function v($arg) {
	var_dump($arg);
}

結果

【テストケース1の実行結果】

$ php memcache_access.php
string(15) "REPEATABLE-READ"
string(17) "--- 更新前 ---"
string(13) "memcache: abc"
string(13) "memcache: def"
string(13) "memcache: ghi"
string(10) "mysql: abc"
string(10) "mysql: def"
string(10) "mysql: ghi"
string(30) "# テストケース1を実行"
string(15) "# Memcache::set"
string(17) "--- 更新後 ---"
string(13) "memcache: zzz"
string(13) "memcache: def"
string(13) "memcache: ghi"
string(10) "mysql: abc"
string(10) "mysql: def"
string(10) "mysql: ghi"
string(34) "--- MySQLロールバック後 ---"
string(13) "memcache: zzz"
string(13) "memcache: def"
string(13) "memcache: ghi"
string(10) "mysql: zzz"
string(10) "mysql: def"
string(10) "mysql: ghi"

【テストケース2の実行結果】

$ php memcache_access.php
string(15) "REPEATABLE-READ"
string(17) "--- 更新前 ---"
string(13) "memcache: abc"
string(13) "memcache: def"
string(13) "memcache: ghi"
string(10) "mysql: abc"
string(10) "mysql: def"
string(10) "mysql: ghi"
string(30) "# テストケース2を実行"
string(30) "# 行ロックを実行: mkey2"
string(37) "# UPDATE: 影響を与えた行数: 1"
string(17) "--- 更新後 ---"
string(13) "memcache: abc"
string(13) "memcache: yyy"
string(13) "memcache: ghi"
string(10) "mysql: abc"
string(10) "mysql: yyy"
string(10) "mysql: ghi"
string(34) "--- MySQLロールバック後 ---"
string(13) "memcache: abc"
string(13) "memcache: def"
string(13) "memcache: ghi"
string(10) "mysql: abc"
string(10) "mysql: def"
string(10) "mysql: ghi"

【テストケース3の実行結果】

$ php memcache_access.php
string(15) "REPEATABLE-READ"
string(17) "--- 更新前 ---"
string(13) "memcache: abc"
string(13) "memcache: def"
string(13) "memcache: ghi"
string(10) "mysql: abc"
string(10) "mysql: def"
string(10) "mysql: ghi"
string(30) "# テストケース3を実行"
string(30) "# 行ロックを実行: mkey3"
string(15) "# Memcache::set"
PHP Notice:  MemcachePool::set(): Server 192.168.3.254 (tcp 11211, udp 0) failed with:
 Network timeout (0) in memcache_access.php on line 71
string(17) "--- 更新後 ---"
string(10) "memcache: "
string(10) "memcache: "
string(10) "memcache: "
string(10) "mysql: abc"
string(10) "mysql: def"
string(10) "mysql: ghi"
string(34) "--- MySQLロールバック後 ---"
string(10) "memcache: "
string(10) "memcache: "
string(10) "memcache: "
string(10) "mysql: abc"
string(10) "mysql: def"
string(10) "mysql: ghi"

【まとめ】

  1. データ更新があってもMySQL側からは一貫した読み取りが行えた
  2. 参照に制限はかからない。またコミット前のデータが見えた。ロールバックしても挙動は同じ
  3. タイムアウトとして処理された。

1は想定内の挙動です。
2が致命的ですね。
どうやら分離レベルはREAD UNCOMMITTEDに相当するようです。
確かに、「このあたり」に分離レベルはREAD UNCOMMITTEDに設定しなさい、みたいな事が書かれていました。
3の挙動は興味深いですね。不整合は許されない箇所なので、

MemcachePool::set(): Server 192.168.3.254 (tcp 11211, udp 0) failed with: Network timeout (0)

のように、タイムアウトとして処理されるようです。面白い。

結論

設定値を変えて再検証しました。追記欄をご覧下さい。
あわよくばソーシャルゲーム案件のマスターDBの負荷軽減と高速化を目論んでいましたが、何も考えずに使うには難しいようです。
トラブル無く使うには、格納されているデータの内容や処理の流れを意識しながら使わなくてはならないでしょう。
スレイブDBの参照には使えそうですが、その用途であれば今もKVSなどである程度まかなえているでしょうから、それほどありがたみのある使い方とは言えないでしょう。
キャッシュの生存期間の判定が微妙なデータのアクセスに利用したり、いっそMemcachedなどのKVSは捨て、参照はMySQLへの一本化を行うことで、システムをシンプルにするという形では使えそうです。
（そうすると今度は実行コストの比較が必要ですね）
※勉強会の時間内で確認しただけで、深い検証はしていませんので、ツッコミなどありましたら遠慮なくお願いします。
【追記】
https://twitter.com/matsuu/status/341766721398521856
「innodb_api_trx_level」というパラメータがあるとのアドバイスをいただきましたので、変更して再検証してみたいと思います。
ありがとうございます。

再検証

my.cnfに「innodb_api_trx_level=2」のように加えて再検証してみました。
テストケース1と3の結果は変わらず。問題だった2番の結果を貼ります。
【テストケース2の再実行結果】

$ php memcache_access.php
string(15) "REPEATABLE-READ"
string(17) "--- 更新前 ---"
string(13) "memcache: abc"
string(13) "memcache: def"
string(13) "memcache: ghi"
string(10) "mysql: abc"
string(10) "mysql: def"
string(10) "mysql: ghi"
string(30) "# テストケース2を実行"
string(30) "# 行ロックを実行: mkey2"
string(37) "# UPDATE: 影響を与えた行数: 1"
string(17) "--- 更新後 ---"
string(13) "memcache: abc"
string(13) "memcache: def"
string(13) "memcache: ghi"
string(10) "mysql: abc"
string(10) "mysql: yyy"
string(10) "mysql: ghi"
string(34) "--- MySQLロールバック後 ---"
string(13) "memcache: abc"
string(13) "memcache: def"
string(13) "memcache: ghi"
string(10) "mysql: abc"
string(10) "mysql: def"
string(10) "mysql: ghi"

コミット前のデータが見えるようなことは無くなりました。素晴らしい。
【改めてまとめ】
デフォルトではREAD UNCOMITTEDだった挙動が変化しており、innodb_api_trx_levelの変更が利いているように見えます。
「こちらのドキュメントページ」によると、値は0～3で設定できるため、4つの分離レベル全てで動作するようです。
なお、上記のケースのように行ロックしたデータをMemcacheで読み込んでも、特に参照抑止などはかからないようです。
これは通常のトランザクション中のSELECTも同じ挙動なので特に違和感はありませんが、真に最新の値を取得するようなシビアな用途の場合は、SQLに任せる必要があるということになります。

改めて結論

innodb_api_trx_levelをセットすることで、普通に案件に使えるレベルとなりそうです。
（アドバイスをくださった@matsuuさんありがとうございます）
他にも、innodb_api_disable_rowlockやinnodb_api_enable_mdlなど気になる設定値がありましたので、折りをみて研究してみたいと思います。
MySQLのInnoDBの分離レベルのデフォルトはREPEATABLE READ、InnoDB Memcached PluginはREAD UNCOMITTED。
ご利用の際には、ここにご注意ください。

arrow_back 前の記事へ
一覧へ戻る
次の記事へ arrow_forward

ブログ記事検索

このブログについて

このブログは、札幌市・仙台市の「株式会社インフィニットループ」が運営する技術ブログです。 お仕事で使えるITネタを社員たちが発信します！

最新の記事

人気タグ

YouTube あいえるちゃんねる
広報ブログ