昨日の夜思い立って作りはじめた小さなプログラムが、問題はあるものの、一応動くようになりましたので。

PostgreSQLについてくるpgbenchをPython DB API(python.org)で移植してみた。

興味のある方は、dbench.pyをダウンロードしてみて下さい。

特長は以下の通り。

• 1つのプログラムでMySQL、PostgreSQL、sqliteに対応

→ 同じコード、同じSQL文で測定することで、比較がしやすくなる*はず*

• pgbenchと同じ負荷をかける(一部制約あり：後述)
• sqliteの:memory:(メモリDB)でも測定可能(:memory:の場合、-iで初期化後にそのままベンチマークが走るように書いた)

弱点は以下の通りかな。

• Pythonだからインタプリタ側がネックになるかも

→ そんなに遅くない気がする。気がするだけ。

• 3つのDBMSに対応させるため、SQL文やAPIの呼び出し方に制約がある(後述)
• MySQL以外では複数コネクションで動かない(致命的。理由の推測は後述)

使い方は以下の通り。引き数を指定しないで実行するとusageが表示されます。

DBの初期化

 # ./dbench.py -i bench
drop/creating tables...
creating branch/tellers...
creating account... (may take while)
creating indexes...
done

実行

 # ./dbench.py bench
DB type: sqlite
options: TPC-B (sort of)
nclients: 1 nxacts: 10 dbName: bench scale: 1 branch: 1 teller: 10 accounts: 100000
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = xxx.xxxxxx (including connections establishing)
tps = xxx.xxxxxx (excluding connections establishing)

1接続で10トランザクションでは測定と呼べない規模かと思いますが、pgbenchのデフォルトがそうなので、しょうがないです。勘弁して下さい。

PostgreSQLとsqliteで複数コネクションに対応していない理由ですが、だいたいストーリーはこんなもんでしょう。2コネクションの場合は以下のように2つのトランザクションが並列に、しかしプログラム的には1スレッドで逐次的に実行されていきます。

• 1:BEGIN
• 2:BEGIN
• 1:UPDATE accounts
• 2:UPDATE accounts
• 1:SELECT abalance
• 2:SELECT abalance
• 1:UPDATE tellers
• 2:UPDATE tellers
• 1:UPDATE branches
• 2:UPDATE branches
• 1:INSERT history
• 2:INSERT history
• 1:COMMIT
• 2:COMMIT

sqliteの場合、トランザクション中はDB全体にロックがかかります。恐らく、1:BEGINの後のDBの更新時に書き込みロックをかけて、COMMITまではかかったままになっていると思います。次の2:BEGINで別の接続も同じような状態になり、次の1:UPDATEでロックがかかり、2:UPDATEは待たされます。しかし1スレッドで実行しているので、ここで処理が止まったらベンチマークは次に進めなくなり、デッドロックという形に見えます。

PostgreSQLのトランザクションも似たようなことになっているのでしょう。pgbenchを移植しただけなんですけど、そして、pgbenchはもちろん複数の接続に対応しています。

なぜこういうことになるのかと言うと、pgbenchでは(何と)、接続のソケットディスクリプタを取り出して、selectにかけて入力を受けつける状態の接続のみ次の処理に進むようになっています。UPDATEの結果が戻ってきたところでselectが反応するようになっていて、その結果、1つ目のトランザクションが先に進み、1:COMMITの後に2つ目の接続のselectが反応、2つ目の接続のトランザクションの処理が続くのです。Python DB APIにはそんな機能はないので、pgdbでは同様の処理を書くことはできません。pg(Python DB APIに準拠しないライブラリ)なら同じように書けるのかもしれませんが。

この仮説が正しいとすると、1つの解決策としては、マルチスレッドにしてしまうというのがあります。1接続1スレッドにして実行すれば、結果を待たされているスレッドより先に別のスレッドを実行してくれるでしょう。pgbenchがselectを使う書き方をしたのは、恐らくpgbenchが書かれた時代のPostgreSQLがマルチスレッドに対応していなかったためではないかと私は邪推しています。

ちなみにPython DB APIで規定されているthreadsafetyの値は3つのDBMSで全て1(Threads may share the module, but not connections.)でした。従って、スレッド間で接続やカーソルを共有せず、1接続1スレッドにして実装することは可能です。各モジュールの主張する値が正しければ、の話ですが(笑)

