「ソーシャルアプリでNoSQL(あるいはKVS) 〜実践NoSQL〜」に行ってきた

2010/07/27 | Written by rust | under Blog

No Comments

ソーシャルアプリでNoSQL(あるいはKVS) 〜実践NoSQL〜

2010/07/23(金)
オプトさんの例のセミナー
GREEのCTOの人
ソーシャルアプリでNoSQL(KVS)の話
KVSの利用者は少ない？ 2/3ぐらい
副題 : 如何にして5キーに耐えるか
NoSQL = Not Only SQL

世にプロダクトはたくさんある

それぞれさまざまな特性がある(オンメモリ、分散、性能、実績などなど)

デファクトスタンダードがないので乱立？

それ MySQL(Drizzle) でできるよ

それでもいいんじゃないかと思ってます

NoSQL と言い出すまでの歴史

1998 年ぐらいから言われ始めたらしい
第一期 : RDBMS でいいよね期

最初はポスギレが多かった

第二期 : memcached も便利だよね期

まあセッションとかも使い始めたところ

第三期 : やっぱりスケーラビリティだよね期

mixi などでの分散とか
sharding

JOIN できない＞＜
トランザクションできない＞＜
複雑なSQL書いたら怒られた＞＜

RDBMS の機能を余り使っていない

Relationa じゃないよね

第四期 : 全部 RDBMS じゃなくてもいいよね期

たぶんいまここ

NoSQLのデータモデル

1 : 1

ハッシュテーブル
一番シンプル

kumofs / Flare / Voldemort とか
パフォーマンス / 安定性 / sharding / replication / Failover
サーバを追加すると自動で負荷が分散されたりする

メソッドとか

set / get / delete
add / replace / cas の違いとか

add -> INSERT
replace -> UPDATE
cas -> compare and swap (version 管理して意図しない上書きを抑制できる)

incr / decr -> increment / decrement
append /prepend

vector clock

consistency を確保するための仕組みらしい

何かしらの指標によって、replication の失敗を発見して、回復しようとする仕組み？

consistency policy
serialized object
multi get problem
disk i/o -> tmpfs

sparse file problem

実例

複数のデータを一気にとるときに大変

100パラメータ x 100ユーザ x 100アクセスとか

serialize する？

get は1回
並列すると conflict -> CAS

更新合戦になると大変 -> ある程度であきらめる

値でのランキングとか

要するに集計したい
全データを dump する

あんまりすぎる

適当なタイミングで RDBMS に flush

一番無難

集計系はリアルタイム性がなくても大丈夫だから

あとでやってみよう

だいたい in memory での運用

MMORPG とか

オンメモリでいろいろやって、レベルアップとかのタイミングでデータベースに flush するそうな

1 : n

Redis / Casandra

Redis

1 : 1
List
Set (Hash)
便利になるが、パフォーマンスリスクが高い
さらに table っぽく

Casandra

Keyspace.ColumnFamiry.Key.Column
多重Hashのような
バイナリログ + メモリテーブルのような感じ

m : n

Key の prefix search がしたい！

解決法？

Index Server
Ring w/ Sorted Key
Skip Graph ?

value で検索したい！

DataStore でできてるよ？

index をKVSに置いている

MongoDB

Power Play っぽい？

各ノードから検索してマージして持ってくる

RDBMS との使い分けも考えてみるのがいいと思っているらしい
Casandra / Redis の好きじゃないところ

自動分散ができない？
master/slaveにはできるがslaveに書き込めたりする

GREE では？

Flare(1:1)のものだけ

serializeに適切なフォーマットは？

GREEはPHPなので…
多言語ならMessagePackとかいいんじゃないでしょうか

はてなブックマーク - 「ソーシャルアプリでNoSQL(あるいはKVS) 〜実践NoSQL〜」に行ってきた

Livedoorクリップ - 「ソーシャルアプリでNoSQL(あるいはKVS) 〜実践NoSQL〜」に行ってきた

Tags: Casandra, MongoDB, MySQL, NoSQL, TokyoTyrant

InnoDBテーブルでインデックスを拡張するとパフォーマンスが落ちるかも from MySQL Performance Blog

2010/06/16 | Written by rust | under Blog

No Comments

Extending Index for Innodb tables can hurt performance in a surprising way

