腾讯开源OpenTenBase企业级分布式HTAP数据库部署指南

腾讯开源OpenTenBase企业级分布式HTAP数据库部署指南 | 极客日志

# 系统内核参数优化
cat >> /etc/sysctl.conf << EOF
# 内存管理
vm.swappiness = 10
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10
vm.overcommit_memory = 2
vm.overcommit_ratio = 90
# 网络参数
net.core.rmem_max = 134217728
net.core.wmem_max = 134217728
net.ipv4.tcp_rmem = 4096 87380 134217728
net.ipv4.tcp_wmem = 4096 65536 134217728
# 文件句柄限制
fs.file-max = 6815744
EOF
# 应用系统参数
sysctl -p

# 更新系统
sudo dnf update -y

# 安装 OpenTenBase 编译依赖
sudo dnf install -y \
 gcc \
 gcc-c++ \
 make \
 cmake \
 readline-devel \
 zlib-devel \
 openssl-devel \
 uuid-devel \
 bison \
 flex \
 git \
 libcurl-devel \
 libxml2-devel \
 libxslt-devel \
 perl-IPC-Run \
 perl-Test-Simple \
 tcl-devel \
 python3-devel \
 rpm-build \
 pkgconfig \
 krb5-devel \
 openldap-devel \
 cli11-devel

# 创建数据目录
sudo mkdir /data
# 创建 opentenbase 用户
sudo useradd -d /data/opentenbase -s /bin/bash -m opentenbase
# 设置密码
sudo passwd opentenbase

su - opentenbase

cd /data/opentenbase
git clone https://gitee.com/mirrors/OpenTenBase.git

# 设置环境变量
export SOURCECODE_PATH=/data/opentenbase/OpenTenBase
export INSTALL_PATH=/data/opentenbase/install

# 进入源码目录
cd ${SOURCECODE_PATH}
chmod +x configure*

# 重新配置，添加 SSE4.2 支持
CFLAGS="-g -O2 -w -msse4.2 -mcrc32" \
CXXFLAGS="-g -O2 -w -msse4.2 -mcrc32" \
./configure --prefix=/data/opentenbase/install/opentenbase_bin_v2.0 \
--enable-user-switch \
--with-openssl \
--with-ossp-uuid \
--with-libxml

# 编译
make
make install

# 编译 contrib 模块
chmod +x contrib/pgxc_ctl/make_signature
cd contrib
make
make install

# 编辑 bashrc 文件
vim ~/.bashrc
# 添加以下内容：
export OPENTENBASE_HOME=/data/opentenbase/install/opentenbase_bin_v2.0
export PATH=$OPENTENBASE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$OPENTENBASE_HOME/lib:${LD_LIBRARY_PATH}
export LC_ALL=C
# 生效环境变量
source ~/.bashrc

mkdir /data/opentenbase/pgxc_ctl
cd /data/opentenbase/pgxc_ctl

