• PostgreSQL单机、同步复制、异步复制性能测试对比


    测试环境:

    •测试机:PC
    •内存:8GB
    •CPU:Intel(R) Core(TM) i5-3450 3.10GHz
    •硬盘:HDD
    •数据量:20GB
    •测试工具:pgbench
    •PostgreSQL版本:9.5.1
    •参数配置:默认配置
    测试结果

     

    PostgreSQL同步复制与PostgreSQL单机性能测试对比

    terminals

    10

    20

    30

    40

    50

    PostgreSQL单机(tps)

    54

    54

    61

    68

    71

    PostgreSQL

    同步复制(tps)

    27

    33

    53

    57

    61

    PostgreSQL

    异步复制(tps)

    70

    65

    77

    83

    87

    测试过程中的数据如下所示:

    PostgreSQL同步复制

    20GB data    8GB Memory    4CPU

    master:192.168.0.14

    standby: 192.168.0.15

    单机192.168.0.14默认配置测试

    [postgres@minion1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 10  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 10

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 32774

    latency average: 183.072 ms

    tps = 54.614739 (including connections establishing)

    tps = 54.614954 (excluding connections establishing)

    statement latencies in milliseconds:

          0.007900 set nbranches 1 * :scale

          0.021082 set ntellers 10 * :scale

          0.027008 set naccounts 100000 * :scale

          0.017157 setrandom aid 1 :naccounts

          0.007154 setrandom bid 1 :nbranches

          0.006974 setrandom tid 1 :ntellers

          0.006926 setrandom delta -5000 5000

          0.387514 BEGIN;

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

          0.531161 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          123.453907  END;

    [postgres@minion1 bin]$  ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 20  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 20

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 32665

    latency average: 367.366 ms

    tps = 54.396128 (including connections establishing)

    tps = 54.396331 (excluding connections establishing)

    statement latencies in milliseconds:

          0.007506 set nbranches 1 * :scale

          0.048462 set ntellers 10 * :scale

          0.015586 set naccounts 100000 * :scale

          0.027542 setrandom aid 1 :naccounts

          0.157314 setrandom bid 1 :nbranches

          0.006869 setrandom tid 1 :ntellers

          0.047938 setrandom delta -5000 5000

          2.134529 BEGIN;

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

          0.813658 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          184.626413  END;

    [postgres@minion1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 30  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 30

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 36783

    latency average: 489.356 ms

    tps = 61.251723 (including connections establishing)

    tps = 61.251950 (excluding connections establishing)

    statement latencies in milliseconds:

          0.026379 set nbranches 1 * :scale

          0.018963 set ntellers 10 * :scale

          0.030436 set naccounts 100000 * :scale

          0.059545 setrandom aid 1 :naccounts

          0.007369 setrandom bid 1 :nbranches

          0.006821 setrandom tid 1 :ntellers

          0.006753 setrandom delta -5000 5000

          1.081480 BEGIN;

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

          1.253963 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          246.731468  END;

    [postgres@minion1 bin]$  ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 40  -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 40

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 41001

    latency average: 585.352 ms

    tps = 68.143229 (including connections establishing)

    tps = 68.143475 (excluding connections establishing)

    [postgres@minion1 bin]$  ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 50  -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 50

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 43309

    latency average: 692.697 ms

    tps = 71.867790 (including connections establishing)

    tps = 71.868053 (excluding connections establishing)

    PG同步复制性能测试

    [postgres@node1 bin]$  ./pgbench -d postgres -U postgres -h 192.168.0.14 -p 5432 -c 10  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 10

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 16440

    latency average: 364.964 ms

    tps = 27.380710 (including connections establishing)

    tps = 27.380806 (excluding connections establishing)

    statement latencies in milliseconds:

          0.008551 set nbranches 1 * :scale

          0.006313 set ntellers 10 * :scale

          0.006117 set naccounts 100000 * :scale

          0.007933 setrandom aid 1 :naccounts

          0.007494 setrandom bid 1 :nbranches

          0.007123 setrandom tid 1 :ntellers

          0.007161 setrandom delta -5000 5000

          0.203136 BEGIN;

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

          0.270357 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          243.549121  END;

    ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 20  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 20

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 20203

    latency average: 593.971 ms

    tps = 33.628754 (including connections establishing)

    tps = 33.628872 (excluding connections establishing)

    statement latencies in milliseconds:

          0.007198 set nbranches 1 * :scale

          0.005499 set ntellers 10 * :scale

          0.005457 set naccounts 100000 * :scale

          0.007148 setrandom aid 1 :naccounts

          0.014756 setrandom bid 1 :nbranches

          0.006343 setrandom tid 1 :ntellers

          0.006322 setrandom delta -5000 5000

          0.332977 BEGIN;

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

          0.655481 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          321.324808  END;

     

    ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 30  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 30

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 32375

    latency average: 555.985 ms

    tps = 53.872946 (including connections establishing)

    tps = 53.873141 (excluding connections establishing)

    statement latencies in milliseconds:

          0.006620 set nbranches 1 * :scale

          0.005146 set ntellers 10 * :scale

          0.005040 set naccounts 100000 * :scale

          0.006596 setrandom aid 1 :naccounts

          0.006322 setrandom bid 1 :nbranches

          0.005923 setrandom tid 1 :ntellers

          0.005909 setrandom delta -5000 5000

          0.336000 BEGIN;

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

          0.591474 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          310.427730  END;

    ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 40  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 40

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 34765

    latency average: 690.349 ms

    tps = 57.691611 (including connections establishing)

    tps = 57.691811 (excluding connections establishing)

    statement latencies in milliseconds:

          0.006601 set nbranches 1 * :scale

          0.005098 set ntellers 10 * :scale

          0.004962 set naccounts 100000 * :scale

          0.047420 setrandom aid 1 :naccounts

          0.006216 setrandom bid 1 :nbranches

          0.005986 setrandom tid 1 :ntellers

          0.005855 setrandom delta -5000 5000

          0.525683 BEGIN;

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

          1.446397 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          391.076596  END;

    [postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 50  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 50

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 36851

    latency average: 814.089 ms

    tps = 61.288387 (including connections establishing)

    tps = 61.288599 (excluding connections establishing)

    statement latencies in milliseconds:

          0.036619 set nbranches 1 * :scale

          0.015365 set ntellers 10 * :scale

          0.048885 set naccounts 100000 * :scale

          0.006212 setrandom aid 1 :naccounts

          0.005962 setrandom bid 1 :nbranches

          0.005748 setrandom tid 1 :ntellers

          0.028424 setrandom delta -5000 5000

          1.002420 BEGIN;

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

          1.338118 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          462.642218  END;

    PostgreSQL异步复制性能测试

    [postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 10  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 10

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 42402

    latency average: 141.503 ms

    tps = 70.656084 (including connections establishing)

    tps = 70.656332 (excluding connections establishing)

    statement latencies in milliseconds:

          0.009604 set nbranches 1 * :scale

          0.008152 set ntellers 10 * :scale

          0.007491 set naccounts 100000 * :scale

          0.008265 setrandom aid 1 :naccounts

          0.007667 setrandom bid 1 :nbranches

          0.007304 setrandom tid 1 :ntellers

          0.127113 setrandom delta -5000 5000

          0.061162 BEGIN;

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

          0.990680 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          0.101391 END;

    [postgres@node1 bin]$  ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 20  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 20

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 39253

    latency average: 305.709 ms

    tps = 65.333390 (including connections establishing)

    tps = 65.333617 (excluding connections establishing)

    statement latencies in milliseconds:

          0.009897 set nbranches 1 * :scale

          0.007471 set ntellers 10 * :scale

          0.006880 set naccounts 100000 * :scale

          0.032549 setrandom aid 1 :naccounts

          0.097106 setrandom bid 1 :nbranches

          0.006860 setrandom tid 1 :ntellers

          0.125257 setrandom delta -5000 5000

          0.100855 BEGIN;

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

          4.123210 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          0.495837 END;

    [postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 30  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 30

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 46800

    latency average: 384.615 ms

    tps = 77.815440 (including connections establishing)

    tps = 77.815712 (excluding connections establishing)

    statement latencies in milliseconds:

          0.010180 set nbranches 1 * :scale

          0.007174 set ntellers 10 * :scale

          0.006647 set naccounts 100000 * :scale

          0.065834 setrandom aid 1 :naccounts

          0.026308 setrandom bid 1 :nbranches

          0.006901 setrandom tid 1 :ntellers

          0.054161 setrandom delta -5000 5000

          0.203354 BEGIN;

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

          6.878700 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          0.414110 END;

    [postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 40  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 40

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 50331

    latency average: 476.843 ms

    tps = 83.765539 (including connections establishing)

    tps = 83.765830 (excluding connections establishing)

    statement latencies in milliseconds:

          0.009836 set nbranches 1 * :scale

          0.010446 set ntellers 10 * :scale

          0.006072 set naccounts 100000 * :scale

          0.010755 setrandom aid 1 :naccounts

          0.024105 setrandom bid 1 :nbranches

          0.006772 setrandom tid 1 :ntellers

          0.036164 setrandom delta -5000 5000

          0.132524 BEGIN;

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

          15.298400    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          0.482677 END;

    [postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 50  -r -T 600

    transaction type: TPC-B (sort of)

    scaling factor: 1428

    query mode: simple

    number of clients: 50

    number of threads: 1

    duration: 600 s

    number of transactions actually processed: 52834

    latency average: 567.816 ms

    tps = 87.954780 (including connections establishing)

    tps = 87.955086 (excluding connections establishing)

    statement latencies in milliseconds:

          0.009852 set nbranches 1 * :scale

          0.006589 set ntellers 10 * :scale

          0.005597 set naccounts 100000 * :scale

          0.081271 setrandom aid 1 :naccounts

          0.012686 setrandom bid 1 :nbranches

          0.006291 setrandom tid 1 :ntellers

          0.102962 setrandom delta -5000 5000

          0.326865 BEGIN;

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

          20.356945    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

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

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

          0.531862 END;

     

  • 相关阅读:
    C#调用存储过程带输出参数或返回值
    车辆售票坐位图
    C#操作SQL Server通用类
    Java基础知识总结
    Maven 安装与配置
    读取文件内容
    复制一个文件
    求1+2+3+...+n,要求不能使用乘除法、for、while、if、else、switch、case等关键字及条件判断语句(A?B:C)。
    输入一个递增的顺序排序的二维数组和一个整数,判断数组中是否含有该整数
    在由N个元素构成的集合S中,找出最小元素C,满足C=A-B,其中A,B是都集合S中的元素,没找到则返回-1
  • 原文地址:https://www.cnblogs.com/songyuejie/p/5210273.html
Copyright © 2020-2023  润新知