Citus性能测试

1.【测试环境】

1.1.测试组:Citus with PG10 1+2

1.1.1.协调节点:

CPU:单核2GHz
RAM:2GB
DISK:30GB HDD
主机名:citus
IP地址:192.168.7.129

1.1.2.工作节点1:

CPU:单核2GHz
RAM:2GB
DISK:30GB HDD
主机名:citus-w1
IP地址:192.168.7.130

1.1.3.工作节点2:

CPU:单核2GHz
RAM:2GB
DISK:30GB HDD
主机名:citus-w2
IP地址:192.168.7.131

1.1.4.Citus部署配置:

两分片,单副本。

1.2.对照组:PG10

1.2.1.服务节点:

CPU:单核2GHz
RAM:2GB
DISK:30GB HDD
主机名:pg10host
IP地址:192.168.7.132

2.【测试方法】

PgBench,TPC-B,记录数100万

3.【测试过程】

3.1.对照组环境部署与数据准备

对照组是单机版PG10,所以不需要额外部署环境,数据准备采用了pgbench的initials。

pgbench.exe -i -s 10 -h 192.168.7.132 -p 5432 -U postgres -d pgbench
#参数说明
#-i 初始化模式
#-s 规模 即 10x100000 条账户记录

用COPY方法取出数据给citus备用

psql -h 192.168.7.132 -p 5432 -U postgres -d pgbench
pgbench=# copy (select * from pgbench_tellers) to '/var/lib/pgsql/init/pgbench_tellers.txt';
COPY 100
pgbench=# copy (select * from pgbench_accounts) to '/var/lib/pgsql/init/pgbench_accounts.txt';
COPY 1000000
pgbench=# copy (select * from pgbench_branches) to '/var/lib/pgsql/init/pgbench_branches.txt';
COPY 10

将文件pgbench_tellers.txt、pgbench_accounts.txt、pgbench_branches.txt从pg10host拷贝到citus协调节点上。

3.2.对照组环境部署与数据准备
3.2.1.创建数据库,创建citus插件,部署citus

在3个节点上:

psql -h 192.168.7.129/130/131 -p 5432 -U postgres
pgbench=# drop database pgbench;
pgbench=# create database pgbench;
psql -h 192.168.7.129/130/131 -p 5432 -U postgres -d pgbench
pgbench=# create extension citus;

在协调节点上:

psql -h 192.168.7.129 -p 5432 -U postgres -d pgbench 
pgbench=# SELECT * from master_add_node('192.168.7.130', 5432);
pgbench=# SELECT * from master_add_node('192.168.7.131', 5432);
pgbench=# SELECT * FROM master_get_active_worker_nodes();
3.2.2.创建表

在协调节点上:

psql -h 192.168.7.129 -p 5432 -U postgres -d pgbench

pgbench=# drop table pgbench_accounts;
pgbench=# drop table pgbench_branches;
pgbench=# drop table pgbench_history;
pgbench=# drop table pgbench_tellers;

pgbench=# CREATE TABLE pgbench_accounts
            (
              aid integer NOT NULL,
              bid integer,
              abalance integer,
              filler character(84),
              CONSTRAINT pgbench_accounts_pkey PRIMARY KEY (aid)
            )
            WITH (
              FILLFACTOR=100, 
              OIDS=FALSE
            );
            ALTER TABLE pgbench_accounts
              OWNER TO postgres;


pgbench=# CREATE TABLE pgbench_branches
            (
              bid integer NOT NULL,
              bbalance integer,
              filler character(88),
              CONSTRAINT pgbench_branches_pkey PRIMARY KEY (bid)
            )
            WITH (
              FILLFACTOR=100, 
              OIDS=FALSE
            );
            ALTER TABLE pgbench_branches
              OWNER TO postgres;


pgbench=# CREATE TABLE pgbench_history
            (
              tid integer,
              bid integer,
              aid integer,
              delta integer,
              mtime timestamp without time zone,
              filler character(22)
            )
            WITH (
              OIDS=FALSE
            );
            ALTER TABLE pgbench_history
              OWNER TO postgres;


pgbench=# CREATE TABLE pgbench_tellers
            (
              tid integer NOT NULL,
              bid integer,
              tbalance integer,
              filler character(84),
              CONSTRAINT pgbench_tellers_pkey PRIMARY KEY (tid)
            )
            WITH (
              FILLFACTOR=100, 
              OIDS=FALSE
            );
            ALTER TABLE pgbench_tellers
              OWNER TO postgres;
