Citus MX与Citus 11新特性

原创 gavin 2023-09-15

980

[[toc]]

一、Citus MX

Citus集群由Coordinator(CN节点)和Worker节点组成。CN节点上放元数据负责SQL分发; Worker节点上放实际的分片，各司其职。但是，citus里它们的功能也可以灵活的转换。

1.1 MX模式是什么

MX模式是Citus的扩展，允许app直接连接work节点进行数据的读取和写入并增加集群的并发数量，类似于多CN的架构，。

1.2 MX原理

Citus将分布式表的元信息存储在系统表，当work节点拥有这些元信息后，便可以提供数据的读取和写入服务

1.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，社区版虽然没有公开说支持，但也没有从代码上限制这个功能。

1.4 Citus MX开启的前提：

Citus的复制模式必须配置为streaming。即不支持在多副本的HA部署架构下使用

1.5 Citus MX测试：

1.5.1 部署

略

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)

二、Citus 11新特性

2.1 新特性网址

https://www.citusdata.com/updates/v11-0/

Citus 11对之前Citus企业版进行了开源，以下介绍两个新特性，其实也是上面第一节进行测试的Citus MX的功能。

2.2 新特性1——元数据同步

Citus 11.0 changes to a new mode of operation by default: All Citus database clusters always use metadata syncing. That means that with Citus 11.0, you can always run distributed Postgres queries, from any node.

翻译：Citus 11.0 更改新的默认操作：所有Citus数据库集群始终使用元数据同步。这意味着使用 Citus 11.0，您始终可以从任何节点运行分布式 Postgres 查询。

也就说，从Citus 11开始，无需跟之前的版本一样，配置MX节点，所有的节点均可以查询到元数据表，也就说说，所有的节点都可以提供数据的读取和写入服务

从此刻开始所有的dml包括truncate都可以在任意节点和表上进行操作，至于ddl则仍然需要到CN节点进行。

2.3 分片可见性控制

Citus 11.0 by default hides all the shards from all external applications. This is controlled by a GUC citus.show_shards_for_app_name_prefixes.

Note that, if you want to make all the shards visible, you can do that via citus.override_table_visibility

也就是说，通过配置show_shards_for_app_name_prefixes或citus.override_table_visibility，可以控制用户是否能够看到实际的分片表，与Citus 11之前版本的MX节点操作类似

2.4 其他新特性

详见2.1官方文档，其中也有各个版本引入的特性介绍。有兴趣可以参阅下

2.5 简单测试

2.5.1 修改分片数量

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)

2.5.2 添加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  | t           | t        | primary  | default     | t              | t
      2 |       2 | 172.16.166.53 |     5435 | default  | t           | t        | primary  | default     | t              | t
(2 rows)

2.5.3 创建测试数据

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

2.5.4 创建不同的分片表

postgres=# select create_distributed_table('test_dist','id');
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_dist$$)
 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)

2.5.5 CN节点和worker节点查看