酒日記

SQLite というデータベースエンジンがある。 サーバプロセスが常駐するような大層なものではなく、1ファイル == 1データベース というシンプルな DB。dbi-japan のメーリングリストで話題になっていたのだが、やっと実験できた。

ちなみにトップページでは Four times faster than PostgreSQL. とか、 速度比較のページでは The speed of SQLite 2.4.0 is similar to MySQL. とか言っていて速度には自信がある模様。

...

以前 MySQL vs PostgreSQL のベンチマークを取った スクリプトを転用して計測してみる。 英和辞典の、89万レコードを検索。

前回のとはベンチマークスクリプトをちょっといじってみた (like 検索のみではなく単純一致の検索も追加)。結果は以下のように。

Pg(connect 0.05747) 
'create %'    0.01597 (select:fetch=0.01160:0.00437) 195 rows
'table a%'    0.00201 (select:fetch=0.00170:0.00031) 5 rows
'%text%'    4.03208 (select:fetch=4.02349:0.00859) 573 rows
'%integer%'    4.04652 (select:fetch=4.04606:0.00046) 16 rows
'computer'    0.00237 (select:fetch=0.00212:0.00025) 1 rows
'performance'    0.00166 (select:fetch=0.00142:0.00024) 1 rows

Pg(connect 0.01653)  (sorted) 
'create %'    0.01534 (select:fetch=0.01225:0.00309) 195 rows
'table a%'    0.00210 (select:fetch=0.00179:0.00031) 5 rows
'%text%'    4.04583 (select:fetch=4.03721:0.00862) 573 rows
'%integer%'    4.06391 (select:fetch=4.06345:0.00046) 16 rows
'computer'    0.00242 (select:fetch=0.00217:0.00025) 1 rows
'performance'    0.00171 (select:fetch=0.00147:0.00024) 1 rows

mysql(connect 0.01662) MyISAM
'create %'    0.00783 (select:fetch=0.00503:0.00280) 198 rows
'table a%'    0.00186 (select:fetch=0.00156:0.00030) 5 rows
'%text%'    2.69851 (select:fetch=2.69053:0.00798) 612 rows
'%integer%'    2.68792 (select:fetch=2.68748:0.00044) 16 rows
'computer'    0.00150 (select:fetch=0.00125:0.00025) 2 rows
'performance'    0.00132 (select:fetch=0.00109:0.00023) 1 rows

mysql(connect 0.00146) MyISAM (sorted) 
'create %'    0.01063 (select:fetch=0.00783:0.00280) 198 rows
'table a%'    0.00222 (select:fetch=0.00191:0.00031) 5 rows
'%text%'    2.73597 (select:fetch=2.72789:0.00808) 612 rows
'%integer%'    2.73082 (select:fetch=2.73039:0.00043) 16 rows
'computer'    0.00152 (select:fetch=0.00127:0.00025) 2 rows
'performance'    0.00134 (select:fetch=0.00110:0.00024) 1 rows

SQLite(connect 0.00795) 
'create %'    1.68071 (select:fetch=1.67791:0.00280) 198 rows
'table a%'    1.67695 (select:fetch=1.67664:0.00031) 5 rows
'%text%'    1.95743 (select:fetch=1.94950:0.00793) 612 rows
'%integer%'    1.93003 (select:fetch=1.92957:0.00046) 16 rows
'computer'    0.00107 (select:fetch=0.00085:0.00022) 1 rows
'performance'    0.00096 (select:fetch=0.00075:0.00021) 1 rows

SQLite(connect 0.00164)  (sorted) 
'create %'    1.68540 (select:fetch=1.68266:0.00274) 198 rows
'table a%'    1.66519 (select:fetch=1.66489:0.00030) 5 rows
'%text%'    1.95285 (select:fetch=1.94384:0.00901) 612 rows
'%integer%'    1.93351 (select:fetch=1.93306:0.00045) 16 rows
'computer'    0.00110 (select:fetch=0.00087:0.00023) 1 rows
'performance'    0.00100 (select:fetch=0.00079:0.00021) 1 rows

