印刻数据库

外观

KingBaseES 系统参数优化 ​

参数优化概述 ​

KingBaseES 的性能很大程度上取决于系统参数的配置。合理的参数优化可以充分利用硬件资源，提高数据库的吞吐量和响应速度，减少资源争用和等待时间。

参数优化原则 ​

1. 基于实际负载优化 ​

  • 根据业务特点和负载类型进行优化
  • 区分OLTP和OLAP负载的不同优化策略
  • 考虑峰值负载和平均负载的差异

2. 逐步调整原则 ​

  • 每次只修改少量参数，避免大范围修改导致系统不稳定
  • 修改后观察系统性能变化，评估优化效果
  • 建立基线，便于比较优化前后的性能差异

3. 资源平衡原则 ​

  • 平衡CPU、内存、磁盘和网络资源的使用
  • 避免某一资源成为瓶颈
  • 考虑系统的整体性能，而不仅仅是单个指标

4. 版本差异原则 ​

  • 不同版本的参数可能有不同的默认值和取值范围
  • 某些参数在新版本中可能被废弃或新增
  • 参考官方文档中针对特定版本的优化建议

内存参数优化 ​

内存是影响KingBaseES性能的最重要资源之一。合理配置内存参数可以减少磁盘I/O，提高查询执行速度。

1. shared_buffers ​

参数说明：共享缓冲区大小，用于缓存数据块和索引块

默认值：256MB

推荐值：系统内存的25%-30%

优化建议：

  • 对于8GB内存的服务器，建议设置为2GB
  • 对于16GB内存的服务器，建议设置为4GB
  • 对于32GB内存的服务器，建议设置为8GB
  • 对于64GB内存的服务器，建议设置为16GB

配置示例：

sql
ALTER SYSTEM SET shared_buffers = '4GB';

2. work_mem ​

参数说明：每个查询操作（如排序、哈希连接）可使用的内存

默认值：4MB

推荐值：根据查询复杂度和并发连接数调整，一般8MB-32MB

优化建议：

  • 对于复杂查询较多的系统，适当增大此参数
  • 注意：此参数是每个操作的内存限制，不是每个连接
  • 计算公式：work_mem × 并发查询数 × 每个查询的操作数 ≤ 可用内存

配置示例：

sql
ALTER SYSTEM SET work_mem = '16MB';

3. maintenance_work_mem ​

参数说明：维护操作（如VACUUM、CREATE INDEX）可使用的内存

默认值：64MB

推荐值：系统内存的10%，最大不超过2GB

优化建议：

  • 增大此参数可以加快VACUUM和CREATE INDEX操作
  • 建议在维护窗口期间临时增大此参数

配置示例：

sql
-- 日常配置
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- 维护窗口临时调整
SET maintenance_work_mem = '2GB';

4. effective_cache_size ​

参数说明：查询优化器假设的可用缓存大小（包括shared_buffers和操作系统缓存）

默认值：1GB

推荐值：系统内存的50%-75%

优化建议：

  • 此参数不分配实际内存，仅用于优化器决策
  • 合理设置可以帮助优化器生成更好的执行计划

配置示例：

sql
ALTER SYSTEM SET effective_cache_size = '12GB';

5. wal_buffers ​

参数说明：WAL缓冲区大小，用于缓存WAL日志

默认值：-1（自动计算，一般为shared_buffers的1/32）

推荐值：64MB-256MB

优化建议：

  • 对于写入频繁的系统，适当增大此参数
  • 避免设置过大导致内存浪费

配置示例：

sql
ALTER SYSTEM SET wal_buffers = '128MB';

WAL参数优化 ​

WAL（Write-Ahead Logging）是KingBaseES保证数据一致性和可靠性的关键机制。优化WAL参数可以提高写入性能和故障恢复速度。

1. wal_level ​

参数说明：WAL日志级别

默认值：replica

可选值：minimal, replica, logical

推荐值：

  • 对于主备复制，设置为replica
  • 对于逻辑复制，设置为logical

配置示例：

sql
ALTER SYSTEM SET wal_level = 'replica';

2. checkpoint_timeout ​

参数说明：检查点超时时间

默认值：5min

推荐值：15min-30min