cat > /data/opentenbase/install/opentenbase_bin_v2.0/pgxc_ctl.conf << 'EOF'
#!/usr/bin/env bash
#---- OVERALL -----------------------------------------------------------------------------
pgxcOwner=opentenbase
pgxcUser=$pgxcOwner
tmpDir=/tmp
localTmpDir=$tmpDir
configBackup=y
configBackupHost=localhost
configBackupDir=$HOME/pgxc
configBackupFile=pgxc_ctl.bak
#---- GTM --------------------------------------------------------------------------------
gtmName=gtm
gtmMasterServer=localhost
gtmMasterPort=6666
gtmMasterDir=/data/opentenbase/data/gtm
gtmExtraConfig=none
gtmMasterSpecificExtraConfig=none
gtmSlave=n
gtmSlaveName=gtmSlave
gtmSlaveServer=none
gtmSlavePort=20001
gtmSlaveDir=none
gtmSlaveSpecificExtraConfig=none
gtmProxy=n
gtmProxyNames=()
gtmProxyServers=()
gtmProxyPorts=()
gtmProxyDirs=()
gtmPxyExtraConfig=none
gtmPxySpecificExtraConfig=()
#---- Coordinators --------------------------------------------------------------------
coordMasterDir=/data/opentenbase/data/coord_master
coordSlaveDir=/data/opentenbase/data/coord_slave
coordArchLogDir=/data/opentenbase/data/coord_archlog
coordNames=(cn001)
coordPorts=(30004)
poolerPorts=(30014)
coordForwardPorts=(30024)
coordPgHbaEntries=(0.0.0.0/0)
coordMasterServers=(localhost)
coordMasterDirs=(/data/opentenbase/data/coord_master/cn001)
coordMaxWALsender=5
coordMaxWALSenders=(5)
coordSlave=n
coordSlaveSync=n
coordSlaveServers=(none)
coordSlavePorts=(30005)
coordSlavePoolerPorts=(30015)
coordSlaveForwardPorts=(30025)
coordSlaveDirs=(none)
coordArchLogDirs=(none)
coordExtraConfig=none
coordSpecificExtraConfig=(none)
coordSpecificExtraPgHba=(none)
#---- Datanodes -----------------------------------------------------------------------
datanodeMasterDir=/data/opentenbase/data/dn_master
datanodeSlaveDir=/data/opentenbase/data/dn_slave
datanodeArchLogDir=/data/opentenbase/data/datanode_archlog
primaryDatanode=dn001
datanodeNames=(dn001)
datanodePorts=(20008)
datanodePoolerPorts=(20018)
datanodeForwardPorts=(20028)
datanodePgHbaEntries=(0.0.0.0/0)
datanodeMasterServers=(localhost)
datanodeMasterDirs=(/data/opentenbase/data/dn_master/dn001)
datanodeMaxWalSender=5
datanodeMaxWALSenders=(5)
datanodeSlave=n
datanodeSlaveServers=(none)
datanodeSlavePorts=(20009)
datanodeSlavePoolerPorts=(20019)
datanodeSlaveForwardPorts=(20029)
datanodeSlaveDirs=(none)
datanodeArchLogDirs=(none)
datanodeExtraConfig=none
datanodeSpecificExtraConfig=(none)
datanodeSpecificExtraPgHba=(none)
walArchive=n
EOF

bash -n /data/opentenbase/install/opentenbase_bin_v2.0/pgxc_ctl.conf
echo "语法检查结果：$?"

# 生成 SSH 密钥（如果还没有）
if [ ! -f ~/.ssh/id_rsa ]; then
  ssh-keygen -t rsa -N "" -f ~/.ssh/id_rsa
fi
# 配置本地免密登录
ssh-copy-id opentenbase@localhost
# 或者手动添加
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
chmod 700 ~/.ssh

ssh opentenbase@localhost "echo 'SSH connection test successful'"

# 启动 pgxc_ctl 工具
pgxc_ctl
# 在 pgxc_ctl 命令行中执行：
deploy all
init all
start all
monitor all
exit

# 在 pgxc_ctl 中检查集群状态
monitor all

Running: gtm master
Running: coordinator master cn001
Running: datanode master dn001

# 开放 GTM 端口
sudo firewall-cmd --permanent --add-port=50001/tcp
# 开放 Coordinator 端口
sudo firewall-cmd --permanent --add-port=30004/tcp
sudo firewall-cmd --permanent --add-port=31110/tcp
# 开放 Datanode 端口
sudo firewall-cmd --permanent --add-port=40004/tcp
sudo firewall-cmd --permanent --add-port=41110/tcp
# 重新加载防火墙规则
sudo firewall-cmd --reload

psql -h localhost -p 30004 -d postgres -U opentenbase

-- 创建默认节点组
CREATE DEFAULT NODE GROUP default_group WITH (dn001);
-- 创建分片组
CREATE SHARDING GROUP TO GROUP default_group;