見づらいですが。おおざっぱにまとめると、

• MySQL、SQLite は connect が速い
• SQLite は like 検索で (前方一致でも) インデックスが有効になっていないような
• ベタサーチでは、SQLite が最速
• 単純一致検索でも SQLite > MySQL > PostgreSQL

PostgreSQL の 4倍、てことはないにしても、確かに速い。基本的なSQLは一通り使えるし、とにかくシンプルで軽快。 バックアップもファイル一つ取っておけば大丈夫(多分)。 スタンドアロンで使うなら (複数台に分散するような規模でなければ)、かなりいい感じではないかと。

MySQL と PostgreSQL のベンチマーク第3弾。Webアプリでのパフォーマンス比較。

アプリケーションは mod_perl で実装した、

1. 10万件のユーザ情報テーブルから user_name, password の完全一致で認証し、ユーザ情報を取得 (SELECT)。
2. user_name, ip_address, timestamp をログ記録テーブルに記録 (INSERT)。
3. 1/20 の確率でランダムに、ユーザ情報テーブルの内容を更新 (UPDATE)。
4. 1/10 の確率でランダムに、12万件の郵便番号辞書テーブルから前方一致で検索し、取得 (SELECT)。

という動作をするもの。検索には全てインデックスが使用されるように設定。

ab (apache bench) で、多重度を 1 5 10 25 50 として 100秒間測定、というシェルスクリプトでベンチマークしてみる。 環境は OS=Kondara-2.1RC1, Apache 1.3.22, mod_perl 1.26, Apache::DBI 0.88, CPU=Duron 800MHz, Mem=384MB。

結果。数字は Requests/sec.

DB/多重度                          1         5        10       25       50
---------------------------------------------------------------------------
MySQL(InnoDB)                   86.02     85.15     84.62    78.41    68.23
MySQL(InnoDB) + Apache::DBI     98.06     98.37     98.68    96.32    81.93
MySQL(MyISAM) + Apache::DBI    102.28    103.07    102.69    98.95    82.95
PostgreSQL                      26.33     27.25     27.40    28.03    23.40
PostgreSQL + Apache::DBI        73.59     72.73     74.53    72.28    69.02

上記のグラフ。

MySQLは、更新の必要なユーザ情報テーブルを InnoDB にした場合と、MyISAM にした場合それぞれ測定 (ログ記録、郵便番号テーブルはどちらも MyISAM)。

MySQL は Apache::DBI を使わない場合でもかなりパフォーマンスが高い (=接続のコストが安い)。 PostgreSQL は Apache::DBI を使うと3倍近くパフォーマンスが向上する。

しかし、上記の結果、MySQL と PostgreSQL の差が (Apache::DBIを使うと) あまりでてないような。もしかしてこれ、Webアプリの方が CPU を食っていて、そっちがボトルネックになっているのではないか?

...

Celeron1.2G, 256MB RAM のマシン (Kondara-2.1RC1) を Webサーバとして投入。 DBは上と同じ、Duron 800, 384MB RAM のマシンでベンチマークを取り直す。 2台のマシン間は 100Base-T で接続。(スイッチングハブ使用)

WebサーバをDBサーバと別にした場合の結果。(Apache::DBI 使用。MySQL は MyISAM)

                     1         5        10        25        50
MySQL           143.46    164.31    168.25    163.53    158.67
PostgreSQL       96.15    132.37    138.17    137.75    139.47

ベンチマーク中、WebサーバのCPU使用率はほぼ 100%。DBサーバは、 MySQL の場合で 30% 前後。PostgreSQL だと 60-70% 程度。 つまりこれ、Webサーバのほうで律速になってたわけね・・・だめじゃん。

意地になったので Webサーバをもう1台投入することに。 Celeron 800, 256MB RAM のマシン (Kondara-2.1 β3)。よもや自宅で3台マシンを並べてベンチを走らせることになろうとは。

