PostgreSQL使用pgbench测试citus

原创多米爸比 2020-02-06

3056

Citus介绍
citus是PostgreSQL数据库中的一种轻量级的分库分表解决方案。它是PostgreSQL数据库中的一个插件，每个citus集群由多个PostgreSQL数据库实例组成，数据库实例分为两类：
master节点，通常有一台。master节点只存储分库分表的元数据，不存储实际的数据。
worker节点，通常有多台。worker节点存储实际的分片数据(shard)。

环境
华为云ECS 8核，32G内存 3台
CentOS 7.2 x64
PostgreSQL 12.1
Citus 9.1.2

集群配置1
192.168.1.132 coordinator node
192.168.1.105 worker(data) node
192.168.1.58 worker(data) node

集群配置2
192.168.1.58 coordinator node
192.168.1.132 worker(data) node
192.168.1.105 worker(data) node

1.OS 配置内核参数

# vi /etc/sysctl.conf  
 fs.aio-max-nr = 1048576  
 fs.file-max = 76724600  
 kernel.sem = 4096 2147483647 2147483646 32000      
 kernel.shmall = 6710886
 kernel.shmmax = 17179869184
 kernel.shmmni = 819200           
 net.core.netdev_max_backlog = 10000  
net.core.rmem_default = 262144         
net.core.rmem_max = 4194304            
net.core.wmem_default = 262144           
net.core.wmem_max = 4194304            
net.core.somaxconn = 4096  
net.ipv4.tcp_max_syn_backlog = 4096  
net.ipv4.tcp_keepalive_intvl = 20  
net.ipv4.tcp_keepalive_probes = 3  
net.ipv4.tcp_keepalive_time = 60  
net.ipv4.tcp_mem = 8388608 12582912 16777216  
net.ipv4.tcp_fin_timeout = 5  
net.ipv4.tcp_synack_retries = 2  
net.ipv4.tcp_syncookies = 1      
net.ipv4.tcp_timestamps = 1      
net.ipv4.tcp_tw_recycle = 0      
net.ipv4.tcp_tw_reuse = 1       
net.ipv4.tcp_max_tw_buckets = 262144  
net.ipv4.tcp_rmem = 8192 87380 16777216  
net.ipv4.tcp_wmem = 8192 65536 16777216  
net.nf_conntrack_max = 1200000  
net.netfilter.nf_conntrack_max = 1200000  
vm.dirty_background_bytes = 409600000         
vm.dirty_expire_centisecs = 3000                 
vm.dirty_ratio = 95                            
vm.dirty_writeback_centisecs = 100              
vm.mmap_min_addr = 65536  
vm.overcommit_memory = 0       
vm.overcommit_ratio = 90       
vm.swappiness = 0                
vm.zone_reclaim_mode = 0       
fs.nr_open=20480000  
net.ipv4.tcp_max_syn_backlog = 16384
net.core.somaxconn = 16384
复制

2.配置OS资源限制

# vi /etc/security/limits.conf  
# nofile超过1048576的话，一定要先将sysctl的fs.nr_open设置为更大的值，并生效后才能继续设置nofile.  
  
* soft    nofile  1024000  
* hard    nofile  1024000  
* soft    nproc   unlimited  
* hard    nproc   unlimited  
* soft    core    unlimited  
* hard    core    unlimited  
* soft    memlock unlimited  
* hard    memlock unlimited  
复制

3.安装PG以及Citus

# useradd jerome
# passwd jerome
# cd /ssd_data
# mkdir pg_test
# chown jerome.jerome pg_test
# chmod 0700 pg_test
# su - jerome

--上传12.1源码至服务器并安装PG
$ tar zxvf postgresql-12.1.tar.gz
$ cd  ~/postgresql-12.1
$ ./configure --prefix=/home/jerome/pg12 --with-pgport=1912  --without-zlib
$ make && make install

--下载最新release版 v9.1.2
$ wget https://codeload.github.com/citusdata/citus/tar.gz/v9.1.2
$ tar zxvf v9.1.2
$ cd citus-9.1.2/
$ ./configure PG_CONFIG=/home/jerome/pg12/bin/pg_config
$ make && make install

--初始化数据目录
$ /home/jerome/pg12/bin/initdb -D /ssd_data/pg_test/data1912 -Ujerome

--参数配置
$ vi /ssd_data/pg_test/data1912/postgresql.conf
shared_preload_libraries='citus'  
listen_addresses = '0.0.0.0'  
port = 1912 
log_destination = 'csvlog'  
logging_collector = on  
log_directory = 'log'  
log_filename = 'postgresql-%a.log'  
log_truncate_on_rotation = on  
log_rotation_age = 1d  
log_rotation_size = 0  
log_min_duration_statement = 5s  
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose  
log_line_prefix = '%m [%p] '  
log_lock_waits = on  
log_statement = 'ddl'  
log_timezone = 'PRC'  
log_autovacuum_min_duration = 0
  
$ vi /ssd_data/pg_test/data1912/pg_hba.conf
--添加如下内容
host    all             all             192.168.1.0/24          trust

--启动PG服务
$ /home/jerome/pg12/bin/pg_ctl -D /ssd_data/pg_test/data1912 -l /ssd_data/pg_test/data1912/pg.log start &

--安装citus扩展
$ /home/jerome/pg12/bin/psql -p1912 -Ujerome postgres
psql (12.1)
Type "help" for help.
postgres=# create extension citus;
CREATE EXTENSION
复制

4.连接测试