-- 创建测试数据库
CREATE DATABASE testdb;
-- 创建用户
CREATE USER testuser WITH PASSWORD 'testpass';
-- 授权
ALTER DATABASE testdb OWNER TO testuser;
-- 切换到测试数据库
\c testdb testuser
-- 创建分片表
CREATE TABLE test_table(
  id BIGINT,
  name TEXT,
  created_time TIMESTAMP DEFAULT NOW()
) DISTRIBUTE BY SHARD(id);
-- 插入测试数据
INSERT INTO test_table(id, name) VALUES (1, 'OpenTenBase'), (2, 'TencentCloud'), (3, 'OpenCloudOS');
-- 查询测试
SELECT * FROM test_table;

pgxc_ctl start all

pgxc_ctl stop all

pgxc_ctl clean all

# 查看 pgxc_ctl 日志
ls ~/pgxc_ctl/pgxc_log/
cat ~/pgxc_ctl/pgxc_log/最新的日志文件
# 查看各组件日志
ls /data/opentenbase/data/gtm/pg_log/
ls /data/opentenbase/data/coord/pg_log/
ls /data/opentenbase/data/dn001/pg_log/

shared_buffers = 25% of RAM # 例如 8GB 内存设置为 2GB
effective_cache_size = 75% of RAM
work_mem = 4MB
maintenance_work_mem = 64MB

max_connections = 200 # 根据应用需求调整
max_pool_size = 1000 # 连接池大小

log_min_duration_statement = 1000 # 记录执行时间超过 1 秒的查询
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on

# 初始化 Coordinator
/opt/opentenbase/bin/initdb -D /data/opentenbase/coord \
  --nodename coord1 \
  -E UTF8 \
  --locale=en_US.UTF-8
# Coordinator 主要配置参数
cat >> /data/opentenbase/coord/postgresql.conf << EOF
# 网络连接
listen_addresses = '*'
port = 5432
max_connections = 500
# 内存配置
shared_buffers = 2GB
work_mem = 256MB
maintenance_work_mem = 512MB
effective_cache_size = 8GB
# GTM 连接配置
gtm_host = '192.168.1.100'
gtm_port = 6666
# 分布式配置
pool_number = 4
persistent_datanode_connections = on
EOF

# 批量部署 Datanode 脚本
#!/bin/bash
NODES=("dn1:192.168.1.101:5433" "dn2:192.168.1.102:5433" "dn3:192.168.1.103:5433" "dn4:192.168.1.104:5433")
for node_info in "${NODES[@]}"; do
  IFS=':' read -r name ip port <<< "$node_info"
  # 初始化 Datanode
  /opt/opentenbase/bin/initdb -D /data/opentenbase/${name} \
    --nodename ${name} \
    -E UTF8
  # 配置 Datanode 参数
  cat >> /data/opentenbase/${name}/postgresql.conf << EOF
listen_addresses = '*'
port = ${port}
gtm_host = '192.168.1.100'
gtm_port = 6666
shared_buffers = 1GB
EOF
  echo "Datanode ${name} initialized successfully"
done

-- 集群节点状态查询
SELECT node_name, node_type, node_host, node_port, 
       CASE WHEN status = 'N' THEN '正常' WHEN status = 'D' THEN '下线' ELSE '未知' END AS node_status 
FROM pgxc_node ORDER BY node_type, node_name;

-- 分布式表统计信息
SELECT schemaname, tablename, n_tup_ins as "插入行数", n_tup_upd as "更新行数", n_tup_del as "删除行数", last_analyze as "最近分析时间" 
FROM pg_stat_user_tables 
WHERE schemaname NOT IN ('information_schema', 'pg_catalog') 
ORDER BY n_tup_ins DESC LIMIT 10;

-- 数据节点负载分析
WITH node_stats AS (
  SELECT node_name, pg_stat_get_db_blocks_fetched(oid) as blocks_read, pg_stat_get_db_blocks_hit(oid) as blocks_hit
  FROM pg_database, pgxc_node WHERE datname = current_database()
)
SELECT node_name, blocks_read, blocks_hit, 
       CASE WHEN (blocks_read + blocks_hit) > 0 THEN round(blocks_hit::numeric / (blocks_read + blocks_hit) * 100, 2) ELSE 0 END as cache_hit_ratio 
