5分钟了解Citus核心系统表
一、实战环境
节点 cn_node1(协调节点，不存储数据) postgresql 14.2 10.211.55.9
节点 worker1_node2(工作节点，存储数据) postgresql 14.2 10.211.55.4
节点 worker2_node3(工作节点，存储数据) postgresql 14.2 10.211.55.6
citus-10.2 / debian 9
二、Citus核心系统表
1、查看shard分布信息
列出所有表及shard情况
d1=# select * from pg_dist_shard ;
logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue
--------------+---------+--------------+---------------+---------------
t1 | 102266 | t | -2147483648 | -2013265921
t1 | 102267 | t | -2013265920 | -1879048193
t1 | 102268 | t | -1879048192 | -1744830465
t1 | 102269 | t | -1744830464 | -1610612737
t1 | 102270 | t | -1610612736 | -1476395009
t1 | 102271 | t | -1476395008 | -1342177281
t1 | 102272 | t | -1342177280 | -1207959553
t1 | 102273 | t | -1207959552 | -1073741825
t1 | 102274 | t | -1073741824 | -939524097
t1 | 102275 | t | -939524096 | -805306369
t1 | 102276 | t | -805306368 | -671088641
t1 | 102277 | t | -671088640 | -536870913
t1 | 102278 | t | -536870912 | -402653185
t1 | 102279 | t | -402653184 | -268435457
t1 | 102280 | t | -268435456 | -134217729
t1 | 102281 | t | -134217728 | -1
t1 | 102282 | t | 0 | 134217727
t1 | 102283 | t | 134217728 | 268435455
t1 | 102284 | t | 268435456 | 402653183
t1 | 102285 | t | 402653184 | 536870911
t1 | 102286 | t | 536870912 | 671088639
t1 | 102287 | t | 671088640 | 805306367
t1 | 102288 | t | 805306368 | 939524095
t1 | 102289 | t | 939524096 | 1073741823
t1 | 102290 | t | 1073741824 | 1207959551
t1 | 102291 | t | 1207959552 | 1342177279
t1 | 102292 | t | 1342177280 | 1476395007
t1 | 102293 | t | 1476395008 | 1610612735
t1 | 102294 | t | 1610612736 | 1744830463
t1 | 102295 | t | 1744830464 | 1879048191
t1 | 102296 | t | 1879048192 | 2013265919
t1 | 102297 | t | 2013265920 | 2147483647
t2 | 102072 | t | |
t22 | 102073 | t | |
(34 rows)
2、 查看shard分布信息，及与worker节点的对应关系
d1=# SELECT * FROM citus_shards;
table_name | shardid | shard_name | citus_table_type | colocation_id | nodename | nodeport | shard_size
------------+---------+------------+------------------+---------------+----------------+----------+------------
t1 | 102266 | t1_102266 | distributed | 2 | 192.168.40.132 | 5432 | 139264
t1 | 102267 | t1_102267 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102268 | t1_102268 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102269 | t1_102269 | distributed | 2 | 192.168.40.132 | 5432 | 147456
t1 | 102270 | t1_102270 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102271 | t1_102271 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102272 | t1_102272 | distributed | 2 | 192.168.40.132 | 5432 | 139264
t1 | 102273 | t1_102273 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102274 | t1_102274 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102275 | t1_102275 | distributed | 2 | 192.168.40.132 | 5432 | 147456
t1 | 102276 | t1_102276 | distributed | 2 | 192.168.40.143 | 5432 | 147456
t1 | 102277 | t1_102277 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102278 | t1_102278 | distributed | 2 | 192.168.40.132 | 5432 | 139264
t1 | 102279 | t1_102279 | distributed | 2 | 192.168.40.143 | 5432 | 139264
t1 | 102280 | t1_102280 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102281 | t1_102281 | distributed | 2 | 192.168.40.132 | 5432 | 147456
t1 | 102282 | t1_102282 | distributed | 2 | 192.168.40.143 | 5432 | 139264
t1 | 102283 | t1_102283 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102284 | t1_102284 | distributed | 2 | 192.168.40.132 | 5432 | 139264
t1 | 102285 | t1_102285 | distributed | 2 | 192.168.40.143 | 5432 | 147456
t1 | 102286 | t1_102286 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102287 | t1_102287 | distributed | 2 | 192.168.40.132 | 5432 | 139264
t1 | 102288 | t1_102288 | distributed | 2 | 192.168.40.143 | 5432 | 147456
t1 | 102289 | t1_102289 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102290 | t1_102290 | distributed | 2 | 192.168.40.132 | 5432 | 139264
t1 | 102291 | t1_102291 | distributed | 2 | 192.168.40.143 | 5432 | 147456
t1 | 102292 | t1_102292 | distributed | 2 | 192.168.40.147 | 5432 | 147456
t1 | 102293 | t1_102293 | distributed | 2 | 192.168.40.132 | 5432 | 139264
t1 | 102294 | t1_102294 | distributed | 2 | 192.168.40.143 | 5432 | 139264
t1 | 102295 | t1_102295 | distributed | 2 | 192.168.40.147 | 5432 | 139264
t1 | 102296 | t1_102296 | distributed | 2 | 192.168.40.132 | 5432 | 139264
t1 | 102297 | t1_102297 | distributed | 2 | 192.168.40.143 | 5432 | 139264
t2 | 102072 | t2_102072 | reference | 1 | 192.168.40.147 | 5432 | 4431872
t2 | 102072 | t2_102072 | reference | 1 | 192.168.40.143 | 5432 | 4431872
t2 | 102072 | t2_102072 | reference | 1 | 192.168.40.132 | 5432 | 4431872
t22 | 102073 | t22_102073 | reference | 1 | 192.168.40.143 | 5432 | 4431872
t22 | 102073 | t22_102073 | reference | 1 | 192.168.40.147 | 5432 | 4431872
t22 | 102073 | t22_102073 | reference | 1 | 192.168.40.132 | 5432 | 4431872
(38 rows)
3、查看worker节点信息
d1=# select * from pg_dist_node ;
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+----------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
1 | 1 | 192.168.40.143 | 5432 | default | f | t | primary | default | f | t
2 | 2 | 192.168.40.147 | 5432 | default | f | t | primary | default | f | t
9 | 9 | 192.168.40.132 | 5432 | default | f | t | primary | default | f | t
(3 rows)
4、查看分布表信息
有表大小、分片键等信息
d1=# SELECT * FROM citus_tables;
table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method
------------+------------------+---------------------+---------------+------------+-------------+-------------+---------------
t1 | distributed | id | 2 | 8160 kB | 32 | postgres | heap
t2 | reference | <none> | 1 | 19 MB | 1 | postgres | heap
t22 | reference | <none> | 1 | 19 MB | 1 | postgres | heap
(3 rows
5、查看进程，同pg_stat_activity;
SELECT * FROM citus_dist_stat_activity;
citus_dist_stat_activity
显示在所有节点上执行的分布式查询
citus_worker_stat_activity
显示worker的查询包括针对各个分片的查询
citus_lock_waits
整个群集中的锁的信息
————————————————
版权声明：本文为CSDN博主「童虎学习笔记」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/tonghu_note/article/details/124553461
相关文章