Citus集群简介
Citus是Postgres的开源扩展，将Postgres转换成一个分布式数据库，在集群的多个节点上分发数据和查询，具有像分片、分布式SQL引擎、复制表和分布式表等特性。
因为Citus是Postgres的扩展(而不是一个独立的代码分支)，所以当你使用Citus时，你也在使用Postgres，可以利用最新的Postgres特性、工具和生态系统。
Citus是一款基于PostgreSQL的开源分布式数据库，自动继承了PostgreSQL强大的SQL支持能力和应用生态（不仅是客户端协议的兼容还包括服务端扩展和管理工具的完全兼容）。Citus是PostgreSQL的扩展（not a fork），采用shared nothing架构，节点之间无共享数据，由协调器节点和Work节点构成一个数据库集群。专注于高性能HTAP分布式数据库。
相比单机PostgreSQL，Citus可以使用更多的CPU核心，更多的内存数量，保存更多的数据。通过向集群添加节点，可以轻松的扩展数据库。与其他类似的基于PostgreSQL的分布式方案，比如Greenplum，PostgreSQL-XL相比，citus最大的不同在于它是一个PostgreSQL扩展而不是一个独立的代码分支。Citus可以用很小的代价和更快的速度紧跟PostgreSQL的版本演进；同时又能最大程度的保证数据库的稳定性和兼容性。
Citus支持新版本PostgreSQL的特性，并保持与现有工具的兼容。Citus使用分片和复制在多台机器上横向扩展PostgreSQL。它的查询引擎将在这些服务器上执行SQL进行并行化查询，以便在大型数据集上实现实时（不到一秒）的响应。
Citus集群由一个中心的协调节点(CN)和若干个工作节点(Worker)构成。
coordinate：协调节点，一般称为cn，存储所有元数据，不存实际数据，该节点直接对用户开放，等于一个客户端。
worker：工作节点，不存储元数据，存储实际数据。执行协调节点发来的查询请求。一般不直接对用户开放。
环境
| 主机名 | IP | 角色 | 端口 | 备注 |
|---|---|---|---|---|
| coordinate | 172.72.6.80 | coordinate | 5432 | 安装PG 13.3 + Citus 10.2.4 |
| worker1 | 172.72.6.81 | worker | 5432 | 安装PG 13.3 + Citus 10.2.4 |
| worker2 | 172.72.6.82 | worker | 5432 | 安装PG 13.3 + Citus 10.2.4 |
| worker3 | 172.72.6.83 | worker | 5432 | 安装PG 13.3 + Citus 10.2.4 |
| worker4 | 172.72.6.84 | worker | 5432 | 安装PG 13.3 + Citus 10.2.4 |
1-- 网卡
2docker network create --subnet=172.72.6.0/24 pg-network
3
4
5-- pg cn
6docker rm -f lhrpgcituscn80
7docker run -d --name lhrpgcituscn80 -h lhrpgcituscn80 \
8 --net=pg-network --ip 172.72.6.80 \
9 -p 64380:5432 \
10 -v /sys/fs/cgroup:/sys/fs/cgroup \
11 --privileged=true lhrbest/lhrpgall:2.0 \
12 /usr/sbin/init
13
14docker rm -f lhrpgcitusdn81
15docker run -d --name lhrpgcitusdn81 -h lhrpgcitusdn81 \
16 --net=pg-network --ip 172.72.6.81 \
17 -p 64381:5432 \
18 -v /sys/fs/cgroup:/sys/fs/cgroup \
19 --privileged=true lhrbest/lhrpgall:2.0 \
20 /usr/sbin/init
21
22
23docker rm -f lhrpgcitusdn82
24docker run -d --name lhrpgcitusdn82 -h lhrpgcitusdn82 \
25 --net=pg-network --ip 172.72.6.82 \
26 -p 64382:5432 \
27 -v /sys/fs/cgroup:/sys/fs/cgroup \
28 --privileged=true lhrbest/lhrpgall:2.0 \
29 /usr/sbin/init
30
31
32docker rm -f lhrpgcitusdn83
33docker run -d --name lhrpgcitusdn83 -h lhrpgcitusdn83 \
34 --net=pg-network --ip 172.72.6.83 \
35 -p 64383:5432 \
36 -v /sys/fs/cgroup:/sys/fs/cgroup \
37 --privileged=true lhrbest/lhrpgall:2.0 \
38 /usr/sbin/init
39
40
41docker rm -f lhrpgcitusdn84
42docker run -d --name lhrpgcitusdn84 -h lhrpgcitusdn84 \
43 --net=pg-network --ip 172.72.6.84 \
44 -p 64384:5432 \
45 -v /sys/fs/cgroup:/sys/fs/cgroup \
46 --privileged=true lhrbest/lhrpgall:2.0 \
47 /usr/sbin/init
48
49
50[root@docker35 ~]# docker ps
51CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
520183e7a9704a lhrbest/lhrpgall:2.0 "/usr/sbin/init" 6 seconds ago Up 3 seconds 0.0.0.0:64384->5432/tcp, :::64384->5432/tcp lhrpgcitusdn84
53877d897a5a76 lhrbest/lhrpgall:2.0 "/usr/sbin/init" 8 seconds ago Up 6 seconds 0.0.0.0:64383->5432/tcp, :::64383->5432/tcp lhrpgcitusdn83
5498dafcefc505 lhrbest/lhrpgall:2.0 "/usr/sbin/init" 10 seconds ago Up 7 seconds 0.0.0.0:64382->5432/tcp, :::64382->5432/tcp lhrpgcitusdn82
5504510e0bfa96 lhrbest/lhrpgall:2.0 "/usr/sbin/init" 11 seconds ago Up 10 seconds 0.0.0.0:64381->5432/tcp, :::64381->5432/tcp lhrpgcitusdn81
568cf991b0633f lhrbest/lhrpgall:2.0 "/usr/sbin/init" 13 seconds ago Up 11 seconds 0.0.0.0:64380->5432/tcp, :::64380->5432/tcp lhrpgcituscn80
防火墙修改
其中，coordinate节点的pg_hba.conf配置：
1cat >> /var/lib/pgsql/13/data/pg_hba.conf <<"EOF"
2host all all 0.0.0.0/0 md5
3EOF
worker节点的pg_hba.conf配置:
1cat >> /var/lib/pgsql/13/data/pg_hba.conf <<"EOF"
2host all all 172.72.6.0/24 trust
3EOF
安装citus
在每个节点上都安装citus，包括cn和dn。
可以在以下位置下载citus的源码：
https://github.com/citusdata/citus/releases
https://pgxn.org/dist/citus/10.2.4/
最新版本10.2.4，如下：
1-- yum直接安装
2yum list | grep citus
3yum install -y citus_13
4
5
6su - postgresql
7psql
8create database lhrdb;
9\c lhrdb
10alter system set shared_preload_libraries='citus';
11select * from pg_available_extensions where name='citus';
12
13pg_ctl restart
14psql -d lhrdb
15create extension citus;
16
17\dx
18\dx+ citus
安装过程：
1[postgres@lhrpgcituscn80 ~]$ psql
2psql (13.3)
3Type "help" for help.
4
5postgres=# create database lhrdb;
6CREATE DATABASE
7postgres=# \c lhrdb;
8You are now connected to database "lhrdb" as user "postgres".
9lhrdb=# create extension citus;
10ERROR: Citus can only be loaded via shared_preload_libraries
11HINT: Add citus to shared_preload_libraries configuration variable in postgresql.conf in master and workers. Note that citus should be at the beginning of shared_preload_libraries.
12lhrdb=# show shared_preload_libraries;
13 shared_preload_libraries
14--------------------------
15
16(1 row)
17
18lhrdb=# alter system set shared_preload_libraries='citus';
19ALTER SYSTEM
20
21lhrdb=# select pg_reload_conf();
22 pg_reload_conf
23----------------
24 t
25(1 row)
26
27lhrdb=# select * from pg_available_extensions where name='citus';
28 name | default_version | installed_version | comment
29-------+-----------------+-------------------+----------------------------
30 citus | 10.2-4 | | Citus distributed database
31(1 row)
32
33lhrdb=# exit
34[postgres@lhrpgcituscn80 ~]$ pg_ctl restart
35waiting for server to shut down.... done
36server stopped
37waiting for server to start....2022-02-17 10:27:34.354 CST [1589] LOG: number of prepared transactions has not been configured, overriding
382022-02-17 10:27:34.354 CST [1589] DETAIL: max_prepared_transactions is now set to 200
392022-02-17 10:27:34.415 CST [1589] LOG: redirecting log output to logging collector process
402022-02-17 10:27:34.415 CST [1589] HINT: Future log output will appear in directory "pg_log".
41 done
42server started
43[postgres@lhrpgcituscn80 ~]$ psql
44psql (13.3)
45Type "help" for help.
46
47postgres=# \c lhrdb
48You are now connected to database "lhrdb" as user "postgres".
49lhrdb=# show shared_preload_libraries;
50 shared_preload_libraries
51--------------------------
52 citus
53(1 row)
54
55lhrdb=# create extension citus;
56CREATE EXTENSION
57lhrdb=# \dx
58 List of installed extensions
59 Name | Version | Schema | Description
60---------+---------+------------+------------------------------
61 citus | 10.2-4 | pg_catalog | Citus distributed database
62 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
63(2 rows)
64
65lhrdb=# \dx+ citus
66 Objects in extension "citus"
67 Object description
68-----------------------------------------------------------------------------------------------------------------
69 access method columnar
70 event trigger citus_cascade_to_partition
71 function alter_columnar_table_reset(regclass,boolean,boolean,boolean,boolean)
72 function alter_columnar_table_set(regclass,integer,integer,name,integer)
73 function alter_distributed_table(regclass,text,integer,text,boolean)
74 function alter_old_partitions_set_access_method(regclass,timestamp with time zone,name)
75 function alter_role_if_exists(text,text)
76 function alter_table_set_access_method(regclass,text)
77 function any_value_agg(anyelement,anyelement)
78 function any_value(anyelement)
79 function array_cat_agg(anyarray)
80 function assign_distributed_transaction_id(integer,bigint,timestamp with time zone)
81 function authinfo_valid(text)
82 function broadcast_intermediate_result(text,text)
83 function check_distributed_deadlocks()
84 function citus_activate_node(text,integer)
85 function citus_add_inactive_node(text,integer,integer,noderole,name)
86 function citus_add_local_table_to_metadata(regclass,boolean)
87 function citus_add_node(text,integer,integer,noderole,name)
88 function citus_add_rebalance_strategy(name,regproc,regproc,regproc,real,real,real)
89 function citus_add_secondary_node(text,integer,text,integer,name)
90 function citus_blocking_pids(integer)
91 function citus_cleanup_orphaned_shards()
92 function citus_conninfo_cache_invalidate()
93 function citus_copy_shard_placement(bigint,text,integer,text,integer,boolean,citus.shard_transfer_mode)
94 function citus_create_restore_point(text)
95 function citus_disable_node(text,integer)
96 function citus_dist_local_group_cache_invalidate()
97 function citus_dist_node_cache_invalidate()
98 function citus_dist_object_cache_invalidate()
99 function citus_dist_partition_cache_invalidate()
100 function citus_dist_placement_cache_invalidate()
101 function citus_dist_shard_cache_invalidate()
102 function citus_dist_stat_activity()
103 function citus_drain_node(text,integer,citus.shard_transfer_mode,name)
104 function citus_drop_all_shards(regclass,text,text,boolean)
105 function citus_drop_trigger()
106 function citus_executor_name(integer)
107 function citus_extradata_container(internal)
108 function citus_finish_pg_upgrade()
109 function citus_get_active_worker_nodes()
110 function citus_internal_add_partition_metadata(regclass,"char",text,integer,"char")
111 function citus_internal_add_placement_metadata(bigint,integer,bigint,integer,bigint)
112 function citus_internal_add_shard_metadata(regclass,bigint,"char",text,text)
113 function citus_internal.columnar_ensure_am_depends_catalog()
114 function citus_internal_delete_shard_metadata(bigint)
115 function citus_internal.downgrade_columnar_storage(regclass)
116 function citus_internal.find_groupid_for_node(text,integer)
117 function citus_internal.pg_dist_node_trigger_func()
118 function citus_internal.pg_dist_rebalance_strategy_trigger_func()
119 function citus_internal.pg_dist_shard_placement_trigger_func()
120 function citus_internal.refresh_isolation_tester_prepared_statement()
121 function citus_internal.replace_isolation_tester_func()
122 function citus_internal.restore_isolation_tester_func()
123 function citus_internal_update_placement_metadata(bigint,integer,integer)
124 function citus_internal_update_relation_colocation(oid,integer)
125 function citus_internal.upgrade_columnar_storage(regclass)
126 function citus_isolation_test_session_is_blocked(integer,integer[])
127 function citus_jsonb_concatenate_final(jsonb)
128 function citus_jsonb_concatenate(jsonb,jsonb)
129 function citus_json_concatenate_final(json)
130 function citus_json_concatenate(json,json)
131 function citus_local_disk_space_stats()
132 function citus_move_shard_placement(bigint,text,integer,text,integer,citus.shard_transfer_mode)
133 function citus_node_capacity_1(integer)
134 function citus_prepare_pg_upgrade()
135 function citus_query_stats()
136 function citus_relation_size(regclass)
137 function citus_remote_connection_stats()
138 function citus_remove_node(text,integer)
139 function citus_server_id()
140 function citus_set_coordinator_host(text,integer,noderole,name)
141 function citus_set_default_rebalance_strategy(text)
142 function citus_set_node_property(text,integer,text,boolean)
143 function citus_shard_allowed_on_node_true(bigint,integer)
144 function citus_shard_cost_1(bigint)
145 function citus_shard_cost_by_disk_size(bigint)
146 function citus_shard_sizes()
147 function citus_stat_statements()
148 function citus_stat_statements_reset()
149 function citus_table_is_visible(oid)
150 function citus_table_size(regclass)
151 function citus_text_send_as_jsonb(text)
152 function citus_total_relation_size(regclass,boolean)
153 function citus_truncate_trigger()
154 function citus_unmark_object_distributed(oid,oid,integer)
155 function citus_update_node(integer,text,integer,boolean,integer)
156 function citus_update_shard_statistics(bigint)
157 function citus_update_table_statistics(regclass)
158 function citus_validate_rebalance_strategy_functions(regproc,regproc,regproc)
159 function citus_version()
160 function citus_worker_stat_activity()
161 function columnar.columnar_handler(internal)
162 function column_name_to_column(regclass,text)
163 function column_to_column_name(regclass,text)
164 function coord_combine_agg_ffunc(internal,oid,cstring,anyelement)
165 function coord_combine_agg(oid,cstring,anyelement)
166 function coord_combine_agg_sfunc(internal,oid,cstring,anyelement)
167 function create_distributed_function(regprocedure,text,text)
168 function create_distributed_table(regclass,text,citus.distribution_type,text,integer)
169 function create_intermediate_result(text,text)
170 function create_reference_table(regclass)
171 function create_time_partitions(regclass,interval,timestamp with time zone,timestamp with time zone)
172 function distributed_tables_colocated(regclass,regclass)
173 function drop_old_time_partitions(regclass,timestamp with time zone)
174 function dump_global_wait_edges()
175 function dump_local_wait_edges()
176 function fetch_intermediate_results(text[],text,integer)
177 function fix_all_partition_shard_index_names()
178 function fix_partition_shard_index_names(regclass)
179 function fix_pre_citus10_partitioned_table_constraint_names()
180 function fix_pre_citus10_partitioned_table_constraint_names(regclass)
181 function get_all_active_transactions()
182 function get_colocated_shard_array(bigint)
183 function get_colocated_table_array(regclass)
184 function get_current_transaction_id()
185 function get_global_active_transactions()
186 function get_missing_time_partition_ranges(regclass,interval,timestamp with time zone,timestamp with time zone)
187 function get_rebalance_progress()
188 function get_rebalance_table_shards_plan(regclass,real,integer,bigint[],boolean,name,real)
189 function get_shard_id_for_distribution_column(regclass,"any")
190 function isolate_tenant_to_new_shard(regclass,"any",text)
191 function jsonb_cat_agg(jsonb)
192 function json_cat_agg(json)
193 function lock_relation_if_exists(text,text)
194 function lock_shard_metadata(integer,bigint[])
195 function lock_shard_resources(integer,bigint[])
196 function master_activate_node(text,integer)
197 function master_add_inactive_node(text,integer,integer,noderole,name)
198 function master_add_node(text,integer,integer,noderole,name)
199 function master_add_secondary_node(text,integer,text,integer,name)
200 function master_append_table_to_shard(bigint,text,text,integer)
201 function master_apply_delete_command(text)
202 function master_copy_shard_placement(bigint,text,integer,text,integer,boolean,citus.shard_transfer_mode)
203 function master_create_empty_shard(text)
204 function master_disable_node(text,integer)
205 function master_drain_node(text,integer,citus.shard_transfer_mode,name)
206 function master_get_active_worker_nodes()
207 function master_get_new_placementid()
208 function master_get_new_shardid()
209 function master_get_table_ddl_events(text)
210 function master_get_table_metadata(text)
211 function master_move_shard_placement(bigint,text,integer,text,integer,citus.shard_transfer_mode)
212 function master_remove_distributed_table_metadata_from_workers(regclass,text,text)
213 function master_remove_node(text,integer)
214 function master_remove_partition_metadata(regclass,text,text)
215 function master_run_on_worker(text[],integer[],text[],boolean)
216 function master_set_node_property(text,integer,text,boolean)
217 function master_unmark_object_distributed(oid,oid,integer)
218 function master_update_node(integer,text,integer,boolean,integer)
219 function master_update_shard_statistics(bigint)
220 function master_update_table_statistics(regclass)
221 function notify_constraint_dropped()
222 function poolinfo_valid(text)
223 function read_intermediate_results(text[],citus_copy_format)
224 function read_intermediate_result(text,citus_copy_format)
225 function rebalance_table_shards(regclass,real,integer,bigint[],citus.shard_transfer_mode,boolean,name)
226 function recover_prepared_transactions()
227 function relation_is_a_known_shard(regclass)
228 function remove_local_tables_from_metadata()
229 function replicate_reference_tables()
230 function replicate_table_shards(regclass,integer,integer,bigint[],citus.shard_transfer_mode)
231 function role_exists(name)
232 function run_command_on_colocated_placements(regclass,regclass,text,boolean)
233 function run_command_on_placements(regclass,text,boolean)
234 function run_command_on_shards(regclass,text,boolean)
235 function run_command_on_workers(text,boolean)
236 function shard_name(regclass,bigint)
237 function start_metadata_sync_to_node(text,integer)
238 function stop_metadata_sync_to_node(text,integer,boolean)
239 function time_partition_range(regclass)
240 function truncate_local_data_after_distributing_table(regclass)
241 function undistribute_table(regclass,boolean)
242 function update_distributed_table_colocation(regclass,text)
243 function worker_append_table_to_shard(text,text,text,integer)
244 function worker_apply_inter_shard_ddl_command(bigint,text,bigint,text,text)
245 function worker_apply_sequence_command(text)
246 function worker_apply_sequence_command(text,regtype)
247 function worker_apply_shard_ddl_command(bigint,text)
248 function worker_apply_shard_ddl_command(bigint,text,text)
249 function worker_change_sequence_dependency(regclass,regclass,regclass)
250 function worker_cleanup_job_schema_cache()
251 function worker_create_or_alter_role(text,text,text)
252 function worker_create_or_replace_object(text)
253 function worker_create_schema(bigint,text)
254 function worker_create_truncate_trigger(regclass)
255 function worker_drop_distributed_table(text)
256 function worker_fetch_foreign_file(text,text,bigint,text[],integer[])
257 function worker_fetch_partition_file(bigint,integer,integer,integer,text,integer)
258 function worker_fix_partition_shard_index_names(regclass,text,text)
259 function worker_fix_pre_citus10_partitioned_table_constraint_names(regclass,bigint,text)
260 function worker_hash("any")
261 function worker_hash_partition_table(bigint,integer,text,text,oid,anyarray)
262 function worker_last_saved_explain_analyze()
263 function worker_merge_files_into_table(bigint,integer,text[],text[])
264 function worker_nextval(regclass)
265 function worker_partial_agg_ffunc(internal)
266 function worker_partial_agg(oid,anyelement)
267 function worker_partial_agg_sfunc(internal,oid,anyelement)
268 function worker_partitioned_relation_size(regclass)
269 function worker_partitioned_relation_total_size(regclass)
270 function worker_partitioned_table_size(regclass)
271 function worker_partition_query_result(text,text,integer,citus.distribution_type,text[],text[],boolean)
272 function worker_range_partition_table(bigint,integer,text,text,oid,anyarray)
273 function worker_record_sequence_dependency(regclass,regclass,name)
274 function worker_repartition_cleanup(bigint)
275 function worker_save_query_explain_analyze(text,jsonb)
276 schema citus
277 schema citus_internal
278 schema columnar
279 sequence columnar.storageid_seq
280 sequence pg_dist_colocationid_seq
281 sequence pg_dist_groupid_seq
282 sequence pg_dist_node_nodeid_seq
283 sequence pg_dist_placement_placementid_seq
284 sequence pg_dist_shardid_seq
285 table citus.pg_dist_object
286 table columnar.chunk
287 table columnar.chunk_group
288 table columnar.options
289 table columnar.stripe
290 table pg_dist_authinfo
291 table pg_dist_colocation
292 table pg_dist_local_group
293 table pg_dist_node
294 table pg_dist_node_metadata
295 table pg_dist_partition
296 table pg_dist_placement
297 table pg_dist_poolinfo
298 table pg_dist_rebalance_strategy
299 table pg_dist_shard
300 table pg_dist_transaction
301 type citus_copy_format
302 type citus.distribution_type
303 type citus.shard_transfer_mode
304 type noderole
305 view citus_dist_stat_activity
306 view citus_lock_waits
307 view citus_shard_indexes_on_worker
308 view citus_shards
309 view citus_shards_on_worker
310 view citus_stat_statements
311 view citus_tables
312 view citus_worker_stat_activity
313 view pg_dist_shard_placement
314 view time_partitions
315(246 rows)
316
集群配置
协调节点新增工作节点
管理操作仅仅在协调节点（cn）上操作：
1[postgres@lhrpgcituscn80 ~]$ psql -d lhrdb
2psql (13.3)
3Type "help" for help.
4
5lhrdb=#
6
7-- 节点可以是ip或者dns name
8SELECT * from master_add_node('172.72.6.81', 5432);
9SELECT * from master_add_node('172.72.6.82', 5432);
10SELECT * from master_add_node('172.72.6.83', 5432);
11SELECT * from master_add_node('172.72.6.84', 5432);
12
13-- 查看工作节点：
14lhrdb=# SELECT * FROM master_get_active_worker_nodes();
15 node_name | node_port
16-------------+-----------
17 172.72.6.81 | 5432
18 172.72.6.83 | 5432
19 172.72.6.84 | 5432
20 172.72.6.82 | 5432
21(4 rows)
22lhrdb=# select * from pg_dist_node;
23 nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
24--------+---------+-------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
25 1 | 1 | 172.72.6.81 | 5432 | default | f | t | primary | default | f | t
26 2 | 2 | 172.72.6.82 | 5432 | default | f | t | primary | default | f | t
27 3 | 3 | 172.72.6.83 | 5432 | default | f | t | primary | default | f | t
28 4 | 4 | 172.72.6.84 | 5432 | default | f | t | primary | default | f | t
29(4 rows)
30
创建分片表
1lhrdb=# create table test(id int primary key ,name varchar);
2
3#配置分片策略
4#设置分片数，4个主机，设置分片4，每个主机一个分片
5lhrdb=# set citus.shard_count=4;
6# 配置副本数
7lhrdb=# set citus.shard_replication_factor=2;
8lhrdb=# SELECT create_distributed_table('test', 'id', 'hash');
9lhrdb=# insert into test select id,md5(random()::text) from generate_series(1,500) as id;
10
11# 查看分片分布
12lhrdb=# select * from citus_tables;
13 table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method
14------------+------------------+---------------------+---------------+------------+-------------+-------------+---------------
15 test | distributed | id | 1 | 384 kB | 4 | postgres | heap
16(1 row)
17
18lhrdb=# select * from master_get_table_metadata('test');
19 logical_relid | part_storage_type | part_method | part_key | part_replica_count | part_max_size | part_placement_policy
20---------------+-------------------+-------------+----------+--------------------+---------------+-----------------------
21 16995 | t | h | id | 2 | 1073741824 | 2
22(1 row)
23
24
25lhrdb=# select * from pg_dist_placement where shardid in (select shardid from pg_dist_shard where logicalrelid='test'::regclass);
26 placementid | shardid | shardstate | shardlength | groupid
27-------------+---------+------------+-------------+---------
28 1 | 102008 | 1 | 0 | 1
29 2 | 102008 | 1 | 0 | 2
30 3 | 102009 | 1 | 0 | 2
31 4 | 102009 | 1 | 0 | 3
32 5 | 102010 | 1 | 0 | 3
33 6 | 102010 | 1 | 0 | 4
34 7 | 102011 | 1 | 0 | 4
35 8 | 102011 | 1 | 0 | 1
36(8 rows)
37
38
39lhrdb=# SELECT * from pg_dist_shard_placement order by shardid, placementid;
40 shardid | shardstate | shardlength | nodename | nodeport | placementid
41---------+------------+-------------+-------------+----------+-------------
42 102008 | 1 | 0 | 172.72.6.81 | 5432 | 1
43 102008 | 1 | 0 | 172.72.6.82 | 5432 | 2
44 102009 | 1 | 0 | 172.72.6.82 | 5432 | 3
45 102009 | 1 | 0 | 172.72.6.83 | 5432 | 4
46 102010 | 1 | 0 | 172.72.6.83 | 5432 | 5
47 102010 | 1 | 0 | 172.72.6.84 | 5432 | 6
48 102011 | 1 | 0 | 172.72.6.84 | 5432 | 7
49 102011 | 1 | 0 | 172.72.6.81 | 5432 | 8
50(8 rows)
51
52lhrdb=# select count(*) from test;
53 count
54-------
55 500
56(1 row)
57
58-- 查看分片表
59[postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.80 -d lhrdb -c "\dt";
60 List of relations
61 Schema | Name | Type | Owner
62--------+------+-------+----------
63 public | test | table | postgres
64(1 row)
65
66[postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.81 -d lhrdb -c "\dt";
67 List of relations
68 Schema | Name | Type | Owner
69--------+-------------+-------+----------
70 public | test_102008 | table | postgres
71 public | test_102011 | table | postgres
72(2 rows)
73
74[postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.82 -d lhrdb -c "\dt";
75 List of relations
76 Schema | Name | Type | Owner
77--------+-------------+-------+----------
78 public | test_102008 | table | postgres
79 public | test_102009 | table | postgres
80(2 rows)
81
82[postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.83 -d lhrdb -c "\dt";
83 List of relations
84 Schema | Name | Type | Owner
85--------+-------------+-------+----------
86 public | test_102009 | table | postgres
87 public | test_102010 | table | postgres
88(2 rows)
89
90[postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.84 -d lhrdb -c "\dt";
91 List of relations
92 Schema | Name | Type | Owner
93--------+-------------+-------+----------
94 public | test_102010 | table | postgres
95 public | test_102011 | table | postgres
96(2 rows)
有4个worker，所以数据分片为4，每个分片，做两个副本。
通过分片分布，如102008分布在172.72.6.81，172.72.6.82上，同理102009分布在172.72.6.82，172.72.6.83上。
假设6.81机器宕机了，集群访问102008原先是方位6.81的，现在会自动访问6.82上的102008分片。也就是说，单个数据节点故障，集群还能正常用，通过多设置副本，多个节点故障也能更强壮。
CN节点的进程：
1[root@lhrpgcituscn80 /]# ps -ef|grep post
2postgres 1589 0 0 10:27 ? 00:00:00 /usr/pgsql-13/bin/postgres -D /var/lib/pgsql/13/data/
3postgres 1590 1589 0 10:27 ? 00:00:00 postgres: logger
4postgres 1592 1589 0 10:27 ? 00:00:00 postgres: checkpointer
5postgres 1593 1589 0 10:27 ? 00:00:00 postgres: background writer
6postgres 1594 1589 0 10:27 ? 00:00:00 postgres: walwriter
7postgres 1595 1589 0 10:27 ? 00:00:00 postgres: autovacuum launcher
8postgres 1596 1589 0 10:27 ? 00:00:00 postgres: stats collector
9postgres 1597 1589 0 10:27 ? 00:00:00 postgres: logical replication launcher
10postgres 1641 1589 0 10:28 ? 00:00:03 postgres: Citus Maintenance Daemon: 16430/10
DN节点的进程：
1[root@lhrpgcitusdn81 /]# ps -ef|grep post
2postgres 8661 0 0 11:09 ? 00:00:00 /usr/pgsql-13/bin/postgres -D /var/lib/pgsql/13/data/
3postgres 8662 8661 0 11:09 ? 00:00:00 postgres: logger
4postgres 8665 8661 0 11:09 ? 00:00:00 postgres: checkpointer
5postgres 8666 8661 0 11:09 ? 00:00:00 postgres: background writer
6postgres 8667 8661 0 11:09 ? 00:00:00 postgres: walwriter
7postgres 8668 8661 0 11:09 ? 00:00:00 postgres: autovacuum launcher
8postgres 8669 8661 0 11:09 ? 00:00:00 postgres: stats collector
9postgres 8670 8661 0 11:09 ? 00:00:00 postgres: logical replication launcher
10postgres 8710 8661 0 11:10 ? 00:00:00 postgres: Citus Maintenance Daemon: 13255/10
11postgres 8720 8661 0 11:10 ? 00:00:00 postgres: Citus Maintenance Daemon: 16430/10
12postgres 9591 8661 0 11:25 ? 00:00:00 postgres: postgres lhrdb 172.72.6.80(58852) idle
13postgres 13145 8661 0 12:27 ? 00:00:00 postgres: postgres lhrdb 172.72.6.80(58998) idle
所有变量查询，可以使用tab键自动返回相关变量：
1lhrdb=# set citus.
2citus.all_modifications_commutative citus.count_distinct_error_rate
3citus.enable_binary_protocol citus.enable_local_execution
4citus.enable_repartition_joins citus.explain_analyze_sort_method
5citus.local_hostname citus.log_remote_commands
6citus.max_cached_connection_lifetime citus.max_intermediate_result_size
7citus.multi_shard_modify_mode citus.node_connection_timeout
8citus.propagate_set_commands citus.shard_count
9citus.shard_placement_policy citus.task_assignment_policy
10citus.values_materialization_threshold citus.writable_standby_coordinator
11citus.coordinator_aggregation_strategy citus.defer_drop_after_shard_move
12citus.enable_deadlock_prevention citus.enable_local_reference_table_foreign_keys
13citus.explain_all_tasks citus.limit_clause_row_fetch_count
14citus.local_table_join_policy citus.max_adaptive_executor_pool_size
15citus.max_cached_conns_per_worker citus.multi_shard_commit_protocol
16citus.multi_task_query_log_level citus.partition_buffer_size
17citus.remote_task_check_interval citus.shard_max_size
18citus.shard_replication_factor citus.task_executor_type
19citus.worker_min_messages
20
21lhrdb=# set citus.shard_
22citus.shard_count citus.shard_max_size
23citus.shard_placement_policy citus.shard_replication_factor
24
25lhrdb=# show citus.shard_count ;
26 citus.shard_count
27-------------------
28 32
29(1 row)
查看所有执行计划
默认情况下，Citus中查看执行计划会省略大部分不同节点的相同计划，如果想查看完整的查询计划，会话设置如下：
1lhrdb=# explain select count(*) from test;
2 QUERY PLAN
3------------------------------------------------------------------------------------------
4 Aggregate (cost=250.00..250.02 rows=1 width=8)
5 -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=8)
6 Task Count: 4
7 Tasks Shown: One of 4
8 -> Task
9 Node: host=172.72.6.81 port=5432 dbname=lhrdb
10 -> Aggregate (cost=2.49..2.50 rows=1 width=8)
11 -> Seq Scan on test_102008 test (cost=0.00..2.19 rows=119 width=0)
12(8 rows)
13
14
15lhrdb=# SET citus.explain_all_tasks = 'TRUE';
16SET
17lhrdb=# explain select count(*) from test;
18 QUERY PLAN
19------------------------------------------------------------------------------------------
20 Aggregate (cost=250.00..250.02 rows=1 width=8)
21 -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=8)
22 Task Count: 4
23 Tasks Shown: All
24 -> Task
25 Node: host=172.72.6.81 port=5432 dbname=lhrdb
26 -> Aggregate (cost=2.49..2.50 rows=1 width=8)
27 -> Seq Scan on test_102008 test (cost=0.00..2.19 rows=119 width=0)
28 -> Task
29 Node: host=172.72.6.82 port=5432 dbname=lhrdb
30 -> Aggregate (cost=3.73..3.73 rows=1 width=8)
31 -> Seq Scan on test_102009 test (cost=0.00..3.38 rows=138 width=0)
32 -> Task
33 Node: host=172.72.6.83 port=5432 dbname=lhrdb
34 -> Aggregate (cost=2.47..2.48 rows=1 width=8)
35 -> Seq Scan on test_102010 test (cost=0.00..2.18 rows=118 width=0)
36 -> Task
37 Node: host=172.72.6.84 port=5432 dbname=lhrdb
38 -> Aggregate (cost=3.56..3.57 rows=1 width=8)
39 -> Seq Scan on test_102011 test (cost=0.00..3.25 rows=125 width=0)
40(20 rows)
性能测试
参考：https://pgfans.cn/a/1274
这里，我做简单的一个压测，创建一个1000万的本地表和分片表，分别做读写测试，压测5分钟，threads=100：
1-- 本地表
2
3alter system set max_connections=1000;
4pg_ctl restart
5
6sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql --pgsql-host=172.72.6.80 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb --table-size=10000000 --tables=1 --threads=100 --events=999999999 --time=300 prepare
7
8sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql --pgsql-host=172.72.6.80 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb --table-size=100000 --tables=1 --threads=100 --events=999999999 --time=300 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
9
10
11-- 分片表
12-- 配置16个分片，2个副本
13set citus.shard_count=16;
14set citus.shard_replication_factor=2;
15
16-- 转换为分片表
17SELECT create_distributed_table('sbtest1', 'id', 'hash');
测试过程
本地表：
1[postgres@lhrpgcituscn80 ~]$ sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql --pgsql-host=172.72.6.80 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb --table-size=10000000 --tables=1 --threads=100 --events=999999999 --time=300 prepare
2sysbench 1.0.17 (using system LuaJIT 2.0.4)
3
4Initializing worker threads...
5
6Creating table 'sbtest1'...
7Inserting 10000000 records into 'sbtest1'
8Creating a secondary index on 'sbtest1'...
9
10[postgres@lhrpgcituscn80 ~]$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql --pgsql-host=172.72.6.80 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb --table-size=100000 --tables=1 --threads=100 --events=999999999 --time=300 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
11sysbench 1.0.17 (using system LuaJIT 2.0.4)
12
13Running the test with following options:
14Number of threads: 100
15Report intermediate results every 10 second(s)
16Initializing random number generator from current time
17
18Forcing shutdown in 301 seconds
19
20Initializing worker threads...
21
22Threads started!
23
24[ 10s ] thds: 100 tps: 2058.53 qps: 42459.02 (r/w/o: 29828.31/8252.63/4378.07) lat (ms,95%): 77.19 err/s: 64.58 reconn/s: 0.00
25[ 20s ] thds: 100 tps: 1943.48 qps: 39815.96 (r/w/o: 27945.47/7764.92/4105.57) lat (ms,95%): 77.19 err/s: 53.10 reconn/s: 0.00
26[ 30s ] thds: 100 tps: 1829.76 qps: 37566.41 (r/w/o: 26366.44/7325.95/3874.02) lat (ms,95%): 108.68 err/s: 53.70 reconn/s: 0.00
27[ 40s ] thds: 100 tps: 2072.87 qps: 42495.29 (r/w/o: 29829.24/8280.29/4385.75) lat (ms,95%): 74.46 err/s: 55.60 reconn/s: 0.00
28[ 50s ] thds: 100 tps: 2086.30 qps: 42791.05 (r/w/o: 30033.43/8337.41/4420.20) lat (ms,95%): 75.82 err/s: 58.00 reconn/s: 0.00
29[ 60s ] thds: 100 tps: 2017.70 qps: 41377.51 (r/w/o: 29048.81/8059.00/4269.70) lat (ms,95%): 81.48 err/s: 57.70 reconn/s: 0.00
30[ 70s ] thds: 100 tps: 1865.25 qps: 38286.44 (r/w/o: 26876.63/7451.91/3957.90) lat (ms,95%): 92.42 err/s: 56.70 reconn/s: 0.00
31[ 80s ] thds: 100 tps: 1828.59 qps: 37540.92 (r/w/o: 26361.20/7307.47/3872.26) lat (ms,95%): 73.13 err/s: 52.79 reconn/s: 0.00
32[ 90s ] thds: 100 tps: 1921.21 qps: 39479.24 (r/w/o: 27713.54/7691.54/4074.15) lat (ms,95%): 84.47 err/s: 59.31 reconn/s: 0.00
33[ 100s ] thds: 100 tps: 2134.06 qps: 43828.35 (r/w/o: 30767.48/8538.54/4522.33) lat (ms,95%): 73.13 err/s: 63.90 reconn/s: 0.00
34[ 110s ] thds: 100 tps: 1305.49 qps: 26910.18 (r/w/o: 18892.82/5241.78/2775.59) lat (ms,95%): 104.84 err/s: 41.60 reconn/s: 0.00
35[ 120s ] thds: 100 tps: 1997.63 qps: 41037.51 (r/w/o: 28815.43/7980.83/4241.26) lat (ms,95%): 89.16 err/s: 62.90 reconn/s: 0.00
36[ 130s ] thds: 100 tps: 2062.18 qps: 42315.11 (r/w/o: 29703.23/8244.11/4367.77) lat (ms,95%): 80.03 err/s: 61.10 reconn/s: 0.00
37[ 140s ] thds: 100 tps: 1705.73 qps: 34983.78 (r/w/o: 24558.25/6815.21/3610.32) lat (ms,95%): 99.33 err/s: 48.69 reconn/s: 0.00
38[ 150s ] thds: 100 tps: 1689.21 qps: 34688.18 (r/w/o: 24355.48/6750.55/3582.15) lat (ms,95%): 89.16 err/s: 48.21 reconn/s: 0.00
39[ 160s ] thds: 100 tps: 1886.56 qps: 38698.17 (r/w/o: 27155.82/7537.53/4004.82) lat (ms,95%): 90.78 err/s: 56.20 reconn/s: 0.00
40[ 170s ] thds: 100 tps: 1928.81 qps: 39732.12 (r/w/o: 27913.78/7717.92/4100.41) lat (ms,95%): 87.56 err/s: 61.20 reconn/s: 0.00
41[ 180s ] thds: 100 tps: 2000.75 qps: 41118.15 (r/w/o: 28863.46/8007.89/4246.79) lat (ms,95%): 82.96 err/s: 62.50 reconn/s: 0.00
42[ 190s ] thds: 100 tps: 2015.51 qps: 41414.15 (r/w/o: 29080.08/8059.95/4274.13) lat (ms,95%): 82.96 err/s: 61.70 reconn/s: 0.00
43[ 200s ] thds: 100 tps: 2035.94 qps: 41807.51 (r/w/o: 29344.44/8149.18/4313.89) lat (ms,95%): 81.48 err/s: 59.90 reconn/s: 0.00
44[ 210s ] thds: 100 tps: 1479.80 qps: 30401.46 (r/w/o: 21349.37/5911.79/3140.30) lat (ms,95%): 155.80 err/s: 44.70 reconn/s: 0.00
45[ 220s ] thds: 100 tps: 1889.21 qps: 38913.33 (r/w/o: 27323.68/7575.64/4014.01) lat (ms,95%): 89.16 err/s: 61.80 reconn/s: 0.00
46[ 230s ] thds: 100 tps: 1990.00 qps: 40848.46 (r/w/o: 28681.08/7950.48/4216.90) lat (ms,95%): 84.47 err/s: 58.80 reconn/s: 0.00
47[ 240s ] thds: 100 tps: 2036.04 qps: 41779.80 (r/w/o: 29321.86/8146.27/4311.68) lat (ms,95%): 81.48 err/s: 59.70 reconn/s: 0.00
48[ 250s ] thds: 100 tps: 1710.84 qps: 35211.32 (r/w/o: 24722.28/6850.06/3638.98) lat (ms,95%): 95.81 err/s: 53.20 reconn/s: 0.00
49[ 260s ] thds: 100 tps: 1956.01 qps: 40131.82 (r/w/o: 28176.76/7813.74/4141.32) lat (ms,95%): 90.78 err/s: 57.90 reconn/s: 0.00
50[ 270s ] thds: 100 tps: 1948.48 qps: 40056.11 (r/w/o: 28122.56/7796.60/4136.95) lat (ms,95%): 86.00 err/s: 60.30 reconn/s: 0.00
51[ 280s ] thds: 100 tps: 1983.93 qps: 40732.06 (r/w/o: 28594.99/7927.81/4209.26) lat (ms,95%): 80.03 err/s: 58.60 reconn/s: 0.00
52[ 290s ] thds: 100 tps: 1992.00 qps: 40948.79 (r/w/o: 28743.49/7980.10/4225.20) lat (ms,95%): 82.96 err/s: 58.70 reconn/s: 0.00
53[ 300s ] thds: 100 tps: 2014.59 qps: 41323.30 (r/w/o: 29017.03/8036.08/4270.19) lat (ms,95%): 81.48 err/s: 60.40 reconn/s: 0.00
54SQL statistics:
55 queries performed:
56 read: 8275582
57 write: 2295354
58 other: 1216960
59 total: 11787896
60 transactions: 573971 (1910.48 per sec.)
61 queries: 11787896 (39236.46 per sec.)
62 ignored errors: 17142 (57.06 per sec.)
63 reconnects: 0 (0.00 per sec.)
64
65General statistics:
66 total time: 300.4295s
67 total number of events: 573971
68
69Latency (ms):
70 min: 8.06
71 avg: 52.30
72 max: 2816.03
73 95th percentile: 86.00
74 sum: 30017266.14
75
76Threads fairness:
77 events (avg/stddev): 5739.7100/129.92
78 execution time (avg/stddev): 300.1727/0.15
分片表：
1lhrdb=# set citus.shard_count=16;
2SET
3lhrdb=#
4lhrdb=# set citus.shard_replication_factor=2;
5SET
6lhrdb=#
7lhrdb=# SELECT create_distributed_table('sbtest1', 'id', 'hash');
8NOTICE: Copying data from local table...
9NOTICE: copying the data has completed
10DETAIL: The local data in the table is no longer visible, but is still on disk.
11HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$public.sbtest1$$)
12 create_distributed_table
13--------------------------
14
15(1 row)
16
17
18lhrdb=# SELECT truncate_local_data_after_distributing_table($$public.sbtest1$$);
19 truncate_local_data_after_distributing_table
20----------------------------------------------
21
22(1 row)
23
24[postgres@lhrpgcituscn80 ~]$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql --pgsql-host=172.72.6.80 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb --table-size=100000 --tables=1 --threads=100 --events=999999999 --time=300 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
25sysbench 1.0.17 (using system LuaJIT 2.0.4)
26
27Running the test with following options:
28Number of threads: 100
29Report intermediate results every 10 second(s)
30Initializing random number generator from current time
31
32Forcing shutdown in 301 seconds
33
34Initializing worker threads...
35
36Threads started!
37
38[ 10s ] thds: 100 tps: 2.00 qps: 300.64 (r/w/o: 265.85/13.69/21.09) lat (ms,95%): 8955.74 err/s: 7.00 reconn/s: 0.00
39[ 20s ] thds: 100 tps: 0.20 qps: 155.41 (r/w/o: 141.41/3.70/10.30) lat (ms,95%): 15650.42 err/s: 9.90 reconn/s: 0.00
40[ 30s ] thds: 100 tps: 0.80 qps: 161.90 (r/w/o: 143.80/6.90/11.20) lat (ms,95%): 29926.15 err/s: 9.60 reconn/s: 0.00
41[ 40s ] thds: 100 tps: 2.20 qps: 201.60 (r/w/o: 173.60/13.50/14.50) lat (ms,95%): 36481.47 err/s: 10.10 reconn/s: 0.00
42[ 50s ] thds: 100 tps: 1.10 qps: 162.70 (r/w/o: 143.10/8.30/11.30) lat (ms,95%): 48662.51 err/s: 9.10 reconn/s: 0.00
43[ 60s ] thds: 100 tps: 2.10 qps: 205.50 (r/w/o: 177.50/13.20/14.80) lat (ms,95%): 58263.41 err/s: 10.60 reconn/s: 0.00
44[ 70s ] thds: 100 tps: 0.80 qps: 138.40 (r/w/o: 122.20/6.70/9.50) lat (ms,95%): 69758.52 err/s: 7.90 reconn/s: 0.00
45[ 80s ] thds: 100 tps: 0.40 qps: 171.60 (r/w/o: 155.40/4.70/11.50) lat (ms,95%): 74968.33 err/s: 10.70 reconn/s: 0.00
46[ 90s ] thds: 100 tps: 1.50 qps: 200.50 (r/w/o: 176.40/10.00/14.10) lat (ms,95%): 89759.24 err/s: 11.10 reconn/s: 0.00
47[ 100s ] thds: 100 tps: 0.90 qps: 114.30 (r/w/o: 99.00/7.30/8.00) lat (ms,95%): 96462.77 err/s: 6.20 reconn/s: 0.00
48[ 110s ] thds: 100 tps: 1.60 qps: 167.20 (r/w/o: 144.40/10.90/11.90) lat (ms,95%): 100000.00 err/s: 8.70 reconn/s: 0.00
49[ 120s ] thds: 100 tps: 2.10 qps: 181.30 (r/w/o: 155.30/12.80/13.20) lat (ms,95%): 100000.00 err/s: 9.00 reconn/s: 0.00
50[ 130s ] thds: 100 tps: 1.40 qps: 211.00 (r/w/o: 185.10/11.30/14.60) lat (ms,95%): 100000.00 err/s: 11.80 reconn/s: 0.00
51[ 140s ] thds: 100 tps: 0.20 qps: 130.20 (r/w/o: 117.60/4.00/8.60) lat (ms,95%): 43679.10 err/s: 8.20 reconn/s: 0.00
52[ 150s ] thds: 100 tps: 1.90 qps: 192.00 (r/w/o: 165.20/13.10/13.70) lat (ms,95%): 100000.00 err/s: 9.90 reconn/s: 0.00
53[ 160s ] thds: 100 tps: 1.70 qps: 147.40 (r/w/o: 126.00/10.70/10.70) lat (ms,95%): 100000.00 err/s: 7.30 reconn/s: 0.00
54[ 170s ] thds: 100 tps: 1.80 qps: 172.50 (r/w/o: 148.40/11.70/12.40) lat (ms,95%): 100000.00 err/s: 8.80 reconn/s: 0.00
55[ 180s ] thds: 100 tps: 0.70 qps: 156.30 (r/w/o: 138.60/7.10/10.60) lat (ms,95%): 69758.52 err/s: 9.20 reconn/s: 0.00
56[ 190s ] thds: 100 tps: 0.90 qps: 143.80 (r/w/o: 127.00/6.80/10.00) lat (ms,95%): 100000.00 err/s: 8.20 reconn/s: 0.00
57[ 200s ] thds: 100 tps: 0.80 qps: 156.70 (r/w/o: 138.80/7.20/10.70) lat (ms,95%): 88157.45 err/s: 9.10 reconn/s: 0.00
58[ 210s ] thds: 100 tps: 1.70 qps: 150.90 (r/w/o: 128.60/11.40/10.90) lat (ms,95%): 100000.00 err/s: 7.50 reconn/s: 0.00
59[ 220s ] thds: 100 tps: 1.30 qps: 135.20 (r/w/o: 116.60/9.00/9.60) lat (ms,95%): 100000.00 err/s: 7.00 reconn/s: 0.00
60[ 230s ] thds: 100 tps: 0.50 qps: 85.40 (r/w/o: 74.20/5.40/5.80) lat (ms,95%): 100000.00 err/s: 4.80 reconn/s: 0.00
61[ 240s ] thds: 100 tps: 1.50 qps: 171.20 (r/w/o: 148.40/10.70/12.10) lat (ms,95%): 100000.00 err/s: 9.10 reconn/s: 0.00
62[ 250s ] thds: 100 tps: 1.30 qps: 195.00 (r/w/o: 170.80/10.70/13.50) lat (ms,95%): 100000.00 err/s: 10.90 reconn/s: 0.00
63[ 260s ] thds: 100 tps: 0.60 qps: 104.40 (r/w/o: 92.40/4.80/7.20) lat (ms,95%): 100000.00 err/s: 6.00 reconn/s: 0.00
64[ 270s ] thds: 100 tps: 0.80 qps: 133.60 (r/w/o: 117.60/6.80/9.20) lat (ms,95%): 100000.00 err/s: 7.60 reconn/s: 0.00
65[ 280s ] thds: 100 tps: 2.40 qps: 195.70 (r/w/o: 166.60/14.80/14.30) lat (ms,95%): 100000.00 err/s: 9.50 reconn/s: 0.00
66[ 290s ] thds: 100 tps: 1.70 qps: 106.80 (r/w/o: 89.20/9.50/8.10) lat (ms,95%): 100000.00 err/s: 4.70 reconn/s: 0.00
67[ 300s ] thds: 100 tps: 0.90 qps: 190.60 (r/w/o: 168.00/9.70/12.90) lat (ms,95%): 100000.00 err/s: 11.10 reconn/s: 0.00
68FATAL: The --max-time limit has expired, forcing shutdown...
69SQL statistics:
70 queries performed:
71 read: 43336
72 write: 2772
73 other: 3475
74 total: 49583
75 transactions: 378 (1.26 per sec.)
76 queries: 49583 (164.73 per sec.)
77 ignored errors: 2618 (8.70 per sec.)
78 reconnects: 0 (0.00 per sec.)
79
80Number of unfinished transactions on forced shutdown: 100
81
82General statistics:
83 total time: 301.0003s
84 total number of events: 378
85
86Latency (ms):
87 min: 101.71
88 avg: 59858.70
89 max: 282897.47
90 95th percentile: 100000.00
91 sum: 22626588.73
92
93Threads fairness:
94 events (avg/stddev): 4.7800/1.83
95 execution time (avg/stddev): 226.2659/74.67
结果
| 环境 | 测试类型 | TPS | QPS |
|---|---|---|---|
| 本地表 | 读写 | 1910.48 | 39236.46 |
| 分片表 | 读写 | 1.26 | 164.73 |
分片表比本地表的性能反而更差了，😂😂😂😂😂， 这个估计和服务器有关系，因为我这套系统是部署再同一台主机上的。算了，等后续有服务器的时候，再测试一次。
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