优化建议：

  • 增大此参数可以减少检查点次数，降低对系统性能的影响
  • 但会增加故障恢复时间
  • 建议与max_wal_size配合调整

配置示例：

sql
ALTER SYSTEM SET checkpoint_timeout = '15min';

3. max_wal_size ​

参数说明：最大WAL文件大小，决定检查点之间可以生成的WAL量

默认值：1GB

推荐值：4GB-8GB

优化建议：

  • 与checkpoint_timeout配合调整，确保在超时前不会达到最大WAL大小
  • 增大此参数可以减少检查点次数

配置示例：

sql
ALTER SYSTEM SET max_wal_size = '4GB';

4. min_wal_size ​

参数说明：最小WAL文件大小，检查点后保留的最小WAL量

默认值：80MB

推荐值：512MB-1GB

优化建议：

  • 增大此参数可以减少WAL文件的创建和删除频率
  • 对于写入频繁的系统，适当增大此参数

配置示例：

sql
ALTER SYSTEM SET min_wal_size = '512MB';

5. wal_compression ​

参数说明：是否压缩WAL日志

默认值：off（V8 R6），on（V8 R7）

推荐值：on

优化建议：

  • 启用WAL压缩可以减少WAL文件大小，降低磁盘I/O和网络传输量
  • 会增加CPU使用率，但一般影响不大

配置示例：

sql
ALTER SYSTEM SET wal_compression = 'on';

查询优化参数 ​

查询优化参数影响查询优化器的决策和查询执行计划的生成。合理配置这些参数可以提高查询执行效率。

1. random_page_cost ​

参数说明：随机读取页面的成本估算

默认值：4.0

推荐值：

  • 机械硬盘：4.0
  • SSD：1.1-1.5
  • 全内存系统：1.0

优化建议：

  • 根据存储设备类型调整此参数
  • 合理的成本估算可以帮助优化器选择更好的执行计划

配置示例：

sql
-- SSD存储
ALTER SYSTEM SET random_page_cost = '1.1';

2. seq_page_cost ​

参数说明：顺序读取页面的成本估算

默认值：1.0

推荐值：保持默认或根据存储设备调整

优化建议：

  • 对于高性能存储设备，可适当降低此参数
  • 但一般不需要调整

3. effective_io_concurrency ​

参数说明：查询优化器假设的I/O并发度

默认值：1

推荐值：

  • 机械硬盘：2-4
  • SSD：100-200
  • NVMe SSD：200-400

优化建议：

  • 根据存储设备的IOPS能力调整此参数
  • 合理设置可以提高并行查询的性能

配置示例：

sql
-- NVMe SSD
ALTER SYSTEM SET effective_io_concurrency = '200';

4. default_statistics_target ​

参数说明：统计信息收集的目标样本数

默认值：100

推荐值：100-500

优化建议：

  • 对于数据分布不均匀的表，适当增大此参数
  • 可以提高统计信息的准确性，帮助优化器生成更好的执行计划
  • 但会增加ANALYZE操作的时间和资源消耗

配置示例：

sql
ALTER SYSTEM SET default_statistics_target = '200';

5. enable_partitionwise_join ​

参数说明：是否启用分区表的分区级连接

默认值：off

推荐值：on（对于分区表较多的系统）

优化建议：

  • 启用此参数可以提高分区表连接的性能
  • 但会增加优化器的计算时间

配置示例：

sql
ALTER SYSTEM SET enable_partitionwise_join = 'on';

连接与并发参数 ​

连接与并发参数控制数据库的并发处理能力，影响系统的吞吐量和响应速度。

1. max_connections ​

参数说明：最大并发连接数

默认值：100

推荐值：根据业务需求和系统资源调整，一般500-2000

优化建议：

  • 考虑系统内存和CPU资源
  • 计算公式：max_connections × work_mem ≤ 可用内存
  • 对于高并发系统，建议使用连接池

配置示例：

sql
ALTER SYSTEM SET max_connections = '500';

2. superuser_reserved_connections ​

参数说明：为超级用户保留的连接数

默认值：3

推荐值：5-10

优化建议：

  • 确保超级用户在系统高负载时仍能连接数据库
  • 避免设置过大浪费连接资源

配置示例：

sql
ALTER SYSTEM SET superuser_reserved_connections = '5';