Webサーバ1台構成の時と同様の条件で、2台のWebサーバに同時に ab でリクエストを投げてみる。 下記は2台分の数値を合計した値。

                     1         5        10        25        50
MySQL           268.77    251.27    289.38    279.36    277.21
PostgreSQL      160.79    158.77    184.52    175.82    183.88

Webサーバを別マシンにした結果のグラフ。

これでようやく(?) MySQL と PostgreSQL に大差が。 MySQL は、1台構成のときよりも 1.7倍程度向上。 追加した2台目の方がCPUが遅いことを考えると、ほぼリニアに処理能力が上がっているとみてよい。 PostgreSQL は 1.3倍程度の向上にとどまっている。 ベンチマーク中の CPU 使用率は、MySQL だと 60-70%程度、PostgreSQL はほぼ100%。 MySQL ならば、もう1台 Web サーバを追加してもパフォーマンス向上が見込めるが、PostgreSQL ではおそらくほとんど向上しないはず。

こうなったら MySQL が悲鳴を上げるまで、Celeron466 のマシンやら C3 667 のやら、Libretto L1 やらを追加してやろうか・・・ と思わないでもなかったのだが、ベンチマークを1セット (上の結果での1行分) 実行するのに実時間で40分ほど掛かる。さすがにそればっかりやって週末を潰すのもなあ、ということで見送り。

...

追記。DBサーバ側の最大同時接続数を256まで増やした。その方法。

MySQL の場合、/etc/my.cnf に

set-variable    = max_connections = 255

を追加。

PostgreSQL の場合、/var/lib/pgsql/data/postgresql.conf に

max_connections = 256
shared_buffers = 512

を追加。

PostgreSQL でのベンチ実行時に、

[Sun Feb 24 05:54:31 2002] [error] DBI->connect(dbname=bench;host=192.168.0.3) failed: FATAL 1:  cann
ot open /var/lib/pgsql/data/PG_VERSION: Too many open files in system

というようなメッセージが。'Too many open files in system' てことは、ファイルディスクリプタが足りてない。 DBサーバマシン上で、

# echo 65536 > /proc/sys/fs/file-max

として、ファイルディスクリプタを増やす。

MySQL vs PostgreSQL ベンチマーク対決の続き。 MySQL でトランザクションをサポートする InnoDB を組み込んで ( --with-innodb )、SRPM からリビルドしたもので再実験。 gcc-2.96 でコンパイルしちゃだめよ、とダウンロードページでも警告されている ので、コンパイラには gcc-2.95-3 (/usr/bin/gcc_2_95_3) を使用。

ベンチマークスクリプトは これ。 Perl + DBI + DBD::(mysql | Pg) で計測する。

テーブルは「英辞郎」の、892,200語が入った

CREATE TABLE eijiro (word text, trans text);

このような単純なもの。word カラムにはインデックスを設定。

$ perl bench.pl
mysql(connect 0.03092) MyISAM
'create %'      0.00771 (select:fetch=0.00480:0.00291) 198 rows
'table a%'      0.00830 (select:fetch=0.00800:0.00030) 5 rows
'%text%'        2.75875 (select:fetch=2.75073:0.00802) 612 rows
'%integer%'     2.75166 (select:fetch=2.75120:0.00046) 16 rows

mysql(connect 0.00139) MyISAM (sorted) 
'create %'      0.01085 (select:fetch=0.00798:0.00287) 198 rows
'table a%'      0.00266 (select:fetch=0.00235:0.00031) 5 rows
'%text%'        3.19451 (select:fetch=3.18637:0.00814) 612 rows
'%integer%'     3.17291 (select:fetch=3.17246:0.00045) 16 rows

mysql(connect 0.00139) InnoDB
'create %'      0.01823 (select:fetch=0.01534:0.00289) 198 rows
'table a%'      0.00402 (select:fetch=0.00371:0.00031) 5 rows
'%text%'        8.31590 (select:fetch=8.30760:0.00830) 612 rows
'%integer%'     8.30163 (select:fetch=8.30118:0.00045) 16 rows

