Ciuts的MX模式
Ciuts的MX模式
Citus集群由Coordinator(CN节点)和Worker节点组成。CN节点上放元数据负责SQL分发; Worker节点上放实际的分片，各司其职。 但是，citus里它们的功能也可以灵活的转换。
1. 什么是MX模式？
MX模式是Citus的扩展，允许app直接连接work节点进行数据的读取和写入并增加集群的并发数量，类似于多CN的架构。
2. MX原理
Citus将分布式表的元信息存储在系统表，当work节点拥有这些元信息后，便可以提供数据的读取和写入服务。
3. Citus版本：
Citus社区版
Citus商业版
Cloud [AWS，citus cloud]
我们通常使用的是社区版，而社区版存在其中一个限制是CN可能成为性能瓶颈；因为社区版只支持一个coordinator。
citus的架构中正常只有1个CN节点，有时候CN会成为性能瓶颈。在citus的具体实现中，CN和worker的区别就在于是否存储了相关的元数据，如果把CN的元数据拷贝一份到worker上，那么worker也可以向CN一样工作，这个多CN的模式早期被称做masterless。citus有一个开关，打开后，会自动拷贝CN的元数据到Worker上，让worker也可以当CN用。 这个功能官方称做Citus MX，社区版虽然没有公开说支持，但也没有从代码上限制这个功能。
4 Citus MX开启的前提：
Citus的复制模式必须配置为streaming。即不支持在多副本的HA部署架构下使用。
5 Citus MX测试：
5.1 部署
略
5.2 分片参数调整
postgres=# show citus.shard_replication_factor;
citus.shard_replication_factor
--------------------------------
1
(1 row)
postgres=# set citus.shard_replication_factor =2;
SET
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
1.5.3 创建worker节点
postgres=# select * from master_add_node('172.16.166.52',5435);
master_add_node
-----------------
1
(1 row)
postgres=# select * from master_add_node('172.16.166.53',5435);
master_add_node
-----------------
2
(1 row)
postgres=# select * from master_get_active_worker_nodes();
node_name | node_port
---------------+-----------
172.16.166.53 | 5435
172.16.166.52 | 5435
(2 rows)
postgres=# select * from pg_dist_node;
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+---------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
1 | 1 | 172.16.166.52 | 5435 | default | f | t | primary | default | f | t
2 | 2 | 172.16.166.53 | 5435 | default | f | t | primary | default | f | t
(2 rows)
1.5.4 MX节点配置
postgres=# SELECT citus_add_node('172.16.166.52',5435);
citus_add_node
----------------
1
(1 row)
postgres=# SELECT start_metadata_sync_to_node('172.16.166.52', 5435);
start_metadata_sync_to_node
-----------------------------
(1 row)
1.5.5 创建测试表
postgres=# create table test(id serial,updatetime timestamptz default now());
CREATE TABLE
postgres=# create table test_dist(id serial,updatetime timestamptz default now());
CREATE TABLE
postgres=# create table test_ref(id serial,updatetime timestamptz default now());
CREATE TABLE
postgres=# create table test_col(id serial,updatetime timestamptz default now());
CREATE TABLE
postgres=# insert into test select generate_series(1,100);
INSERT 0 100
postgres=# insert into test_dist select generate_series(1,100);
INSERT 0 100
postgres=# insert into test_ref select generate_series(1,100);
INSERT 0 100
postgres=# insert into test_col select generate_series(1,100);
INSERT 0 100
1.5.6 创建各类分片表
# 分片表：
postgres=# select create_distributed_table('test_dist','id');
create_distributed_table
--------------------------
(1 row)
# 亲和表：
postgres=# select create_distributed_table('test_col','id',colocate_with =>'test_dist');
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.test_col$$)
create_distributed_table
--------------------------
(1 row)
# 引用表（参考表）：
postgres=# select create_reference_table('test_ref');
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.test_ref$$)
create_reference_table
------------------------
(1 row)
1.5.7 各节点查询测试
CN节点：
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | test | table | chengang
public | test_col | table | chengang
public | test_dist | table | chengang
public | test_ref | table | chengang
(4 rows)
MX节点、worker节点1：
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | test_col | table | chengang
public | test_dist | table | chengang
public | test_ref | table | chengang
(3 rows)
worker节点2：
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | test_col_102041 | table | chengang
public | test_col_102043 | table | chengang
public | test_col_102045 | table | chengang
public | test_col_102047 | table | chengang
public | test_col_102049 | table | chengang
public | test_col_102051 | table | chengang
public | test_col_102053 | table | chengang
public | test_col_102055 | table | chengang
public | test_col_102057 | table | chengang
public | test_col_102059 | table | chengang
public | test_col_102061 | table | chengang
public | test_col_102063 | table | chengang
public | test_col_102065 | table | chengang
public | test_col_102067 | table | chengang
public | test_col_102069 | table | chengang
public | test_col_102071 | table | chengang
public | test_dist_102009 | table | chengang
public | test_dist_102011 | table | chengang
public | test_dist_102013 | table | chengang
public | test_dist_102015 | table | chengang
public | test_dist_102017 | table | chengang
public | test_dist_102019 | table | chengang
public | test_dist_102021 | table | chengang
public | test_dist_102023 | table | chengang
public | test_dist_102025 | table | chengang
public | test_dist_102027 | table | chengang
public | test_dist_102029 | table | chengang
public | test_dist_102031 | table | chengang
public | test_dist_102033 | table | chengang
public | test_dist_102035 | table | chengang
public | test_dist_102037 | table | chengang
public | test_dist_102039 | table | chengang
public | test_ref_102072 | table | chengang
(33 rows)
可以看到在同时作为MX节点的worker节点1上，在默认情况下，通过\dt只能看到各类分片表的元数据表，而看不到普通worker节点上的各个元数据表的实际分片（表），可以通过如下两种方法查看：
方法1.配置参数override_table_visibility为off或false
postgres=# show citus.override_table_visibility ;
citus.override_table_visibility
---------------------------------
on
(1 row)
postgres=# set citus.override_table_visibility =false;
SET
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | test_col | table | chengang
public | test_col_102040 | table | chengang
public | test_col_102042 | table | chengang
public | test_col_102044 | table | chengang
public | test_col_102046 | table | chengang
public | test_col_102048 | table | chengang
public | test_col_102050 | table | chengang
public | test_col_102052 | table | chengang
public | test_col_102054 | table | chengang
public | test_col_102056 | table | chengang
public | test_col_102058 | table | chengang
public | test_col_102060 | table | chengang
public | test_col_102062 | table | chengang
public | test_col_102064 | table | chengang
public | test_col_102066 | table | chengang
public | test_col_102068 | table | chengang
public | test_col_102070 | table | chengang
public | test_dist | table | chengang
public | test_dist_102008 | table | chengang
public | test_dist_102010 | table | chengang
public | test_dist_102012 | table | chengang
public | test_dist_102014 | table | chengang
public | test_dist_102016 | table | chengang
public | test_dist_102018 | table | chengang
public | test_dist_102020 | table | chengang
public | test_dist_102022 | table | chengang
public | test_dist_102024 | table | chengang
public | test_dist_102026 | table | chengang
public | test_dist_102028 | table | chengang
public | test_dist_102030 | table | chengang
public | test_dist_102032 | table | chengang
public | test_dist_102034 | table | chengang
public | test_dist_102036 | table | chengang
public | test_dist_102038 | table | chengang
public | test_ref | table | chengang
public | test_ref_102072 | table | chengang
(36 rows)
可以看到在执行set citus.override_table_visibility =false后，该节点除了可以看到各个分片表的元数据表，还能看到他们的实际的分片表。
方法2.查询citus_shards_on_worker视图：
postgres=# select * from citus_shards_on_worker;
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | test_col_102040 | table | chengang
public | test_col_102042 | table | chengang
public | test_col_102044 | table | chengang
public | test_col_102046 | table | chengang
public | test_col_102048 | table | chengang
public | test_col_102050 | table | chengang
public | test_col_102052 | table | chengang
public | test_col_102054 | table | chengang
public | test_col_102056 | table | chengang
public | test_col_102058 | table | chengang
public | test_col_102060 | table | chengang
public | test_col_102062 | table | chengang
public | test_col_102064 | table | chengang
public | test_col_102066 | table | chengang
public | test_col_102068 | table | chengang
public | test_col_102070 | table | chengang
public | test_dist_102008 | table | chengang
public | test_dist_102010 | table | chengang
public | test_dist_102012 | table | chengang
public | test_dist_102014 | table | chengang
public | test_dist_102016 | table | chengang
public | test_dist_102018 | table | chengang
public | test_dist_102020 | table | chengang
public | test_dist_102022 | table | chengang
public | test_dist_102024 | table | chengang
public | test_dist_102026 | table | chengang
public | test_dist_102028 | table | chengang
public | test_dist_102030 | table | chengang
public | test_dist_102032 | table | chengang
public | test_dist_102034 | table | chengang
public | test_dist_102036 | table | chengang
public | test_dist_102038 | table | chengang
public | test_ref_102072 | table | chengang
(33 rows)
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？
· 如何调用 DeepSeek 的自然语言处理 API 接口并集成到在线客服系统