Bucardo的性能压测

最新推荐文章于 2024-07-06 18:31:11 发布

weixin_33877092

最新推荐文章于 2024-07-06 18:31:11 发布

阅读量248

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/Kenyon/blog/318650

版权

为什么80%的码农都做不了架构师？>>>

做完Bucardo的部署后，来简单看一下使用bucardo作为双主工具的postgresql性能会降低多少。

环境：
OS :CentOS 6.5
DB:PostgreSQL 9.4beta2

准备：
复制的表tbl_ken
普通的表tt
测试数据100W

[postgres@localhost ~]$ psql db_ken
psql (9.4beta2)
Type "help" for help.

db_ken=# create table tbl_ken(id serial primary key,vname text);
CREATE TABLE
db_ken=# create table tt(id serial primary key,vname text);
CREATE TABLE
db_ken=# insert into tbl_ken select generate_series(1,1000000),generate_series(1,1000000)||'haha';
INSERT 0 1000000
db_ken=# insert into tt select generate_series(1,1000000),generate_series(1,1000000)||'haha';
INSERT 0 1000000

简单的更新测试脚本

[postgres@localhost ~]$ more bench.sql 
\setrandom id 1 200000
update tbl_ken set vname = :id||vname where id = :id;

[postgres@localhost ~]$ more bench_2.sql 
\setrandom id 1 200000
update tt set vname = :id||vname where id = :id;

a.带bucardo的测试

[postgres@localhost bucardo]$ pgbench -M simple -r -c 8 -f /home/postgres/bench.sql -j 1 -n -T 120 db_ken
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 120 s
number of transactions actually processed: 140272
latency average: 6.844 ms
tps = 1168.769297 (including connections establishing)
tps = 1169.112792 (excluding connections establishing)
statement latencies in milliseconds:
        0.006535        \setrandom id 1 200000
        6.829436        update tbl_ken set vname = :id||vname where id = :id;
[postgres@localhost bucardo]$ pgbench -M simple -r -c 8 -f /home/postgres/bench.sql -j 2 -n -T 120 db_ken
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
duration: 120 s
number of transactions actually processed: 169682
latency average: 5.658 ms
tps = 1413.838476 (including connections establishing)
tps = 1414.077325 (excluding connections establishing)
statement latencies in milliseconds:
        0.006166        \setrandom id 1 200000
        5.644760        update tbl_ken set vname = :id||vname where id = :id;
[postgres@localhost bucardo]$ pgbench -M simple -r -c 8 -f /home/postgres/bench.sql -j 4 -n -T 120 db_ken
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 4
duration: 120 s
number of transactions actually processed: 142868
latency average: 6.719 ms
tps = 1190.362510 (including connections establishing)
tps = 1190.495668 (excluding connections establishing)
statement latencies in milliseconds:
        0.006326        \setrandom id 1 200000
        6.707248        update tbl_ken set vname = :id||vname where id = :id;
[postgres@localhost bucardo]$ pgbench -M simple -r -c 8 -f /home/postgres/bench.sql -j 8 -n -T 120 db_ken
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
duration: 120 s
number of transactions actually processed: 163147
latency average: 5.884 ms
tps = 1359.437978 (including connections establishing)
tps = 1359.610058 (excluding connections establishing)
statement latencies in milliseconds:
        0.006142        \setrandom id 1 200000
        5.871954        update tbl_ken set vname = :id||vname where id = :id;

b.不带bucardo的测试

[postgres@localhost bucardo]$ pgbench -M simple -r -c 8 -f /home/postgres/bench_2.sql -j 1 -n -T 120 db_ken
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 120 s
number of transactions actually processed: 364978
latency average: 2.630 ms
tps = 3041.268975 (including connections establishing)
tps = 3041.827237 (excluding connections establishing)
statement latencies in milliseconds:
        0.004722        \setrandom id 1 200000
        2.621466        update tt set vname = :id||vname where id = :id;
[postgres@localhost bucardo]$ pgbench -M simple -r -c 8 -f /home/postgres/bench_2.sql -j 2 -n -T 120 db_ken
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
duration: 120 s
number of transactions actually processed: 443756
latency average: 2.163 ms
tps = 3697.710107 (including connections establishing)
tps = 3698.308622 (excluding connections establishing)
statement latencies in milliseconds:
        0.005630        \setrandom id 1 200000
        2.152280        update tt set vname = :id||vname where id = :id;
[postgres@localhost bucardo]$ psql db_ken
psql (9.4beta2)
Type "help" for help.

db_ken=# select * from tt where id = 789
db_ken-# ;
 id  |            vname             
-----+------------------------------
 789 | 789789789789789789789789haha
(1 row)

db_ken=# \q
[postgres@localhost bucardo]$ pgbench -M simple -r -c 8 -f /home/postgres/bench_2.sql -j 4 -n -T 120 db_ken
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 4
duration: 120 s
number of transactions actually processed: 373288
latency average: 2.572 ms
tps = 3110.222738 (including connections establishing)
tps = 3110.829289 (excluding connections establishing)
statement latencies in milliseconds:
        0.005779        \setrandom id 1 200000
        2.560499        update tt set vname = :id||vname where id = :id;
[postgres@localhost bucardo]$ pgbench -M simple -r -c 8 -f /home/postgres/bench_2.sql -j 8 -n -T 120 db_ken
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
duration: 120 s
number of transactions actually processed: 314072
latency average: 3.057 ms
tps = 2617.157313 (including connections establishing)
tps = 2617.630725 (excluding connections establishing)
statement latencies in milliseconds:
        0.005790        \setrandom id 1 200000
        3.044878        update tt set vname = :id||vname where id = :id;

折线图如下：