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;