一方、MySQLでは現在の実装でも複数コネクションで動きます。理由を考えると、これも完全に推測なんですが、私は2つの仮説を立てました。

• MySQLのトランザクション処理は見せかけで、ロックがかかっていない
• トランザクションのテーブル更新はCOMMIT時に実行され、COMMIT中のみロックがかかっている

まあ、後者であることを期待しています。私は決してDBに詳しいわけではないですけれど、UPDATEのところでスレッドをブロックする実装よりも正しいと思う(し、sqliteやPostgreSQLにもそれを期待していた)。

pgbenchとdbench.pyで同じ条件で測定してみると、かなり値が近いみたいだ(ノートPCのローカルディスクなので遅い値ですが)。ただ、条件がいろいろあるようで、何度かやっているとまた変わるんだけど(笑)。ロード時間はdbench.pyのほうが長い。まあ、せいぜい10万行程度なので、もともとそんなにかかってないけどね。

で、今のところ、MySQLの圧勝。sqliteはとても遅いが:memory:はさすがに速い。プログラムを改良していったらまた変わると思う。デフォルトをsqliteにしたのは設定が不要だからです。

MySQLの場合は、ユーザを作って、mysqladmin createでDBを作る必要があります。

PostgreSQLの場合は、createuserでユーザを作って、createdbでDBを作る必要があります。

 # pgbench -i bench
  :
 # pgbench -t 1000 bench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 162.716480 (including connections establishing)
tps = 163.218859 (excluding connections establishing)

 # ./dbench.py -D postgres -i bench
  :
 # ./dbench.py -t 1000 -D postgres bench
DB type: postgres
options: TPC-B (sort of)
nclients: 1 nxacts: 1000 dbName: bench scale: 1 branch: 1 teller: 10 accounts: 100000
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 159.976499 (including connections establishing)
tps = 160.160977 (excluding connections establishing)

 # ./dbench.py -D mysql -i bench
  :
 # ./dbench.py -t 1000 -D mysql bench
DB type: mysql
options: TPC-B (sort of)
nclients: 1 nxacts: 1000 dbName: bench scale: 1 branch: 1 teller: 10 accounts: 100000
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 393.225941 (including connections establishing)
tps = 393.393015 (excluding connections establishing)

 # ./dbench.py -i bench
  :
 # ./dbench.py -t 1000 bench
DB type: sqlite
options: TPC-B (sort of)
nclients: 1 nxacts: 1000 dbName: bench scale: 1 branch: 1 teller: 10 accounts: 100000
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 39.942188 (including connections establishing)
tps = 39.942984 (excluding connections establishing)

 # ./dbench.py -t 1000 -i :memory:
  :
DB type: sqlite
options: TPC-B (sort of)
nclients: 1 nxacts: 1000 dbName: :memory: scale: 1 branch: 1 teller: 10 accounts: 100000
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 565.587557 (including connections establishing)
tps = 565.598692 (excluding connections establishing)

その他、参考までに、それぞれのライブラリの違いや、気になったことを以下に記しておきます。

• PostgreSQLでは失敗したexecute()の後にはcommit()を呼ばなくてはならないらしい。dropが失敗したときなど
• PostgreSQLでは結果が空のカーソルにfetchに行くと例外が発生する
• sqliteではexecuteのフォーマットで%d変換等が使える。便利に使っていたがMySQLやPostgreSQLでは使えなかった。こいつらケチだな！
• しかもPostgreSQLの%s変換はちょっとおかしい？？ → どうせ空文字列だから、結局使わないことにした
• DB APIはconnectの引き数はできるところだけでいいから統一したほうがいいと思う。DB名の引き数がdbname(sqlite)、db(MySQL)、database(PostgreSQL)と三者三様だったときは正直、引いた
• autocommitの有無が有効なのはsqliteのみ？
• DB APIでcopy from stdinの処理を書く方法がよくわからない → しょうがないのでちまちま全部insertすることにした。これで初期化はかなり遅くなっているはず
• sqliteはalterでprimary keyの属性を後から付加することができないみたい

→ create tableでprimary keyを設定したため、初期化はまんべんなく遅くなっているかも。

• PostgreSQLではVACUUMを発行したいのだが、トランザクション中はダメとか言われてうまくいかない
• 初期化の表示でstdoutをflushし忘れてます