mysql(connect 0.00141) InnoDB (sorted) 
'create %'      0.02415 (select:fetch=0.02141:0.00274) 198 rows
'table a%'      0.01066 (select:fetch=0.01036:0.00030) 5 rows
'%text%'        23.06711 (select:fetch=23.05917:0.00794) 612 rows
'%integer%'     23.53014 (select:fetch=23.52968:0.00046) 16 rows

Pg(connect 0.07262) 
'create %'      0.02140 (select:fetch=0.01822:0.00318) 195 rows
'table a%'      0.00200 (select:fetch=0.00170:0.00030) 5 rows
'%text%'        4.02334 (select:fetch=4.01500:0.00834) 573 rows
'%integer%'     4.03201 (select:fetch=4.03154:0.00047) 16 rows

Pg(connect 0.01641)  (sorted) 
'create %'      0.02060 (select:fetch=0.01762:0.00298) 195 rows
'table a%'      0.00220 (select:fetch=0.00190:0.00030) 5 rows
'%text%'        4.03156 (select:fetch=4.02310:0.00846) 573 rows
'%integer%'     4.04510 (select:fetch=4.04463:0.00047) 16 rows

発行された SQL は、

SELECT * FROM eijiro WHERE word LIKE ? [ ORDER BY word ];

? が検索語句。

結果の見方は以下のとおり。

XXXX(connect YYYY)

XXXX のドライバ (mysql=MySQL, Pg=PostgreSQL) で、connect するのに YYYY 秒

'WWWWW' TTTTT (select:fetch=SSSSS:FFFFF) RRR rows

WHERE 句の LIKE の条件が WWWWW 、SELECT して fetch するのに合計 TTTTT 秒。 そのうち、SELECT (execute メソッドが完了するまで) に SSSSS 秒、 fetch (データを配列にコピーするだけ) に FFFFF 秒。 SELECT された行数は RRR 行。
MySQL と PostgreSQL で行数が違うのは、MySQL は大文字小文字を無視してマッチ、PostgreSQL は大文字小文字を区別してマッチ、という仕様のため。

(sorted)

ORDER BY word を指定した場合。

一応、同じベンチマークを数回繰り返したので、例によってデータベース自体はファイルキャッシュに乗っかってる状態。大体の傾向として、

• connect は MySQL が速い。PostgreSQL は 1クライアント/1サーバプロセス だが、MySQL はスレッドで処理するので、その違いか?
• インデックスが使われる SELECT では MyISAM > InnoDB =~ PostgreSQL
  90万レコードあっても、実用上問題になるほどの違いはない。
• インデックスが使われない(ベタサーチ) SELECT では MyISAM > PostgreSQL > InnoDB
• InnoDB はベタサーチが遅い。更に sort すると劇的に遅い。
• PostgreSQL はソートしてもしなくても、パフォーマンスに違いがでない ＜ VACUUM 直後だからか?

環境は以下のとおり。

• OS : Kondara MNU/Linux 2.1(RC1)
• CPU : AMD Duron 800MHz
• Mem : PC100 SD-RAM 384MB
• /etc/my.cnf
• /var/lib/pgsql/data/postgresql.conf

先日の Open Source Database Conference で MySQL に洗脳されて帰ってきたので、ちょっくら自宅で検証。

手元の Kondara-2.1 RC1 の MySQL-3.23.44 を起動してみるが、どうも日本語対応してないっぽい。 SPEC を見ても、configure に charset 関係の指定がしてなかったりして。だめじゃんこれ。

MySQL の本家 から 3.23.49 のソースを取ってきて、野良ビルド。

