PG高可用之Citus分布式集群搭建及使用
👉 本文共约2768个字，系统预计阅读时间或需11分钟。
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | -- 网卡 docker network create --subnet=172.72.6.0/24 pg-network -- pg cn docker rm -f lhrpgcituscn80 docker run -d --name lhrpgcituscn80 -h lhrpgcituscn80 \ --net=pg-network --ip 172.72.6.80 \ -p 64380:5432 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrpgall:2.0 \ /usr/sbin/init docker rm -f lhrpgcitusdn81 docker run -d --name lhrpgcitusdn81 -h lhrpgcitusdn81 \ --net=pg-network --ip 172.72.6.81 \ -p 64381:5432 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrpgall:2.0 \ /usr/sbin/init docker rm -f lhrpgcitusdn82 docker run -d --name lhrpgcitusdn82 -h lhrpgcitusdn82 \ --net=pg-network --ip 172.72.6.82 \ -p 64382:5432 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrpgall:2.0 \ /usr/sbin/init docker rm -f lhrpgcitusdn83 docker run -d --name lhrpgcitusdn83 -h lhrpgcitusdn83 \ --net=pg-network --ip 172.72.6.83 \ -p 64383:5432 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrpgall:2.0 \ /usr/sbin/init docker rm -f lhrpgcitusdn84 docker run -d --name lhrpgcitusdn84 -h lhrpgcitusdn84 \ --net=pg-network --ip 172.72.6.84 \ -p 64384:5432 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrpgall:2.0 \ /usr/sbin/init [root@docker35 ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 0183e7a9704a lhrbest/lhrpgall:2.0 "/usr/sbin/init" 6 seconds ago Up 3 seconds 0.0.0.0:64384->5432/tcp, :::64384->5432/tcp lhrpgcitusdn84 877d897a5a76 lhrbest/lhrpgall:2.0 "/usr/sbin/init" 8 seconds ago Up 6 seconds 0.0.0.0:64383->5432/tcp, :::64383->5432/tcp lhrpgcitusdn83 98dafcefc505 lhrbest/lhrpgall:2.0 "/usr/sbin/init" 10 seconds ago Up 7 seconds 0.0.0.0:64382->5432/tcp, :::64382->5432/tcp lhrpgcitusdn82 04510e0bfa96 lhrbest/lhrpgall:2.0 "/usr/sbin/init" 11 seconds ago Up 10 seconds 0.0.0.0:64381->5432/tcp, :::64381->5432/tcp lhrpgcitusdn81 8cf991b0633f 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配置：
1 2 3 | cat >> /var/lib/pgsql/13/data/pg_hba.conf <<"EOF" host all all 0.0.0.0/0 md5 EOF |
worker节点的pg_hba.conf配置:
1 2 3 | cat >> /var/lib/pgsql/13/data/pg_hba.conf <<"EOF" host all all 172.72.6.0/24 trust EOF |
安装citus
在每个节点上都安装citus，包括cn和dn。
可以在以下位置下载citus的源码：
https://github.com/citusdata/citus/releases
https://pgxn.org/dist/citus/10.2.4/
最新版本10.2.4，如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- yum直接安装 yum list | grep citus yum install -y citus_13 su - postgresql psql create database lhrdb; \c lhrdb alter system set shared_preload_libraries='citus'; select * from pg_available_extensions where name='citus'; pg_ctl restart psql -d lhrdb create extension citus; \dx \dx+ citus |
安装过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 | [postgres@lhrpgcituscn80 ~]$ psql psql (13.3) Type "help" for help. postgres=# create database lhrdb; CREATE DATABASE postgres=# \c lhrdb; You are now connected to database "lhrdb" as user "postgres". lhrdb=# create extension citus; ERROR: Citus can only be loaded via shared_preload_libraries HINT: 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. lhrdb=# show shared_preload_libraries; shared_preload_libraries -------------------------- (1 row) lhrdb=# alter system set shared_preload_libraries='citus'; ALTER SYSTEM lhrdb=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) lhrdb=# select * from pg_available_extensions where name='citus'; name | default_version | installed_version | comment -------+-----------------+-------------------+---------------------------- citus | 10.2-4 | | Citus distributed database (1 row) lhrdb=# exit [postgres@lhrpgcituscn80 ~]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2022-02-17 10:27:34.354 CST [1589] LOG: number of prepared transactions has not been configured, overriding 2022-02-17 10:27:34.354 CST [1589] DETAIL: max_prepared_transactions is now set to 200 2022-02-17 10:27:34.415 CST [1589] LOG: redirecting log output to logging collector process 2022-02-17 10:27:34.415 CST [1589] HINT: Future log output will appear in directory "pg_log". done server started [postgres@lhrpgcituscn80 ~]$ psql psql (13.3) Type "help" for help. postgres=# \c lhrdb You are now connected to database "lhrdb" as user "postgres". lhrdb=# show shared_preload_libraries; shared_preload_libraries -------------------------- citus (1 row) lhrdb=# create extension citus; CREATE EXTENSION lhrdb=# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------ citus | 10.2-4 | pg_catalog | Citus distributed database plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) lhrdb=# \dx+ citus Objects in extension "citus" Object description ----------------------------------------------------------------------------------------------------------------- access method columnar event trigger citus_cascade_to_partition function alter_columnar_table_reset(regclass,boolean,boolean,boolean,boolean) function alter_columnar_table_set(regclass,integer,integer,name,integer) function alter_distributed_table(regclass,text,integer,text,boolean) function alter_old_partitions_set_access_method(regclass,timestamp with time zone,name) function alter_role_if_exists(text,text) function alter_table_set_access_method(regclass,text) function any_value_agg(anyelement,anyelement) function any_value(anyelement) function array_cat_agg(anyarray) function assign_distributed_transaction_id(integer,bigint,timestamp with time zone) function authinfo_valid(text) function broadcast_intermediate_result(text,text) function check_distributed_deadlocks() function citus_activate_node(text,integer) function citus_add_inactive_node(text,integer,integer,noderole,name) function citus_add_local_table_to_metadata(regclass,boolean) function citus_add_node(text,integer,integer,noderole,name) function citus_add_rebalance_strategy(name,regproc,regproc,regproc,real,real,real) function citus_add_secondary_node(text,integer,text,integer,name) function citus_blocking_pids(integer) function citus_cleanup_orphaned_shards() function citus_conninfo_cache_invalidate() function citus_copy_shard_placement(bigint,text,integer,text,integer,boolean,citus.shard_transfer_mode) function citus_create_restore_point(text) function citus_disable_node(text,integer) function citus_dist_local_group_cache_invalidate() function citus_dist_node_cache_invalidate() function citus_dist_object_cache_invalidate() function citus_dist_partition_cache_invalidate() function citus_dist_placement_cache_invalidate() function citus_dist_shard_cache_invalidate() function citus_dist_stat_activity() function citus_drain_node(text,integer,citus.shard_transfer_mode,name) function citus_drop_all_shards(regclass,text,text,boolean) function citus_drop_trigger() function citus_executor_name(integer) function citus_extradata_container(internal) function citus_finish_pg_upgrade() function citus_get_active_worker_nodes() function citus_internal_add_partition_metadata(regclass,"char",text,integer,"char") function citus_internal_add_placement_metadata(bigint,integer,bigint,integer,bigint) function citus_internal_add_shard_metadata(regclass,bigint,"char",text,text) function citus_internal.columnar_ensure_am_depends_catalog() function citus_internal_delete_shard_metadata(bigint) function citus_internal.downgrade_columnar_storage(regclass) function citus_internal.find_groupid_for_node(text,integer) function citus_internal.pg_dist_node_trigger_func() function citus_internal.pg_dist_rebalance_strategy_trigger_func() function citus_internal.pg_dist_shard_placement_trigger_func() function citus_internal.refresh_isolation_tester_prepared_statement() function citus_internal.replace_isolation_tester_func() function citus_internal.restore_isolation_tester_func() function citus_internal_update_placement_metadata(bigint,integer,integer) function citus_internal_update_relation_colocation(oid,integer) function citus_internal.upgrade_columnar_storage(regclass) function citus_isolation_test_session_is_blocked(integer,integer[]) function citus_jsonb_concatenate_final(jsonb) function citus_jsonb_concatenate(jsonb,jsonb) function citus_json_concatenate_final(json) function citus_json_concatenate(json,json) function citus_local_disk_space_stats() function citus_move_shard_placement(bigint,text,integer,text,integer,citus.shard_transfer_mode) function citus_node_capacity_1(integer) function citus_prepare_pg_upgrade() function citus_query_stats() function citus_relation_size(regclass) function citus_remote_connection_stats() function citus_remove_node(text,integer) function citus_server_id() function citus_set_coordinator_host(text,integer,noderole,name) function citus_set_default_rebalance_strategy(text) function citus_set_node_property(text,integer,text,boolean) function citus_shard_allowed_on_node_true(bigint,integer) function citus_shard_cost_1(bigint) function citus_shard_cost_by_disk_size(bigint) function citus_shard_sizes() function citus_stat_statements() function citus_stat_statements_reset() function citus_table_is_visible(oid) function citus_table_size(regclass) function citus_text_send_as_jsonb(text) function citus_total_relation_size(regclass,boolean) function citus_truncate_trigger() function citus_unmark_object_distributed(oid,oid,integer) function citus_update_node(integer,text,integer,boolean,integer) function citus_update_shard_statistics(bigint) function citus_update_table_statistics(regclass) function citus_validate_rebalance_strategy_functions(regproc,regproc,regproc) function citus_version() function citus_worker_stat_activity() function columnar.columnar_handler(internal) function column_name_to_column(regclass,text) function column_to_column_name(regclass,text) function coord_combine_agg_ffunc(internal,oid,cstring,anyelement) function coord_combine_agg(oid,cstring,anyelement) function coord_combine_agg_sfunc(internal,oid,cstring,anyelement) function create_distributed_function(regprocedure,text,text) function create_distributed_table(regclass,text,citus.distribution_type,text,integer) function create_intermediate_result(text,text) function create_reference_table(regclass) function create_time_partitions(regclass,interval,timestamp with time zone,timestamp with time zone) function distributed_tables_colocated(regclass,regclass) function drop_old_time_partitions(regclass,timestamp with time zone) function dump_global_wait_edges() function dump_local_wait_edges() function fetch_intermediate_results(text[],text,integer) function fix_all_partition_shard_index_names() function fix_partition_shard_index_names(regclass) function fix_pre_citus10_partitioned_table_constraint_names() function fix_pre_citus10_partitioned_table_constraint_names(regclass) function get_all_active_transactions() function get_colocated_shard_array(bigint) function get_colocated_table_array(regclass) function get_current_transaction_id() function get_global_active_transactions() function get_missing_time_partition_ranges(regclass,interval,timestamp with time zone,timestamp with time zone) function get_rebalance_progress() function get_rebalance_table_shards_plan(regclass,real,integer,bigint[],boolean,name,real) function get_shard_id_for_distribution_column(regclass,"any") function isolate_tenant_to_new_shard(regclass,"any",text) function jsonb_cat_agg(jsonb) function json_cat_agg(json) function lock_relation_if_exists(text,text) function lock_shard_metadata(integer,bigint[]) function lock_shard_resources(integer,bigint[]) function master_activate_node(text,integer) function master_add_inactive_node(text,integer,integer,noderole,name) function master_add_node(text,integer,integer,noderole,name) function master_add_secondary_node(text,integer,text,integer,name) function master_append_table_to_shard(bigint,text,text,integer) function master_apply_delete_command(text) function master_copy_shard_placement(bigint,text,integer,text,integer,boolean,citus.shard_transfer_mode) function master_create_empty_shard(text) function master_disable_node(text,integer) function master_drain_node(text,integer,citus.shard_transfer_mode,name) function master_get_active_worker_nodes() function master_get_new_placementid() function master_get_new_shardid() function master_get_table_ddl_events(text) function master_get_table_metadata(text) function master_move_shard_placement(bigint,text,integer,text,integer,citus.shard_transfer_mode) function master_remove_distributed_table_metadata_from_workers(regclass,text,text) function master_remove_node(text,integer) function master_remove_partition_metadata(regclass,text,text) function master_run_on_worker(text[],integer[],text[],boolean) function master_set_node_property(text,integer,text,boolean) function master_unmark_object_distributed(oid,oid,integer) function master_update_node(integer,text,integer,boolean,integer) function master_update_shard_statistics(bigint) function master_update_table_statistics(regclass) function notify_constraint_dropped() function poolinfo_valid(text) function read_intermediate_results(text[],citus_copy_format) function read_intermediate_result(text,citus_copy_format) function rebalance_table_shards(regclass,real,integer,bigint[],citus.shard_transfer_mode,boolean,name) function recover_prepared_transactions() function relation_is_a_known_shard(regclass) function remove_local_tables_from_metadata() function replicate_reference_tables() function replicate_table_shards(regclass,integer,integer,bigint[],citus.shard_transfer_mode) function role_exists(name) function run_command_on_colocated_placements(regclass,regclass,text,boolean) function run_command_on_placements(regclass,text,boolean) function run_command_on_shards(regclass,text,boolean) function run_command_on_workers(text,boolean) function shard_name(regclass,bigint) function start_metadata_sync_to_node(text,integer) function stop_metadata_sync_to_node(text,integer,boolean) function time_partition_range(regclass) function truncate_local_data_after_distributing_table(regclass) function undistribute_table(regclass,boolean) function update_distributed_table_colocation(regclass,text) function worker_append_table_to_shard(text,text,text,integer) function worker_apply_inter_shard_ddl_command(bigint,text,bigint,text,text) function worker_apply_sequence_command(text) function worker_apply_sequence_command(text,regtype) function worker_apply_shard_ddl_command(bigint,text) function worker_apply_shard_ddl_command(bigint,text,text) function worker_change_sequence_dependency(regclass,regclass,regclass) function worker_cleanup_job_schema_cache() function worker_create_or_alter_role(text,text,text) function worker_create_or_replace_object(text) function worker_create_schema(bigint,text) function worker_create_truncate_trigger(regclass) function worker_drop_distributed_table(text) function worker_fetch_foreign_file(text,text,bigint,text[],integer[]) function worker_fetch_partition_file(bigint,integer,integer,integer,text,integer) function worker_fix_partition_shard_index_names(regclass,text,text) function worker_fix_pre_citus10_partitioned_table_constraint_names(regclass,bigint,text) function worker_hash("any") function worker_hash_partition_table(bigint,integer,text,text,oid,anyarray) function worker_last_saved_explain_analyze() function worker_merge_files_into_table(bigint,integer,text[],text[]) function worker_nextval(regclass) function worker_partial_agg_ffunc(internal) function worker_partial_agg(oid,anyelement) function worker_partial_agg_sfunc(internal,oid,anyelement) function worker_partitioned_relation_size(regclass) function worker_partitioned_relation_total_size(regclass) function worker_partitioned_table_size(regclass) function worker_partition_query_result(text,text,integer,citus.distribution_type,text[],text[],boolean) function worker_range_partition_table(bigint,integer,text,text,oid,anyarray) function worker_record_sequence_dependency(regclass,regclass,name) function worker_repartition_cleanup(bigint) function worker_save_query_explain_analyze(text,jsonb) schema citus schema citus_internal schema columnar sequence columnar.storageid_seq sequence pg_dist_colocationid_seq sequence pg_dist_groupid_seq sequence pg_dist_node_nodeid_seq sequence pg_dist_placement_placementid_seq sequence pg_dist_shardid_seq table citus.pg_dist_object table columnar.chunk table columnar.chunk_group table columnar.options table columnar.stripe table pg_dist_authinfo table pg_dist_colocation table pg_dist_local_group table pg_dist_node table pg_dist_node_metadata table pg_dist_partition table pg_dist_placement table pg_dist_poolinfo table pg_dist_rebalance_strategy table pg_dist_shard table pg_dist_transaction type citus_copy_format type citus.distribution_type type citus.shard_transfer_mode type noderole view citus_dist_stat_activity view citus_lock_waits view citus_shard_indexes_on_worker view citus_shards view citus_shards_on_worker view citus_stat_statements view citus_tables view citus_worker_stat_activity view pg_dist_shard_placement view time_partitions (246 rows) |
集群配置
协调节点新增工作节点
管理操作仅仅在协调节点（cn）上操作：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | [postgres@lhrpgcituscn80 ~]$ psql -d lhrdb psql (13.3) Type "help" for help. lhrdb=# -- 节点可以是ip或者dns name SELECT * from master_add_node('172.72.6.81', 5432); SELECT * from master_add_node('172.72.6.82', 5432); SELECT * from master_add_node('172.72.6.83', 5432); SELECT * from master_add_node('172.72.6.84', 5432); -- 查看工作节点： lhrdb=# SELECT * FROM master_get_active_worker_nodes(); node_name | node_port -------------+----------- 172.72.6.81 | 5432 172.72.6.83 | 5432 172.72.6.84 | 5432 172.72.6.82 | 5432 (4 rows) lhrdb=# select * from pg_dist_node; nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards --------+---------+-------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------ 1 | 1 | 172.72.6.81 | 5432 | default | f | t | primary | default | f | t 2 | 2 | 172.72.6.82 | 5432 | default | f | t | primary | default | f | t 3 | 3 | 172.72.6.83 | 5432 | default | f | t | primary | default | f | t 4 | 4 | 172.72.6.84 | 5432 | default | f | t | primary | default | f | t (4 rows) |
创建分片表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | lhrdb=# create table test(id int primary key ,name varchar); #配置分片策略 #设置分片数，4个主机，设置分片4，每个主机一个分片 lhrdb=# set citus.shard_count=4; # 配置副本数 lhrdb=# set citus.shard_replication_factor=2; lhrdb=# SELECT create_distributed_table('test', 'id', 'hash'); lhrdb=# insert into test select id,md5(random()::text) from generate_series(1,500) as id; # 查看分片分布 lhrdb=# select * from citus_tables; table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method ------------+------------------+---------------------+---------------+------------+-------------+-------------+--------------- test | distributed | id | 1 | 384 kB | 4 | postgres | heap (1 row) lhrdb=# select * from master_get_table_metadata('test'); logical_relid | part_storage_type | part_method | part_key | part_replica_count | part_max_size | part_placement_policy ---------------+-------------------+-------------+----------+--------------------+---------------+----------------------- 16995 | t | h | id | 2 | 1073741824 | 2 (1 row) lhrdb=# select * from pg_dist_placement where shardid in (select shardid from pg_dist_shard where logicalrelid='test'::regclass); placementid | shardid | shardstate | shardlength | groupid -------------+---------+------------+-------------+--------- 1 | 102008 | 1 | 0 | 1 2 | 102008 | 1 | 0 | 2 3 | 102009 | 1 | 0 | 2 4 | 102009 | 1 | 0 | 3 5 | 102010 | 1 | 0 | 3 6 | 102010 | 1 | 0 | 4 7 | 102011 | 1 | 0 | 4 8 | 102011 | 1 | 0 | 1 (8 rows) lhrdb=# SELECT * from pg_dist_shard_placement order by shardid, placementid; shardid | shardstate | shardlength | nodename | nodeport | placementid ---------+------------+-------------+-------------+----------+------------- 102008 | 1 | 0 | 172.72.6.81 | 5432 | 1 102008 | 1 | 0 | 172.72.6.82 | 5432 | 2 102009 | 1 | 0 | 172.72.6.82 | 5432 | 3 102009 | 1 | 0 | 172.72.6.83 | 5432 | 4 102010 | 1 | 0 | 172.72.6.83 | 5432 | 5 102010 | 1 | 0 | 172.72.6.84 | 5432 | 6 102011 | 1 | 0 | 172.72.6.84 | 5432 | 7 102011 | 1 | 0 | 172.72.6.81 | 5432 | 8 (8 rows) lhrdb=# select count(*) from test; count ------- 500 (1 row) -- 查看分片表 [postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.80 -d lhrdb -c "\dt"; List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | test | table | postgres (1 row) [postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.81 -d lhrdb -c "\dt"; List of relations Schema | Name | Type | Owner --------+-------------+-------+---------- public | test_102008 | table | postgres public | test_102011 | table | postgres (2 rows) [postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.82 -d lhrdb -c "\dt"; List of relations Schema | Name | Type | Owner --------+-------------+-------+---------- public | test_102008 | table | postgres public | test_102009 | table | postgres (2 rows) [postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.83 -d lhrdb -c "\dt"; List of relations Schema | Name | Type | Owner --------+-------------+-------+---------- public | test_102009 | table | postgres public | test_102010 | table | postgres (2 rows) [postgres@lhrpgcitusdn84 ~]$ psql -U postgres -h 172.72.6.84 -d lhrdb -c "\dt"; List of relations Schema | Name | Type | Owner --------+-------------+-------+---------- public | test_102010 | table | postgres public | test_102011 | table | postgres (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 2 3 4 5 6 7 8 9 10 | [root@lhrpgcituscn80 /]# ps -ef|grep post postgres 1589 0 0 10:27 ? 00:00:00 /usr/pgsql-13/bin/postgres -D /var/lib/pgsql/13/data/ postgres 1590 1589 0 10:27 ? 00:00:00 postgres: logger postgres 1592 1589 0 10:27 ? 00:00:00 postgres: checkpointer postgres 1593 1589 0 10:27 ? 00:00:00 postgres: background writer postgres 1594 1589 0 10:27 ? 00:00:00 postgres: walwriter postgres 1595 1589 0 10:27 ? 00:00:00 postgres: autovacuum launcher postgres 1596 1589 0 10:27 ? 00:00:00 postgres: stats collector postgres 1597 1589 0 10:27 ? 00:00:00 postgres: logical replication launcher postgres 1641 1589 0 10:28 ? 00:00:03 postgres: Citus Maintenance Daemon: 16430/10 |
DN节点的进程：
1 2 3 4 5 6 7 8 9 10 11 12 13 | [root@lhrpgcitusdn81 /]# ps -ef|grep post postgres 8661 0 0 11:09 ? 00:00:00 /usr/pgsql-13/bin/postgres -D /var/lib/pgsql/13/data/ postgres 8662 8661 0 11:09 ? 00:00:00 postgres: logger postgres 8665 8661 0 11:09 ? 00:00:00 postgres: checkpointer postgres 8666 8661 0 11:09 ? 00:00:00 postgres: background writer postgres 8667 8661 0 11:09 ? 00:00:00 postgres: walwriter postgres 8668 8661 0 11:09 ? 00:00:00 postgres: autovacuum launcher postgres 8669 8661 0 11:09 ? 00:00:00 postgres: stats collector postgres 8670 8661 0 11:09 ? 00:00:00 postgres: logical replication launcher postgres 8710 8661 0 11:10 ? 00:00:00 postgres: Citus Maintenance Daemon: 13255/10 postgres 8720 8661 0 11:10 ? 00:00:00 postgres: Citus Maintenance Daemon: 16430/10 postgres 9591 8661 0 11:25 ? 00:00:00 postgres: postgres lhrdb 172.72.6.80(58852) idle postgres 13145 8661 0 12:27 ? 00:00:00 postgres: postgres lhrdb 172.72.6.80(58998) idle |
所有变量查询，可以使用tab键自动返回相关变量：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | lhrdb=# set citus. citus.all_modifications_commutative citus.count_distinct_error_rate citus.enable_binary_protocol citus.enable_local_execution citus.enable_repartition_joins citus.explain_analyze_sort_method citus.local_hostname citus.log_remote_commands citus.max_cached_connection_lifetime citus.max_intermediate_result_size citus.multi_shard_modify_mode citus.node_connection_timeout citus.propagate_set_commands citus.shard_count citus.shard_placement_policy citus.task_assignment_policy citus.values_materialization_threshold citus.writable_standby_coordinator citus.coordinator_aggregation_strategy citus.defer_drop_after_shard_move citus.enable_deadlock_prevention citus.enable_local_reference_table_foreign_keys citus.explain_all_tasks citus.limit_clause_row_fetch_count citus.local_table_join_policy citus.max_adaptive_executor_pool_size citus.max_cached_conns_per_worker citus.multi_shard_commit_protocol citus.multi_task_query_log_level citus.partition_buffer_size citus.remote_task_check_interval citus.shard_max_size citus.shard_replication_factor citus.task_executor_type citus.worker_min_messages lhrdb=# set citus.shard_ citus.shard_count citus.shard_max_size citus.shard_placement_policy citus.shard_replication_factor lhrdb=# show citus.shard_count ; citus.shard_count ------------------- 32 (1 row) |
查看所有执行计划
默认情况下，Citus中查看执行计划会省略大部分不同节点的相同计划，如果想查看完整的查询计划，会话设置如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | lhrdb=# explain select count(*) from test; QUERY PLAN ------------------------------------------------------------------------------------------ Aggregate (cost=250.00..250.02 rows=1 width=8) -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=8) Task Count: 4 Tasks Shown: One of 4 -> Task Node: host=172.72.6.81 port=5432 dbname=lhrdb -> Aggregate (cost=2.49..2.50 rows=1 width=8) -> Seq Scan on test_102008 test (cost=0.00..2.19 rows=119 width=0) (8 rows) lhrdb=# SET citus.explain_all_tasks = 'TRUE'; SET lhrdb=# explain select count(*) from test; QUERY PLAN ------------------------------------------------------------------------------------------ Aggregate (cost=250.00..250.02 rows=1 width=8) -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=8) Task Count: 4 Tasks Shown: All -> Task Node: host=172.72.6.81 port=5432 dbname=lhrdb -> Aggregate (cost=2.49..2.50 rows=1 width=8) -> Seq Scan on test_102008 test (cost=0.00..2.19 rows=119 width=0) -> Task Node: host=172.72.6.82 port=5432 dbname=lhrdb -> Aggregate (cost=3.73..3.73 rows=1 width=8) -> Seq Scan on test_102009 test (cost=0.00..3.38 rows=138 width=0) -> Task Node: host=172.72.6.83 port=5432 dbname=lhrdb -> Aggregate (cost=2.47..2.48 rows=1 width=8) -> Seq Scan on test_102010 test (cost=0.00..2.18 rows=118 width=0) -> Task Node: host=172.72.6.84 port=5432 dbname=lhrdb -> Aggregate (cost=3.56..3.57 rows=1 width=8) -> Seq Scan on test_102011 test (cost=0.00..3.25 rows=125 width=0) (20 rows) |
性能测试
这里，我做简单的一个压测，创建一个1000万的本地表和分片表，分别做读写测试，压测5分钟，threads=100：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- 本地表 alter system set max_connections=1000; pg_ctl restart 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 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 -- 分片表 -- 配置16个分片，2个副本 set citus.shard_count=16; set citus.shard_replication_factor=2; -- 转换为分片表 SELECT create_distributed_table('sbtest1', 'id', 'hash'); |
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
测试过程
本地表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | [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 sysbench 1.0.17 (using system LuaJIT 2.0.4) Initializing worker threads... Creating table 'sbtest1'... Inserting 10000000 records into 'sbtest1' Creating a secondary index on 'sbtest1'... [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 sysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 100 Report intermediate results every 10 second(s) Initializing random number generator from current time Forcing shutdown in 301 seconds Initializing worker threads... Threads started! [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 SQL statistics: queries performed: read: 8275582 write: 2295354 other: 1216960 total: 11787896 transactions: 573971 (1910.48 per sec.) queries: 11787896 (39236.46 per sec.) ignored errors: 17142 (57.06 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.4295s total number of events: 573971 Latency (ms): min: 8.06 avg: 52.30 max: 2816.03 95th percentile: 86.00 sum: 30017266.14 Threads fairness: events (avg/stddev): 5739.7100/129.92 execution time (avg/stddev): 300.1727/0.15 |
分片表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | lhrdb=# set citus.shard_count=16; SET lhrdb=# lhrdb=# set citus.shard_replication_factor=2; SET lhrdb=# lhrdb=# SELECT create_distributed_table('sbtest1', 'id', 'hash'); NOTICE: Copying data from local table... NOTICE: copying the data has completed DETAIL: The local data in the table is no longer visible, but is still on disk. HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$public.sbtest1$$) create_distributed_table -------------------------- (1 row) lhrdb=# SELECT truncate_local_data_after_distributing_table($$public.sbtest1$$); truncate_local_data_after_distributing_table ---------------------------------------------- (1 row) [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 sysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 100 Report intermediate results every 10 second(s) Initializing random number generator from current time Forcing shutdown in 301 seconds Initializing worker threads... Threads started! [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 [ 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 FATAL: The --max-time limit has expired, forcing shutdown... SQL statistics: queries performed: read: 43336 write: 2772 other: 3475 total: 49583 transactions: 378 (1.26 per sec.) queries: 49583 (164.73 per sec.) ignored errors: 2618 (8.70 per sec.) reconnects: 0 (0.00 per sec.) Number of unfinished transactions on forced shutdown: 100 General statistics: total time: 301.0003s total number of events: 378 Latency (ms): min: 101.71 avg: 59858.70 max: 282897.47 95th percentile: 100000.00 sum: 22626588.73 Threads fairness: events (avg/stddev): 4.7800/1.83 execution time (avg/stddev): 226.2659/74.67 |
结果
| 环境 | 测试类型 | TPS | QPS |
|---|---|---|---|
| 本地表 | 读写 | 1910.48 | 39236.46 |
| 分片表 | 读写 | 1.26 | 164.73 |
分片表比本地表的性能反而更差了，😂😂😂😂😂， 这个估计和服务器有关系，因为我这套系统是部署再同一台主机上的。算了，等后续有服务器的时候，再测试一次。