--以上几步在所有服务器都需要执行完后，接着在所有服务器执行以下连接测试
$ /home/jerome/pg12/bin/psql -h192.168.1.132 -p1912 -Ujerome postgres
$ /home/jerome/pg12/bin/psql -h192.168.1.105 -p1912 -Ujerome postgres
$ /home/jerome/pg12/bin/psql -h192.168.1.58 -p1912 -Ujerome postgres
复制

5.citus主服务器添加数据节点

$ /home/jerome/pg12/bin/psql -Ujerome postgres
postgres=# SELECT * from master_add_node('192.168.1.105', 1912);
 master_add_node 
-----------------
               1
(1 row)

postgres=# SELECT * from master_add_node('192.168.1.58', 1912);
 master_add_node 
-----------------
               2
(1 row)

--查看是否添加成功
postgres=# SELECT * FROM master_get_active_worker_nodes(); 
   node_name   | node_port 
---------------+-----------
 192.168.1.58  |      1912
 192.168.1.105 |      1912
(2 rows)
复制

6.citus主服务器初始化数据并分片

--初始化1亿条数据，15GB
$ /home/jerome/pg12/bin/pgbench -i -s1000 -Ujerome postgres

--将tpc-b涉及的4张表转换为sharding表（2个shard,每台物理机每个PG实例一个shard）
postgres=# set citus.shard_count =2;
postgres=# select create_distributed_table('pgbench_accounts','aid');
postgres=# select create_distributed_table('pgbench_branches','bid');  
postgres=# select create_distributed_table('pgbench_tellers','tid');  
postgres=# select create_distributed_table('pgbench_history','aid');  
复制

7.使用citus配置1进行测试

--只读测试
$ /home/jerome/pg12/bin/pgbench -M prepared -v -r -P1 -c80 -j80 -T 120 -S -Ujerome postgres 
transaction type: <builtin: select only>
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 120 s
number of transactions actually processed: 2660652
latency average = 3.608 ms
latency stddev = 39.532 ms
tps = 22161.577047 (including connections establishing)
tps = 22166.005197 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \set aid random(1, 100000 * :scale)
         3.602  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

--读写测试
$ /home/jerome/pg12/bin/pgbench -M prepared -v -r -P1 -c80 -j80 -T 120 -Ujerome postgres 
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 120 s
number of transactions actually processed: 811039
latency average = 11.840 ms
latency stddev = 32.059 ms
tps = 6754.770693 (including connections establishing)
tps = 6755.608354 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \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)
         0.124  BEGIN;
         1.990  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.493  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.747  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.866  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.524  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         7.089  END;
复制

8.使用citus配置2进行测试

--由于磁盘容量有限，测试之前先删除之前的数据(所有节点)
$ /home/jerome/pg12/bin/pg_ctl -D /ssd_data/pg_test/data1912 stop
$ rm -rf /ssd_data/pg_test/data1912/
$ /home/jerome/pg12/bin/initdb -D /ssd_data/pg_test/data1912 -Ujerome
--参数配置及创建扩展与上面步骤一样，这里不再重复

--数据初始化及分片参考第六步

--只读测试
$ /home/jerome/pg12/bin/pgbench -M prepared -v -r -P1 -c80 -j80 -T 120 -S -Ujerome postgres 
transaction type: <builtin: select only>
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 120 s
number of transactions actually processed: 4117363
latency average = 2.331 ms
latency stddev = 26.695 ms
tps = 34300.224677 (including connections establishing)
tps = 34303.949774 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \set aid random(1, 100000 * :scale)
         2.327  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

--读写测试
$ /home/jerome/pg12/bin/pgbench -M prepared -v -r -P1 -c80 -j80 -T 120 -Ujerome postgres 
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 120 s
number of transactions actually processed: 791580
latency average = 12.123 ms
latency stddev = 44.399 ms
tps = 6594.489399 (including connections establishing)
tps = 6596.772027 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \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)
         0.091  BEGIN;
         1.191  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.530  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.810  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.969  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.579  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         7.943  END;
复制

9.单机测试

--测试之前先删除之前的数据(所有节点)
$ /home/jerome/pg12/bin/pg_ctl -D /ssd_data/pg_test/data1912 stop
$ rm -rf /ssd_data/pg_test/data1912/
$ /home/jerome/pg12/bin/initdb -D /ssd_data/pg_test/data1912 -Ujerome
--参数配置与上面步骤一样，这里不再重复

--初始化1亿条数据，15GB
$ /home/jerome/pg12/bin/pgbench -i -s1000 -Ujerome postgres

--只读测试
$ /home/jerome/pg12/bin/pgbench -M prepared -v -r -P1 -c80 -j80 -T120 -S -Ujerome postgres 
transaction type: <builtin: select only>
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 120 s
number of transactions actually processed: 20166343
latency average = 0.475 ms
latency stddev = 0.369 ms
tps = 168027.223089 (including connections establishing)
tps = 168132.730758 (excluding connections establishing)
statement latencies in milliseconds:
         0.000  \set aid random(1, 100000 * :scale)
         0.474  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

--读写测试
$ /home/jerome/pg12/bin/pgbench -M prepared -v -r -P1 -c80 -j80 -T120 -Ujerome postgres 
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 120 s
number of transactions actually processed: 883436
latency average = 10.893 ms
latency stddev = 59.757 ms
tps = 7340.794306 (including connections establishing)
tps = 7342.004672 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \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)
         0.146  BEGIN;
         0.349  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.110  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.154  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.497  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.086  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         9.532  END;
复制