ceph块设备 VS 本地块设备 使用pgbench TPC-B测试数据对比记录

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端只用了一块千兆网卡，观察网卡流量，已经跑满。