3. max_worker_processes ​

参数说明：最大后台工作进程数

默认值：8

推荐值：CPU核心数的1-2倍

优化建议：

  • 对于CPU密集型工作负载，适当增大此参数
  • 与max_parallel_workers配合调整

配置示例：

sql
-- 8核CPU
ALTER SYSTEM SET max_worker_processes = '16';

4. max_parallel_workers ​

参数说明：最大并行工作进程数

默认值：8

推荐值：CPU核心数的50%-75%

优化建议：

  • 对于并行查询较多的系统，适当增大此参数
  • 与max_worker_processes配合调整

配置示例：

sql
-- 8核CPU
ALTER SYSTEM SET max_parallel_workers = '6';

5. max_parallel_workers_per_gather ​

参数说明：每个Gather节点可使用的最大并行工作进程数

默认值：2

推荐值：2-4

优化建议：

  • 对于大型表的并行查询，适当增大此参数
  • 注意控制总并行进程数，避免CPU过载

配置示例：

sql
ALTER SYSTEM SET max_parallel_workers_per_gather = '4';

存储参数优化 ​

存储参数优化可以提高磁盘I/O性能，减少I/O等待时间。

1. random_page_cost ​

参数说明：随机读取页面的成本估算（与查询优化中的参数相同）

优化建议：根据存储设备类型调整，详见查询优化部分

2. effective_io_concurrency ​

参数说明：查询优化器假设的I/O并发度（与查询优化中的参数相同）

优化建议：根据存储设备的IOPS能力调整，详见查询优化部分

3. checkpoint_completion_target ​

参数说明：检查点完成目标，控制检查点期间WAL写入的平滑程度

默认值：0.5

推荐值：0.7-0.9

优化建议：

  • 增大此参数可以使检查点期间的WAL写入更加平滑
  • 减少检查点对系统性能的冲击

配置示例：

sql
ALTER SYSTEM SET checkpoint_completion_target = '0.8';

4. wal_sync_method ​

参数说明：WAL日志的同步方法

默认值：fsync

可选值：fsync, fdatasync, open_datasync, open_sync

推荐值：

  • Linux系统：fdatasync
  • Windows系统：fsync

优化建议：

  • fdatasync只同步文件数据，不同步元数据，性能更好
  • fsync同时同步文件数据和元数据，安全性更高

配置示例：

sql
ALTER SYSTEM SET wal_sync_method = 'fdatasync';

5. synchronous_commit ​

参数说明：同步提交级别

默认值：on

可选值：on, remote_write, local, off

推荐值：

  • 对于需要强一致性的系统：on
  • 对于可以容忍少量数据丢失的系统：remote_write或local
  • 对于性能要求极高的系统：off

优化建议：

  • 权衡数据安全性和性能
  • off模式下，系统崩溃可能导致最近的提交丢失

配置示例：

sql
-- 性能优先
ALTER SYSTEM SET synchronous_commit = 'off';

监控与诊断参数 ​

监控与诊断参数用于收集系统性能数据，帮助DBA排查问题和优化性能。

1. log_min_duration_statement ​

参数说明：记录执行时间超过此值的SQL语句

默认值：-1（不记录）

推荐值：1000ms（1秒）

优化建议：

  • 记录慢查询，便于分析和优化
  • 根据系统响应时间要求调整此参数

配置示例：

sql
ALTER SYSTEM SET log_min_duration_statement = '1000ms';

2. log_statement ​

参数说明：记录的SQL语句类型

默认值：none

可选值：none, ddl, mod, all

推荐值：ddl或mod

优化建议：

  • ddl：记录数据定义语句
  • mod：记录数据修改语句
  • all：记录所有语句（不推荐在生产环境使用）

配置示例：

sql
ALTER SYSTEM SET log_statement = 'ddl';

3. track_io_timing ​

参数说明：是否跟踪I/O操作的时间

默认值：off

推荐值：on

优化建议：

  • 启用此参数可以收集I/O操作的详细时间信息
  • 用于分析I/O瓶颈
  • 会增加少量系统开销

配置示例：

sql
ALTER SYSTEM SET track_io_timing = 'on';

4. track_activities ​

参数说明：是否跟踪每个连接的当前活动

