虚谷数据库_节点单角色_组网_持续插入节点状况记录

最新推荐文章于 2024-08-09 21:25:57 发布

m0_38018848

最新推荐文章于 2024-08-09 21:25:57 发布

阅读量386

点赞数 6

文章标签：数据库

本文链接：https://blog.csdn.net/m0_38018848/article/details/135905613

版权

本文介绍了虚谷数据库集群中各节点的角色配置，如Master、Worker、Store等，观察到Q节点开启TCP端口进行SQL处理，而其他节点仅使用UDP。分析了随着数据插入，各角色节点的IO、CPU、网络IO和内存压力变化，特别关注了W节点在TPCC场景中的SQL查询性能。

摘要由CSDN通过智能技术生成

1. 前言介绍

虚谷数据库集群节点包括 MWQSG五种角色，平常组网一个节点会配置几种角色，对每种角色的行为动作不好区分。现用单角色节点的方式组成集群，分布观察。

2. 组网介绍

集群配置文件样例：

[root@xugudb-m-node1 SETUP]# cat cluster.ini
#MAX_NODES=16    MASTER_GRPS=1    PROTOCOL='UDP'    MSG_PORT_NUM=1    MAX_SEND_WIN=510 
 MSG_HAVE_CRC=0    MERGE_SMALL_MSG=1    MSG_SIZE=64000    TIMEOUT=20000    RPC_WINDOW=16 
 EJE_WINDOW=16    MAX_SHAKE_TIME=1200    MY_NID=0001    CHECK_RACK=0

 NID=0001  RACK=0001  PORTS='10.28.23.114:50000'  ROLE='M'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0002  RACK=0001  PORTS='10.28.23.86:50000'  ROLE='M'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0003  RACK=0001  PORTS='10.28.23.167:50000'  ROLE='W'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0004  RACK=0001  PORTS='10.28.23.74:50000'  ROLE='W'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0005  RACK=0001  PORTS='10.28.23.179:50000'  ROLE='S'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0006  RACK=0001  PORTS='10.28.23.143:50000'  ROLE='S'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0007  RACK=0001  PORTS='10.28.23.112:50000'  ROLE='S'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0008  RACK=0001  PORTS='10.28.23.147:50000'  ROLE='Q'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0009  RACK=0001  PORTS='10.28.23.92:50000'  ROLE='Q'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0010  RACK=0001  PORTS='10.28.23.116:50000'  ROLE='G'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;
 NID=0011  RACK=0001  PORTS='10.28.23.221:50000'  ROLE='G'  LPU=3  STORE_WEIGHT=3  STATE=DETECT;

3.现象观察

3.1 端口情况