FROM node_stats ORDER BY cache_hit_ratio DESC;

-- 创建分布式表（Hash 分片）
CREATE TABLE user_orders (
  order_id BIGINT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  order_amount DECIMAL(10,2),
  order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  order_status VARCHAR(20)
) DISTRIBUTE BY HASH(user_id);

-- 创建分布式表（Range 分片）
CREATE TABLE sales_data (
  sale_id BIGINT,
  sale_date DATE NOT NULL,
  region VARCHAR(50),
  amount DECIMAL(12,2)
) DISTRIBUTE BY RANGE(sale_date);

-- 为 Range 分片表创建分片规则
SELECT create_distributed_table('sales_data', 'sale_date', 'range');

# 1. 连接池优化
cat >> postgresql.conf << EOF
# 连接池配置
max_pool_size = 100
pool_maintenance_timeout = 30
pool_max_lifetime = 1800
# 查询优化
enable_mergejoin = on
enable_hashjoin = on
enable_nestloop = off
# 分布式场景下禁用嵌套循环
# 内存优化
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 2048
EOF

# 2. 系统级优化脚本
#!/bin/bash
echo "开始 OpenTenBase 性能优化..."
# CPU 绑定优化
echo 'performance' > /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor
# I/O 调度器优化
echo 'deadline' > /sys/block/*/queue/scheduler
# 网络优化
echo 32768 > /proc/sys/net/core/rps_sock_flow_entries
echo 65536 > /proc/sys/net/core/netdev_max_backlog
echo "优化完成！"

分片策略	适用场景	优势	劣势	推荐指数
Hash 分片	均匀分布的 OLTP 负载	数据分布均匀，查询性能稳定	范围查询性能较差	⭐⭐⭐⭐⭐
Range 分片	时序数据，需要范围查询	范围查询性能优秀	可能产生热点	⭐⭐⭐⭐
List 分片	按业务维度分片	业务逻辑清晰	维护复杂度高	⭐⭐⭐

腾讯开源OpenTenBase企业级分布式HTAP数据库部署指南

腾讯开源OpenTenBase企业级分布式HTAP数据库部署指南

摘要

1. OpenTenBase 架构概述

1.1 核心组件介绍

1.2 分片策略对比

2. 环境准备与系统要求

2.1 硬件资源规划

2.2 操作系统配置

2.3 软件依赖

3. OpenTenBase 安装部署

3.1 部署流程概览

3.2 环境准备

1. 更新系统并安装依赖包

2. 创建专用用户

3. 切换到 opentenbase 用户

3.3 源码编译安装

1. 获取源码

2. 编译源码

4. 集中式单节点集群配置

4.1 配置环境变量

4.2 创建集群配置目录

4.3 创建配置文件

4.4 配置 SSH 免密登录

4.5 部署和初始化集群

4.6 验证集群状态

5. 配置防火墙（可选）

6. 数据库初始化和使用

6.1 连接数据库

6.2 创建必要的节点组和分片组

6.3 创建数据库和表

7. 集群管理

7.1 启动集群

7.2 停止集群

7.3 清理集群（重新初始化时使用）

8. 故障排查

8.1 查看日志

8.2 常见问题解决

9. 性能优化建议

9.1 内存优化

9.2 连接优化

9.3 日志优化

10. 集群节点配置

10.1 Coordinator 节点部署

10.2 Datanode 节点部署

11. 集群管理与运维

11.1 集群健康状态监控

11.2 性能监控查询

11.3 分布式表创建与分片

12. 故障排查与优化

12.1 常见问题排查流程

12.2 性能优化实践

13. 实战经验总结

参考链接

微信扫一扫，关注极客日志

更多推荐文章

相关免费在线工具