ホーム > mysql
mysqlのアーカイブ
DBD::mysql 4.020がリリースされました
DBD::mysqlの4.020が昨日リリースされました。
このリリースにはmysql_server_prepare=1を使っている場合のバグの修正が5件ほど含まれています。(ChangeLog)
DBD::mysql で mysql_server_prepare=1 のとき TEXT 型の欄が自動 utf8::decode されなくなる
こちらのブログで指摘されていた件を直してパッチを送ろうと思っておもむろにmysql_server_prepare=1の状態でDBD::mysqlのテストを実行したら失敗しまくったため、もう駄目かもしれないと思ったのですが、何故かmysql_server_prepareと心中する腹をくくり一応すべてのテストを通すようにパッチを送りまくってみたところ取り込まれたという感じになりました。(リリース後に2つほどさらにpull reqしていますが…。)
TEXT型がdecodeされない件はユーザ側で回避できない話ではないんだと思いますが、LONGBLOBで4GBのメモリをアロケートしようとする件はDB構成によっては回避のしようもなく、mysql_server_prepare=1はほとんど使われてないんだなぁという印象を持ちました。
一応改めて整理しておくと、mysqlのサーバーサイドのPrepared Statementは以前はクエリキャッシュがされないというかなり致命的なデメリットがありましたがバージョン5.1.17以降でC API等から利用されるバイナリプロトコルではキャッシュされるようになり、また5.1.22以降からはテキストプロトコル上でのPREPARE, EXECUTEでもキャッシュされるようになっています。(詳細はクエリキャッシュの動作を参照してください。)
mysqlのPrepared Statementはbind値を展開した後のクエリの結果をキャッシュしてくれるいわゆるQueryCacheのみの対応となっています。パラメータ値に依存しない実行計画をキャッシュしてくれるわけでは無いのでメリットは薄いと思われる部分はあるとは思うのですが、通信データが大きい場合には速いというメリットも持っています。(今回のDBD::mysqlのバグはそのメリットを享受するべきLONGBLOBがほぼ動かないというひどいものではありましたが)
memory leakのような現象が起きるという中の人の指摘もあったりしますが、これはステートメントハンドルやコネクションの管理をクライアント側できちんと行えば問題ないと思っていたりもします。（大規模環境でどうなのかというわれるとちょっとどうなのかなと思わなくもないですが）
というわけで、やはりまあ積極的に利用を勧める理由も無いのですが、積極的に回避する理由も無いとも思ってますので是非使えるかたは使ってみてください。で、DBD::mysql的にもMySQL本体的にも枯れてくれるとうれしいなと思ってます。
mysqlでskip-character-set-client-handshakeはもう使わないほうがいいと思われ
skip-character-set-client-handshake を [mysqld] セクションに追記すると、クライアントがどんな文字コード設定をもっていようが問答無用で character_set_* を (_system をのぞいて) すべて同じ値に統一してくれる
http://d.hatena.ne.jp/a666666/20090826/1251270979
ふーむ。
skip-character-set-client-handshakeを薦める文書がネット上にはやたら転がってるんだけど、これには大きな落とし穴がある。
たしかに表示されるcharacter_set_*は統一されるかもしれないがこれはあくまでもサーバー側の認識であってクライアント(libmysqlclient)がcharsetをどう認識しているかというのとは関係ないのだ。で、実はlibmysqlclientが認識しているcharsetはcharacter_set_clientとは必ずしも一致しているわけではなくて、これを絶対的に一致させるには以前のエントリでも紹介したがhttp://www.klab.jp/media/mysql/index6.htmlの図3：クライアント側文字コードの指定チャートが参考になる。
skip-character-set-client-handshakeをしただけだと結局libmysqlclientが認識しているcharsetはクライアント側のコンパイル時のcharset(デフォルトではlatin-1)になる。ここで実際に送信するcharsetと乖離が起これば当然組み合わせによってはSQLインジェクションの脆弱性が発生する。id:a666666氏のmysqlはutf8でコンパイルしてujisを送信ということなので脆弱性はなさそうだが（あったら誰か教えてください）、例えばcp932を送信するのであれば以前のエントリと同様に(反対方向だけど)”\xe3\x81\x95\x5c”等が危険な文字列になる。これをlibmysqlclientはUTF-8として認識してエスケープするので”\xe3\x81\x95\x5c\x5c”になるが、サーバー側ではcp932と認識するので”\xe3\x81″ “\x95\x5c” “\x5c”と分割されてしまうという感じだ。
そもそもskip-character-set-client-handshakeなんてのはmysql4.0からの移行組用の臨時救済措置で作られたものだろうし、こんなものを使うことを前提に運用するのはやめたほうがいい。よくいわれている文字コードの変換処理をさせたくない、ということであればフィールドの文字コードと通信の文字コードを統一しておけばいいだけでしょう。
libmysqlclientを使うプログラムはset namesをutf8であっても使ってはいけない
mysql_enable_utf8 => 1 で DBIC::UTF8Columns 要らなくなるっぽいComments
上記の記事のブクマに
set namesを直接実行しちゃうのはutf8であってもコンパイルオプションによっては問題起こるのでお勧めできない http://b.hatena.ne.jp/nihen/20090204#bookmark-11950629
ってことを書かせてもらったんだけど、この最後のset namesはutf8でも使っちゃダメという話を軽く説明します。
まずは、基本的なことはMySQL5開拓団 – 日本語処理の鉄則 / KLab株式会社を読んでください。mysqlの日本語処理についてのドキュメントとしては、私は今一番信頼できるドキュメントだと思っています。
さて、上記のページの< 図3：クライアント側文字コードの指定チャート>を、勝手ながらすべて引用させていただくことにする。(手抜きもいいところだな）
< 図3：クライアント側文字コードの指定チャート> ■初期値の設定 │ ├mysqlコマンドの場合 │└【my.cnfの[mysql]にdefault-character-setで指定する】 │ └my.cnfを読めてdefault-character-setを解釈することができるクライアントか？ ├(yes)→【my.cnfの[client]にdefault-character-setで指定する】 └(no )→「SET NAMESコース」へ ■途中で変更したい │ ├【途中で変更しなければならないような構成はやめて、初期値だけに頼るようにする】 │ ├mysqlコマンドの場合 │└5.0.25以上か？ │ ├(yes)→【charset命令で指定する】 │ └(no) →「SET NAMESコース」へ │ └C言語APIのmysql_set_character_set()かmysql_options()が使えるクライアントか？ ├(yes)→【mysql_set_character_set()かmysql_options()で指定する】 └(no) →「SET NAMESコース」へ ■SET NAMESコース │ └指定したいのはシフトJIS（cp932かsjis）か？ │ ├(no )→【SET NAMES文で指定する】 └(yes)→【MyNAパッチ（注5）を当てた上で、SET NAMES文で指定する】 http://www.klab.jp/media/mysql/index6.html
で、今回のDBD::mysqlはどこにあてはまるかというと、、
my.cnfを読めてdefault-character-setを解釈することができるクライアントか？
=> yes
mysql_read_default_fileを指定することにより読み込めます。
use DBI;
my $dbh = DBI->connect(
'DBI:mysql:database=sandbox;host=localhost;mysql_read_default_file=/etc/mysql/my.cnf',
qw/id password/,
);
下記のように一時ファイルを使うこともできるようにファイルの場所はどこでもかまわなかったりする。
use DBI;
use File::Temp qw/tempfile/;
my ($fh, $filename) = tempfile();
print {$fh} "[client]\ndefault-character-set=utf8\n";
close $fh;
my $dbh = DBI->connect(
'DBI:mysql:database=sandbox;host=localhost;mysql_read_default_file=' . $filename,
qw/id password/,
);
さて、実は上記のようにmysql_read_default_fileを使う方法がDBD::mysqlで日本語を扱う際のほぼ唯一の”正しい”接続方法になる。
なぜか？
とりあえず上記のチャートを進めてみよう。
└C言語APIのmysql_set_character_set()かmysql_options()が使えるクライアントか？
=> 半分 yes /半分no
mysql_set_character_set()は使えない。
mysql_options()は直接は使えないが、上記で話題にあがったmysql_enable_utf8を使うとmysql_optionsでMYSQL_SET_CHARSET_NAMEが設定できる。のでutf8の場合のみこのオプションを使うことで”正しい”接続ができる。experimentalだけどね。ちなみにphpではmysql_set_charsetというのがちゃんと用意されていたりする。
さて、最後に問題のSET NAMESコースである。
■SET NAMESコース │ └指定したいのはシフトJIS（cp932かsjis）か？ │ ├(no )→【SET NAMES文で指定する】 └(yes)→【MyNAパッチ（注5）を当てた上で、SET NAMES文で指定する】
さて、ここではShift_JIS以外はSET NAMESでも問題ないと書いてあるように読める。実際ほとんどの場合はそうなのだ。
しかし、これはlatin-1がlibmysqlclientのデフォルトキャラクタセットの場合だけであって（実際コンパイルのデフォルトはそうなっている）libmysqlclientを–witth-charset=cp932オプション付きでコンパイルしていた場合なんかだとその限りではない。
–with-charset=cp932でコンパイルしているlibmysqlclientの場合、下記のコードにおいて\x5cがエスケープされず、SQLインジェクションの危険性が発生する。(手軽に確認するならmysql_read_default_fileを使ってdefault-character-setにcp932をセットすればいい)
my $dbh = DBI->connect(
'DBI:mysql:database=sandbox;host=localhost',
qw/id password/,
);
$dbh->do('set names utf8');
my $sth = $dbh->prepare('insert into sandbox (name) values(?)');
$sth->execute("\xe3\x81\x95\x5c");
“\xe3\x81\x95\x5c”というのは分解すると
\xe3\x81\x95 == “さ”(UTF-8)
\xe3\x81 == “縺”(Shift_JIS)
\x95\x5c == “表”(Shift_JIS)
になる。
libmysqlclientは”\xe3\x81\x95\x5c”をShift_JISとしてparseしてエスケープするため最後の\x5cはエスケープされない。しかしset names utf8しているのでmysqlサーバ側はutf8としてパースし、最後の\x5cをバックスラッシュとして処理してしまう。
長々と書いてしまったが、libmysqlclientを(間接的にでも)使っているプログラマは、libmysqlclientが認識している文字コードとmysqldが認識している文字コードはかならず一致させるという原則を守るといいと思うよ。もちろん今回出したケースはかなり特殊なケースでコンパイルオプションをきちんと管理していれば起こらない問題だけどプログラム側で回避できる問題でもあるのできちんと対策しとくべき。
まぁ、これいっちゃうとlatin-1使う場合でもちゃんとdefault-character-set指定しないと駄目ってことでなんか極論な気がしないでもないけどね・・・。
追記1; ああ、重要なことを1点言い忘れてるね。mysql_server_prepareを使えばこういう問題はもちろん起きません。なのでこっちの対策を個人的には推奨したい。
mysqlでcharsetをucs2にした場合のasciiのみのフィールド
- 2009/1/15 木曜日 2:22:59
- javascript | mysql | perl | vimperator
前回のエントリで書いたように、ucs2をcharsetにするとascii部分がutf8に比べ2倍になってしまう。これを一部解決するために、asciiのみで構成されることが保障されているフィールドはそのフィールドだけasciiにしてしまえばいい。そうすることによって、asciiのみで構成されたフィールドをkeyに検索といった場合でもutf8に比べ遜色のないパフォーマンスを得ることができる。
まずは、ベンチコードを例の如くはりつけ。環境等は前エントリと同一。
ベンチコード
insert用
#!/usr/bin/perl
use strict;
use warnings;
use feature qw/say/;
use utf8;
use DBI;
use Encode;
use List::MoreUtils qw/apply/;
use Benchmark;
use Digest::MD5;
my @engines = qw/myisam innodb/;
my @charsets = qw/utf8 ucs2/;
my $bench_count = 100000;
my $client_dsn = 'DBI:mysql:database=sandbox;host=localhost;mysql_read_default_file=/etc/mysql/my.cnf';
my $server_dsn = $client_dsn . ';mysql_server_prepare=1';
my @idpass = qw/sandbox sandbox/;
my @init_query = (
'drop table if exists %table',
q{
create table %table(
id int unsigned not null auto_increment,
name char(3) not null,
b64digest char(22) %field_charset not null,
created datetime not null, primary key(id),
index(b64digest)
) ENGINE=%engine ROW_FORMAT=%row_format DEFAULT CHARSET=%charset
},
);
my $query = Encode::encode('utf8', q{insert into %table values(null, 'たなか', ?, now())});
my %target = ();
for my $engine ( @engines ) {
for my $charset ( @charsets ) {
for my $field_charset ( 'ascii', '' ) {
for my $row_format ( 'DEFAULT', 'REDUNDANT') {
if ( $engine eq 'myisam' && $row_format ne 'DEFAULT' ) {
next;
}
my $key = join '_', ($engine, $charset, $field_charset || 'non', $row_format);
$target{$key} = {
dbh => DBI->connect(
$server_dsn,
@idpass,
{
AutoCommit => 0,
},
),
engine => $engine,
charset => $charset,
field_charset => $field_charset,
row_format => $row_format,
table => 'sandbox_' . $key,
};
for my $query ( @init_query ) {
$target{$key}->{dbh}->do(apply { s/%(table|charset|engine|field_charset|row_format)/$target{$key}->{$1}/g } $query);
}
$target{$key}->{sth} = $target{$key}->{dbh}->prepare(apply { s/%(table|charset|engine)/$target{$key}->{$1}/g } $query);
}
}
}
}
my $md5 = Digest::MD5->new();
Benchmark::cmpthese($bench_count,{
map {
my $target_sth = $target{$_}->{sth};
$_ => sub {
$md5->reset;
$md5->add(int(rand(10000)));
$target_sth->execute($md5->b64digest);
}
} keys %target
});
$_->{dbh}->commit for values %target;
$_->{dbh}->disconnect for values %target;
select用
#!/usr/bin/perl
use strict;
use warnings;
use feature qw/say/;
use utf8;
use DBI;
use List::MoreUtils qw/apply/;
use Benchmark;
use Digest::MD5;
my @engines = qw/myisam innodb/;
my @charsets = qw/utf8 ucs2/;
my $bench_count = 30000;
my $client_dsn = 'DBI:mysql:database=sandbox;host=localhost;mysql_read_default_file=/etc/mysql/my.cnf';
my $server_dsn = $client_dsn . ';mysql_server_prepare=1';
my @idpass = qw/sandbox sandbox/;
my $query = q{select * from %table where b64digest = ?};
my %target = ();
for my $engine ( @engines ) {
for my $charset ( @charsets ) {
for my $field_charset ( 'ascii', '' ) {
for my $row_format ( 'DEFAULT', 'REDUNDANT') {
if ( $engine eq 'myisam' && $row_format ne 'DEFAULT' ) {
next;
}
my $key = join '_', ($engine, $charset, $field_charset || 'non', $row_format);
$target{$key} = {
dbh => DBI->connect(
$server_dsn,
@idpass,
),
engine => $engine,
charset => $charset,
field_charset => $field_charset,
row_format => $row_format,
table => 'sandbox_' . $key,
};
$target{$key}->{sth} = $target{$key}->{dbh}->prepare(apply { s/%(table|charset|engine)/$target{$key}->{$1}/g } $query);
}
}
}
}
my $md5 = Digest::MD5->new();
Benchmark::cmpthese($bench_count,{
map {
my $target_sth = $target{$_}->{sth};
$_ => sub {
$md5->reset;
$md5->add(int(rand(10000)));
$target_sth->execute($md5->b64digest);
$target_sth->fetch;
}
} keys %target
});
結果
b64digest char(22)
insert
Rate innodb_utf8_ascii_DEFAULT innodb_ucs2_non_DEFAULT myisam_ucs2_ascii_DEFAULT innodb_utf8_non_REDUNDANT innodb_ucs2_non_REDUNDANT innodb_ucs2_ascii_DEFAULT myisam_utf8_non_DEFAULT myisam_ucs2_non_DEFAULT myisam_utf8_ascii_DEFAULT innodb_utf8_ascii_REDUNDANT innodb_ucs2_ascii_REDUNDANT innodb_utf8_non_DEFAULT
innodb_utf8_ascii_DEFAULT 32895/s -- -1% -2% -2% -2% -8% -10% -10% -13% -14% -14% -15%
innodb_ucs2_non_DEFAULT 33113/s 1% -- -1% -1% -1% -8% -9% -10% -12% -13% -13% -15%
myisam_ucs2_ascii_DEFAULT 33445/s 2% 1% -- -0% -0% -7% -8% -9% -11% -12% -12% -14%
innodb_utf8_non_REDUNDANT 33445/s 2% 1% 0% -- -0% -7% -8% -9% -11% -12% -12% -14%
innodb_ucs2_non_REDUNDANT 33557/s 2% 1% 0% 0% -- -6% -8% -8% -11% -12% -12% -13%
innodb_ucs2_ascii_DEFAULT 35842/s 9% 8% 7% 7% 7% -- -1% -2% -5% -6% -6% -8%
myisam_utf8_non_DEFAULT 36364/s 11% 10% 9% 9% 8% 1% -- -1% -4% -5% -5% -6%
myisam_ucs2_non_DEFAULT 36630/s 11% 11% 10% 10% 9% 2% 1% -- -3% -4% -4% -5%
myisam_utf8_ascii_DEFAULT 37736/s 15% 14% 13% 13% 12% 5% 4% 3% -- -1% -1% -3%
innodb_utf8_ascii_REDUNDANT 38168/s 16% 15% 14% 14% 14% 6% 5% 4% 1% -- -0% -2%
innodb_ucs2_ascii_REDUNDANT 38168/s 16% 15% 14% 14% 14% 6% 5% 4% 1% 0% -- -2%
innodb_utf8_non_DEFAULT 38760/s 18% 17% 16% 16% 16% 8% 7% 6% 3% 2% 2% --
まーinsertはたいして差なし。変換コストなんかも気にしないでいいかなぁ？
select
innodb_utf8_non_REDUNDANT 8333/s -- -11% -19% -32% -35% -35% -38% -40% -59% -64% -65% -65% innodb_ucs2_non_REDUNDANT 9317/s 12% -- -10% -24% -27% -28% -30% -33% -55% -60% -61% -61% innodb_ucs2_non_DEFAULT 10309/s 24% 11% -- -16% -20% -20% -23% -26% -50% -55% -57% -57% innodb_ucs2_ascii_DEFAULT 12295/s 48% 32% 19% -- -4% -5% -8% -12% -40% -47% -48% -48% innodb_ucs2_ascii_REDUNDANT 12821/s 54% 38% 24% 4% -- -0% -4% -8% -38% -44% -46% -46% innodb_utf8_non_DEFAULT 12876/s 55% 38% 25% 5% 0% -- -4% -8% -37% -44% -46% -46% innodb_utf8_ascii_DEFAULT 13393/s 61% 44% 30% 9% 4% 4% -- -4% -35% -42% -44% -44% innodb_utf8_ascii_REDUNDANT 13953/s 67% 50% 35% 13% 9% 8% 4% -- -32% -40% -41% -41% myisam_utf8_non_DEFAULT 20548/s 147% 121% 99% 67% 60% 60% 53% 47% -- -11% -14% -14% myisam_ucs2_non_DEFAULT 23077/s 177% 148% 124% 88% 80% 79% 72% 65% 12% -- -3% -3% myisam_ucs2_ascii_DEFAULT 23810/s 186% 156% 131% 94% 86% 85% 78% 71% 16% 3% -- -0% myisam_utf8_ascii_DEFAULT 23810/s 186% 156% 131% 94% 86% 85% 78% 71% 16% 3% 0% --
5.x以降のinnodbのデフォルトROW_FORMATはCOMPACTなんですが、やはりutf8のchar型の場合においてREDUNDANTに比べうまいこと動いていることがわかるベンチ結果ですね。でもって問題のasciiですが基本常にascii指定のほうがパフォーマンスが上になりましたね。
b64digest varchar(22)
フィールドをvarcharにしてみる。
insert
Rate innodb_ucs2_non_REDUNDANT innodb_ucs2_ascii_DEFAULT innodb_ucs2_non_DEFAULT myisam_ucs2_ascii_DEFAULT innodb_utf8_ascii_DEFAULT myisam_utf8_non_DEFAULT myisam_ucs2_non_DEFAULT innodb_utf8_ascii_REDUNDANT innodb_utf8_non_REDUNDANT innodb_utf8_non_DEFAULT myisam_utf8_ascii_DEFAULT innodb_ucs2_ascii_REDUNDANT
innodb_ucs2_non_REDUNDANT 34247/s -- -3% -5% -5% -6% -7% -8% -9% -9% -9% -10% -11%
innodb_ucs2_ascii_DEFAULT 35336/s 3% -- -2% -2% -3% -4% -5% -6% -6% -6% -7% -8%
innodb_ucs2_non_DEFAULT 35971/s 5% 2% -- -1% -1% -3% -4% -4% -4% -5% -5% -6%
myisam_ucs2_ascii_DEFAULT 36232/s 6% 3% 1% -- -0% -2% -3% -4% -4% -4% -5% -5%
innodb_utf8_ascii_DEFAULT 36364/s 6% 3% 1% 0% -- -1% -3% -3% -3% -4% -4% -5%
myisam_utf8_non_DEFAULT 36900/s 8% 4% 3% 2% 1% -- -1% -2% -2% -2% -3% -4%
myisam_ucs2_non_DEFAULT 37313/s 9% 6% 4% 3% 3% 1% -- -1% -1% -1% -2% -3%
innodb_utf8_ascii_REDUNDANT 37594/s 10% 6% 5% 4% 3% 2% 1% -- -0% -0% -1% -2%
innodb_utf8_non_REDUNDANT 37594/s 10% 6% 5% 4% 3% 2% 1% 0% -- -0% -1% -2%
innodb_utf8_non_DEFAULT 37736/s 10% 7% 5% 4% 4% 2% 1% 0% 0% -- -1% -2%
myisam_utf8_ascii_DEFAULT 38023/s 11% 8% 6% 5% 5% 3% 2% 1% 1% 1% -- -1%
innodb_ucs2_ascii_REDUNDANT 38314/s 12% 8% 7% 6% 5% 4% 3% 2% 2% 2% 1% --
ほぼ変化なし
select
Rate innodb_ucs2_non_REDUNDANT innodb_ucs2_non_DEFAULT innodb_ucs2_ascii_REDUNDANT innodb_utf8_ascii_REDUNDANT innodb_utf8_ascii_DEFAULT innodb_utf8_non_DEFAULT innodb_ucs2_ascii_DEFAULT innodb_utf8_non_REDUNDANT myisam_utf8_non_DEFAULT myisam_ucs2_non_DEFAULT myisam_utf8_ascii_DEFAULT myisam_ucs2_ascii_DEFAULT
innodb_ucs2_non_REDUNDANT 8902/s -- -3% -24% -25% -27% -27% -28% -32% -58% -60% -60% -62%
innodb_ucs2_non_DEFAULT 9202/s 3% -- -21% -23% -24% -25% -25% -30% -57% -58% -59% -60%
innodb_utf8_non_REDUNDANT 11719/s 32% 27% -- -2% -4% -4% -5% -11% -45% -47% -47% -50%
innodb_utf8_ascii_REDUNDANT 11905/s 34% 29% 2% -- -2% -2% -4% -9% -44% -46% -46% -49%
innodb_ucs2_ascii_DEFAULT 12146/s 36% 32% 4% 2% -- -0% -2% -7% -43% -45% -45% -48%
innodb_ucs2_ascii_REDUNDANT 12195/s 37% 33% 4% 2% 0% -- -1% -7% -43% -45% -45% -48%
innodb_utf8_ascii_DEFAULT 12346/s 39% 34% 5% 4% 2% 1% -- -6% -42% -44% -44% -47%
innodb_utf8_non_DEFAULT 13100/s 47% 42% 12% 10% 8% 7% 6% -- -39% -41% -41% -44%
myisam_utf8_ascii_DEFAULT 21429/s 141% 133% 83% 80% 76% 76% 74% 64% -- -3% -4% -8%
myisam_utf8_non_DEFAULT 22059/s 148% 140% 88% 85% 82% 81% 79% 68% 3% -- -1% -5%
myisam_ucs2_ascii_DEFAULT 22222/s 150% 141% 90% 87% 83% 82% 80% 70% 4% 1% -- -4%
myisam_ucs2_non_DEFAULT 23256/s 161% 153% 98% 95% 91% 91% 88% 78% 9% 5% 5% --
varcharだとutf8のままが若干早い。変換コストかな。
まとめ
innodb (defaultが)utf8 varchar ROW_FORMAT=COMPACT以外の組み合わせの場合は、できるだけasciiを指定しておくといいかんじになりそう。
参考
[mysql 14237] フィールドごとのキャラクタセットの異なる指定
mysqlの内部キャラセットはucs2にするといいんじゃないだろうか
mysqlの保存用のcharsetをucs2にすると、utf8の場合に比べて速くなる状況があるんじゃないのだろうかとふと思い立ちいろいろとベンチマークとってみた。視点は以下の三つ。
・myisam || innodb
・ucs2 || utf8
・client-prepare || serverside-prepare
mysqlのバージョンはmysql-server-5.0.51a-20(debian lenny)
perl v5.10.0
DBI 1.607
DBD::mysqlは4.0.10にBug #41815のパッチあてたもの。
ベンチコード
insert用
#!/usr/bin/perl
use strict;
use warnings;
use feature qw/say/;
use utf8;
use DBI;
use List::MoreUtils qw/apply/;
use Benchmark;
my @engines = qw/myisam innodb/;
my @charsets = qw/utf8 ucs2/;
my $bench_count = 100000;
my $insert_str = 'ほげ' x 100;
my $client_dsn = 'DBI:mysql:database=sandbox;host=localhost;mysql_read_default_file=/etc/mysql/my.cnf';
my $server_dsn = $client_dsn . ';mysql_server_prepare=1';
my @idpass = qw/sandbox sandbox/;
my @init_query = (
'drop table if exists %table',
q{
create table %table(
id int unsigned not null auto_increment,
name varchar(1024) not null,
created datetime not null,
primary key(id)
) ENGINE=%engine DEFAULT CHARSET=%charset
},
);
my $query = q{insert into %table values(null, ?, now())};
my %target = ();
for my $engine ( @engines ) {
for my $charset ( @charsets ) {
for my $prepare_type ( qw/client server/ ) {
my $key = join '_', ($engine, $charset, $prepare_type);
$target{$key} = {
dbh => DBI->connect(
$prepare_type eq 'client' ? $client_dsn : $server_dsn,
@idpass,
{
AutoCommit => 0,
},
),
engine => $engine,
charset => $charset,
table => 'sandbox_' . $key,
};
for my $query ( @init_query ) {
$target{$key}->{dbh}->do(apply { s/%(table|charset|engine)/$target{$key}->{$1}/g } $query);
}
$target{$key}->{sth} = $target{$key}->{dbh}->prepare(apply { s/%(table|charset|engine)/$target{$key}->{$1}/g } $query);
}
}
}
Benchmark::cmpthese($bench_count,{
map {
my $target_sth = $target{$_}->{sth};
$_ => sub {
$target_sth->execute($insert_str);
}
} keys %target
});
$_->{dbh}->commit for values %target;
$_->{dbh}->disconnect for values %target;
select用
#!/usr/bin/perl
use strict;
use warnings;
use feature qw/say/;
use utf8;
use DBI;
use List::MoreUtils qw/apply/;
use Benchmark;
my @engines = qw/myisam innodb/;
my @charsets = qw/utf8 ucs2/;
my $bench_count = 100000;
my $client_dsn = 'DBI:mysql:database=sandbox;host=localhost;mysql_read_default_file=/etc/mysql/my.cnf';
my $server_dsn = $client_dsn . ';mysql_server_prepare=1';
my @idpass = qw/sandbox sandbox/;
my $query = q{select * from %table where id = ?};
my %target = ();
for my $engine ( @engines ) {
for my $charset ( @charsets ) {
for my $prepare_type ( qw/client server/ ) {
my $key = join '_', ($engine, $charset, $prepare_type);
$target{$key} = {
dbh => DBI->connect(
$prepare_type eq 'client' ? $client_dsn : $server_dsn,
@idpass
),
engine => $engine,
charset => $charset,
table => 'sandbox_' . $key,
};
$target{$key}->{sth} = $target{$key}->{dbh}->prepare(apply { s/%(table|charset|engine)/$target{$key}->{$1}/g } $query);
}
}
}
Benchmark::cmpthese($bench_count,{
map {
my $target_sth = $target{$_}->{sth};
$_ => sub {
$target_sth->execute(150000);
$target_sth->fetch;
}
} keys %target
});
/etc/mysql/my.cnfには
[client] default-character-set=utf8 [mysqld] query_cache_size = 0
がかいてある。
で、ベンチ結果を以下にはっつけて少し解釈してみますが、すべての解釈には「今回の環境でこのコードにおいては」という前提付きです。
insert
my $insert_str = ‘ほげ’ x 10;
Rate myisam_utf8_client innodb_ucs2_client myisam_ucs2_client innodb_utf8_client innodb_utf8_server myisam_utf8_server innodb_ucs2_server myisam_ucs2_server
myisam_utf8_client 36101/s -- -0% -6% -7% -10% -15% -16% -16%
innodb_ucs2_client 36232/s 0% -- -5% -7% -9% -15% -16% -16%
myisam_ucs2_client 38314/s 6% 6% -- -2% -4% -10% -11% -11%
innodb_utf8_client 38911/s 8% 7% 2% -- -3% -9% -9% -10%
innodb_utf8_server 40000/s 11% 10% 4% 3% -- -6% -7% -7%
myisam_utf8_server 42553/s 18% 17% 11% 9% 6% -- -1% -1%
innodb_ucs2_server 42918/s 19% 18% 12% 10% 7% 1% -- -0%
myisam_ucs2_server 43103/s 19% 19% 13% 11% 8% 1% 0% --
my $insert_str = ‘ほげ’ x 100;
Rate innodb_ucs2_client innodb_utf8_client myisam_ucs2_client myisam_utf8_client innodb_utf8_server myisam_utf8_server innodb_ucs2_server myisam_ucs2_server
innodb_ucs2_client 29326/s -- -5% -8% -9% -12% -25% -28% -30%
innodb_utf8_client 30769/s 5% -- -3% -5% -7% -21% -24% -27%
myisam_ucs2_client 31746/s 8% 3% -- -2% -4% -19% -22% -25%
myisam_utf8_client 32362/s 10% 5% 2% -- -3% -17% -20% -23%
innodb_utf8_server 33223/s 13% 8% 5% 3% -- -15% -18% -21%
myisam_utf8_server 39063/s 33% 27% 23% 21% 18% -- -4% -7%
innodb_ucs2_server 40486/s 38% 32% 28% 25% 22% 4% -- -4%
myisam_ucs2_server 42194/s 44% 37% 33% 30% 27% 8% 4% --
my $insert_str = ‘ほげ’ x 500;
Rate innodb_utf8_client innodb_ucs2_client myisam_utf8_client myisam_ucs2_client innodb_utf8_server innodb_ucs2_server myisam_utf8_server myisam_ucs2_server
innodb_utf8_client 14948/s -- -4% -22% -26% -44% -47% -55% -58%
innodb_ucs2_client 15552/s 4% -- -19% -23% -42% -45% -53% -56%
myisam_utf8_client 19084/s 28% 23% -- -6% -29% -32% -43% -46%
myisam_ucs2_client 20202/s 35% 30% 6% -- -25% -28% -39% -43%
innodb_utf8_server 26810/s 79% 72% 40% 33% -- -5% -19% -25%
innodb_ucs2_server 28249/s 89% 82% 48% 40% 5% -- -15% -21%
myisam_utf8_server 33223/s 122% 114% 74% 64% 24% 18% -- -7%
myisam_ucs2_server 35587/s 138% 129% 86% 76% 33% 26% 7% --
・myisam || innodb
当然myisamのほうが早い
・ucs2 || utf8
ucs2が若干早い気がするけど誤差の範囲
・client-prepare || serverside-prepare
serversideはデータ量が増えるにつれ明らかにclientに比べ早くなる。
select
上記の’ほげ’ x 500のinsert後に実行
Rate myisam_utf8_client innodb_ucs2_client myisam_utf8_server myisam_ucs2_client innodb_utf8_client myisam_ucs2_server innodb_utf8_server innodb_ucs2_server
myisam_utf8_client 25381/s -- -0% -4% -6% -13% -16% -16% -17%
innodb_ucs2_client 25381/s 0% -- -4% -6% -13% -16% -16% -17%
myisam_utf8_server 26455/s 4% 4% -- -2% -10% -12% -12% -14%
myisam_ucs2_client 27027/s 6% 6% 2% -- -8% -11% -11% -12%
innodb_utf8_client 29240/s 15% 15% 11% 8% -- -3% -3% -5%
myisam_ucs2_server 30211/s 19% 19% 14% 12% 3% -- -0% -2%
innodb_utf8_server 30211/s 19% 19% 14% 12% 3% 0% -- -2%
innodb_ucs2_server 30675/s 21% 21% 16% 13% 5% 2% 2% --
うーん・・・。たいした差はなし。
思いつくutf8に対するucs2のメリット
・日本語だけで埋め尽くされているフィールドの実容量が約2/3になる(＝onメモリ可能なレコード数やページあたりのレコード数が増える)
・バイト数 / 2 = 文字数になる。よって下記のようにchar_lengthをとるものだとかなり早くなる。まぁそんなにうれしいってほどではないけど。
mysql> select max(char_length(name)) from sandbox_innodb_utf8_client; +------------------------+ | max(char_length(name)) | +------------------------+ | 1000 | +------------------------+ 1 row in set (18.02 sec) mysql> select max(char_length(name)) from sandbox_innodb_ucs2_client; +------------------------+ | max(char_length(name)) | +------------------------+ | 1000 | +------------------------+ 1 row in set (12.46 sec) mysql> select max(char_length(name)) from sandbox_myisam_utf8_client; +------------------------+ | max(char_length(name)) | +------------------------+ | 1000 | +------------------------+ 1 row in set (14.99 sec) mysql> select max(char_length(name)) from sandbox_myisam_ucs2_client; +------------------------+ | max(char_length(name)) | +------------------------+ | 1000 | +------------------------+ 1 row in set (7.51 sec)
ucs2のデメリット
・ASCII範囲の容量が2倍になる(myisamのCHAR型とinnodb(ROW_FORMAT=REDUNDANT)のCHAR型を除く）
ぐらい？結果的にucs2に決定的なメリットがあるわけでもないけれど、デメリットもないならばutf8にしてる人はみんなucs2にすればいいよ！とかおもうのだけどどうなんだろう。誰かデメリットが分かる人がいればおしえてください。ただ、mysql6.0.4からはutf8がサロゲートペアの範囲もサポートする(5.xはBMPのみのサポート)ので互換性がなくなるというデメリットは発生しそう。その場合は同様にサポートされたutf32を使うといいんだろうけど容量的なメリットはなくなってしまう。utf16にすると容量的なメリットは維持できるけど、バイト数と文字数の固定関係は得られない。悩みどころ。
クライアントキャラクタセットにucs2が使えないので、utf8からの変換コストが問題になりそうだなぁとおもったけどベンチとってみるとそうでもなかったのが少し意外だった。
番外編:myisamのfixed
myisamはすべてnot nullで固定長のフィールドだと、ROW_FORMATがFIXEDになって早くなる。この話とucs2のバイト数固定性能が関連するんじゃないかと少し探ったけど今のところ関連は見いだせず。
insert
my $insert_str = ‘ほげ’ x 100;
char(255)
Rate innodb_ucs2_client myisam_ucs2_client myisam_utf8_client innodb_utf8_client innodb_utf8_server innodb_ucs2_server myisam_utf8_server myisam_ucs2_server
innodb_ucs2_client 29499/s -- -2% -6% -6% -8% -21% -26% -28%
myisam_ucs2_client 30211/s 2% -- -4% -4% -6% -19% -24% -27%
myisam_utf8_client 31348/s 6% 4% -- -0% -2% -16% -21% -24%
innodb_utf8_client 31447/s 7% 4% 0% -- -2% -16% -21% -24%
innodb_utf8_server 32051/s 9% 6% 2% 2% -- -14% -19% -22%
innodb_ucs2_server 37313/s 26% 24% 19% 19% 16% -- -6% -9%
myisam_utf8_server 39683/s 35% 31% 27% 26% 24% 6% -- -4%
myisam_ucs2_server 41152/s 40% 36% 31% 31% 28% 10% 4% --
my $insert_str = ‘ほげ’ x 100;
varchar(255)
Rate innodb_ucs2_client myisam_ucs2_client innodb_utf8_client myisam_utf8_client innodb_utf8_server myisam_utf8_server innodb_ucs2_server myisam_ucs2_server
innodb_ucs2_client 30395/s -- -8% -12% -13% -19% -21% -24% -25%
myisam_ucs2_client 33003/s 9% -- -4% -5% -12% -14% -18% -18%
innodb_utf8_client 34483/s 13% 4% -- -1% -8% -10% -14% -15%
myisam_utf8_client 34843/s 15% 6% 1% -- -7% -9% -13% -14%
innodb_utf8_server 37453/s 23% 13% 9% 7% -- -3% -7% -7%
myisam_utf8_server 38462/s 27% 17% 12% 10% 3% -- -4% -5%
innodb_ucs2_server 40161/s 32% 22% 16% 15% 7% 4% -- -1%
myisam_ucs2_server 40486/s 33% 23% 17% 16% 8% 5% 1% --
select
char(255)
Rate innodb_ucs2_client innodb_utf8_server myisam_utf8_client innodb_utf8_client innodb_ucs2_server myisam_ucs2_client myisam_utf8_server myisam_ucs2_server
innodb_ucs2_client 26385/s -- -3% -6% -6% -9% -11% -15% -18%
innodb_utf8_server 27174/s 3% -- -3% -3% -7% -8% -13% -15%
myisam_utf8_client 28011/s 6% 3% -- -0% -4% -6% -10% -13%
innodb_utf8_client 28090/s 6% 3% 0% -- -4% -5% -10% -12%
innodb_ucs2_server 29155/s 10% 7% 4% 4% -- -2% -6% -9%
myisam_ucs2_client 29674/s 12% 9% 6% 6% 2% -- -4% -7%
myisam_utf8_server 31056/s 18% 14% 11% 11% 7% 5% -- -3%
myisam_ucs2_server 32051/s 21% 18% 14% 14% 10% 8% 3% --
varchar(255)
Rate myisam_ucs2_server myisam_utf8_server innodb_ucs2_client innodb_ucs2_server myisam_utf8_client innodb_utf8_server myisam_ucs2_client innodb_utf8_client
myisam_ucs2_server 27855/s -- -3% -3% -4% -8% -8% -12% -14%
myisam_utf8_server 28736/s 3% -- -0% -1% -5% -5% -9% -11%
innodb_ucs2_client 28736/s 3% 0% -- -1% -5% -5% -9% -11%
innodb_ucs2_server 29070/s 4% 1% 1% -- -4% -4% -8% -10%
myisam_utf8_client 30211/s 8% 5% 5% 4% -- -1% -5% -7%
innodb_utf8_server 30395/s 9% 6% 6% 5% 1% -- -4% -6%
myisam_ucs2_client 31646/s 14% 10% 10% 9% 5% 4% -- -3%
innodb_utf8_client 32468/s 17% 13% 13% 12% 7% 7% 3% --
正月早々DBD::mysqlのデバッグやってた
DBD::mysqlのベンチマークとってたらメモリリークが発生。
どうやらサーバサイドprepareなStatementHandlerを使いまわしているとリークする模様。rt.cpan.orgとどっちが反応よいのかよくわからなかったがとりあえずbugs.mysql.comのほうにバグリポート。Bug #41815
というわけで、今年もよろしくおねがいします。
mysqlのパーサのわからないところ
- 2008/12/30 火曜日 6:28:13
- mysql
ここんところ文字コードとmysqlの関係が頭から離れずにいろいろと調査中だったりします。
で。mysqlのパーサのコードを読んだりもしてるわけなのですがどうしてもわからないことが。
サーバサイドPrepareを使わないで、
character-set-clientが’sjis’でフィールドのキャラセットが’utf8’のときに
0x815f(＼)が0x5c(\)ではいり
0x955c(表)が0xE8A1A8(表)
できちんと入ること。
SQLParse => utf8化
の順番であれば、0x955cのあとの’がエスケープされてエラーになりそうだし
utf8化 => SQLParse
の順番であれば、0x815f(=>0x5c)のあとの’がエスケープされてエラーになりそう。
でもエラーにならない。うーむ。ちなみにコード読んだ限りではParse後にutf8化してるように感じるけど・・・。
yaccがマルチバイトなencodingを認識してパースしてくれているんだろうか。ぐふう。
まぁはっきりいってコードの読みがまだまだ浅いのとyacc(bison)をちゃんと理解しないとわかるはずもないんだろうけど。
追記1: あ、おｋ。分かった。sql/sql_lex.ccのget_textでマルチバイトの処理やってた。
ホーム > mysql
- about
- カテゴリー
- 最近の投稿
-
- YAPC::Kyoto 2023参加してきました
- #isucon12 参戦記録
- #isucon10 予選惨敗してきました
- #isucon9 参戦記録
- #isucon 8 本戦10位で負けてきました
- #isucon 8 予選15位で通過しました
- #isucon 7 予選敗退しました
- #isucon 6 本戦10位で負けてきました
- #isucon 6 予選二位で通過しました
- #isucon 5 予選、惨敗でした
- YAPC::Asia Tokyo 2015に参加してきました
- #isucon 4 本選10位で惨敗でした
- #isucon 4 予選参加してきました
- #isucon 負けてきました
- #isucon 3 予選総合3位でした
- #ISUCON2 で特別賞を頂いて来ました
- YAPC::Asia Tokyo 2012に参加してきました
- YAPC::Asia Tokyo 2011に参加してきました
- ISUCONに参加してきました
- DBD::mysql 4.020がリリースされました
ブックマーク
- 検索
- フィード
- メタ情報