holly's wiki

$PGSRC/contribとしてある。yumでもpgsql-contribっぽい名前であるはず

benchmark tool

公式なbenchmark toolではないらしい。そしてTCP-Bなテストをするらしい。わかんねー

TPC-A	銀行の窓口業務をモデルにしたOLTP（オンライントランザクション処理）性能を測定する
TPC-B	オンライン処理の負荷をテスト内容から除外する
TPC-C	卸売会社の受発注業務をモデルとしている
TPC-D	意思決定支援システムの性能を評価する

どっかからぱくったのをそのまま

銀行のATMを想定している。ATMから送られたデータに対して、データベースアクセス、結果の返信などの一連の処理を行う
TPC-Aは、一連の処理手順に人間の思考時間が入るというのが特徴であり、オンライントランザクションを処理するイメージである。

TPC-Aと同様にATMを想定しているが、人間の試行時間は考慮せず、専らデータベースアクセスだけを評価対象にしている。つまり、バッチ処理を行うイメージである。

TPC-AやTPC-Bの改良版。TPC-Cではさらに複雑な処理やデータベースアクセスを想定している。業務端末からのデータベースに対するトランザクションをシミュレートすることで、現実的な企業システムとしてのコンピュータ処理能力を、適切に評価する。OLTP用のベンチマークとして使われる。
注文入力や納品、支払記録、在庫管理など、実際の企業活動で必要とされる様々なオペレーションを含んでいる。

大規模なデータベースに対して多種類の問い合わせを同時に発生させてデータベース性能を測定する。顧客情報、取引先情報、オーダー履歴などから構成されるデータベースに17種類の問合せが用意されている。
個々の問合せ時間の貴下平均を測るPower Metric（QppD）と算術平均を取るThroughput（QthD）の2種類がある。

# PGSRCはソースディレクトリ
cd $PGSRC/contrib/pgbench
make
make install

基本オプションは同じ

Usage:
  pgbench [OPTIONS]... [DBNAME]

Initialization options:
  -i           invokes initialization mode
  -F NUM       fill factor
  -s NUM       scaling factor

Benchmarking options:
  -c NUM       number of concurrent database clients (default: 1)
  -C           establish new connection for each transaction
  -D VARNAME=VALUE
               define variable for use by custom script
  -f FILENAME  read transaction script from FILENAME
  -j NUM       number of threads (default: 1)
  -r           report average latency per command
  -l           write transaction times to log file
  -M {simple|extended|prepared}
               protocol for submitting queries to server (default: simple)
  -n           do not run VACUUM before tests
  -N           do not update tables "pgbench_tellers" and "pgbench_branches"
  -s NUM       report this scale factor in output
  -S           perform SELECT-only transactions
  -t NUM       number of transactions each client runs (default: 10)
  -T NUM       duration of benchmark test in seconds
  -v           vacuum all four standard tables before tests

まずこれをしないといけない。-iでテストで使うDBを初期化する

pgbench -i <DB>
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
70000 tuples done.
80000 tuples done.
90000 tuples done.
100000 tuples done.
set primary key...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"
vacuum...done.

実行後は以下のテーブルが作成される

             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+-------
 public | pgbench_accounts | table | kurt
 public | pgbench_branches | table | kurt
 public | pgbench_history  | table | kurt
 public | pgbench_tellers  | table | kurt
(4 rows)

それぞれデータの件数は以下となる
※scaling factor = 1 データサイズの規模と考えればよい

pgbench_accounts	100000(100000 * scaling factor)
pgbench_branches	1(scaling factor)
pgbench_history	0
pgbench_tellers	10

初期化時に-sを指定することにより、scaling factorの値を変更することが可能

pgbench -i -s <NUM> <DB>

# -c 同時接続数
# -t 1clientごとのトランザクション数
pgbench -c <CLIENT NUM> -t <TRANSACTION NUM> <DB>

結果は以下のように表示される

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 = 405.034120 (including connections establishing)
tps = 409.884412 (excluding connections establishing)

見方の詳細はここのベンチマーク結果の見方参照

1. pgbench_accountsを1件更新
2. pgbench_accountsから1件検索
3. pgbench_tellersを1件更新
4. pgbench_branchesを1件更新
5. pgbench_historyに1件行を追加

pgbench_branchesがscaling factorと同じ値で更新処理が同時に走るとロックがかかったりして、本来のbenchmarkができないようなケースに出くわすので、その場合は

# -N tellers/branchesの更新は行わない
pgbench -N -c <CLIENT NUM> -t <TRANSACTION NUM> <DB>

としたり参照のみの試験も可能

pgbench -S -c <CLIENT NUM> -t <TRANSACTION NUM> <DB>

pgbench -c <CLIENT NUM> -T <SEC> <DB>