3.3.3.并设置分片(两分片,单副本)

在协调节点上:

pgbench=# SELECT master_create_distributed_table('pgbench_accounts', 'aid', 'hash');
pgbench=# SELECT master_create_distributed_table('pgbench_branches', 'bid', 'hash');
pgbench=# SELECT master_create_distributed_table('pgbench_history', 'mtime', 'hash');
pgbench=# SELECT master_create_distributed_table('pgbench_tellers', 'tid', 'hash');
pgbench=# SELECT master_create_worker_shards('pgbench_accounts', 2, 1);
pgbench=# SELECT master_create_worker_shards('pgbench_branches', 2, 1);
pgbench=# SELECT master_create_worker_shards('pgbench_history', 2, 1);
pgbench=# SELECT master_create_worker_shards('pgbench_tellers', 2, 1);

pgbench=# SELECT * from pg_dist_shard_placement;
3.3.4.数据准备

将刚才COPY出的数据复制到citus协调节点,用COPY命令导入数据

psql -h 192.168.7.129 -p 5432 -U postgres -d pgbench
pgbench=# copy pgbench_tellers from '/var/lib/pgsql/init/pgbench_tellers.txt';
COPY 100
pgbench=# copy pgbench_branches from '/var/lib/pgsql/init/pgbench_branches.txt';
COPY 10
pgbench=# copy pgbench_accounts from '/var/lib/pgsql/init/pgbench_accounts.txt';
COPY 1000000

pgbench=# vacuum analyze pgbench_branches;
pgbench=# vacuum analyze pgbench_tellers;
pgbench=# vacuum analyze pgbench_accounts;
pgbench=# vacuum analyze pgbench_history;

4.【开始测试】

4.1.测试组

pgbench.exe -c 50 -j 10 -M prepared -n -s 10 -T 60 -r -h 192.168.7.129 -p 5432 -U postgres -d pgbench> E:\pgbench_all.out  2>&1

4.2.对照组

pgbench.exe -c 50 -j 10 -M prepared -n -s 10 -T 60 -r -h 192.168.7.132 -p 5432 -U postgres -d pgbench> E:\pgbench_all.out  2>&1
#参数说明:
#-c 客户端并发数
#-j 线程数
#-M 简单模式/扩展模式/预备模式
#-n 不做vacuum
#-s 规模
#-T 持续时间
#-r 报告

5.【测试结果】

5.1.测试组Citus

pghost: 192.168.7.129 pgport: 5432 nclients: 50 duration: 60 dbName: pgbench
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: prepared
number of clients: 50
number of threads: 10
duration: 60 s
number of transactions actually processed: 15564
latency average: 192.753 ms
tps = 258.061287 (including connections establishing)
tps = 258.262831 (excluding connections establishing)
statement latencies in milliseconds:
    0.020357    \set nbranches 1 * :scale
    0.014827    \set ntellers 10 * :scale
    0.011903    \set naccounts 100000 * :scale
    0.017210    \setrandom aid 1 :naccounts
    0.016534    \setrandom bid 1 :nbranches
    0.013833    \setrandom tid 1 :ntellers
    0.016387    \setrandom delta -5000 5000
    4.548151    BEGIN;
    18.692651   UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
    15.102640   SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    42.506431   UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
    77.037080   UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
    5.961420    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    28.680671   END;

5.2.对照组PG10

pghost: 192.168.7.132 pgport: 5432 nclients: 50 duration: 60 dbName: pgbench
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: prepared
number of clients: 50
number of threads: 10
duration: 60 s
number of transactions actually processed: 41387
latency average: 72.487 ms
tps = 688.209654 (including connections establishing)
tps = 688.786509 (excluding connections establishing)
statement latencies in milliseconds:
    0.016704    \set nbranches 1 * :scale
    0.015198    \set ntellers 10 * :scale
    0.011359    \set naccounts 100000 * :scale
    0.016007    \setrandom aid 1 :naccounts
    0.015096    \setrandom bid 1 :nbranches
    0.012979    \setrandom tid 1 :ntellers
    0.014853    \setrandom delta -5000 5000
    4.223193    BEGIN;
    2.817537    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
    6.485673    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    15.400302   UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
    30.805555   UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
    1.907898    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    10.506634   END;
  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

皓月如我

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值