2013年8月7日水曜日

プリペアードクエリの効果

お疲れ様です。稲垣です。

今回は、PostgreSQL のプリペアードクエリについてご紹介します。

■はじめに

PostgreSQLはPostgreSQL7.3(2002年 11月)からプリペアードクエリをサポートしています。プリペアードクエリは SQL 文の解析と実行プランの作成を一度だけ行う為、PostgreSQL サーバの負荷を軽減し、クエリをより高速に実行します。

■プリペアードクエリの効果

PostgreSQL には pgbench と呼ばれる簡単なベンチマークツールが付属しています。PostgreSQL 8.4 の pgbench から -M オプションでクエリタイプ(simple, extended, prepared)を選べるようになりました。これにより簡単にプリペアードクエリの効果を測定できるようになりました。

【-M オプション】

  • simple - libpq の PQexec() を用いてクエリを実行(デフォルト)
  • extended - libpq の PQexecParams() を用いて実行
  • prepared - libpq の PQexecPrepared() を用いて実行

pgbench は TCP-B 相当のベンチマークを持っています。実際のアプリケーションに近いベンチマークを期待できるはずです。

pgbench は SELECT クエリのみのベンチマーク(-S オプション)もサポートしています。参照のみの場合のパフォーマンスの違いも興味深いので、-S オプション付きのテストも行います。

今回は実際アプリケーションとして PHP のセッションデータを PostgreSQL に保存するモジュールである session_pgsql のダイナミッククエリ版とプリペアードクエリ版を用いてどのような効果があるか確認してみます。

ベンチマークは複数台のコンピュータを利用する方が好ましいですが、今回は一台のコンピュータのみ利用します。

  • CPU : Core2 Quad Q9400 (2.66GHz)
  • MEM : 8GB
  • HDD : SATA 1TB (7200rpm)
  • OS : Linux 2.6
  • PostgreSQL : 9.1
  • PHP : 5.4
  • session_pgsql : GitHub版

https://github.com/yohgaki/session_pgsql/tree/dynamic_query
(動的クエリ)

https://github.com/yohgaki/session_pgsql/tree/prepared_query
(プリペアードクエリ)

[postgresql.conf]

shared_buffers = 1024MB
fsync = off

Web のセッションデータを取り扱うので fsync は off にしています。セッションデータに高い耐障害性は必要ないので、session_pgsql のベンチマーク結果がより現実的なデータとなるように off に設定しました。

■ベンチマークで実行するクエリ

今回の実行されるクエリは postgresql.conf の log_statement=all に設定して取得しました。実行されるクエリは次の通りです。

◇pgbench -M simple

LOG:  statement: BEGIN;
LOG:  statement: UPDATE pgbench_accounts SET abalance = abalance + -3911 WHERE aid = 22225;
LOG:  statement: SELECT abalance FROM pgbench_accounts WHERE aid = 22225;
LOG:  statement: UPDATE pgbench_tellers SET tbalance = tbalance + -3911 WHERE tid = 7;
LOG:  statement: UPDATE pgbench_branches SET bbalance = bbalance + -3911 WHERE bid = 1;
LOG:  statement: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (7, 1, 22225, -3911, CURRENT_TIMESTAMP);
LOG:  statement: END;

◇pgbench -M extended

LOG:  execute : BEGIN;
LOG:  execute : UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
DETAIL:  parameters: $1 = '412', $2 = '90211'
LOG:  execute : SELECT abalance FROM pgbench_accounts WHERE aid = $1;
DETAIL:  parameters: $1 = '90211'
LOG:  execute : UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
DETAIL:  parameters: $1 = '412', $2 = '3'
LOG:  execute : UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
DETAIL:  parameters: $1 = '412', $2 = '1'
LOG:  execute : INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP);
DETAIL:  parameters: $1 = '3', $2 = '1', $3 = '90211', $4 = '412'
LOG:  execute : END;

◇pgbench -M prepared