只有 Q节点才开TCP 5138端口，其他节点只有UDP端口

Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.114
-------------------------------------------------
[10.28.23.114:22] out: xugudb-m-node1
[10.28.23.114:22] out: udp        0      0 10.28.23.114:50000      0.0.0.0:*                           11113/xugu12_linux_ 
[10.28.23.114:22] out: udp        0      0 10.28.23.114:50020      0.0.0.0:*                           11113/xugu12_linux_ 
[10.28.23.114:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.86
-------------------------------------------------
[10.28.23.86:22] out: xugudb-m-node2
[10.28.23.86:22] out: udp        0      0 10.28.23.86:50000       0.0.0.0:*                           10728/xugu12_linux_ 
[10.28.23.86:22] out: udp        0      0 10.28.23.86:50020       0.0.0.0:*                           10728/xugu12_linux_ 
[10.28.23.86:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.167
-------------------------------------------------
[10.28.23.167:22] out: xugudb-w-node1
[10.28.23.167:22] out: udp        0      0 10.28.23.167:50000      0.0.0.0:*                           10588/xugu12_linux_ 
[10.28.23.167:22] out: udp        0      0 10.28.23.167:50020      0.0.0.0:*                           10588/xugu12_linux_ 
[10.28.23.167:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.74
-------------------------------------------------
[10.28.23.74:22] out: xugudb-w-node2
[10.28.23.74:22] out: udp        0      0 10.28.23.74:50000       0.0.0.0:*                           10254/xugu12_linux_ 
[10.28.23.74:22] out: udp        0      0 10.28.23.74:50020       0.0.0.0:*                           10254/xugu12_linux_ 
[10.28.23.74:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.179
-------------------------------------------------
[10.28.23.179:22] out: xugudb-s-node1
[10.28.23.179:22] out: udp     1536      0 10.28.23.179:50000      0.0.0.0:*                           10282/xugu12_linux_ 
[10.28.23.179:22] out: udp        0      0 10.28.23.179:50020      0.0.0.0:*                           10282/xugu12_linux_ 
[10.28.23.179:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.143
-------------------------------------------------
[10.28.23.143:22] out: xugudb-s-node2
[10.28.23.143:22] out: udp        0      0 10.28.23.143:50000      0.0.0.0:*                           10283/xugu12_linux_ 
[10.28.23.143:22] out: udp        0      0 10.28.23.143:50020      0.0.0.0:*                           10283/xugu12_linux_ 
[10.28.23.143:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.112
-------------------------------------------------
[10.28.23.112:22] out: xugudb-s-node3
[10.28.23.112:22] out: udp    20224      0 10.28.23.112:50000      0.0.0.0:*                           10213/xugu12_linux_ 
[10.28.23.112:22] out: udp        0      0 10.28.23.112:50020      0.0.0.0:*                           10213/xugu12_linux_ 
[10.28.23.112:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.147
-------------------------------------------------
[10.28.23.147:22] out: xugudb-q-node1
[10.28.23.147:22] out: tcp        0      0 0.0.0.0:5138            0.0.0.0:*               LISTEN      10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0     30 10.28.23.147:5138       10.28.23.125:45522      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0      0 10.28.23.147:5138       10.28.23.125:45526      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0      0 10.28.23.147:5138       10.28.23.125:45510      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0     30 10.28.23.147:5138       10.28.23.125:45518      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0      0 10.28.23.147:5138       10.28.23.125:45512      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0      0 10.28.23.147:5138       10.28.23.125:45524      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0     30 10.28.23.147:5138       10.28.23.125:45516      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0      0 10.28.23.147:5138       10.28.23.125:45508      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0     30 10.28.23.147:5138       10.28.23.125:45514      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: tcp        0     30 10.28.23.147:5138       10.28.23.125:45520      ESTABLISHED 10260/xugu12_linux_ 
[10.28.23.147:22] out: udp        0      0 10.28.23.147:50000      0.0.0.0:*                           10260/xugu12_linux_ 
[10.28.23.147:22] out: udp        0      0 10.28.23.147:50020      0.0.0.0:*                           10260/xugu12_linux_ 
[10.28.23.147:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.92
-------------------------------------------------
[10.28.23.92:22] out: xugudb-q-node2
[10.28.23.92:22] out: tcp        0      0 0.0.0.0:5138            0.0.0.0:*               LISTEN      10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0      0 10.28.23.92:5138        10.28.23.96:36140       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0     30 10.28.23.92:5138        10.28.23.96:36146       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0     30 10.28.23.92:5138        10.28.23.96:36142       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0     30 10.28.23.92:5138        10.28.23.96:36152       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0      0 10.28.23.92:5138        10.28.23.96:36138       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0     30 10.28.23.92:5138        10.28.23.96:36150       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0     30 10.28.23.92:5138        10.28.23.96:36156       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0      0 10.28.23.92:5138        10.28.23.96:36144       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0     30 10.28.23.92:5138        10.28.23.96:36148       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: tcp        0     30 10.28.23.92:5138        10.28.23.96:36154       ESTABLISHED 10233/xugu12_linux_ 
[10.28.23.92:22] out: udp        0      0 10.28.23.92:50000       0.0.0.0:*                           10233/xugu12_linux_ 
[10.28.23.92:22] out: udp        0      0 10.28.23.92:50020       0.0.0.0:*                           10233/xugu12_linux_ 
[10.28.23.92:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.116
-------------------------------------------------
[10.28.23.116:22] out: xugudb-g-node1
[10.28.23.116:22] out: udp        0      0 10.28.23.116:50000      0.0.0.0:*                           10214/xugu12_linux_ 
[10.28.23.116:22] out: udp        0      0 10.28.23.116:50020      0.0.0.0:*                           10214/xugu12_linux_ 
[10.28.23.116:22] out: 
-------------------------------------------------
Execute command : "hostname --fqdn && netstat -anptlu |grep xugu" at Host : 10.28.23.221
-------------------------------------------------
[10.28.23.221:22] out: xugudb-g-node2
[10.28.23.221:22] out: udp        0      0 10.28.23.221:50000      0.0.0.0:*                           10211/xugu12_linux_ 
[10.28.23.221:22] out: udp        0      0 10.28.23.221:50020      0.0.0.0:*                           10211/xugu12_linux_ 
[10.28.23.221:22] out: 
-------------------------------------------------

3.2 集群初始化

初始化时，磁盘初始化情况大致相同，及都会生成DBF,REDO,UNDO等文件；

3.3 随着数据插入

1. IO压力在存储角色 S节点。
2. CPU 压力在存储角色 S节点，以及Q 查询节点；
3. 网络IO 压力也在存储角色 S节点，以及Q 查询节点；
4. 内存压力在 M,S,Q 节点

–内存排序

4 W 节点实验

用TPCC的220仓数据的情况下，执行以下语句找统计订单详情。

select count(*) from BMSQL_ORDER_LINE 
where ol_i_id in (
                select count(distinct(s_I_ID)) from BMSQL_STOCK 
                where s_w_id > 116 and s_quantity>90
                )

从网络IO上看，W节点是参与了 sql 处理。

select * from BMSQL_ORDER_LINE 
where ol_i_id in (
    select count(distinct(s_I_ID)) from BMSQL_STOCK 
    where s_w_id > 116 and s_quantity>59
) parallel  100 ;

m0_38018848

关注

6
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
虚谷数据库_节点单角色_组网_持续插入节点状况记录

虚谷数据库集群节点包括 MWQSG五种角色，平常组网一个节点会配置几种角色，对每种角色的行为动作不好区分。现用单角色节点的方式组成集群，分布观察。集群配置文件样例：只有 Q节点才开TCP 5138端口，其他节点只有UDP端口3.2 集群初始化初始化时，磁盘初始化情况大致相同，及都会生成DBF,REDO,UNDO等文件； 1. IO压力在存储角色 S节点。2. CPU 压力在存储角色 S节点，以及Q 查询节点；3. 网络IO 压力也在存储角色 S节点，以及Q 查询节
复制链接

扫一扫