默认值：on

推荐值：on

优化建议：

  • 启用此参数可以通过pg_stat_activity视图查看连接的当前状态
  • 用于监控和排查问题

5. track_counts ​

参数说明：是否跟踪各种统计计数

默认值：on

推荐值：on

优化建议：

  • 启用此参数可以收集表和索引的访问统计信息
  • 用于优化器生成更好的执行计划

参数优化最佳实践 ​

1. 建立性能基线 ​

  • 在优化前，收集系统的性能基线数据
  • 包括CPU使用率、内存使用率、磁盘I/O、查询响应时间等
  • 用于比较优化前后的性能差异

2. 使用专业工具 ​

  • 使用KingBaseES Manager（KEM）进行图形化参数调整
  • 使用pg_stat_statements扩展分析SQL性能
  • 使用EXPLAIN ANALYZE分析查询执行计划
  • 使用pg_stat_activity监控系统状态

3. 定期重新优化 ​

  • 当业务负载发生变化时，重新评估和调整参数
  • 当硬件配置发生变化时，重新优化参数
  • 当数据库版本升级时，重新检查参数配置

4. 测试环境验证 ​

  • 在测试环境中验证参数优化效果
  • 模拟生产环境的负载进行测试
  • 确保优化不会引入新的问题

5. 记录优化过程 ​

  • 记录每次参数调整的原因、时间和效果
  • 建立参数优化文档，便于后续参考
  • 便于回滚到之前的配置

常见问题 ​

Q1: 如何确定哪些参数需要优化？ ​

解决方案：

  • 分析系统性能瓶颈，如CPU使用率高、内存不足、磁盘I/O高
  • 查看慢查询日志，分析执行计划
  • 使用pg_stat_statements找出消耗资源最多的查询
  • 监控系统动态性能视图

Q2: 增大shared_buffers一定会提高性能吗？ ​

解决方案：

  • 不一定，shared_buffers过大可能导致操作系统缓存减少
  • 存在一个最优值，超过此值性能提升不明显
  • 建议通过测试找到最优值

Q3: 如何监控参数优化效果？ ​

解决方案：

  • 使用系统监控工具（如top、vmstat、iostat）
  • 使用KingBaseES内置视图（如pg_stat_bgwriter、pg_stat_database）
  • 监控查询响应时间和吞吐量
  • 比较优化前后的性能指标

Q4: 参数优化后需要重启数据库吗？ ​

解决方案：

  • 取决于参数的context类型
  • 可以通过以下查询查看参数是否需要重启：
    sql
    SELECT name, context FROM pg_settings WHERE name = 'shared_buffers';
  • context为'postmaster'的参数需要重启
  • context为'sighup'的参数可以通过pg_reload_conf()重新加载
  • context为'user'的参数可以在会话级别直接修改

Q5: 如何回滚参数修改？ ​

解决方案：

  • 对于使用ALTER SYSTEM修改的参数，可以使用以下命令重置：
    sql
    ALTER SYSTEM RESET parameter_name;
  • 对于直接修改配置文件的参数，可以恢复备份的配置文件
  • 重启数据库（如果需要）

版本差异注意事项 ​

V8 R6 与 V8 R7 参数差异 ​

参数V8 R6 默认值V8 R7 默认值说明
wal_compressionoffonV8 R7 默认启用WAL压缩
max_wal_size1GB4GBV8 R7 默认增大了最大WAL大小
min_wal_size80MB512MBV8 R7 默认增大了最小WAL大小
checkpoint_timeout5min15minV8 R7 默认增大了检查点超时时间
synchronous_commitonon保持不变
work_mem4MB4MB保持不变

总结 ​

KingBaseES 参数优化是一个持续的过程，需要根据系统负载和业务需求不断调整和优化。合理的参数配置可以充分利用硬件资源，提高数据库的性能和可靠性。

在进行参数优化时，建议：

  1. 基于实际负载和硬件资源进行优化
  2. 逐步调整，避免大范围修改
  3. 建立性能基线，便于比较优化效果
  4. 定期监控和分析系统性能
  5. 记录优化过程，便于回滚和参考

通过持续的参数优化，可以使KingBaseES系统保持最佳性能状态，满足业务不断增长的需求。