yohgaki's blog

PostgreSQLカンファレンス2008が今週金曜日(6/6)に開催されます。

http://www.postgresql.jp/events/postgresql-conference-2008

例年通り参加費が必要ですが懇親会費込みです。

参加費：
カンファレンス ならび に懇親会 4,000 円
チュートリアルも含むカンファレンス ならびに 懇親会 10,000 円

今回のカンファレンスの目玉は色々ありますが、その一つはチュートリアルセッションです。まだ、空席が残っているようなのでライセンスもBSDでMySQLよりも使いやすいPostgreSQLを始めてみたい方には良いチャンスだと思います。新人研修の一環としても良いと思います。

* MySQLユーザのためのPostgreSQL入門
（リナックスアカデミー 学校長 濱野 賢一朗 氏）

興味がある方は是非カンファレンスにお越し下さい。

最近のPostgreSQL, MySQL, SQLiteのパフォーマンスはどうかな?と言うことで非常に簡単なベンチマークをしてみました。

デフォルト状態のデータベースに郵便番号データ（12万件とちょっと）をINSERTしてみました。フラグを除く全てのフィールドをテキスト型として定義し、全てのフィールドを挿入しました。旧番号と現行番号にインデックスを付けています。スクリプトはPHPで記述し、DBが動作しているPC上からPHP 5.2.4で実行しました。1レコードが分割されている場合などは無視して挿入しているので12万2000レコードになりました。

PC

CPU: PentiumD 2.8GHz
Memory: 3GB
HDD: PATA

DBMS

MySQL: 5.0.45
PostgreSQL: 8.2.4
SQLite: 3.4.0 (PDO)

実行結果(12万行INSERTの実行時間）

InnoDb: 130.70663690567
MyISAM: 131.24672317505
Postgres: 159.47350597382
SQLite: 676.43534302711

MySQLもPostgreSQLもチューニングは一切無しで実行しています。

非同期クエリを利用するとPostgreSQLもInnoDb, MyISAMと同じくらいの速度になりました。

どのDBもペンチマーク中はディスク待ちの状態でCPU時間はあまり使っていませんでした。

MySQLとPostgresは概ね予想通りの結果でしたが、SQLiteが異常に遅いのでPDOを使わないで計測してみましたがあまり変わりませんでした。トランザクションかな？と思いBEGIN, COMMITを付けてみましたが変わりませんでした。

INSERTしたレコードをランダムにSELECTしてみました。番号はmt_rand関数で生成したのでほとんどがINDEXにヒットしないので、インデックスを利用した検索の最悪のケースのテストになります。単純にテーブルから検索をするだけです。永続的接続を利用しています。

Webサーバ：Apache 2.2.6
クライアント: ab -n 10000 -c 10　（別PCから実行。Athlon64 3500+/3GBメモリ）

パフォーマンス：リクエスト/秒

Postgres: 947.58
SQLite: 1096.00
MySQL(MyISAM): 1190.35
MySQL(InnoDb): 1245.85

概ね予想通りの結果ですが、SQLiteが思っていたより遅いです。PostgreSQLは接続のネゴシエーション処理が重いので非永続的接続を利用するとかなりパフォーマンスが落ちます。システム全体でDBへの接続数を上手に制御できていない場合はpgpoolを利用するとパフォーマンス（システム全体のスループット）が改善することがこの事からも分かります。

「Postgresでこんな数字でないよ」という方、pg_pconnectで試してみましょう。コネクションプーリングが無いと話になりません。

SQLite 3.3は結構変わりますね。
Alphaなのでデフォルトの仕様は変わるかもしれませんが3.3は3.x系列のファイルは読めるが、3.3より以前のバージョンのSQLiteでは読めない、という点には注意が必要ですね。

* CHECK constraints are now enforced.

* The IF [NOT] EXISTS syntax of MySQL is now recognized on
CREATE/DROP TABLE/INDEX statements.

* DESC indices really are descending now. The DESC keyword
on index definitions used to be ignored.

* The database file format has changed slightly to more
compactly represent boolean values and to support DESC
indices. Version 3.3.0 will read and write all prior
version 3 databases. But new databases created by
version 3.3.0 will not be readable by older versions
of SQLite. If this is a problem for your application,
compile SQLite using

-DSQLITE_DEFAULT_FILE_FORMAT=1

and then version 3.3.0 will create new databases in
the legacy format understood by all prior versions of
SQLite. DESC indices only work in the new format.

* SQLite now distinguishes between REAL and INTEGER columns
and attempts to make appropriate conversions.

* The OS-interface layer has been modified for greater
flexibility and control of custom ports and implementations.

* SQLite now responses better to out-of-memory errors. The
library will recover and reset itself automatically. There
is no longer a need to call sqlite3_global_recover(). The
new sqlite3_enable_memory_management() API can be used to put
SQLite into a mode where it will automatically try to reduce
its database cache size when it comes under memory pressure.

* The database cache and parsed schema information can now
optionally be shared between two or more database connections.
This can be used to reduce I/O and to improve concurrency.
On a database using a shared cache, you can specify
READ UNCOMMITTED isolation as an option (the default is
SERIALIZABLE). With READ UNCOMMITTED, a reader will not
block or be blocked by a writer and you will never get
an SQLITE_BUSY error on a read.

mysql> SHOW ENGINES;
+------------+---------+------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+------------------------------------------------------------+
| MyISAM | DEFAULT | 高性能のMySQL 3.23以降のデフォルト・エンジン|
| HEAP | YES | MEMORY のまたの名前 |
| MEMORY | YES | Hashベースで、メモリー内に格納、テンポラリ・テーブルに適す。|
| MERGE | YES | 同一のMyISAMテーブルのコレクション　 |
| MRG_MYISAM | YES | MERGE のまたの名前 |
| ISAM | NO | もう使われないストレッジ・エンジン、MyISAM を使用のこと。 |
| MRG_ISAM | NO | もう使われないストレッジ・エンジン、MERGEを使用のこと。 |
| InnoDB | YES | トランザクション、行レベルのロッキングとフォーリンキーのサポート |
| INNOBASE | YES | INNODB のまたの名前 |
| BDB | YES | トランザクションとページレベルのロッキングをサポート |
| BERKELEYDB | YES | BDB のまたの名前 |
| NDBCLUSTER | NO | クラスタ、耐障害性のメモリー・ベースのテーブル |
| NDB | NO | NDBCLUSTER のまたの名前 |
| EXAMPLE | NO | ストレッジエンジンの例 |
| ARCHIVE | NO | アーカイブ・ストレッジエンジン |
| CSV | NO | CSV ストレッジエンジン |
+------------+---------+------------------------------------------------------------+

メモリ上にだけ存在するデータベースはPostgreSQLにも欲しいですね。CREATE TEMP TABLEを拡張して実装とか?

ちなみにSQLiteはメモリ上にデータベースを持てます。MSDEもメモリ上にデータベースを持てたと思います。もう何年も前になりますがIMDB（In Memory Database）として話題になっていました。

PostgreSQLをハックするならIMDBの実装しかない?!