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;
10.测试数据
最后修改时间：2020-02-06 12:02:32
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