多くのキーを使うクエリーのときに、よく最適化するときにインデックスを拡張する。通常は問題なく、インデックスの長さが劇的に増加しない限り、インデックスを使うクエリーは新しいインデックスの先頭を使うことができる。では、そうではない場合を見てみよう。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE TABLE `idxitest` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`a` INT(11) NOT NULL,
`b` INT(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1

mysql> SELECT COUNT(*) FROM idxitest WHERE a=5 AND b=5;
+----------+
| COUNT(*) |
+----------+
| 60434 |
+----------+
1 ROW IN SET (0.69 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM idxitest WHERE a=5 AND b=5;
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxitest | REF | a | a | 4 | const | 707820 | USING WHERE |
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
1 ROW IN SET (0.00 sec)

さて、カラム(a)から(a, b)にインデックスを拡張することで、高速化して他に影響を与えない最適化ができる。いいかな？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

mysql> ALTER TABLE idxitest DROP KEY a,ADD KEY(a,b);
Query OK, 0 ROWS affected (24.84 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT COUNT(*) FROM idxitest WHERE a=5 AND b=5;
+----------+
| COUNT(*) |
+----------+
| 60434 |
+----------+
1 ROW IN SET (0.02 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM idxitest WHERE a=5 AND b=5;
+----+-------------+----------+------+---------------+------+---------+-------------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+------+---------------+------+---------+-------------+--------+-------------+
| 1 | SIMPLE | idxitest | REF | a | a | 8 | const,const | 120640 | USING INDEX |
+----+-------------+----------+------+---------------+------+---------+-------------+--------+-------------+
1 ROW IN SET (0.00 sec)

素晴らしい。クエリーは30倍も速くなった。スキャンする行が減っただけではなく、インデックスですべてまかなえるようになった。これだとデータにアクセスする必要がない。

でも喜ぶには早すぎる。ほとんど気がついていないようなそれまでは高速に実行されていたクエリーが他にもあった。しかしそれが遅くなるのだ。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

# 古いスキーマで実行

mysql> SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+---------+-----+---+
| id | a | b |
+---------+-----+---+
| 3000000 | 100 | 7 |
+---------+-----+---+
1 ROW IN SET (0.00 sec)

mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxitest | REF | a | a | 4 | const | 126074 | USING WHERE |
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
1 ROW IN SET (0.00 sec)

# 新しいスキーマで実行(インデックスを拡張)

mysql> SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+---------+-----+---+
| id | a | b |
+---------+-----+---+
| 3000000 | 100 | 7 |
+---------+-----+---+
1 ROW IN SET (1.01 sec)

mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | idxitest | INDEX | a | PRIMARY | 4 | NULL | 36 | USING WHERE |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 ROW IN SET (0.00 sec)

# 実行計画は以下のようになることもある

mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+----+-------------+----------+------+---------------+------+---------+-------+------+------------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+------+---------------+------+---------+-------+------+------------------------------------------+
| 1 | SIMPLE | idxitest | REF | a | a | 4 | const | 1 | USING WHERE; USING INDEX; USING filesort |
+----+-------------+----------+------+---------------+------+---------+-------+------+------------------------------------------+
1 ROW IN SET (0.01 sec)

なぜこんなに遅くなってしまうのか。理由はInnoDBの特性に有利な実行計画にある。つまり、インデックスが各キーの値に対する主キーでソートされているということ。なのでインデックス(a)があってidが主キーであるとき、実際のインデックスは(a, id)となり、拡張されたインデックスが(a, b)であるときには実際のインデックスは(a, b, id)になるということ。なので、元にインデックスで(a, id)を使っていたようなクエリーでは、新しいインデックスを使うことができない。

じゃあどうすれば？こんなときは「冗長」なインデックスを(a)と(a, b)に同時に張ることができる。だが、これをちゃんと動くと仮定する仮定しがちだが、しばしば動作しない。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE `idxitest` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`a` INT(11) NOT NULL,
`b` INT(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `a_2` (`a`,`b`)
) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1

mysql> SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+---------+-----+---+
| id | a | b |
+---------+-----+---+
| 3000000 | 100 | 7 |
+---------+-----+---+
1 ROW IN SET (1.03 sec)

mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | idxitest | INDEX | a,a_2 | PRIMARY | 4 | NULL | 2247 | USING WHERE |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 ROW IN SET (0.00 sec)

MySQLのオプティマイザーは(a)と(a, b)のインデックスの利用を考えた後、最後にどちらも使わずにフルインデックススキャンすることにしたようだ。(a)のインデックスを使えば1行だけでいいことが保証されるのに、2247行もスキャンするような計画を見積もったこのケースでは、オプティマイザーの誤動作のように見える。

正しい計画をMySQLオプティマイザーに使わせるためには、FORCE INDEX(a)を使う必要がある。

これは冗長なインデックスを調べるツールであるmk-duplicate-key-checkerの結果から、インデックスを変更するときには十分気をつけなければならないことを意味する。インデックスの中で主キーによってデータがソートされているInnoDBに依存するようなクエリーがあるのなら、変更が重大な影響を及ぼすことになるからだ。

オプティマイザーの振る舞いはMySQLのバージョンによって異なる。今回は5.1.45で行ったが、5.0でも同じ振る舞いだったことを記しておく。

はてなブックマーク - InnoDBテーブルでインデックスを拡張するとパフォーマンスが落ちるかも from MySQL Performance Blog

Livedoorクリップ - InnoDBテーブルでインデックスを拡張するとパフォーマンスが落ちるかも from MySQL Performance Blog

Tags: MySQL

JOIN使うときに気をつけること from MySQL Performance Blog

| Written by rust | under Blog

No Comments

Tweetいつものように気になったので意訳

Joining on range? Wrong! | MySQL Performance Blog

これら書く問題というのはMySQLの最初の方にある問題なんだが、気をつけてクエリーを解析しないと知らないまま終わってしまうことだ。phpDay.it でこの落とし穴についてデモしてきたが、ブログの記事にするのもいいとおもったんだ。

問題をデモするための典型的な例として、売上げクエリーを考えよう。データは次の3つのテーブルに入っている。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE `products` (
`prd_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`prd_name` VARCHAR(32) NOT NULL,
PRIMARY KEY (`prd_id`),
KEY `name` (`prd_name`)
)

CREATE TABLE `tags` (
`tag_prd_id` INT(10) UNSIGNED NOT NULL,
`tag_name` VARCHAR(32) NOT NULL,
PRIMARY KEY (`tag_name`, `tag_prd_id`)
)

CREATE TABLE `items_ordered` (
`itm_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`itm_prd_id` INT(10) UNSIGNED NOT NULL,
`itm_order_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`itm_id`),
KEY `itm_prd_id__and__itm_order_timestamp` (`itm_prd_id`,`itm_order_timestamp`)
)

そして十分な量のデータを入れた。

そして次のクエリーを考える。昨日にどれだけ液晶テレビが売れたのかを調べるクエリーだ。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT COUNT(1)
FROM tags t
JOIN products p
ON p.prd_id = t.tag_prd_id
JOIN items_ordered i
ON i.itm_prd_id = p.prd_id
WHERE t.tag_name = 'lcd'
AND i.itm_order_timestamp>= '2010-05-16 00:00:00'
AND i.itm_order_timestamp <'2010-05-17 00:00:00'
+----------+
| COUNT(1) |
+----------+
| 4103 |
+----------+

ちゃんとできたかな！

データ構造を見る限り、うまくいったように見える。tagsテーブルにはインデックスtag_nameがあり、items_orderedには(itm_prd_id, itm_order_timestamp)などが結合時に使われている。ではさらに詳細に見てみるとどうだろう。

1
2
3
4
5
6
7
8
9
10
11
12

SHOW STATUS LIKE 'Handler_read%';

+-----------------------+--------+
| Variable_name | VALUE |
+-----------------------+--------+
| Handler_read_first | 0 |
| Handler_read_key | 3 |
| Handler_read_next | 118181 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+--------+

ちょっとよくないように見える。結果が4103行なのに、120000行もスキャンしている。必要なカラムにはちゃんとインデックスつけているのに！EXPLAINはどうなっているのか。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

*************************** 1. ROW ***************************
id: 1
select_type: SIMPLE
TABLE: t
TYPE: REF
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 98
REF: const
ROWS: 1
Extra: USING WHERE; USING INDEX
*************************** 2. ROW ***************************
id: 1
select_type: SIMPLE
TABLE: p
TYPE: eq_ref
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 4
REF: example_db.t.tag_prd_id
ROWS: 1
Extra: USING INDEX
*************************** 3. ROW ***************************
id: 1
select_type: SIMPLE
TABLE: i
TYPE: REF
possible_keys: itm_prd_id__and__itm_order_timestamp
KEY: itm_prd_id__and__itm_order_timestamp
key_len: 4
REF: example_db.p.prd_id
ROWS: 10325
Extra: USING WHERE; USING INDEX

ここでちょっと思い出してみる。

1
2
3

`itm_prd_id` INT(10) UNSIGNED NOT NULL
`itm_order_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
KEY `itm_prd_id__and__itm_order_timestamp` (`itm_prd_id`,`itm_order_timestamp`)

EXPLAINの3行目のkey_lenは4バイトなのだが、(itm_prd_id, itm_order_timestamp)は4+4で8バイトあるはずだ。と言うことは、1つのカラムしか見ていないことになる。

これをどう理解すべきか。データベースはlcdとタグの付いたソートされたデータを全て読み込む。それはやく120000行で、そのあと日付で絞り込まれる。なんと非効率な方法だ！MySQLは製品IDと日付範囲をインデックスでマッチして関連する行を読み込むだけな単純な最適化もできない。

これは結合するときだけ影響する。範囲条件を最初のテーブル(そしてそれのみ)に限定すれば、期待通りに動く。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

EXPLAIN
SELECT COUNT(1)
FROM items_ordered i
WHERE i.itm_prd_id = 5
AND i.itm_order_timestamp>= '2010-05-16 00:00:00'
AND i.itm_order_timestamp <'2010-05-17 00:00:00'

*************************** 1. ROW ***************************
id: 1
select_type: SIMPLE
TABLE: i
TYPE: range
possible_keys: itm_prd_id__and__itm_order_timestamp
KEY: itm_prd_id__and__itm_order_timestamp
key_len: 8
REF: NULL
ROWS: 1306
Extra: USING WHERE; USING INDEX

この場合、結合しないのでMySQLはrefとは表示せず、key_lenも8バイトとなっている。つまりちゃんとインデックスが利用されていると言うことだ。

この問題に対しては多くの回避策があるが、基本的に状況に依存する。本質的には、結合から範囲条件を外すことになる。今回の場合は、日付(DATA)のカラムを追加して、それで絞り込むことにすればよい。

1 2	ALTER TABLE items_ordered ADD itm_order_date DATE NOT NULL, ADD INDEX itm_prd_id__and__itm_order_date (itm_prd_id, itm_order_date); UPDATE items_ordered SET itm_order_date = DATE(itm_order_timestamp);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

EXPLAIN
SELECT COUNT(1)
FROM tags t
JOIN products p
ON p.prd_id = t.tag_prd_id
JOIN items_ordered i
ON i.itm_prd_id = p.prd_id
WHERE t.tag_name = 'lcd'
AND i.itm_order_date = '2010-05-16'

*************************** 1. ROW ***************************
id: 1
select_type: SIMPLE
TABLE: t
TYPE: REF
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 98
REF: const
ROWS: 1
Extra: USING WHERE; USING INDEX
*************************** 2. ROW ***************************
id: 1
select_type: SIMPLE
TABLE: p
TYPE: eq_ref
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 4
REF: example_db.t.tag_prd_id
ROWS: 1
Extra: USING INDEX
*************************** 3. ROW ***************************
id: 1
select_type: SIMPLE
TABLE: i
TYPE: REF
possible_keys: itm_prd_id__and__itm_order_timestamp,itm_prd_id__and__itm_order_date
KEY: itm_prd_id__and__itm_order_date
key_len: 7
REF: example_db.p.prd_id,const
ROWS: 206494
Extra: USING WHERE; USING INDEX

このクエリーはitm_prd_id__and__itm_order_dateで7バイトとなっているので、(itm_prd_id, itm_order_date)をちゃんと利用していることが分かる。またrefでも2つのカラムを結合で利用していることが分かる。

1
2
3
4
5
6
7
8
9
10
11

SHOW STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | VALUE |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 3 |
| Handler_read_next | 4104 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+

非常によくなったことが分かるだろう。

しかし、クエリーが異なれば、解決方法も異なることは覚えておいて欲しい。

この問題に対するバグレポートがいくつかある(例えば #8569, #19548)が、MySQLからの回答では6.0かそれ以降のバージョンで解決されると言うことだ。または「それは解説されている振る舞いである」と言うことらしい。でも実際には仕様ではなく致命的なバグであり、修正が必要だ。

はてなブックマーク - JOIN使うときに気をつけること from MySQL Performance Blog

Livedoorクリップ - JOIN使うときに気をつけること from MySQL Performance Blog

Tags: MySQL

RubyのMySQLドライバを見る – アクティブチェック問題

2010/05/13 | Written by rust | under Blog

No Comments

Tweet前々回、前回の続きです。次はMySQLドライバを調べてみます。

ActiveRecordからRuby/MySQLへ

さてアクティブチェックしては、connection_adaptors/mysql_adaptor.rbのactive?メソッドでした。

275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290

def active?
if @connection.respond_to?(:stat)
@connection.stat
else
@connection.query 'select 1'
end

# mysql-ruby doesn't raise an exception when stat fails.
if @connection.respond_to?(:errno)
@connection.errno.zero?
else
true
end
rescue Mysql::Error
false
end

ここでは、@connection.statか@connection.query 'select 1'が呼び出されます。その前に、まず@connectionとはなにかと言うと、親クラスであるAbstractAdapterの38行目にあるinitializeメソッドで作成されるインスタンス変数です。ファイルはconnection_adaptors/abstract_adaptor.rbです。

38
39
40
41
42
43

def initialize(connection, logger = nil) #:nodoc:
@connection, @logger = connection, logger
@runtime = 0
@last_verification = 0
@query_cache_enabled = false
end

このメソッドはconnection_adaptors/mysql_adaptor.rbの200行目で呼び出されるわけです。このクラスは同じファイルの75行目でインスタンスを生成しています。

48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

# Establishes a connection to the database that's used by all Active Record objects.
def self.mysql_connection(config) # :nodoc:
config = config.symbolize_keys
host = config[:host]
port = config[:port]
socket = config[:socket]
username = config[:username] ? config[:username].to_s : 'root'
password = config[:password].to_s
database = config[:database]

# Require the MySQL driver and define Mysql::Result.all_hashes
unless defined? Mysql
begin
require_library_or_gem('mysql')
rescue LoadError
$stderr.puts '!!! The bundled mysql.rb driver has been removed from Rails 2.2. Please install the mysql gem and try again: gem install mysql.'
raise
end
end

MysqlCompat.define_all_hashes_method!

mysql = Mysql.init
mysql.ssl_set(config[:sslkey], config[:sslcert], config[:sslca], config[:sslcapath], config[:sslcipher]) if config[:sslca] || config[:sslkey]

default_flags = Mysql.const_defined?(:CLIENT_MULTI_RESULTS) ? Mysql::CLIENT_MULTI_RESULTS : 0
options = [host, username, password, database, port, socket, default_flags]
ConnectionAdapters::MysqlAdapter.new(mysql, logger, options, config)
end
end

この引数mysqlはその前のMySQL.initで生成されています。このMySQLはRubyのMySQLドライバであるMySQL/Rubyのことです。

MySQL/Rubyを見てみる

さてこのMySQL/Rubyですが、拡張ライブラリなのでC言語で書かれています。また内部的にはMySQL APIを呼び出しているので、適宜両方見ていきます。

まず、statメソッドがあればそれが呼ばれるので、そちらを調べると、ext/mysql_api/mysql.cの1978行目にstatメソッドの定義があります。

1	rb_define_method(cMysql, "stat", my_stat, 0);

細かいことは省きますが、MySQL#statが呼ばれるとmy_statが実行されます。ではそのmy_statは693行目にありました。

693
694
695
696
697
698
699
700
701

/* stat() */
static VALUE my_stat(VALUE obj)
{
MYSQL* m = GetHandler(obj);
const char* s = mysql_stat(m);
if (s == NULL)
mysql_raise(m);
return rb_tainted_str_new2(s);
}

ここでGetHandlerはマクロで、構造体の中にある接続ハンドラを取得しています。で、mysql_statという関数を実行して結果を判定しているわけですね。ではこのmysql_statはと言うと、MySQL 5.0のリファレンスマニュアルには「mysqladmin statusと同じ結果を返す」とだけ書かれています。

MySQLのソースを読む

あまりC/C++をやった経験がないのですが、とりあえず調査続行と言うことでMySQL本体のソースを追ってみます。まずはダウンロードして展開すると、libmysql/libmysql.cと言うファイルが見つかります。前述のは、いわゆる共有ライブラリ経由での接続なので、ここを見ればいいと思われます。このファイルの1407行目にmysql_statがありました。

1407
1408
1409
1410
1411
1412
1413
1414

const char * STDCALL
mysql_stat(MYSQL *mysql)
{
DBUG_ENTER("mysql_stat");
if (simple_command(mysql,COM_STATISTICS,0,0,0))
DBUG_RETURN(mysql->net.last_error);
DBUG_RETURN((*mysql->methods->read_statistics)(mysql));
}

どうやらsimple_commandを呼び出しているだけのようです。simple_commandはinclude/mysql.hの852行目に定義されているマクロです。

852
853
854

#define simple_command(mysql, command, arg, length, skip_check) \
(*(mysql)->methods->advanced_command)(mysql, command, 0, \
0, arg, length, skip_check, NULL)

mysql経由でadvanced_commandを呼び出しています。mysqlはinclude/mysql.hの258行目で定義されている構造体です。

258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313

typedef struct st_mysql
{
NET net; /* Communication parameters */
unsigned char *connector_fd; /* ConnectorFd for SSL */
char *host,*user,*passwd,*unix_socket,*server_version,*host_info;
char *info, *db;
struct charset_info_st *charset;
MYSQL_FIELD *fields;
MEM_ROOT field_alloc;
my_ulonglong affected_rows;
my_ulonglong insert_id; /* id if insert on table with NEXTNR */
my_ulonglong extra_info; /* Not used */
unsigned long thread_id; /* Id for connection in server */
unsigned long packet_length;
unsigned int port;
unsigned long client_flag,server_capabilities;
unsigned int protocol_version;
unsigned int field_count;
unsigned int server_status;
unsigned int server_language;
unsigned int warning_count;
struct st_mysql_options options;
enum mysql_status status;
my_bool free_me; /* If free in mysql_close */
my_bool reconnect; /* set to 1 if automatic reconnect */

/* session-wide random string */
char scramble[SCRAMBLE_LENGTH+1];

/*
Set if this is the original connection, not a master or a slave we have
added though mysql_rpl_probe() or mysql_set_master()/ mysql_add_slave()
*/
my_bool rpl_pivot;
/*
Pointers to the master, and the next slave connections, points to
itself if lone connection.
*/
struct st_mysql* master, *next_slave;

struct st_mysql* last_used_slave; /* needed for round-robin slave pick */
/* needed for send/read/store/use result to work correctly with replication */
struct st_mysql* last_used_con;

LIST *stmts; /* list of all statements */
const struct st_mysql_methods *methods;
void *thd;
/*
Points to boolean flag in MYSQL_RES or MYSQL_STMT. We set this flag
from mysql_stmt_close if close had to cancel result set of this object.
*/
my_bool *unbuffered_fetch_owner;
/* needed for embedded server - no net buffer to store the 'info' */
char *info_buffer;
void *extension;
} MYSQL;

この中のmethodsはst_mysql_methodsと言う構造体で、753行目に定義されています。

753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782

typedef struct st_mysql_methods
{
my_bool (*read_query_result)(MYSQL *mysql);
my_bool (*advanced_command)(MYSQL *mysql,
enum enum_server_command command,
const unsigned char *header,
unsigned long header_length,
const unsigned char *arg,
unsigned long arg_length,
my_bool skip_check,
MYSQL_STMT *stmt);
MYSQL_DATA *(*read_rows)(MYSQL *mysql,MYSQL_FIELD *mysql_fields,
unsigned int fields);
MYSQL_RES * (*use_result)(MYSQL *mysql);
void (*fetch_lengths)(unsigned long *to,
MYSQL_ROW column, unsigned int field_count);
void (*flush_use_result)(MYSQL *mysql);
#if !defined(MYSQL_SERVER) || defined(EMBEDDED_LIBRARY)
MYSQL_FIELD * (*list_fields)(MYSQL *mysql);
my_bool (*read_prepare_result)(MYSQL *mysql, MYSQL_STMT *stmt);
int (*stmt_execute)(MYSQL_STMT *stmt);
int (*read_binary_rows)(MYSQL_STMT *stmt);
int (*unbuffered_fetch)(MYSQL *mysql, char **row);
void (*free_embedded_thd)(MYSQL *mysql);
const char *(*read_statistics)(MYSQL *mysql);
my_bool (*next_result)(MYSQL *mysql);
int (*read_change_user_result)(MYSQL *mysql, char *buff, const char *passwd);
int (*read_rows_from_cursor)(MYSQL_STMT *stmt);
#endif
} MYSQL_METHODS;

だんだん長くなってきました。さてこれはMYSQL_METHODS型なのですが、定義しかありません。実体はと言うと、sql-common/client.cの1885行目で代入されている、client_methodsです。

1885
1886
1887
1888
1889

/* Don't give sigpipe errors if the client doesn't want them */
set_sigpipe(mysql);
mysql->methods= &client_methods;
net->vio = 0; /* If something goes wrong */
mysql->client_flag=0; /* For handshake */

で、このclient_methodsは1763行目で定義されています。

1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783

static MYSQL_METHODS client_methods=
{
cli_read_query_result, /* read_query_result */
cli_advanced_command, /* advanced_command */
cli_read_rows, /* read_rows */
cli_use_result, /* use_result */
cli_fetch_lengths, /* fetch_lengths */
cli_flush_use_result /* flush_use_result */
#ifndef MYSQL_SERVER
,cli_list_fields, /* list_fields */
cli_read_prepare_result, /* read_prepare_result */
cli_stmt_execute, /* stmt_execute */
cli_read_binary_rows, /* read_binary_rows */
cli_unbuffered_fetch, /* unbuffered_fetch */
NULL, /* free_embedded_thd */
cli_read_statistics, /* read_statistics */
cli_read_query_result, /* next_result */
cli_read_change_user_result, /* read_change_user_result */
cli_read_binary_rows /* read_rows_from_cursor */
#endif
};

もうちょい続きそうです。で、ここにあるcli_advanced_commandはと言うと、764行目にありました。

1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829

my_bool
cli_advanced_command(MYSQL *mysql, enum enum_server_command command,
const uchar *header, ulong header_length,
const uchar *arg, ulong arg_length, my_bool skip_check,
MYSQL_STMT *stmt)
{
NET *net= &mysql->net;
my_bool result= 1;
init_sigpipe_variables
my_bool stmt_skip= stmt ? stmt->state != MYSQL_STMT_INIT_DONE : FALSE;
DBUG_ENTER("cli_advanced_command");

/* Don't give sigpipe errors if the client doesn't want them */
set_sigpipe(mysql);

if (mysql->net.vio == 0)
{ /* Do reconnect if possible */
if (mysql_reconnect(mysql) || stmt_skip)
DBUG_RETURN(1);
}
if (mysql->status != MYSQL_STATUS_READY ||
mysql->server_status & SERVER_MORE_RESULTS_EXISTS)
{
DBUG_PRINT("error",("state: %d", mysql->status));
set_mysql_error(mysql, CR_COMMANDS_OUT_OF_SYNC, unknown_sqlstate);
DBUG_RETURN(1);
}

net_clear_error(net);
mysql->info=0;
mysql->affected_rows= ~(my_ulonglong) 0;
/*
We don't want to clear the protocol buffer on COM_QUIT, because if
the previous command was a shutdown command, we may have the
response for the COM_QUIT already in the communication buffer
*/
net_clear(&mysql->net, (command != COM_QUIT));

if (net_write_command(net,(uchar) command, header, header_length,
arg, arg_length))
{
DBUG_PRINT("error",("Can't send command to server. Error: %d",
socket_errno));
if (net->last_errno == ER_NET_PACKET_TOO_LARGE)
{
set_mysql_error(mysql, CR_NET_PACKET_TOO_LARGE, unknown_sqlstate);
goto end;
}
end_server(mysql);
if (mysql_reconnect(mysql) || stmt_skip)
goto end;
if (net_write_command(net,(uchar) command, header, header_length,
arg, arg_length))
{
set_mysql_error(mysql, CR_SERVER_GONE_ERROR, unknown_sqlstate);
goto end;
}
}
result=0;
if (!skip_check)
result= ((mysql->packet_length=cli_safe_read(mysql)) == packet_error ?
1 : 0);
end:
reset_sigpipe(mysql);
DBUG_PRINT("exit",("result: %d", result));
DBUG_RETURN(result);
}

だんだん面倒になってきたので、これぐらいにしておいて上から順に見ていくと、いろいろ初期化して何らかの事情で切断されたことを知っていれば(mysql->net.vio == 0)再接続。続いて準備をしてコマンド(COM_STATISTICS)送信(net_write_command)し、結果を返して終わる。

まとめ

長くなりましたが、MySQL/RubyのstatメソッドではCOM_STATISTICSを送信していると言うことがわかりました。これは統計情報を取得しようとしていると言うことで、件の負荷上昇に原因となります。ただ、じゃあ外せばいいかというと、Rails + MySQL (+ Mongrel?) でDB接続の通信が無い状態が続くとデッドロックする。 – こせきの技術日記と言うこともあるのでそう簡単ではないようです。今回の場合はRubyではTCP接続をしていないのでMongrelの問題は発生しませんが、負荷軽減を目論んでアクティブチェックを外す場合には、フレームワークやドライバまでちゃんと調べた方が良さそうだと感じました。

はてなブックマーク - RubyのMySQLドライバを見る – アクティブチェック問題

Livedoorクリップ - RubyのMySQLドライバを見る – アクティブチェック問題

Tags: MySQL, Rails, Ruby, Ruby on Rails, ネットワーク

ActiveRecordでの接続チェックはどこでやっているのか

2010/05/11 | Written by rust | under Blog

No Comments

Tweet前回の話を受けて、Railsの場合はどうなのかを調べてみたいと思います。ターゲットはRails 2.3.5です。

ActiveRecordに潜る

RailsのO/R MapperはActiveRecordです。今回はActiveRecordでMySQLの場合について調べます。

まずはアクティブチェックしている部分を探します。ざっと見ると、connection_adaptors/mysql_adaptor.rbの275行目にactive?メソッドがありました。

275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290

def active?
if @connection.respond_to?(:stat)
@connection.stat
else
@connection.query 'select 1'
end

# mysql-ruby doesn't raise an exception when stat fails.
if @connection.respond_to?(:errno)
@connection.errno.zero?
else
true
end
rescue Mysql::Error
false
end

うぉ、SELECT 1とかそのままじゃないか。とりあえず次はこれを呼び出しているところを探します。するとconnection_adaptors/abstract_adaptor.rbの149行目にverify!(*ignored)メソッドがありました。

147
148
149
150
151
152

# Checks whether the connection to the database is still active (i.e. not stale).
# This is done under the hood by calling <tt>active?</tt>. If the connection
# is no longer active, then this method will reconnect to the database.
def verify!(*ignored)
reconnect! unless active?
end

アクティブでなければ再接続するようです。ではこのメソッドはどこで呼ばれているのかというと、connection_adaptors/abstract/connection_pool.rbの255行目にあるcheckout_and_verifyメソッドです。

255
256
257
258
259
260

def checkout_and_verify(c)
c.verify!
c.run_callbacks :checkout
@checked_out << c
c
end

ここで接続確認して接続を取得、それをインスタンス変数@checked_outに保存しているわけです。まだ続きますが、ではこのメソッドはと言うと、すぐ上にあるchechout_new_connectionとcheckout_existing_connectionメソッドで呼び出されています。

244
245
246
247
248
249
250
251
252
253

def checkout_new_connection
c = new_connection
@connections << c
checkout_and_verify(c)
end

def checkout_existing_connection
c = (@connections - @checked_out).first
checkout_and_verify(c)
end

まだ続きそうです。では、これはと言うと同じファイルの181行目にあるcheckoutメソッドで呼び出されているようです。

181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203

def checkout
# Checkout an available connection
@connection_mutex.synchronize do
loop do
conn = if @checked_out.size < @connections.size
checkout_existing_connection
elsif @connections.size < @size
checkout_new_connection
end
return conn if conn
# No connections available; wait for one
if @queue.wait(@timeout)
next
else
# try looting dead threads
clear_stale_cached_connections!
if @size == @checked_out.size
raise ConnectionTimeoutError, "could not obtain a database connection#{" within #{@timeout} seconds" if @timeout}. The max pool size is currently #{@size}; consider increasing it."
end
end
end
end
end

さてさて、ではこのメソッドはどこから呼ばれるかというと、同じファイルにあるconnectionとwith_connectionメソッドのようです。

89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117

# Retrieve the connection associated with the current thread, or call
# #checkout to obtain one if necessary.
#
# #connection can be called any number of times; the connection is
# held in a hash keyed by the thread id.
def connection
if conn = @reserved_connections[current_connection_id]
conn
else
@reserved_connections[current_connection_id] = checkout
end
end

# Signal that the thread is finished with the current connection.
# #release_connection releases the connection-thread association
# and returns the connection to the pool.
def release_connection
conn = @reserved_connections.delete(current_connection_id)
checkin conn if conn
end

# Reserve a connection, and yield it to a block. Ensure the connection is
# checked back in when finished.
def with_connection
conn = checkout
yield conn
ensure
checkin conn
end

とまあだいたいこんな感じで、ActiveRecord::Base.connection.execute("SELECT * FROM users")が実行されるときには、active?メソッドが呼び出されているわけです。実際に、例えばp "Active Check!"なんてのを仕込むと毎回表示されます。これはproduction環境でも変わりませんでした。

さて次回はactive?のところでは実際は何が呼び出されるのかを調べるために、MySQLドライバを見てみたいと思います。

はてなブックマーク - ActiveRecordでの接続チェックはどこでやっているのか

Livedoorクリップ - ActiveRecordでの接続チェックはどこでやっているのか

Tags: MySQL, Rails, Ruby, Ruby on Rails, ネットワーク

データベースアクティブチェックは負荷を上昇させるだけ

2010/05/07 | Written by rust | under Blog

No Comments

Tweet例によって気になったので意訳。

Checking for a live database connection considered harmful on MySQL Performance Blog

顧客のデータベースでよく見かけて注意するのだが、クエリーを送信する前にデータベース接続がアクティブかどうかをチェックするのは大きなオーバーヘッドになる。これは、次のような擬似コードで書かれるデザインパターンに由来する。

1
2
3
4
5
6

function query_database(connection, sql)
if !connection.is_alive() and !connection.reconnect() then
throw exception
end
return connection.execute(sql)
end

多くの開発環境やフレームワークで、こういうコードになっている。これには、実際には期待したとおりには動かないと言うことと、大きなパフォーマンスのオーバーヘッドがあると言う２点で間違っている。
２つの間違いがあり

実はちゃんと動かない

このコードはレースコンディション(競合状態)によって動作しない。もしチェックしたときに接続がアクティブだとしても、connection.execute(sql)を実行するときにアクティブだとは保証されない。さらにもし非アクティブで再接続したとしても、同様にアクティブであるとは保証されない。

チェックして実行するのは実用的ではない。代わりに、次のように書き換えるべきだ。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

function query_database(connection, sql, retries=1)
while true
try
result=connection.execute(sql)
return result
catch InactiveConnectionException e
if retries> 0 then
retries = retries - 1
connection.reconnect()
else
throw e
end
end
end
end

is_active()が無くなったのに気付いただろうか。接続がアクティブであればクエリーが実行され、そうでなければ失敗して再接続し、再度実行しようとする。

このコードだと、必要に応じてロック待ちのタイムアウトやデッドロックのときに再試行することができるようになっている。私の経験上、多くのアプリケーションで有効である。ほとんどのアプリケーションでは、こう言うときには単純に再試行するだけで、ちゃんと扱おうとはしていない。

パフォーマンスのオーバーヘッド

アクティブチェックには大抵の場合、MySQLのプロトコルレベルのコマンドであるpingかstatisticsの呼び出しか、SELECT 1のような自明なクエリーが実行される。前者のコマンドはSHOW GLOBAL STATUSで表示されるCom_admin_commandsをインクリメントし、後者のクエリーは診断を難しくする。これは多くのアプリケーションで非常に高コストとなる。ここには２つのコストがある。１つはネットワーク通信とクエリー実行時間のアプリケーションへのコストで、もう１つはデータベースサーバの負荷上昇。このデータベースサーバへの負荷はかなり大きい。何日か前に、「管理コマンドのstatistics」を使うRuby on Railsアプリケーションを見たが、全クエリー実行時間の40%がこのコマンドだった。この不必要な接続チェックを削除したところ、データベースの負荷が半分程度に削減できた。これは普通じゃない！

アプリケーションのクエリーが長いとき、追加のクエリーはノイズの中で消えてしまう。しかし高トラフィックアプリケーションはクエリー実行時間を短くするのに途方もない努力を費やし、いくつかのチューニングしたアプリケーションでは、クエリーの実行時間がミリ秒より長くならないかドキドキしている。もしデータベースで毎秒20,000クエリー走っているなら、コネクションチェックも毎秒20,000回行われていることになる。これらpingやstatisticsと言うクエリーは、アプリケーションで実行すべきクエリーと同じぐらいコストがかかっているんだ。

これはデータベースサーバへの負荷であった。アプリケーション側では、クエリー実行時間の２倍の遅延があるのがわかるだろう。クエリーを実行するときには、そのアプリケーションフレームワークがチェックのためにネットワーク通信をして、さらに別のネットワーク通信でクエリーを実行する。これもやっぱり問題だ。

問題なのは、さっきの擬似コードがレアケースに気を取られて一般的な場合にペナルティーを課しているところ。普通は接続は生きていて、確認したり再接続したりはしなくてもいい。良い方法は、レースコンディションを解決したコードを使うことだ。もし接続が切れていても、クエリーを実行するときに探せばいいだけだ。そのときまでは、全てがOKで、クエリーを実行できる。

問題となるライブラリのアップストリームメンテナが、この問題を見つけ出して解決することを願っている。アプリケーションが成長するときの大きな手助けになるからだ。ラボではうまくいっていて、現場でもそうであったとしても、パフォーマンスはすぐに問題となる。そしてそれはすごく目立つんだ。

追記

この馬鹿げた結果を見て欲しい。

1
2
3
4
5
6
7
8
9

# Rank Query ID Response time Calls R/Call Item
# ==== ================== ================ ====== ======== ===============
# 1 0x5E796D5A4A7D1CA9 10651.0708 73.1% 120487 0.0884 ADMIN STATISTICS
# 2 0x85FFF5AA78E5FF6A 1090.0772 7.5% 23621 0.0461 BEGIN
# 3 0x6E85B9A9C9FF813E 868.0335 6.0% 6923 0.1254 UPDATE scores
# 4 0xA3A0423749EC0E37 851.0152 5.8% 6020 0.1414 UPDATE user_datas
# 5 0x813031B8BBC3B329 822.0041 5.6% 23299 0.0353 COMMIT
# 6 0xA873BBC4583C4C85 278.4533 1.9% 6985 0.0399 SELECT users user_devices
That's right, 73% of the server's load is consumed by checking to see if the connection is still alive

まとめ

これが本当だとすると、Rubyで使われているMySQLライブラリや、RailsのMySQLアダプタとかをちゃんと調べないとやばいんじゃないか？と言うわけで調査にはいります。

Livedoorクリップ - データベースアクティブチェックは負荷を上昇させるだけ

Tags: MySQL, Rails, Ruby, Ruby on Rails, ネットワーク

FLUSH TABLES WITH READ LOCKの速度について

2010/04/26 | Written by rust | under Blog

No Comments

Tweet気になったので、How fast is FLUSH TABLES WITH READ LOCK?の意訳。

FLUSH TABLES WITH READ LOCKってどれだけ時間がかかる？

ちょっとまえのMySQLカンファレンスでバックアップソフトのベンダーのとこに行ったんだ。で、バックアップソフトについて話をし始めたんだけど、それはデータベース全体のロックを取得する、FLUSH TABLES WITH READ LOCKを使ってるということだった。でも彼は、ロックは「数ミリ秒」しかかからないと誇らしげに語ってた。バックアップソフトベンダーはそう思っているようだが、これは大きな勘違いだ。

実際にはこのコマンドがロックする時間はわからない。研究室のテスト環境ではそりゃミリ秒で終わるかも知れないが、実際にはもっと時間がかかる。数分から下手すれば数時間もかかることがあるだろう。そしてこの間、サーバは完全にロックされる(readonlyでもないんだ！)。なぜそうなのか、このコマンドが何をしてるか見てみよう。このコマンドには、いくつか重要な処理が含まれている。

ロックのリクエスト

FLUSH TABLES WITH READ LOCKコマンドは、すぐにグローバルロックを要求する。すると、そのロックが許可される前に、システムの中で動作している全てのプロセスがロックアウトされる。理論上、結局は読み出しのロックを取得するだけなのだから、このことは悪いようには見えない。他の読み出しロックだけが必要なコマンドとは、共存できるはずだ。

でも実際には、多くのテーブルは読み書きされている。最初の書き込みクエリーがグローバルロックによってブロックされると、それに続く読み出しクエリーは、その前の書き込みクエリーが要求したテーブルロックによってブロックされる。結局、実質的にテーブルは排他ロックされてしまい、新しいリクエストは全てブロックされてしまう。読み出しのクエリーでさえも！

ロックを待つ

FLUSH TABLES WITH READ LOCKがロックを取得する前に、ロックを持っている実行中のものは全て終わらなければならない。要するに、SELECTも含めて、全てのクエリーが終わる必要がある。もしすごい時間のかかるクエリーが動いていたり、テーブルロックするトランザクションがあったりすると、FLUSH TABLES WITH READ LOCKは全ての処理が終わってロックが解放されるまでブロックされることになる。これには結構な時間がかかるんじゃないかと思う。僕にとっては顧客のサーバにログインして走ってるクエリーを見ることなんて珍しくないこと。FLUSH TABLES WITH READ LOCKの前に、こういうクエリーが走っていたりすると、結果はさんざんなことになる。

このプロセスが動いているときにシステムがどう見えるかについて、例を示そう。

1
2
3
4
5
6
7
8
9
10

Id 6の接続はログインできていないことがわかる。これはMySQLのコマンドラインクライアントが-Aオプション付きで起動されていて、テーブルとカラムのリストを取り出そうとタブ補完しようとしたところだ。また「Flushing tables」と言うのも間違っている。まだテーブルをフラッシュしていない。ロックを得ようと待ってるだけなんだ。

テーブルのフラッシュ

FLUSH TABLES WITH READ LOCKがようやくロックを取得すると、データのフラッシュが始まる。でも全てのストレージエンジンに適用される訳じゃない。MyISAMは通常処理中ではディスクにデータをフラッシュしようとはしないにもかかわらずだ。MyISAMはOSが適切なときにディスクにフラッシュすることを当てにしている。結果的に、MyISAMが多いとOSのバッファにダーティーなのが多くなってしまい、フラッシュするのに結構な時間がかかってしまう。この間、システムはロックされたままになってしまい、全てが終わってようやくFLUSH TABLES WITH READ LOCKは完了し、レスポンスを返すことになる。

ロックの保持

このコマンドの最後の部分は、ロックが保持される時間。このロックはUNLOCK TABLESや他のいくつかのコマンドで解放される。FLUSH TABLES WITH READ LOCKを使っているたいていのバックアップシステムでは、ロックの間の処理は、ファイルシステムのスナップショットの初期化のような比較的短い時間で終わる。だから実際には処理の中で非常に短い時間を費やすだけのことが多い。

結論

実環境で動作するバックアップシステムは、FLUSH TABLES WITH READ LOCK willが瞬時に完了すると仮定するべきではない。非常に時間がかかる場合があるからだ。例えばMyISAMとInnoDBが混在している場合がそう。でも多くの場合は混在させたりしないし、バックアップシステムがグローバルロックを回避するように設定できるはずだ。InnoDBのみの場合だと、ロックを取得する必要はないはずだ。だからロックフリーなバックアップをとれる。バックアップベンダーはこのことを考慮して製品を開発すべきだ。

まとめ

MyISAM使っていると、FLUSH TABLES WITH READ LOCK willが瞬時に終わらない場合があるので注意。あとInnoDBだとそもそもロック取得しなくてもいいから、別の方法を使うべき。要調査。

はてなブックマーク - FLUSH TABLES WITH READ LOCKの速度について

Livedoorクリップ - FLUSH TABLES WITH READ LOCKの速度について

Tags: MySQL, 翻訳

stnard.jp