一、介绍:
达梦大规模并行处理 MPP(DM Massively Parallel Processing,缩写 DM MPP),是基于达梦数据库管理系统研发的完全对等无共享式集群组件,支持将多个 DM 数据库实例组织为一个并行计算网络,对外提供统一的数据库服务,DM MPP 系统内每个 EP 只负责自身部分数据的读写,执行计划在所有 EP 并行执行,能充分利用各 EP 的计算能力及发挥各 EP 独立存储的优势。数据只在必要时通过 DM 的高速邮件 MAL 系统在 EP 间传递。
MAL 系统:
MAL 系统是 DM 数据库实例间的高速通信系统,是基于 TCP 协议实现的一种内部通信机制,具有可靠、灵活、高效的特性。DM 通过 MAL 系统实现实例间的消息通讯
配置模式:
普通配置:当用户对系统的可靠性要求不高、系统可中断、能保证节点安全或进行简单的MPP测试时,只需使用普通配置。
主备配置:用于数据冗余,保证系统的可靠性——主备镜像关系
二、配置
1、普通模式配置
创建建库模板 dminstall.xml
<?xml version="1.0"?>
<DATABASE>
<LANGUAGE>zh</LANGUAGE>
<TIME_ZONE>+08:00</TIME_ZONE>
<KEY></KEY>
<INSTALL_TYPE>0</INSTALL_TYPE>
<INSTALL_PATH>/dm/dmdbms</INSTALL_PATH>
<INIT_DB>Y</INIT_DB>
<DB_PARAMS>
<PATH>/dm/dmdbms/data</PATH>
<DB_NAME>dmdb</DB_NAME>
<INSTANCE_NAME>dmdb1</INSTANCE_NAME> #四个节点分别修改对应实例名dmdb2、dmdb3、dmdb4
<PORT_NUM>5236</PORT_NUM>
<CTL_PATHS>
<CTL_PATH>/dm/dmdbms/data/dmdb/dm.ctl</CTL_PATH>
</CTL_PATHS>
<LOG_PATHS>
<LOG_PATH>/dm/dmdbms/data/dmdb/DAMENG01.log</LOG_PATH>
<LOG_PATH>/dm/dmdbms/data/dmdb/DAMENG02.log</LOG_PATH>
</LOG_PATHS>
<EXTENT_SIZE>16</EXTENT_SIZE>
<PAGE_SIZE>8</PAGE_SIZE>
<LOG_SIZE>100</LOG_SIZE>
<CASE_SENSITIVE>Y</CASE_SENSITIVE>
<CHARSET>0</CHARSET>
<LENGTH_IN_CHAR>0</LENGTH_IN_CHAR>
<USE_NEW_HASH>1</USE_NEW_HASH>
<SYSDBA_PWD></SYSDBA_PWD>
<SYSAUDITOR_PWD></SYSAUDITOR_PWD>
<SYSSSO_PWD></SYSSSO_PWD>
<SYSDBO_PWD></SYSDBO_PWD>
<TIME_ZONE>+08:00</TIME_ZONE>
<PAGE_CHECK>0</PAGE_CHECK>
<EXTERNAL_CIPHER_NAME></EXTERNAL_CIPHER_NAME>
<EXTERNAL_HASH_NAME></EXTERNAL_HASH_NAME>
<EXTERNAL_CRYPTO_NAME></EXTERNAL_CRYPTO_NAME>
<SVR_KEY></SVR_KEY>
<DB_KEY></DB_KEY>
<ENCRYPT_PWD></ENCRYPT_PWD>
<ENCRYPT_NAME></ENCRYPT_NAME>
<RLOG_ENC_FLAG>N</RLOG_ENC_FLAG>
<USBKEY_PIN></USBKEY_PIN>
<BLANK_PAD_MODE>0</BLANK_PAD_MODE>
<SYSTEM_MIRROR_PATH></SYSTEM_MIRROR_PATH>
<MAIN_MIRROR_PATH></MAIN_MIRROR_PATH>
<ROLL_MIRROR_PATH></ROLL_MIRROR_PATH>
<PRIV_FLAG>0</PRIV_FLAG>
</DB_PARAMS>
<CREATE_DB_SERVICE>Y</CREATE_DB_SERVICE>
<STARTUP_DB_SERVICE>Y</STARTUP_DB_SERVICE>
</DATABASE>
静默创建数据库(4个节点)
./DMInstall.bin -q /dm/dminstall.xml
修改配置文件
vim dm.ini
MAIL_INI = 1
MPP_INI = 1
修改dmmpp.ini配置文件
cat >> /dm/dmdbms/data/dmdb/dmmpp.ini << EOF
[service_name1]
mpp_seq_no = 0
mpp_inst_name = DMDB1
[service_name2]
mpp_seq_no = 1
mpp_inst_name = DMDB2
[service_name3]
mpp_seq_no = 2
mpp_inst_name = DMDB3
[service_name4]
mpp_seq_no = 3
mpp_inst_name = DMDB4
EOF
在一节点生成dmmpp.ctl
./dmctlcvt t2c /dm/dmdbms/data/dmdb/dmmpp.ini /dm/dmdbms/data/dmdb/dmmpp.ctl
cd /dm/dmdbms/data/dmdb/
scp -p dmmpp.ctl 192.168.100.2:/dm/dmdbms/data/dmdb/
scp -p dmmpp.ctl 192.168.100.3:/dm/dmdbms/data/dmdb/
scp -p dmmpp.ctl 192.168.100.4:/dm/dmdbms/data/dmdb/
cat >> /dm/dmdbms/data/dmdb/dmmal.ini << EOF
[mal_inst1]
mal_inst_name = DMDB1
mal_host = 192.168.100.1
mal_port = 5237
[mal_inst2]
mal_inst_name = DMDB2
mal_host = 192.168.100.2
mal_port = 5237
[mal_inst3]
mal_inst_name = DMDB3
mal_host = 192.168.100.3
mal_port = 5237
[mal_inst4]
mal_inst_name = DMDB4
mal_host = 192.168.100.4
mal_port = 5237
EOF
启动数据库
cd /dm/dmdbms/bin
./DmServicedmdb1 start
./DmServicedmdb2 start
./DmServicedmdb3 start
./DmServicedmdb4 start
2、主备模式配置(两个主节点,两个备节点)
初始化数据库
dmdb1:
./dminit path=/dm/dmdbms/data instance_name=dmdb1 db_name=dmdb
dmdb2:
./dminit path=/dm/dmdbms/data instance_name=dmdb2 db_name=dmdb
将数据库文件的文件夹传输至备节点
dmdb3:
cd /dm/dmdbms/data
scp -r 192.168.100.1:/dm/dmdbms/data/dmdb ./
dmdb4:
cd /dm/dmdbms/data
scp -r 192.168.100.2:/dm/dmdbms/data/dmdb ./
修改配置文件
vim dm.ini
MAIL_INI = 1
ARCH_INI = 1
MPP_INI = 1
vim dmmpp.ini
[service_name1]
mpp_seq_no = 0
mpp_inst_name = DMDB1
[service_name2]
mpp_seq_no = 1
mpp_inst_name = DMDB2
在一节点生成dmmpp.ctl
./dmctlcvt t2c /dm/dmdbms/data/dmdb/dmmpp.ini /dm/dmdbms/data/dmdb/dmmpp.ctl
将dmmpp.ctl传输至其他节点
cd /dm/dmdbms/data/dmdb/
scp -p dmmpp.ctl 192.168.100.2:/dm/dmdbms/data/dmdb/
scp -p dmmpp.ctl 192.168.100.3:/dm/dmdbms/data/dmdb/
scp -p dmmpp.ctl 192.168.100.4:/dm/dmdbms/data/dmdb/
cat >> /dm/dmdbms/data/dmdb/dmmal.ini << EOF
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[mal_inst1]
mal_inst_name = DMDB1
mal_host = 192.168.100.1
mal_port = 5237
mal_inst_host = 192.168.100.1
mal_inst_port = 5236
mal_dw_port = 5239
mal_inst_dw_port = 5238
[mal_inst2]
mal_inst_name = DMDB2
mal_host = 192.168.100.2
mal_port = 5237
mal_inst_host = 192.168.100.2
mal_inst_port = 5236
mal_dw_port = 5239
mal_inst_dw_port = 5238
[mal_inst3]
mal_inst_name = DMDB3
mal_host = 192.168.100.3
mal_port = 5237
mal_inst_host = 192.168.100.3
mal_inst_port = 5236
mal_dw_port = 5239
mal_inst_dw_port = 5238
[mal_inst4]
mal_inst_name = DMDB4
mal_host = 192.168.100.4
mal_port = 5237
mal_inst_host = 192.168.100.4
mal_inst_port = 5236
mal_dw_port = 5239
mal_inst_dw_port = 5238
EOF
vi /dm/dmdbms/data/dmdb/dmarch.ini
dmdb1:
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm/dmdbms/data/dmdb/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0
[ARCHIVE_MPP1]
ARCH_TYPE = REALTIME
ARCH_DEST = dmdb3
dmdb2:
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm/dmdbms/data/dmdb/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0
[ARCHIVE_MPP1]
ARCH_TYPE = REALTIME
ARCH_DEST = dmdb4
dmdb3:
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm/dmdbms/data/dmdb/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0
[ARCHIVE_MPP1]
ARCH_TYPE = REALTIME
ARCH_DEST = dmdb1
dmdb4:
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm/dmdbms/data/dmdb/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0
[ARCHIVE_MPP1]
ARCH_TYPE = REALTIME
ARCH_DEST = dmdb2
vi dmwatcher.ini
[MPP_GRP]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 666666
INST_INI = /dm/dmdbms/data/dmdb/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm/dmdbms/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0
在dmdb1节点生成dmwatcher.ctl
./dmctlcvt TYPE=3 SRC=/dm/dmdbms/data/dmdb/dmwatcher.ini DEST=/dm/dmdbms
将dmwatcher.ctl复制和传输到数据文件所在路径
cd /dm/dmdbms/MPP_GRP
cp dmwatcher.ctl /dm/dmdbms/data/dmdb
scp dmwatcher.ctl 192.168.100.2:/dm/dmdbms/data/dmdb
scp dmwatcher.ctl 192.168.100.3:/dm/dmdbms/data/dmdb
scp dmwatcher.ctl 192.168.100.4:/dm/dmdbms/data/dmdb
注册dmwatcher服务——四个节点分别用root执行
cd /dm/dmdbms/script/root
./dm_service_installer.sh -t dmwatcher -p dmdb1 -i /dm/dmdbms/data/dmdb/dmwatcher.ini
./dm_service_installer.sh -t dmwatcher -p dmdb2 -i /dm/dmdbms/data/dmdb/dmwatcher.ini
./dm_service_installer.sh -t dmwatcher -p dmdb3 -i /dm/dmdbms/data/dmdb/dmwatcher.ini
./dm_service_installer.sh -t dmwatcher -p dmdb4 -i /dm/dmdbms/data/dmdb/dmwatcher.ini
启动数据库到mount状态(4个节点)
/dm/dmdbms/bin/dmserver /dm/dmdbms/data/dmdb/dm.ini -noconsole mount &
设置主机和备机模式
disql sysdba/sysdba
dmdb1:
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(666666);
SQL>alter database primary;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
dmdb2:
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(666666);
SQL>alter database primary;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
dmdb3:
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(666666);
SQL>alter database standby;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
dmdb4:
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(666666);
SQL>alter database standby;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
启动守护进程
cd /dm/dmdbms/bin
./DmWatcherServicedmdb1 start
./DmWatcherServicedmdb2 start
./DmWatcherServicedmdb3 start
./DmWatcherServicedmdb4 start
在dmdb1节点配置监控
dmmonitor.ini
cat >> /dm/dmdbms/data/dmdb/dmmonitor.ini << EOF
MON_DW_CONFIRM = 1
MON_LOG_PATH = /dm/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 0
[MPP_GRP]
MON_INST_OGUID = 666666
MON_DW_IP = 192.168.100.1:5239
MON_DW_IP = 192.168.100.2:5239
MON_DW_IP = 192.168.100.3:5239
MON_DW_IP = 192.168.100.4:5239
EOF
启动监视器
/dm/dmdbms/bin/dmmonitor path=/dm/dmdbms/data/dmdb/dmmonitor.ini
三、使用限制:
支持表数据的哈希分布、随机分布、复制分布、范围分布、LIST 分布类型:
- 哈希分布按照表定义中指定的一列或多列对行数据计算一个哈希值,再根据哈希值和哈希映射表,将该行数据分布到映射的节点上;
- 随机分布对于复杂查询及存在较多的节点间数据分发情况,性能不如哈希分布高;
- 复制分布表在每个节点上的本地数据都是一份完整的拷贝,查询该表数据时在任意节点上都能单独完成,不需要从其他节点获取数据。复制分布一般用于数据量不是很大的表;
- 范围分布按照表定义中指定的一个或多个列的列值范围分布项,决定将一行数据存储到MPP 的哪个相应 EP 上;
- LIST 分布通过指定表中的一个或多个列的离散值集,来确定将一行数据存储到 MPP 的哪个相应 EP 上。此分布用于表中列值可列举的情况;
- 单机模式下建的分布表和普通表一样,但是不能创建指定实例名的分布表(如范围分布表和 LIST 分布表);
- 在 MPP 模式下创建分布表,如果未指定列,则默认为 RANDOMLY(随机)分布表;
- 分布列类型不支持 BLOB、CLOB、IMAGE、TEXT、LONGVARCHAR、BIT、BINARY、VARBINARY、LONGVARBINARY、BFILE、时间间隔类型、虚拟列和用户自定义类型;
- HASH 分布、RANGE 分布、LIST 分布允许更新分布列,并支持包含大字段列的表的分布列更新,但包含 INSTEAD OF 触发器的表、堆表不允许更新分布列;
- 对于 FULLY(复制)分布表,只支持单表查询的更新和删除操作,并且查询项或者条件表达式中都不能包含 ROWID 伪列表达式;
- RANGE(范围)分布表和 LIST(列表)分布表,分布列与分布列值列表必须一致,并且指定的实例名不能重复;
- 引用约束的引用列和被引用列都必需包含分布列,且分布情况完全相同;
- 随机分布表不支持 UNIQUE 索引;