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)