postgresql pgbench工具(二)
00 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
举例测试一下机器insert的速度有多快
insertsize.sql内容：create table data(filler text);
insert-size.sql内容：insert into data (filler) values (repeat('X',:scale));
$psql -d pgbench -f insertsize.sql
$pgbench -s 100 -c 10 -t 10000 pgbench -f insert-size.sql
starting vacuum...end.
transaction type: Custom query
scaling factor: 100
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 608.144907 (including connections establishing)
tps = 608.234303 (excluding connections establishing)