LOG:  execute P0_7: BEGIN;
LOG:  execute P0_8: UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
DETAIL:  parameters: $1 = '4971', $2 = '72543'
LOG:  execute P0_9: SELECT abalance FROM pgbench_accounts WHERE aid = $1;
DETAIL:  parameters: $1 = '72543'
LOG:  execute P0_10: UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
DETAIL:  parameters: $1 = '4971', $2 = '9'
LOG:  execute P0_11: UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
DETAIL:  parameters: $1 = '4971', $2 = '1'
LOG:  execute P0_12: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP);
DETAIL:  parameters: $1 = '9', $2 = '1', $3 = '72543', $4 = '4971'
LOG:  execute P0_13: END;

◇pgbench -M extended -S

LOG:  statement: SELECT abalance FROM pgbench_accounts WHERE aid = 9800;

◇pgbench -M prepared -S

LOG:  execute P0_2: SELECT abalance FROM pgbench_accounts WHERE aid = $1;
DETAIL:  parameters: $1 = '81557'

◇session_pgsql(動的クエリ)

LOG:  statement: SELECT relname FROM pg_class WHERE relname = 'php_session';
LOG:  statement: SELECT relname FROM pg_class WHERE relname = 'php_app_vars';
LOG:  statement: BEGIN;
LOG:  statement: SELECT sess_expire, sess_counter, sess_error, sess_warning, sess_notice, sess_data, sess_custom, sess_created, sess_modified, sess_addr_created, sess_addr_modified FROM php_session WHERE sess_id = 'i8hnijfj2opkqb6je6aqku3fp3';
LOG:  statement: UPDATE php_session SET sess_data = 'i|i:2;', sess_modified = 1345532444, sess_addr_modified = '', sess_expire = 1345533884 , sess_counter = 2, sess_error = 0, sess_warning = 0 , sess_notice = 0 WHERE sess_id = 'i8hnijfj2opkqb6je6aqku3fp3';
LOG:  statement: END;

◇session_pgsql(静的クエリ)

LOG:  statement: SELECT relname FROM pg_class WHERE relname = 'php_session';
LOG:  statement: SELECT relname FROM pg_class WHERE relname = 'php_app_vars';
LOG:  execute READ_BEGIN: BEGIN;
LOG:  execute READ_SELECT: SELECT sess_expire, sess_counter, sess_error, sess_warning, sess_notice, sess_data, sess_custom, sess_created, sess_modified, sess_addr_created, sess_addr_modified FROM php_session WHERE sess_id = $1;
DETAIL:  parameters: $1 = 'i8hnijfj2opkqb6je6aqku3fp3'
LOG:  execute WRITE_DELETE: DELETE FROM php_session WHERE sess_id = $1;
DETAIL:  parameters: $1 = 'i8hnijfj2opkqb6je6aqku3fp3'
LOG:  execute WRITE_INSERT: INSERT INTO php_session (sess_id, sess_name, sess_created, sess_addr_created, sess_modified, sess_expire, sess_data, sess_counter, sess_error, sess_warning, sess_notice, sess_custom) VALUES ($1, $2, $3, $4, $5, $6, $7, 1, 0, 0, 0, $8);
DETAIL:  parameters: $1 = 'i8hnijfj2opkqb6je6aqku3fp3', $2 = 'PHPSESSID', $3 = '1345532372', $4 = '', $5 = '1345532372', $6 = '1345533812', $7 = 'i|i:1;', $8 = NULL
LOG:  execute WRITE_END: END;

■ベンチマーク

今回は pgbench と Apache Web サーバ付属の ab コマンドを使います。どちらもテスト結果にはゆらぎがあるので、三回実行して中間の値を掲載します。

【ベンチマーク結果】

◇pgbench -M simple

[user1@dev pgbench]$ ./pgbench -c 10 -t 1000 -M simple -p 5491
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 1884.224575 (including connections establishing)
tps = 1894.097953 (excluding connections establishing)

◇pgbench -M extended

[user1@dev pgbench]$ ./pgbench -c 10 -t 1000 -M extended -p 5491
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: extended
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 1706.512598 (including connections establishing)
tps = 1714.728506 (excluding connections establishing)

◇pgbench -M prepared

