kingbase教程FG125-金仓数据库混合负载优化隔离
本教程详细介绍金仓数据库混合负载的优化和隔离方法，包括混合负载的概念、优化策略、隔离技术和实战案例。风哥教程参考kingbase官方文档kingbase8性能优化指南、kingbase8系统管理员手册等内容。
混合负载是指在同一数据库实例中同时运行OLTP（在线事务处理）和OLAP（在线分析处理）工作负载，这种情况下需要采取特殊的优化和隔离措施，以确保两种工作负载都能获得良好的性能。
本教程将从基础概念、生产环境规划、项目实施方案、生产案例和经验总结五个部分，全面讲解金仓数据库混合负载的优化和隔离方法。
目录大纲
1.1 金仓数据库混合负载概念
1.2 混合负载的特点与挑战
1.3 混合负载优化隔离的原理
2.1 混合负载环境规划
2.2 资源分配策略
2.3 隔离技术选择
3.1 混合负载优化配置
3.2 资源隔离实施
3.3 监控与管理
4.1 混合负载优化实战
4.2 资源隔离配置实战
4.3 性能监控与调优实战
5.1 混合负载优化最佳实践
5.2 常见问题与解决方案
5.3 性能调优建议，风哥提示：
Part01-基础概念与理论知识
1.1 金仓数据库混合负载概念
混合负载是指在同一数据库实例中同时运行以下类型的工作负载：
- OLTP（在线事务处理）：特点是短事务、高并发、低延迟，如电商交易、银行转账等
- OLAP（在线分析处理）：特点是长事务、低并发、高资源消耗，如报表分析、数据挖掘等
- 批处理作业：特点是定期执行、资源消耗大，如数据ETL、批量数据处理等
混合负载在现代企业中非常常见，因为企业既需要实时的事务处理，又需要对数据进行分析，以支持业务决策。
1.2 混合负载的特点与挑战
混合负载的特点包括：
- 工作负载类型多样，需求不同
- 资源竞争激烈，容易相互影响
- 性能要求差异大，OLTP要求低延迟，OLAP要求高吞吐量
- 管理复杂度高，需要平衡不同工作负载的需求，学习交流加群风哥微信: itpux-com
混合负载面临的挑战包括：
- OLAP查询可能占用大量资源，影响OLTP性能
- OLTP事务可能锁定资源，影响OLAP查询
- 资源分配不合理，导致某些工作负载性能下降
- 监控和管理难度大，难以识别性能瓶颈
1.3 混合负载优化隔离的原理
混合负载优化隔离的原理是通过以下方法实现不同工作负载的和谐共存：
- 资源隔离：为不同类型的工作负载分配独立的资源，避免资源竞争
- 优先级管理：为不同类型的工作负载设置不同的优先级，确保关键工作负载获得足够的资源
- 调度优化：优化数据库的调度算法，合理安排不同工作负载的执行顺序
- 参数调优：根据不同工作负载的特点，调整数据库参数，优化性能，学习交流加群风哥QQ113257174
- 监控与管理：建立完善的监控和管理机制，及时发现和解决性能问题
通过这些方法，可以确保不同类型的工作负载在同一数据库实例中稳定运行，互不影响。
Part02-生产环境规划与建议
2.1 混合负载环境规划
混合负载环境的规划应考虑以下因素：
- 工作负载分析：分析不同类型工作负载的特点、频率和资源需求
- 硬件资源规划：根据工作负载需求，规划服务器的CPU、内存、存储和网络资源
- 数据库架构设计：选择合适的数据库架构，如单机、主从复制、集群等
- 存储规划：为不同类型的工作负载选择合适的存储设备，如SSD、NVMe等
- 网络规划：确保网络带宽满足不同工作负载的需求
合理的规划可以为混合负载环境奠定良好的基础，确保系统的稳定运行。
2.2 资源分配策略
资源分配的策略建议：，更多视频教程www.fgedu.net.cn
- CPU分配：为OLTP工作负载分配更多的CPU核心，确保事务处理的低延迟；为OLAP工作负载分配足够的CPU核心，确保分析查询的性能
- 内存分配：为OLTP工作负载分配足够的shared_buffers，提高事务处理速度；为OLAP工作负载分配足够的work_mem，提高分析查询性能
- 存储分配：将OLTP数据和OLAP数据存储在不同的存储设备上，或使用存储分级技术，提高I/O性能
- 网络分配：为不同类型的工作负载分配不同的网络带宽，确保关键工作负载的网络需求
资源分配应根据工作负载的实际需求进行调整，确保资源的合理利用。
2.3 隔离技术选择
混合负载隔离技术的选择：
- 资源管理器：使用数据库内置的资源管理器，为不同工作负载分配资源
- cgroups：使用操作系统的cgroups功能，为不同工作负载分配CPU、内存等资源
- 表空间隔离：将不同类型的数据存储在不同的表空间中，实现存储隔离
- 用户组隔离：将不同类型的用户分配到不同的用户组中，设置不同的资源限制，更多学习教程公众号风哥教程itpux_com
- 时间隔离：将OLAP查询和批处理作业安排在非业务高峰期执行，避免与OLTP工作负载冲突
选择合适的隔离技术，可以有效减少不同工作负载之间的相互影响，提高系统的整体性能。
Part03-生产环境项目实施方案
3.1 混合负载优化配置
混合负载优化的配置步骤如下：
# 查看当前参数配置
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW max_connections;
SHOW random_page_cost;
shared_buffers
—————-
128MB
work_mem
———-
4MB
maintenance_work_mem
——————–
64MB
max_connections
—————-
100
random_page_cost
—————-
4
# 优化混合负载参数配置
ALTER SYSTEM SET shared_buffers = ‘4GB’;
ALTER SYSTEM SET work_mem = ’16MB’;
ALTER SYSTEM SET maintenance_work_mem = ‘1GB’;
ALTER SYSTEM SET max_connections = ‘200’;
ALTER SYSTEM SET random_page_cost = ‘1.1’;
ALTER SYSTEM SET effective_cache_size = ‘8GB’;
ALTER SYSTEM SET wal_buffers = ’16MB’;
ALTER SYSTEM SET checkpoint_completion_target = ‘0.9’;
ALTER SYSTEM SET synchronous_commit = ‘off’;
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
# 重新加载配置
SELECT pg_reload_conf();
pg_reload_conf
—————-
t
3.2 资源隔离实施
资源隔离的实施步骤如下：
# 创建资源组
CREATE RESOURCE GROUP oltp_group WITH (cpu_rate_limit=60, memory_limit=50);
CREATE RESOURCE GROUP olap_group WITH (cpu_rate_limit=40, memory_limit=50);
CREATE RESOURCE GROUP
CREATE RESOURCE GROUP
# 创建用户并分配到资源组
CREATE USER oltp_user WITH PASSWORD ‘oltp_password’ RESOURCE GROUP oltp_group;
CREATE USER olap_user WITH PASSWORD ‘olap_password’ RESOURCE GROUP olap_group;
CREATE ROLE
CREATE ROLE
# 创建表空间
CREATE TABLESPACE oltp_tablespace LOCATION ‘/kingbase/fgdata/oltp’;
CREATE TABLESPACE olap_tablespace LOCATION ‘/kingbase/fgdata/olap’;
CREATE TABLESPACE
CREATE TABLESPACE
# 创建表并指定表空间
CREATE TABLE oltp_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
value INTEGER
) TABLESPACE oltp_tablespace;
CREATE TABLE olap_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
value INTEGER
) TABLESPACE olap_tablespace;
CREATE TABLE
CREATE TABLE
3.3 监控与管理
混合负载的监控与管理步骤如下：
# 创建监控脚本
cat > /kingbase/scripts/mixed_workload_monitor.sh << 'EOF'
#!/bin/bash
# mixed_workload_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
KINGBASE_HOME=/kingbase/app
export KINGBASE_HOME
PATH=$KINGBASE_HOME/bin:$PATH
export PATH
echo “========== 混合负载监控报告 ==========”
echo “报告时间: $(date)”
echo “\n========== 资源使用情况 ==========”
psql -U fgedu -d fgedudb -c ”
SELECT
rgrpname AS resource_group,
cpu_rate_limit,
memory_limit
FROM
pg_resource_group;
”
echo “\n========== 会话状态 ==========”
psql -U fgedu -d fgedudb -c ”
SELECT
usename,
rgrpname AS resource_group,
state,
current_query,
now() – query_start AS duration
FROM
pg_stat_activity
WHERE
state = ‘active’
ORDER BY
duration DESC;
”
echo “\n========== 表空间使用情况 ==========”
psql -U fgedu -d fgedudb -c ”
SELECT
spcname AS tablespace_name,
pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM
pg_tablespace
WHERE
spcname NOT LIKE ‘pg_%’;
”
EOF
# 脚本创建成功
# 执行监控脚本
chmod +x /kingbase/scripts/mixed_workload_monitor.sh
/kingbase/scripts/mixed_workload_monitor.sh
========== 混合负载监控报告 ==========
报告时间: 2024-01-01 10:00:00
========== 资源使用情况 ==========
resource_group | cpu_rate_limit | memory_limit
—————+—————+————–
default | 100 | 100
oltp_group | 60 | 50
olap_group | 40 | 50
========== 会话状态 ==========
usename | resource_group | state | current_query | duration
———-+—————+——–+————————————-+————–
olap_user | olap_group | active | SELECT * FROM olap_table GROUP BY id | 00:01:30.000
oltp_user | oltp_group | active | INSERT INTO oltp_table VALUES (1, ‘fgedudb’, 100) | 00:00:01.000
========== 表空间使用情况 ==========
tablespace_name | size
—————-+——–
oltp_tablespace | 10 GB
olap_tablespace | 50 GB
Part04-生产案例与实战讲解
4.1 混合负载优化实战
混合负载优化的实战案例：
# 创建混合负载测试脚本
cat > /kingbase/scripts/mixed_workload_test.sh << 'EOF'
#!/bin/bash
# mixed_workload_test.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
KINGBASE_HOME=/kingbase/app
export KINGBASE_HOME
PATH=$KINGBASE_HOME/bin:$PATH
export PATH
# 插入OLTP测试数据
echo “开始插入OLTP测试数据…”
for i in {1..10000}
do
psql -U oltp_user -d fgedudb -c ”
INSERT INTO oltp_table (name, value) VALUES (‘fgedudb$i’, $i);
”
done
echo “OLTP测试数据插入完成”
# 执行OLAP查询
echo “开始执行OLAP查询…”
psql -U olap_user -d fgedudb -c ”
SELECT
value,
COUNT(*),
AVG(value),
SUM(value)
FROM
olap_table
GROUP BY
value
ORDER BY
value;
” > /kingbase/logs/olap_query.log
echo “OLAP查询执行完成”
# 同时执行OLTP和OLAP操作
echo “开始同时执行OLTP和OLAP操作…”
# 启动OLTP插入操作
(for i in {1..5000}
do
psql -U oltp_user -d fgedudb -c ”
INSERT INTO oltp_table (name, value) VALUES (‘fgedudb$i’, $i);
”
done) &
# 启动OLAP查询操作
(psql -U olap_user -d fgedudb -c ”
SELECT
value,
COUNT(*),
AVG(value),
SUM(value)
FROM
olap_table
GROUP BY
value
ORDER BY
value;
” > /kingbase/logs/olap_query2.log) &
# 等待操作完成
wait
echo “混合负载测试完成”
EOF
# 脚本创建成功
# 执行混合负载测试脚本
chmod +x /kingbase/scripts/mixed_workload_test.sh
/kingbase/scripts/mixed_workload_test.sh
开始插入OLTP测试数据…
OLTP测试数据插入完成
开始执行OLAP查询…
OLAP查询执行完成
开始同时执行OLTP和OLAP操作…
混合负载测试完成
4.2 资源隔离配置实战
资源隔离配置的实战案例：
# 创建资源隔离配置脚本
cat > /kingbase/scripts/configure_resource_isolation.sh << 'EOF'
#!/bin/bash
# configure_resource_isolation.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
KINGBASE_HOME=/kingbase/app
export KINGBASE_HOME
PATH=$KINGBASE_HOME/bin:$PATH
export PATH
# 创建资源组
psql -U fgedu -d fgedudb -c ”
CREATE RESOURCE GROUP oltp_group WITH (cpu_rate_limit=70, memory_limit=60);
CREATE RESOURCE GROUP olap_group WITH (cpu_rate_limit=30, memory_limit=40);
CREATE RESOURCE GROUP batch_group WITH (cpu_rate_limit=20, memory_limit=30, active_statements=5);
”
# 创建用户并分配到资源组
psql -U fgedu -d fgedudb -c ”
CREATE USER oltp_user WITH PASSWORD ‘oltp_password’ RESOURCE GROUP oltp_group;
CREATE USER olap_user WITH PASSWORD ‘olap_password’ RESOURCE GROUP olap_group;
CREATE USER batch_user WITH PASSWORD ‘batch_password’ RESOURCE GROUP batch_group;
”
# 授权
psql -U fgedu -d fgedudb -c ”
GRANT ALL PRIVILEGES ON oltp_table TO oltp_user;
GRANT ALL PRIVILEGES ON olap_table TO olap_user;
GRANT ALL PRIVILEGES ON oltp_table TO batch_user;
GRANT ALL PRIVILEGES ON olap_table TO batch_user;
”
# 创建cgroup配置
cat > /etc/cgconfig.conf << 'CGEOF'
group kingbase_oltp {
cpu {
cpu.shares = 700;
cpu.cfs_quota_us = 70000;
cpu.cfs_period_us = 100000;
}
memory {
memory.limit_in_bytes = 6G;
memory.soft_limit_in_bytes = 5G;
}
}
group kingbase_olap {
cpu {
cpu.shares = 300;
cpu.cfs_quota_us = 30000;
cpu.cfs_period_us = 100000;
}
memory {
memory.limit_in_bytes = 4G;
memory.soft_limit_in_bytes = 3G;
}
}
CGEOF
# 重启cgroup服务
systemctl restart cgconfig
echo “资源隔离配置完成”
EOF
# 脚本创建成功
# 执行资源隔离配置脚本
chmod +x /kingbase/scripts/configure_resource_isolation.sh
/kingbase/scripts/configure_resource_isolation.sh
CREATE RESOURCE GROUP
CREATE RESOURCE GROUP
CREATE RESOURCE GROUP
CREATE ROLE
CREATE ROLE
CREATE ROLE
GRANT
GRANT
GRANT
GRANT
资源隔离配置完成
4.3 性能监控与调优实战
性能监控与调优的实战案例：
# 创建性能监控与调优脚本
cat > /kingbase/scripts/performance_tuning.sh << 'EOF'
#!/bin/bash
# performance_tuning.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
KINGBASE_HOME=/kingbase/app
export KINGBASE_HOME
PATH=$KINGBASE_HOME/bin:$PATH
export PATH
echo “========== 性能监控与调优 ==========”
# 查看系统负载
echo “\n========== 系统负载 ==========”
uptime
# 查看CPU使用情况
echo “\n========== CPU使用情况 ==========”
top -b -n 1 | head -20
# 查看内存使用情况
echo “\n========== 内存使用情况 ==========”
free -h
# 查看磁盘I/O情况
echo “\n========== 磁盘I/O情况 ==========”
iostat -x 1 3
# 查看数据库连接情况
echo “\n========== 数据库连接情况 ==========”
psql -U fgedu -d fgedudb -c ”
SELECT
usename,
rgrpname AS resource_group,
state,
count(*) AS connection_count
FROM
pg_stat_activity
GROUP BY
usename, rgrpname, state
ORDER BY
connection_count DESC;
”
# 查看慢SQL
echo “\n========== 慢SQL ==========”
psql -U fgedu -d fgedudb -c ”
SELECT
usename,
query,
duration
FROM
pg_stat_statements
ORDER BY
duration DESC
LIMIT 10;
”
# 查看等待事件
echo “\n========== 等待事件 ==========”
psql -U fgedu -d fgedudb -c ”
SELECT
event_type,
event,
count(*)
FROM
pg_stat_activity
WHERE
state = ‘active’
GROUP BY
event_type, event
ORDER BY
count(*) DESC;
”
EOF
# 脚本创建成功
# 执行性能监控与调优脚本
chmod +x /kingbase/scripts/performance_tuning.sh
/kingbase/scripts/performance_tuning.sh
========== 性能监控与调优 ==========
========== 系统负载 ==========
10:00:00 up 10 days, 2:34, 1 user, load average: 1.50, 1.20, 1.00
========== CPU使用情况 ==========
top – 10:00:00 up 10 days, 2:34, 1 user, load average: 1.50, 1.20, 1.00
Tasks: 200 total, 1 running, 199 sleeping, 0 stopped, 0 zombie
%Cpu(s): 30.0 us, 5.0 sy, 0.0 ni, 60.0 id, 5.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 16384000 total, 8192000 free, 4096000 used, 4096000 buff/cache
KiB Swap: 8192000 total, 8192000 free, 0 used. 11264000 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1234 kingbase 20 0 409600 204800 153600 S 25.0 1.3 1:00.00 postgres
5678 kingbase 20 0 409600 204800 153600 S 15.0 1.3 0:30.00 postgres
9012 kingbase 20 0 409600 204800 153600 S 10.0 1.3 0:15.00 postgres
========== 内存使用情况 ==========
total used free shared buff/cache available
Mem: 16G 4.0G 8.0G 512M 4.0G 11G
Swap: 8.0G 0B 8.0G
========== 磁盘I/O情况 ==========
Linux 5.14.0-1.el9.x86_64 (fgedu.net.cn) 01/01/2024 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
30.00 0.00 5.00 5.00 0.00 60.00
Device tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 10.00 100.00 200.00 100 200
sdb 5.00 50.00 100.00 50 100
========== 数据库连接情况 ==========
usename | resource_group | state | connection_count
———-+—————+——–+——————
oltp_user | oltp_group | active | 50
olap_user | olap_group | active | 10
batch_user | batch_group | idle | 5
========== 慢SQL ==========
usename | query | duration
———-+———————————–+————
olap_user | SELECT * FROM olap_table GROUP BY id | 00:01:30.00
batch_user | INSERT INTO oltp_table SELECT * FROM olap_table | 00:00:45.00
oltp_user | UPDATE oltp_table SET value = value + 1 WHERE id = 1 | 00:00:05.00
========== 等待事件 ==========
event_type | event | count
————+———————-+——-
IO | db file sequential read | 10
Lock | row lock | 5
CPU | CPU | 2
风哥提示：混合负载优化需要根据实际工作负载情况进行调整，确保OLTP和OLAP工作负载都能获得良好的性能。
Part05-风哥经验总结与分享
5.1 混合负载优化最佳实践
- 工作负载分析：详细分析不同类型工作负载的特点和资源需求，为优化提供依据。
- 资源隔离：使用资源组、cgroups等技术，为不同类型的工作负载分配独立的资源。
- 参数调优：根据混合负载的特点，调整数据库参数，优化性能。，from DB视频:www.itpux.com
- 存储优化：为不同类型的工作负载选择合适的存储设备，提高I/O性能。
- 监控与管理：建立完善的监控和管理机制，及时发现和解决性能问题。
- 时间调度：将OLAP查询和批处理作业安排在非业务高峰期执行，避免与OLTP工作负载冲突。
- 查询优化：优化OLAP查询，减少资源消耗，提高查询性能。
5.2 常见问题与解决方案
| 问题 | 原因 | 解决方案 |
|---|---|---|
| OLTP性能下降 | OLAP查询占用过多资源 | 为OLAP查询设置资源限制，或在非业务高峰期执行 |
| OLAP查询性能差 | 资源分配不足，或查询语句未优化 | 增加OLAP查询的资源分配，优化查询语句 |
| 资源竞争激烈 | 资源分配不合理，或工作负载过于集中 | 重新调整资源分配，分散工作负载 |
| 系统稳定性下降 | 混合负载导致系统资源耗尽 | 设置资源限制，确保系统有足够的空闲资源 |
5.3 性能调优建议
- 针对OLTP工作负载：
- 配置较大的shared_buffers，提高事务处理速度
- 设置合理的work_mem，提高并发性能
- 启用同步提交，确保数据一致性
- 优化索引，提高查询性能
- 针对OLAP工作负载：
- 配置较大的work_mem和maintenance_work_mem，提高分析查询性能
- 使用并行查询，提高查询速度
- 优化表结构，如使用分区表
- 收集统计信息，确保执行计划的准确性
- 针对混合工作负载：
- 使用资源管理器，为不同工作负载分配资源
- 设置合理的资源限制，避免资源耗尽
- 监控系统性能，及时调整资源分配
- 使用读写分离，将OLAP查询引导到从库
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
