前言
從可靠性和使用便利性來講單機RDBMS完勝N多各類數(shù)據(jù)庫，但當(dāng)數(shù)據(jù)量到了一定量之后，又不得不尋求分布式，列存儲等等解決方案。citus是基于PostgreSQL的分布式實時分析解決方案，由于其只是作為PostgreSQL的擴展插件而沒有動PG內(nèi)核，所有隨快速隨PG主版本升級，可靠性也非常值得信任。
citus在支持SQL特性上有一定的限制，比如不支持跨庫事務(wù)，不支持部分join和子查詢的寫法等等，做選型時需要留意(大部分的分布式系統(tǒng)對SQL支持或多或少都有些限制，不足為奇，按場景選型即可)。
citus主要適合下面兩種場景
多租戶
每個租戶的數(shù)據(jù)按租戶ID分片，互不干擾，避免跨庫操作。
實時數(shù)據(jù)分析
通過分片將數(shù)據(jù)打散到各個worker上，查詢時由master生成分布式執(zhí)行計劃驅(qū)動所有worker并行工作。支持過濾，投影，聚合，join等各類常見算子的下推。
在實時數(shù)據(jù)分析場景，單位時間的數(shù)據(jù)增量會很大，本文實測一下citus的數(shù)據(jù)插入能力(更新，刪除的性能類似)。
CentOS release 6.5 x64物理機(16C/128G/300GB SSD)
CPU: 2*8core 16核32線程， Intel(R) Xeon(R) CPU E5-2630 v3 @ 2.40GHz
PostgreSQL 9.6.2
citus 6.1.0
sysbench-1.0.3
master
192.168.0.177
worker(8個)
192.168.0.181~192.168.0.188
軟件的安裝都比較簡單，參考官方文檔即可，這里略過。
listen_addresses = '*'
port = 5432
max_connections = 1100
shared_buffers = 32GB
effective_cache_size = 96GB
work_mem = 16MB
maintenance_work_mem = 2GB
min_wal_size = 4GB
max_wal_size = 32GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
shared_preload_libraries = 'citus'
checkpoint_timeout = 60min
wal_level = replica
wal_compression = on
wal_level = replica
wal_log_hints = on
synchronous_commit = off
選用sysbench-1.0.3的oltp_insert.lua作為測試用例，執(zhí)行的SQL的示例如下：
INSERT INTO sbtest1 (id, k, c, pad) VALUES (525449452, 5005, '28491622445-08162085385-16839726209-31171823540-28539137588-93842246002-13643098812-68836434394-95216556185-07917709646', '49165640733-86514010343-02300194630-37380434155-24438915047')
但是，sysbench-1.0.3的oltp_insert.lua中有一個bug，需要先將其改正
i = sysbench.rand.unique()
==>
i = sysbench.rand.unique() - 2147483648
CREATE TABLE sbtest1
(
? id integer NOT NULL,
? k integer NOT NULL DEFAULT 0,
? c character(120) NOT NULL DEFAULT ''::bpchar,
? pad character(60) NOT NULL DEFAULT ''::bpchar,
? PRIMARY KEY (id)
);
CREATE INDEX k_1 ON sbtest1(k);
src/sysbench --test=src/lua/oltp_insert.lua \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres? \
--pgsql-db=dbone? \
--auto_inc=0? \
--time=10 \
--threads=128? \
--report-interval=1 \
run
TPS為134030
-bash-4.1$ src/sysbench --test=src/lua/oltp_insert.lua --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres? --pgsql-db=dbone? --auto_inc=0? --time=20 --threads=128? --report-interval=5 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.3 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 128
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 128 tps: 138381.74 qps: 138381.74 (r/w/o: 0.00/138381.74/0.00) lat (ms,95%): 2.07 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 128 tps: 134268.30 qps: 134268.30 (r/w/o: 0.00/134268.30/0.00) lat (ms,95%): 2.07 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 128 tps: 132830.91 qps: 132831.11 (r/w/o: 0.00/132831.11/0.00) lat (ms,95%): 2.07 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 128 tps: 132073.81 qps: 132073.61 (r/w/o: 0.00/132073.61/0.00) lat (ms,95%): 2.03 err/s: 0.00 reconn/s: 0.00
SQL statistics:
? ? queries performed:
? ? ? ? read:? ? ? ? ? ? ? ? ? ? ? ? ? ? 0
? ? ? ? write:? ? ? ? ? ? ? ? ? ? ? ? ? 2688192
? ? ? ? other:? ? ? ? ? ? ? ? ? ? ? ? ? 0
? ? ? ? total:? ? ? ? ? ? ? ? ? ? ? ? ? 2688192
? ? transactions:? ? ? ? ? ? ? ? ? ? ? ? 2688192 (134030.18 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 2688192 (134030.18 per sec.)
? ? ignored errors:? ? ? ? ? ? ? ? ? ? ? 0? ? ? (0.00 per sec.)
? ? reconnects:? ? ? ? ? ? ? ? ? ? ? ? ? 0? ? ? (0.00 per sec.)
General statistics:
? ? total time:? ? ? ? ? ? ? ? ? ? ? ? ? 20.0547s
? ? total number of events:? ? ? ? ? ? ? 2688192
Latency (ms):
? ? ? ? min:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0.10
? ? ? ? avg:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0.95
? ? ? ? max:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 88.80
? ? ? ? 95th percentile:? ? ? ? ? ? ? ? ? ? ? 2.07
? ? ? ? sum:? ? ? ? ? ? ? ? ? ? ? ? ? ? 2554006.85
Threads fairness:
? ? events (avg/stddev):? ? ? ? ? 21001.5000/178.10
? ? execution time (avg/stddev):? 19.9532/0.01
此時CPU利用率90%，已經(jīng)接近瓶頸。
-bash-4.1$ iostat sdc -xk 5
...
avg-cpu:? %user? %nice %system %iowait? %steal? %idle
? ? ? ? ? 69.12? ? 0.00? 20.56? ? 0.15? ? 0.00? 10.17
Device:? ? ? ? rrqm/s? wrqm/s? ? r/s? ? w/s? ? rkB/s? ? wkB/s avgrq-sz avgqu-sz? await? svctm? %util
sdc? ? ? ? ? ? ? 0.00 25302.60? 18.20? 705.20? ? 72.80 104019.20? 287.79? ? 5.96? ? 8.21? 0.81? 58.48
CREATE TABLE sbtest1
(
? id integer NOT NULL,
? k integer NOT NULL DEFAULT 0,
? c character(120) NOT NULL DEFAULT ''::bpchar,
? pad character(60) NOT NULL DEFAULT ''::bpchar,
? PRIMARY KEY (id)
);
CREATE INDEX k_1 ON sbtest1(k);
set citus.shard_count = 128;
set citus.shard_replication_factor = 1;
select create_distributed_table('sbtest1','id');
/bak/soft/sysbench-1.0.3/src/sysbench --test=/bak/soft/sysbench-1.0.3/src/lua/oltp_insert.lua \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres? \
--pgsql-db=dbcitus? \
--auto_inc=0? \
--time=10 \
--threads=64? \
--report-interval=1 \
run
TPS為44637，遠(yuǎn)低于單機。
-bash-4.1$ /bak/soft/sysbench-1.0.3/src/sysbench --test=/bak/soft/sysbench-1.0.3/src/lua/oltp_insert.lua --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres? --pgsql-db=dbcitus? --auto_inc=0? --time=20 --threads=64? --report-interval=5 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.3 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 64
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 64 tps: 44628.01 qps: 44628.01 (r/w/o: 0.00/44628.01/0.00) lat (ms,95%): 2.48 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 64 tps: 44780.80 qps: 44780.80 (r/w/o: 0.00/44780.80/0.00) lat (ms,95%): 2.48 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 64 tps: 44701.32 qps: 44701.72 (r/w/o: 0.00/44701.72/0.00) lat (ms,95%): 2.48 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 64 tps: 44801.41 qps: 44801.01 (r/w/o: 0.00/44801.01/0.00) lat (ms,95%): 2.48 err/s: 0.00 reconn/s: 0.00
SQL statistics:
? ? queries performed:
? ? ? ? read:? ? ? ? ? ? ? ? ? ? ? ? ? ? 0
? ? ? ? write:? ? ? ? ? ? ? ? ? ? ? ? ? 894715
? ? ? ? other:? ? ? ? ? ? ? ? ? ? ? ? ? 0
? ? ? ? total:? ? ? ? ? ? ? ? ? ? ? ? ? 894715
? ? transactions:? ? ? ? ? ? ? ? ? ? ? ? 894715 (44637.47 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 894715 (44637.47 per sec.)
? ? ignored errors:? ? ? ? ? ? ? ? ? ? ? 0? ? ? (0.00 per sec.)
? ? reconnects:? ? ? ? ? ? ? ? ? ? ? ? ? 0? ? ? (0.00 per sec.)
General statistics:
? ? total time:? ? ? ? ? ? ? ? ? ? ? ? ? 20.0421s
? ? total number of events:? ? ? ? ? ? ? 894715
Latency (ms):
? ? ? ? min:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0.42
? ? ? ? avg:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1.43
? ? ? ? max:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 203.28
? ? ? ? 95th percentile:? ? ? ? ? ? ? ? ? ? ? 2.48
? ? ? ? sum:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1277233.99
Threads fairness:
? ? events (avg/stddev):? ? ? ? ? 13979.9219/71.15
? ? execution time (avg/stddev):? 19.9568/0.01
性能瓶頸在master的CPU上，master生成執(zhí)行計劃消耗了大量CPU。
master
master的CPU利用率達(dá)到69%
[root@node1 ~]# iostat sdc -xk 5
Linux 2.6.32-431.el6.x86_64 (node1)? ? 2017年03月13日? ? _x86_64_? ? (32 CPU)
...
avg-cpu:? %user? %nice %system %iowait? %steal? %idle
? ? ? ? ? 50.61? ? 0.00? 17.80? ? 0.00? ? 0.00? 31.59
Device:? ? ? ? rrqm/s? wrqm/s? ? r/s? ? w/s? ? rkB/s? ? wkB/s avgrq-sz avgqu-sz? await? svctm? %util
sdc? ? ? ? ? ? ? 0.00? ? 0.00? ? 0.00? ? 0.00? ? 0.00? ? 0.00? ? 0.00? ? 0.00? ? 0.00? 0.00? 0.00
其中一個worker
worker的CPU利用率只有3%，IO也不高。
[root@node5 ~]# iostat sdc -xk 5
Linux 2.6.32-431.el6.x86_64 (node5)? ? 2017年03月13日? ? _x86_64_? ? (32 CPU)
...
avg-cpu:? %user? %nice %system %iowait? %steal? %idle
? ? ? ? ? 2.24? ? 0.00? ? 0.63? ? 0.00? ? 0.00? 97.13
Device:? ? ? ? rrqm/s? wrqm/s? ? r/s? ? w/s? ? rkB/s? ? wkB/s avgrq-sz avgqu-sz? await? svctm? %util
sdc? ? ? ? ? ? ? 0.00? 774.00? ? 0.00? 265.80? ? 0.00? 4159.20? ? 31.30? ? 0.25? ? 0.96? 0.01? 0.38
既然性能瓶頸在master上，那可以多搞幾個master，甚至每個worker都作為master。 這并不困難，只要把master上的元數(shù)據(jù)拷貝到每個worker上，worker就可以當(dāng)master用了。
在8個worker上分別執(zhí)行以下SQL：
CREATE TABLE sbtest1
(
? id integer NOT NULL,
? k integer NOT NULL DEFAULT 0,
? c character(120) NOT NULL DEFAULT ''::bpchar,
? pad character(60) NOT NULL DEFAULT ''::bpchar,
? PRIMARY KEY (id)
);
CREATE INDEX k_1 ON sbtest1(k);
copy pg_dist_node from PROGRAM 'psql "host=192.168.0.177 port=5432 dbname=dbcitus user=postgres" -Atc "copy pg_dist_node to STDOUT"';
copy pg_dist_partition from PROGRAM 'psql "host=192.168.0.177 port=5432 dbname=dbcitus user=postgres" -Atc "copy pg_dist_partition to STDOUT"';
copy pg_dist_shard from PROGRAM 'psql "host=192.168.0.177 port=5432 dbname=dbcitus user=postgres" -Atc "copy pg_dist_shard to STDOUT"';
copy pg_dist_shard_placement from PROGRAM 'psql "host=192.168.0.177 port=5432 dbname=dbcitus user=postgres" -Atc "copy pg_dist_shard_placement to STDOUT"';
copy pg_dist_colocation from PROGRAM 'psql "host=192.168.0.177 port=5432 dbname=dbcitus user=postgres" -Atc "copy pg_dist_colocation to STDOUT"';
分別修改每個worker上的oltp_insert.lua中下面一行,使各個worker上產(chǎn)生的主鍵不容易沖突
i = sysbench.rand.unique() - 2147483648
worker2
i = sysbench.rand.unique() - 2147483648 + 1
worker3
i = sysbench.rand.unique() - 2147483648 + 2
...
worker8
i = sysbench.rand.unique() - 2147483648 + 7
在每個worker上準(zhǔn)備測試腳本
/tmp/run_oltp_insert.sh:
#!/bin/bash
cd /bak/soft/sysbench-1.0.3
/bak/soft/sysbench-1.0.3/src/sysbench /bak/soft/sysbench-1.0.3/src/lua/oltp_insert.lua \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres? \
--pgsql-db=dbcitus? \
--auto_inc=0? \
--time=60 \
--threads=64? \
--report-interval=5 \
run >/tmp/run_oltp_insert.log 2>&1
在每個worker上同時執(zhí)行insert測試
[root@node1 ~]# for i in `seq 1 8` ; do ssh 192.168.0.18$i /tmp/run_oltp_insert.sh >/dev/null 2>&1 &? done
[10] 27332
[11] 27333
[12] 27334
[13] 27335
[14] 27336
[15] 27337
[16] 27338
[17] 27339
在其中一個worker上的執(zhí)行結(jié)果如下，QPS 2.5w
-bash-4.1$ cat /tmp/run_oltp_insert.log
sysbench 1.0.3 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 64
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 64 tps: 25662.78 qps: 25662.78 (r/w/o: 0.00/25662.78/0.00) lat (ms,95%): 6.67 err/s: 2.60 reconn/s: 0.00
[ 10s ] thds: 64 tps: 26225.38 qps: 26225.38 (r/w/o: 0.00/26225.38/0.00) lat (ms,95%): 6.67 err/s: 7.00 reconn/s: 0.00
[ 15s ] thds: 64 tps: 25996.42 qps: 25996.42 (r/w/o: 0.00/25996.42/0.00) lat (ms,95%): 6.79 err/s: 11.40 reconn/s: 0.00
[ 20s ] thds: 64 tps: 25670.36 qps: 25670.36 (r/w/o: 0.00/25670.36/0.00) lat (ms,95%): 6.79 err/s: 18.60 reconn/s: 0.00
[ 25s ] thds: 64 tps: 25620.89 qps: 25620.89 (r/w/o: 0.00/25620.89/0.00) lat (ms,95%): 6.79 err/s: 22.60 reconn/s: 0.00
[ 30s ] thds: 64 tps: 25357.39 qps: 25357.39 (r/w/o: 0.00/25357.39/0.00) lat (ms,95%): 6.91 err/s: 33.40 reconn/s: 0.00
[ 35s ] thds: 64 tps: 25247.67 qps: 25247.67 (r/w/o: 0.00/25247.67/0.00) lat (ms,95%): 6.91 err/s: 34.60 reconn/s: 0.00
[ 40s ] thds: 64 tps: 25069.27 qps: 25069.27 (r/w/o: 0.00/25069.27/0.00) lat (ms,95%): 6.91 err/s: 41.00 reconn/s: 0.00
[ 45s ] thds: 64 tps: 24796.27 qps: 24796.27 (r/w/o: 0.00/24796.27/0.00) lat (ms,95%): 7.04 err/s: 49.40 reconn/s: 0.00
[ 50s ] thds: 64 tps: 24801.00 qps: 24801.00 (r/w/o: 0.00/24801.00/0.00) lat (ms,95%): 7.04 err/s: 47.40 reconn/s: 0.00
[ 55s ] thds: 64 tps: 24752.83 qps: 24752.83 (r/w/o: 0.00/24752.83/0.00) lat (ms,95%): 7.04 err/s: 57.20 reconn/s: 0.00
[ 60s ] thds: 64 tps: 24533.35 qps: 24533.35 (r/w/o: 0.00/24533.35/0.00) lat (ms,95%): 7.17 err/s: 63.60 reconn/s: 0.00
SQL statistics:
? ? queries performed:
? ? ? ? read:? ? ? ? ? ? ? ? ? ? ? ? ? ? 0
? ? ? ? write:? ? ? ? ? ? ? ? ? ? ? ? ? 1518786
? ? ? ? other:? ? ? ? ? ? ? ? ? ? ? ? ? 0
? ? ? ? total:? ? ? ? ? ? ? ? ? ? ? ? ? 1518786
? ? transactions:? ? ? ? ? ? ? ? ? ? ? ? 1518786 (25277.24 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1518786 (25277.24 per sec.)
? ? ignored errors:? ? ? ? ? ? ? ? ? ? ? 1944? (32.35 per sec.)
? ? reconnects:? ? ? ? ? ? ? ? ? ? ? ? ? 0? ? ? (0.00 per sec.)
General statistics:
? ? total time:? ? ? ? ? ? ? ? ? ? ? ? ? 60.0829s
? ? total number of events:? ? ? ? ? ? ? 1518786
Latency (ms):
? ? ? ? min:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0.47
? ? ? ? avg:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2.53
? ? ? ? max:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1015.04
? ? ? ? 95th percentile:? ? ? ? ? ? ? ? ? ? ? 6.91
? ? ? ? sum:? ? ? ? ? ? ? ? ? ? ? ? ? ? 3835098.18
Threads fairness:
? ? events (avg/stddev):? ? ? ? ? 23731.0312/213.31
? ? execution time (avg/stddev):? 59.9234/0.02
CPU消耗了66%
-bash-4.1$ iostat sdc -xk 5
Linux 2.6.32-431.el6.x86_64 (node5)? ? 2017年03月13日? ? _x86_64_? ? (32 CPU)
avg-cpu:? %user? %nice %system %iowait? %steal? %idle
? ? ? ? ? 47.09? ? 0.00? 18.35? ? 0.47? ? 0.00? 34.10
Device:? ? ? ? rrqm/s? wrqm/s? ? r/s? ? w/s? ? rkB/s? ? wkB/s avgrq-sz avgqu-sz? await? svctm? %util
sdc? ? ? ? ? ? ? 0.00? 4195.60? ? 0.00 19787.60? ? 0.00 95932.80? ? 9.70? ? 0.98? ? 0.05? 0.02? 42.54
8臺worker的總qps為214362
[root@node1 ~]# for i in `seq 1 8` ; do ssh 192.168.0.18$i grep queries: /tmp/run_oltp_insert.log ; done
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1518786 (25277.24 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1587323 (26412.68 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1700562 (28305.06 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1631516 (27151.82 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1615778 (26885.48 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1649236 (27449.03 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1621940 (26993.20 per sec.)
? ? queries:? ? ? ? ? ? ? ? ? ? ? ? ? ? 1554917 (25890.71 per sec.)
在master上查詢插入的記錄數(shù)。
dbcitus=# select count(1) from sbtest1;
? count?
----------
12880058
(1 行記錄)
時間：73.197 ms
查詢是在128個分片上并行執(zhí)行的，所以速度很快。
citus的執(zhí)行計劃生成影響了數(shù)據(jù)插入的速度,通過Masterless部署可提升到20w/s以上。
進(jìn)一步提升插入性能可以從citus源碼入手，根據(jù)分片列值做快速SQL分發(fā)，避免在master上解析SQL，之前在另一個場景上做過原型，性能可提升10倍以上。
極致的做法是繞過master直接插入數(shù)據(jù)到worker上的分片表，還可以利用copy或批更新。
Real-time Inserts:0-50k/s
Real-time Updates:0-50k/s
Bulk Copy:100-200k/s
Masterless Citus:50k/s-500k/s