[user1@dev pgbench]$ ./pgbench -c 10 -t 1000 -M prepared -p 5491
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 2505.903282 (including connections establishing)
tps = 2524.246014 (excluding connections establishing)

◇pgbench -M simple -S (SELECTのみ)

[user1@dev pgbench]$ ./pgbench -c 10 -t 1000 -M simple -p 5491 -S
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 19441.371628 (including connections establishing)
tps = 20546.537908 (excluding connections establishing)

◇pgbench -M prepared -S (SELECTのみ)

[user1@dev pgbench]$ ./pgbench -c 10 -t 1000 -M prepared -p 5491 -S
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 27275.306029 (including connections establishing)
tps = 29525.176118 (excluding connections establishing)

◇session_pgsql (動的クエリ)

[user1@dev pgbench]$ ab -c 10 -n 5000 http://127.0.0.1:1122/session_pgsql.php?PHPSESSID=nb1arr0ducdldhfcj4itsgrlv3
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 500 requests
Completed 1000 requests
Completed 1500 requests
Completed 2000 requests
Completed 2500 requests
Completed 3000 requests
Completed 3500 requests
Completed 4000 requests
Completed 4500 requests
Completed 5000 requests
Finished 5000 requests


Server Software:        Apache/2.2.22
Server Hostname:        127.0.0.1
Server Port:            1122

Document Path:          /session_pgsql.php?PHPSESSID=nb1arr0ducdldhfcj4itsgrlv3
Document Length:        104 bytes

