使用pgbench測試PostgreSQL

標籤：

pgbench –help

初始化選項：

-i            調用初始化模式

-F NUM  填滿因數

-s NUM   規模因子（與產生資料量大小有關）

Benchmarking選項：

-c NUM    資料庫用戶端並發數（預設：1）

-C （為每個事務建立新的串連）

-Ｄ VARNAME=VALUE 通過客戶指令碼為使用者定義變數

-f FILENAME 從檔案FILENAME讀取事務指令碼

-j NUM  線程數（預設：1）

-i  寫事務時間到記錄檔

-M{simple|extended|prepared} 給伺服器提交查詢的協議

-n 在測試之前不運行VACUUM

-N 不更新表“pgbench_tellers” “pgbench_branches”

-r 報告每條命令的平均延遲

-s NUM 在輸出中報告規模因子

-S      執行 SELECT-only事務

-t NUM 每個用戶端啟動並執行事務數（預設：10）

-T NUM benchmark測試時間（單位：秒）

-v 在測試前清空所有的四個標準表

常用選項：

-d 輸出列印調試資訊

-h HOSTNAME 資料庫伺服器主機或socket 目錄

-U USERNAME 指定資料庫使用者的串連

--help 顯示協助資訊，然後退出

--version 輸出版本資訊，然後退出

產生資料

postgres=# create user benchmarksql with superuser password ‘123456‘;

CREATE ROLE

postgres=# create database benchmarksql owner benchmarksql;

CREATE DATABASE

 

[[email protected] bin]$ ./pgbench -i -F 100 -s 1428 -h 127.0.0.1 -p 6432 -U benchmarksql benchmarksql

串連的資料

[[email protected] bin]$ ./psql -h 127.0.0.1 -p 6432 -U benchmarksql benchmarksql

 

查看資料庫大小

select pg_database_size(‘benchmarksql‘)/1024/1024/1024|| ‘GB‘;

 

開始測試

[[email protected] bin]$ ./pgbench -c 10 –j –t  -r  -T 1800 -h 127.0.0.1 -p 6432 -U benchmarksql

 

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: prepared

number of clients: 10

number of threads: 10

duration: 1800 s

number of transactions actually processed: 1294479

latency average: 13.905 ms

tps = 715.507310 (including connections establishing)

tps = 715.510043 (excluding connections establishing)

statement latencies in milliseconds:

         0.002768 \set nbranches 1 * :scale

         0.000647 \set ntellers 10 * :scale

         0.000562 \set naccounts 100000 * :scale

         0.000823 \setrandom aid 1 :naccounts

         0.000640 \setrandom bid 1 :nbranches

         0.000684 \setrandom tid 1 :ntellers

         0.000615 \setrandom delta -5000 5000

         0.054656 BEGIN;

         6.063291 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

         0.092079 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

         2.897447 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

         2.026997 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

         1.640868 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

         1.184043 END;

 

[[email protected] bin]$ ./pgbench -c 20 -j 20 -M prepared -n -s 1428 -T 1800 -r -h 127.0.0.1 -p 6432 -U benchmarksql benchmarksql

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: prepared

number of clients: 20

number of threads: 20

duration: 1800 s

number of transactions actually processed: 1503569

latency average: 23.943 ms

tps = 829.034591 (including connections establishing)

tps = 829.038294 (excluding connections establishing)

statement latencies in milliseconds:

         0.003509 \set nbranches 1 * :scale

         0.001146 \set ntellers 10 * :scale

         0.000805 \set naccounts 100000 * :scale

         0.001075 \setrandom aid 1 :naccounts

         0.000881 \setrandom bid 1 :nbranches

         0.000874 \setrandom tid 1 :ntellers

         0.000881 \setrandom delta -5000 5000

         0.077150 BEGIN;

         9.083130 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

         0.123980 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

         6.025891 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

         3.595181 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

         3.098900 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

         2.098509 END;

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: prepared

number of clients: 30

number of threads: 10

duration: 1800 s

number of transactions actually processed: 1468864

latency average: 36.763 ms

tps = 815.178087 (including connections establishing)

tps = 815.184313 (excluding connections establishing)

statement latencies in milliseconds:

         0.003654 \set nbranches 1 * :scale

         0.001164 \set ntellers 10 * :scale

         0.001123 \set naccounts 100000 * :scale

         0.001288 \setrandom aid 1 :naccounts

         0.001202 \setrandom bid 1 :nbranches

         0.001106 \setrandom tid 1 :ntellers

         0.001129 \setrandom delta -5000 5000

         0.154459 BEGIN;

         13.228743        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

         0.212691 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

         9.070507 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

         4.904218 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

         5.595383 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

         3.612066 END;

 

[[email protected] bin]$ ./pgbench -c 50 -j 50 -M prepared -n -s 1428 -T 1800 -r -h 127.0.0.1 -p 6432 -U benchmarksql benchmarksql

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: prepared

number of clients: 50

number of threads: 50

duration: 1800 s

number of transactions actually processed: 1641175

latency average: 54.839 ms

tps = 901.460449 (including connections establishing)

tps = 901.475075 (excluding connections establishing)

statement latencies in milliseconds:

         0.005079 \set nbranches 1 * :scale

         0.001588 \set ntellers 10 * :scale

         0.001257 \set naccounts 100000 * :scale

         0.001768 \setrandom aid 1 :naccounts

         0.001328 \setrandom bid 1 :nbranches

         0.001424 \setrandom tid 1 :ntellers

         0.001316 \setrandom delta -5000 5000

         0.256724 BEGIN;

         19.791906        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

         0.284488 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

         13.638615        UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

         8.232851 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

         7.664059 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

         5.561957 END;

使用pgbench測試PostgreSQL