なんだかんだで時間を取られたのでろくに検証できなかった(^^; が、ちょっとだけ。 英辞郎 の、892,200語 のテキストデータ (約45MB) が手元にあったので、これで単純なテーブルを作ってみる。 元データは SJIS で CR+LF なので、EUC + LF (タブ区切り) に変換して、eijiro.txt として保存。

MySQL-3.23.49

> CREATE TABLE eijiro (word text, trans text);
> LOAD DATA INFILE '/***/***/eijiro.txt' INTO TABLE eijiro;
> CREATE INDEX eijiro_idx ON eijiro (word (255));

PostgreSQL-7.1.3

$ CREATE TABLE eijiro (word text, trans text);
$ COPY eijiro FROM '/***/***/eijiro.txt';
$ CREATE INDEX eijiro_idx ON eijiro (word);
$ VACUUM ANALYZE eijiro;

こんな風にデータを投入してインデックス作成。

まず、前方一致でインデックスが使われる検索をしてみる。

MySQL
time echo "select * from eijiro where word like 'happy %';" | mysql -u root test
real    0m0.068s (←数回実行した平均 以下同様)
0.068 - 0.023 = 0.045

PostgreSQL
time echo "select * from eijiro where word like 'happy %';" | psql eijiro
real    0m0.093s
0.093 - 0.038 = 0.055

MySQL
time echo "select * from eijiro where word like 'slash%' order by word;" | mysql -u root test
real    0m0.040s
0.040 - 0.023 = 0.017

PostgreSQL
time echo "select * from eijiro where word like 'slash%' order by word;" | psql eijiro
real    0m0.066s
0.066 - 0.038 = 0.028

MySQL
time echo "select count(*) from eijiro where word like 'admin%';" | mysql -u root test
real    0m0.030s
0.033 - 0.023 = 0.010

PostgreSQL
time echo "select count(*) from eijiro where word like 'admin%';" | psql eijiro
real    0m0.064s
0.064 - 0.038 = 0.026

time で計測してるから、これはクライアントアプリ(mysql, psql) の起動時間も含めて測ってるわけで。 単に (mysql, psql) を起動して終了するまでを time で測ったのが、 mysql = 0.023, psql = 0.038 (これも平均)。 その値を引いた方が、実時間に近いはず。

前評判通り、MySQL のほうが高速。データの fetch を伴う select ではあまり差がないようだが(とはいえ 1.5倍程度はあるから大きいといえば大きい)、 count(*) では差が2倍以上に開く。

次に、インデックスの使われないベタ検索。

MySQL
time echo "select * from eijiro where word like '%computer%';" | mysql -u root test
real    0m3.641s
3.641 - 0.023 = 3.618

PostgreSQL
time echo "select * from eijiro where word like '%computer%';" | psql eijiro
real    0m4.509s
4.509 - 0.038 = 4.471

MySQL
time echo "select count(*) from eijiro where word like '%program%';" | mysql -u root test
real    0m2.722
2.722 - 0.023 = 2.699

PostgreSQL
time echo "select count(*) from eijiro where word like '%program%';" | psql eijiro
real    0m4.026
4.026 - 0.038 = 3.988

MySQL
time echo "select count(*) from eijiro;" | mysql -u root test
real    0m0.025
0.025 - 0.023 = 0.002

PostgreSQL
time echo "select count(*) from eijiro;" | psql eijiro
real    0m2.144
2.144 - 0.038 = 2.106

ベタ検索も同様の結果。count で差が開く傾向。 全件の count で1000倍もの差が出たが、これはおそらく、MySQL は常にテーブルのデータ件数を別テーブルに管理していて、それを返す。 PostgreSQL は毎回全件カウントしなおし、という実装の違いが出ているだけだと思う。

まあこの実験は読み込み only だからデータベースのファイルはおそらく全部キャッシュに乗っているし、クライアントが同時接続して複数の query を投げることは考慮していないし、本当に参考のとっかかりに過ぎないような実験。 実際の(実務的な)パフォーマンスを測るのだったら、Web アプリを実装して、Apache Bench で同時接続数十の負荷をかけて・・・とかやるべきでしょうな。

思ったよりも PostgreSQL と MySQL の差が開かなくて、ちょっと拍子抜けしてたりもする。 数倍ぐらい軽く違うのかと思ってた(^^;