Concurrency Level:      10
Time taken for tests:   3.188 seconds
Complete requests:      5000
Failed requests:        0
Write errors:           0
Total transferred:      2205000 bytes
HTML transferred:       520000 bytes
Requests per second:    1568.56 [#/sec] (mean)
Time per request:       6.375 [ms] (mean)
Time per request:       0.638 [ms] (mean, across all concurrent requests)
Transfer rate:          675.52 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       1
Processing:     1    6   3.2      6      36
Waiting:        1    6   3.1      5      36
Total:          1    6   3.2      6      36

Percentage of the requests served within a certain time (ms)
  50%      6
  66%      7
  75%      8
  80%      8
  90%     10
  95%     12
  98%     15
  99%     17
 100%     36 (longest request)

◇session_pgsql (プリペアードクエリ)

[user1@dev pgbench]$ ab -c 10 -n 5000 http://127.0.0.1:1122/session_pgsql.php?PHPSESSID=nb1arr0ducdldhfcj4itsgrlv3
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 500 requests
Completed 1000 requests
Completed 1500 requests
Completed 2000 requests
Completed 2500 requests
Completed 3000 requests
Completed 3500 requests
Completed 4000 requests
Completed 4500 requests
Completed 5000 requests
Finished 5000 requests


Server Software:        Apache/2.2.22
Server Hostname:        127.0.0.1
Server Port:            1122

Document Path:          /session_pgsql.php?PHPSESSID=nb1arr0ducdldhfcj4itsgrlv3
Document Length:        105 bytes

Concurrency Level:      10
Time taken for tests:   2.813 seconds
Complete requests:      5000
Failed requests:        0
Write errors:           0
Total transferred:      2210884 bytes
HTML transferred:       525210 bytes
Requests per second:    1777.60 [#/sec] (mean)
Time per request:       5.626 [ms] (mean)
Time per request:       0.563 [ms] (mean, across all concurrent requests)
Transfer rate:          767.59 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       1
Processing:     1    5   2.9      5      25
Waiting:        1    5   2.9      5      25
Total:          1    6   2.9      5      25

Percentage of the requests served within a certain time (ms)
  50%      5
  66%      6
  75%      7
  80%      8
  90%      9
  95%     11
  98%     13
  99%     15
 100%     25 (longest request)

■ベンチマークの分析

pgbench のベンチマーク結果をまとめた表が次の表です。

種別 結果
pgbench -M simple tps = 1894.097953 (excluding connections establishing)
pgbench -M extended tps = 1714.728506 (excluding connections establishing)
pgbench -M prepared tps = 2524.246014 (excluding connections establishing)
pgbench -M simple -S(SELECTのみ) tps = 20546.537908 (excluding connections establishing)
pgbench -M prepared -S(SELECTのみ) tps = 29525.176118 (excluding connections establishing)

SELECT のみの結果が桁違いに高速ですが、通常のベンチマークの場合は一つのトランザクション中に UPDATE や INSERT を行い、5 つの SQL 文を実行しています。

SELECT のみ場合は、トランザクションなしで一つの SELECT 文だけを実行しています。この為、通常のベンチマークと SELECT のみのベンチマークは直接比較できません。SELECT のみの場合に、10 倍以上の性能となる訳ではない事に注意してください。

pgbench -M extended が最も遅くなる事は予想通りの結果です。-M extended は PQexecParams を利用している為、無名のプリペアードクエリを毎回作成して実行しています。この為、SQL 文を直接実行(-M simple)するクエリより大きなオーバーヘッドが PostgreSQL にかかります。

プリペアードクエリと直接実行した場合を比較すると 33% 高速でした。クエリの解析とクエリプランの作成がかなり大きな負荷となっている事が分かります。

SELECT のみの場合、さらに違いは顕著になり 44% 高速になっています。挿入、削除、更新は参照に比べ、一貫性を保証するための処理が多く必要になります。参照のみ場合、CPU やディスクリソースをあまり使わない為、クエリの解析とクエリプランの作成の負荷が比較的に大きくなることは予想通りの結果です。

session_pgsql の実行結果をまとめた表が次の表です。

種別 結果
session_pgsql (動的クエリ) Requests per second: 1568.56 [#/sec] (mean)
session_pgsql (プリペアードクエリ) Requests per second: 1777.60 [#/sec] (mean)

pgbench は TPC-B 相当のベンチマークを行うため、実際のアプリケーションを実行した場合のベンチマークマークとして参考になります。しかし、session_pgsql の場合、pgbench の場合よりもかなり少ない 13% の高速化にとどまっています。

ここではベンチマークとその分析は省略しますが、session_pgsql を利用したテストのボトルネックは PostgreSQL にありましたが、pgbench の様に約 3割の高速化はできませんでした。

これは予想通りの結果です。session_pgsql は pgbench 同様に PostgreSQL サーバへの接続を維持し、次のクエリ送信に同じ接続を再利用しています。しかし、Webサーバは毎回接続を行なっています。リクエストを受け付ける度に Apache も PHP も初期化処理が必要です。

つまり、クエリだけを送信している pgbench とは異なり、他に多くの処理を行なっている Web アプリケーションの場合、データベースがボトルネックであってもデータベースが高速化した割合と同じ性能向上は期待できない場合が多いです。

■まとめ

プリペアードクエリがデータベースシステムの性能向上にかなり有効に機能する事が分かりました。ただし、データベースがアプリケーションのボトルネックである単純なアプリケーションであっても、データベースを高速化しただけでは、同じ割合で速度が向上する事は無い事も分かりました。

例えデータベース処理が 2 倍に高速化しても、アプリケーション全体におけるデータベース処理時間が 1/2 であれば、25% の高速化しか達成できないので当然の結果です。

プリペアードクエリは確実にシステムの高速化に役立ちます。しかし、プリペアードクエリ風の PQexecParams() を利用する API は反対にパフォーマンス低下の原因となります。PHP の場合、PQexecParams() を利用した API は pg_query_params() を多用すると性能に悪影響を与えます。

プリペアードクエリを利用しても PQprepare でプリペア済みの文を再利用しないと、性能向上は期待できません。

データベースアクセス抽象化レイヤーの種類や設定によっては、高速化する為にプリペアードクエリを使っているつもりでも、実際には PQexecParams() を利用している状態と変わらなくなってしまう場合があるので注意が必要です。

■リファレンス

http://www.postgresql.jp/document/9.1/html/libpq-exec.html#LIBPQ-EXEC-MAIN
https://github.com/yohgaki/session_pgsql

0 件のコメント:

コメントを投稿