Tで指定秒数の間、処理し続ける。長い時間かけ続けたいときはこっちのほうが便利。だいたい30秒 == 1000transactionとなる

fで使用可能。シナリオは自分で考えること。初期化も自分で準備する
custom.sql

BEGIN;
/*
この間は自分で処理を考える
*/
COMMIT;

のようなファイルを準備して、実行する

pgbench -f custom.sql -c <CLIENT NUM> -t <TRANSACTION NUM> <DB>

独自スクリプトを使うといろいろ応用が利くようになるが、1トランザクションごとにsleepをいれるなども可能（実際はpgbenchのような絶え間なくトランザクションが発生するわけではないため）。限界テストではなく、やや実際の操作想定に近いようなシナリオを考えるようなとなど

-- -Sつけた場合と同じ処理+sleep
BEGIN;
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
select * from pgbench_accounts where aid = :aid;
COMMIT;
\sleep 1 ms

なとどすると想定に近いtpsを得ることが可能

トランザクションごとのレスポンスタイムを知りたい場合。-lをつける

pgbench -c 10 -t 1000 d1 -l

結果がpgbench_log.<PID>として出力される

0 0 23977 0 1309237919 358577
0 1 5015 0 1309237919 363706
0 2 4904 0 1309237919 368634
0 3 4949 0 1309237919 373605
0 4 4240 0 1309237919 377867
0 5 4733 0 1309237919 382623
0 6 4060 0 1309237919 386704

フィールドの意味は以下となる。計算方法などは例によってpgbenchの説明に載っているのでここをよくみること

クライアントID	0から始まり、pgbenchからPostgreSQLの接続に対応
トランザクションID	0から始まり、各クライアントID内で実行した順に番号が振られる
処理時間	マイクロ秒単位のトランザクション処理時間
スクリプトファイル番号	-fを指定したときだけ意味がある
最後の2つの数字	トランザクションの終了時刻をマイクロ秒単位の精度で表します。最初の数字は1970年1月1日からの経過秒数、最後の数字はマイクロ秒

# PGSRCはソースディレクトリ
cd $PGSRC/contrib/oid2name
make
make install

port, user, hostなどはpostgres系コマンドと同じ。
引数指定何もなしだと全てのDBの一覧が表示

oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  16559   developperdb  pg_default
  16385           kurt  pg_default
  16604         kurtdb      pgdata
  12753       postgres  pg_default
  12745      template0  pg_default
      1      template1  pg_default

DB指定

 oid2name  -d kurtdb
From database "kurtdb":
  Filenode  Table Name
----------------------
     16692          t1
     16700          t2

テーブル指定

oid2name -t t2 -d kurtdb
From database "kurtdb":
  Filenode  Table Name
----------------------
     16700          t2

9.1から導入されるextension機能

• http://lets.postgresql.jp/documents/technical/9.1/...
• http://developer.postgresql.org/pgdocs/postgres/ex...

参照。

third partyなextensionはhttp://pgxn.org/に公開されていくと思う

※srcからインストールした場合前提で
ソースディレクトリのcontribまで移動

cd $PGSRC/contrib/

extensionインストール。pgcryptoの場合

cd pgcrypto
make
make install

psqlで接続

psql -U postgres

CREATE EXTENSION

CREATE EXTENSION pgcrypto;

確認

select * from pg_available_extensions;
   name   | default_version | installed_version |                     comment
----------+-----------------+-------------------+--------------------------------------------------
 plpgsql  | 1.0             | 1.0               | PL/pgSQL procedural language
 pgcrypto | 1.0             | 1.0               | cryptographic functions

暗号化。本格的な暗号化を行いたいのならCPUがいいサーバにしておいたほうがいい。http://www.postgresql.jp/document/9.0/html/pgcrypt...

暗号化するデータのフィールドはbyteaにする

create table t1 (
   id  serial primary key,
   secret bytea not null
);

単純な暗号化。アルゴリズムはどっちかのみ

• bf Blowfish
• aes AES (Rijndael-128)

-- encrypt(data bytea, key bytea, type text) returns bytea なので、convert_toが必要
insert into t1(secret) values(encrypt(convert_to('たろう', 'UTF8'), 'password'::bytea, 'aes'));

取り出す場合は以下のようにする

select convert_from(decrypt(secret, 'password'::bytea, 'aes'), 'UTF8') as name from t1;
  name
--------
 たろう
(1 row)

高機能、暗号化強度も強いが、オーバーヘッド大

-- pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
insert into t1(secret) values(pgp_sym_encrypt('たろう', 'password'));

取り出し

select pgp_sym_decrypt(secret, 'password') as name from t1;
  name
--------
 たろう
(1 row)

鍵を使って暗号化

pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
insert into t1(secret) values(pgp_pub_encrypt('たろう', ''));