shared_buffer=4G,本地sata盘1000W测试数据:
生产1000W测试数据,tbs_local使用的是本地sata磁盘,无写缓存
pgbench hank -i -s 100 pgbench -h 127.0.0.1 -p 1921 -U hank --tablespace=tbs_local
需要28秒钟生成1000W数据
9900000 of 10000000 tuples (99%) done (elapsed 28.70 s, remaining 0.29 s)
pgbench hank -h 127.0.0.1 -p 1921 -U hank -r -c 100 -T 60
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 100
number of threads: 1
duration: 60 s
number of transactions actually processed: 335921
latency average = 17.866 ms
tps = 5597.281724 (including connections establishing)
tps = 5597.672190 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
1.261 BEGIN;
1.240 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2.862 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
3.220 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
4.330 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2.632 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2.190 END;
shared_buffer=4G,ceph1000W测试数据:
重新生成数据到ceph块设备,
pgbench hank -i -s 100 pgbench -h 127.0.0.1 -p 1921 -U hank --tablespace=tbs_ceph
10000000 of 10000000 tuples (100%) done (elapsed 32.02 s, remaining 0.00 s)
pgbench hank -h 127.0.0.1 -p 1921 -U hank -r -c 100 -T 60
postgres@client01-> pgbench hank -h 127.0.0.1 -p 1921 -U hank -r -c 100 -T 60
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 100
number of threads: 1
duration: 60 s
number of transactions actually processed: 349466
latency average = 17.172 ms
tps = 5823.348895 (including connections establishing)
tps = 5823.777123 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
1.262 BEGIN;
1.193 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2.923 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2.970 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
4.062 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2.541 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2.089 END;
shared_buffer=2M,本地sata盘1000W测试数据:
tps急剧下降到504,没有内存缓存的情况下,性能差很多
postgres@client01-> pgbench hank -h 127.0.0.1 -p 1921 -U hank -r -c 100 -T 100
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 100
number of threads: 1
duration: 100 s
number of transactions actually processed: 51596
latency average = 198.401 ms
tps = 504.030219 (including connections establishing)
tps = 504.051541 (excluding connections establishing)
statement latencies in milliseconds:
0.008 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.643 BEGIN;
126.990 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
11.103 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
14.983 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
17.593 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
22.861 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.788 END;
shared_buffer=2M,ceph1000W测试数据:
postgres@client01-> pgbench hank -h 127.0.0.1 -p 1921 -U hank -r -c 100 -T 100
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 100
number of threads: 1
duration: 100 s
number of transactions actually processed: 217516
latency average = 45.994 ms
tps = 2174.200172 (including connections establishing)
tps = 2174.295161 (excluding connections establishing)
statement latencies in milliseconds:
0.009 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.641 BEGIN;
21.713 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
3.921 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
6.371 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
5.697 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
6.544 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.883 END;
综合以上数据,对比得出,在有内存告诉缓存的时候,性能相差不大,关闭缓存后,直接操作磁盘只有504tps,机械盘性能下降很大,ceph由于我用ssd做了缓存层,性能要好一些有2014tps。
另外由于ceph client端只用了一块千兆网卡,观察网卡流量,已经跑满。