印刻数据库

外观

KingBaseES 参数优化 ​

参数优化是数据库性能调优的重要组成部分，合理的参数配置可以显著提高数据库的性能和稳定性。KingBaseES 提供了大量的参数，用于控制数据库的各种行为。本文将详细介绍 KingBaseES 参数优化的方法和最佳实践。

参数优化的基本概念 ​

什么是参数优化？ ​

参数优化是指根据系统硬件配置和业务需求，调整数据库的各种参数，以达到最佳的性能和稳定性。

参数的分类 ​

  • 内存参数：控制数据库内存使用的参数，如 shared_buffers、work_mem 等
  • I/O 参数：控制数据库 I/O 行为的参数，如 wal_buffers、checkpoint_segments 等
  • 并发参数：控制数据库并发访问的参数，如 max_connections、max_worker_processes 等
  • 查询优化参数：控制查询优化器行为的参数，如 random_page_cost、effective_cache_size 等
  • 日志参数：控制数据库日志行为的参数，如 log_min_duration_statement、log_statement 等
  • 监控参数：控制数据库监控行为的参数，如 track_activity_query_size、stats_temp_directory 等

参数的作用范围 ​

  • 全局参数：影响整个数据库实例，需要重启数据库才能生效
  • 会话参数：只影响当前会话，不需要重启数据库
  • 用户参数：只影响特定用户，不需要重启数据库
  • 数据库参数：只影响特定数据库，不需要重启数据库

内存参数优化 ​

shared_buffers ​

  • 作用：设置共享缓冲区的大小，用于缓存数据页
  • 默认值：128MB
  • 建议值：系统内存的 25%-40%
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL shared_buffers = '2GB';

-- 永久设置（在 kingbase.conf 中）
shared_buffers = 2GB

work_mem ​

  • 作用：设置每个查询操作的工作内存大小
  • 默认值：4MB
  • 建议值：根据并发度和查询复杂度调整，建议 8MB-64MB
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL work_mem = '16MB';

-- 永久设置（在 kingbase.conf 中）
work_mem = 16MB

maintenance_work_mem ​

  • 作用：设置维护操作（如 VACUUM、CREATE INDEX 等）的工作内存大小
  • 默认值：64MB
  • 建议值：系统内存的 5%-10%，建议 256MB-1GB
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL maintenance_work_mem = '512MB';

-- 永久设置（在 kingbase.conf 中）
maintenance_work_mem = 512MB

effective_cache_size ​

  • 作用：告诉优化器系统可用的缓存大小（包括 shared_buffers 和操作系统缓存）
  • 默认值：4GB
  • 建议值：系统内存的 75%-80%
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL effective_cache_size = '16GB';

-- 永久设置（在 kingbase.conf 中）
effective_cache_size = 16GB

wal_buffers ​

  • 作用：设置 WAL（Write-Ahead Log）缓冲区的大小
  • 默认值：16MB
  • 建议值：根据写入负载调整，建议 64MB-256MB
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL wal_buffers = '128MB';

-- 永久设置（在 kingbase.conf 中）
wal_buffers = 128MB

I/O 参数优化 ​

checkpoint_timeout ​

  • 作用：设置检查点的间隔时间
  • 默认值：5min
  • 建议值：15min-30min
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL checkpoint_timeout = '15min';

-- 永久设置（在 kingbase.conf 中）
checkpoint_timeout = 15min

max_wal_size ​

  • 作用：设置检查点之间允许的最大 WAL 大小
  • 默认值：1GB
  • 建议值：根据 checkpoint_timeout 和写入负载调整，建议 4GB-16GB
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL max_wal_size = '8GB';

-- 永久设置（在 kingbase.conf 中）
max_wal_size = 8GB

min_wal_size ​

  • 作用：设置检查点后保留的最小 WAL 大小
  • 默认值：80MB
  • 建议值：根据 max_wal_size 调整，建议 1GB-4GB
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL min_wal_size = '2GB';

-- 永久设置（在 kingbase.conf 中）
min_wal_size = 2GB

checkpoint_completion_target ​

  • 作用：设置检查点完成的目标时间比例
  • 默认值：0.5
  • 建议值：0.8-0.9，平滑检查点过程
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL checkpoint_completion_target = 0.8;

-- 永久设置（在 kingbase.conf 中）
checkpoint_completion_target = 0.8

random_page_cost ​

  • 作用：设置随机页面访问的成本
  • 默认值：4.0
  • 建议值：使用 SSD 存储时，建议 1.1-2.0
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL random_page_cost = 1.5;

-- 永久设置（在 kingbase.conf 中）
random_page_cost = 1.5

并发参数优化 ​

max_connections ​

  • 作用：设置最大连接数
  • 默认值：100
  • 建议值：根据系统资源和并发需求调整，建议 200-500
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL max_connections = 300;

-- 永久设置（在 kingbase.conf 中）
max_connections = 300

max_worker_processes ​

  • 作用：设置最大工作进程数
  • 默认值：8
  • 建议值：CPU 核心数的 1-2 倍
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL max_worker_processes = 16;

-- 永久设置（在 kingbase.conf 中）
max_worker_processes = 16

max_parallel_workers ​

  • 作用：设置最大并行工作进程数
  • 默认值：8
  • 建议值：CPU 核心数的 1-2 倍
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL max_parallel_workers = 16;

-- 永久设置（在 kingbase.conf 中）
max_parallel_workers = 16

max_parallel_workers_per_gather ​

  • 作用：设置每个 Gather 节点的最大并行工作进程数
  • 默认值：2
  • 建议值：根据查询复杂度和并发度调整，建议 2-4
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL max_parallel_workers_per_gather = 4;

-- 永久设置（在 kingbase.conf 中）
max_parallel_workers_per_gather = 4

max_locks_per_transaction ​

  • 作用：设置每个事务的最大锁数量
  • 默认值：64
  • 建议值：根据并发度和查询复杂度调整，建议 128-256
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL max_locks_per_transaction = 128;

-- 永久设置（在 kingbase.conf 中）
max_locks_per_transaction = 128

查询优化参数 ​

effective_cache_size ​

  • 作用：告诉优化器系统可用的缓存大小
  • 默认值：4GB
  • 建议值：系统内存的 75%-80%
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL effective_cache_size = '16GB';

-- 永久设置（在 kingbase.conf 中）
effective_cache_size = 16GB

random_page_cost ​

  • 作用：设置随机页面访问的成本
  • 默认值：4.0
  • 建议值：使用 SSD 存储时，建议 1.1-2.0
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL random_page_cost = 1.5;

-- 永久设置（在 kingbase.conf 中）
random_page_cost = 1.5

seq_page_cost ​

  • 作用：设置顺序页面访问的成本
  • 默认值：1.0
  • 建议值：根据存储类型调整，使用 SSD 存储时，建议 0.5-1.0
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL seq_page_cost = 0.8;

-- 永久设置（在 kingbase.conf 中）
seq_page_cost = 0.8

cpu_tuple_cost ​

  • 作用：设置处理每个元组的 CPU 成本
  • 默认值：0.01
  • 建议值：根据 CPU 性能调整，建议 0.01-0.03
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL cpu_tuple_cost = 0.02;

-- 永久设置（在 kingbase.conf 中）
cpu_tuple_cost = 0.02

cpu_index_tuple_cost ​

  • 作用：设置处理每个索引元组的 CPU 成本
  • 默认值：0.005
  • 建议值：根据 CPU 性能调整，建议 0.005-0.015
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL cpu_index_tuple_cost = 0.01;

-- 永久设置（在 kingbase.conf 中）
cpu_index_tuple_cost = 0.01

日志参数优化 ​

log_min_duration_statement ​

  • 作用：设置记录慢查询的时间阈值
  • 默认值：-1（禁用）
  • 建议值：根据业务需求调整，建议 100ms-1000ms
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL log_min_duration_statement = 500;

-- 永久设置（在 kingbase.conf 中）
log_min_duration_statement = 500

log_statement ​

  • 作用：设置记录的语句类型
  • 默认值：none
  • 建议值：根据监控需求调整，建议 'ddl' 或 'mod'
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL log_statement = 'ddl';

-- 永久设置（在 kingbase.conf 中）
log_statement = 'ddl'

log_checkpoints ​

  • 作用：设置是否记录检查点信息
  • 默认值：off
  • 建议值：建议开启，便于监控检查点行为
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL log_checkpoints = on;

-- 永久设置（在 kingbase.conf 中）
log_checkpoints = on

log_lock_waits ​

  • 作用：设置是否记录锁等待信息
  • 默认值：off
  • 建议值：建议开启，便于监控锁等待情况
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL log_lock_waits = on;

-- 永久设置（在 kingbase.conf 中）
log_lock_waits = on

log_autovacuum_min_duration ​

  • 作用：设置记录 autovacuum 操作的时间阈值
  • 默认值：-1（禁用）
  • 建议值：建议开启，便于监控 autovacuum 行为
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL log_autovacuum_min_duration = 0;

-- 永久设置（在 kingbase.conf 中）
log_autovacuum_min_duration = 0

监控参数优化 ​

track_activity_query_size ​

  • 作用：设置保存当前活动查询的大小
  • 默认值：1024
  • 建议值：根据查询复杂度调整，建议 4096-8192
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL track_activity_query_size = 4096;

-- 永久设置（在 kingbase.conf 中）
track_activity_query_size = 4096

stats_temp_directory ​

  • 作用：设置统计信息临时目录
  • 默认值：pg_stat_tmp
  • 建议值：建议设置为内存文件系统（如 /dev/shm），提高统计信息更新性能
  • 调整方法：
    sql
    -- 永久设置（在 kingbase.conf 中）
stats_temp_directory = '/dev/shm'

track_activities ​

  • 作用：设置是否跟踪每个会话的当前活动
  • 默认值：on
  • 建议值：建议开启，便于监控会话活动
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL track_activities = on;

-- 永久设置（在 kingbase.conf 中）
track_activities = on

track_counts ​

  • 作用：设置是否跟踪表和索引的访问统计信息
  • 默认值：on
  • 建议值：建议开启，便于监控表和索引的使用情况
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL track_counts = on;

-- 永久设置（在 kingbase.conf 中）
track_counts = on

track_io_timing ​

  • 作用：设置是否跟踪 I/O 操作的时间
  • 默认值：off
  • 建议值：建议开启，便于监控 I/O 性能
  • 调整方法：
    sql
    -- 临时设置
SET GLOBAL track_io_timing = on;

-- 永久设置（在 kingbase.conf 中）
track_io_timing = on

参数优化的方法 ​

1. 分析系统资源 ​

  • 了解系统的硬件配置，包括 CPU、内存、磁盘、网络等
  • 了解系统的负载情况，包括并发连接数、查询类型、数据量等

2. 监控当前参数 ​

sql
-- 查看当前参数设置
SHOW ALL;

-- 查看特定参数
SHOW shared_buffers;

-- 查看参数的默认值
SELECT name, setting, unit, short_desc, boot_val
FROM sys_settings
WHERE name = 'shared_buffers';

3. 调整参数 ​

  • 根据系统资源和负载情况，调整相应的参数
  • 从小的调整开始，逐步优化
  • 记录调整前后的性能变化

4. 验证调整效果 ​

  • 使用 EXPLAIN ANALYZE 分析查询性能
  • 查看系统负载和资源使用情况
  • 查看慢查询日志
  • 查看数据库监控指标

5. 持续优化 ​

  • 定期监控数据库性能
  • 根据业务需求变化，调整参数
  • 定期更新系统统计信息

版本差异 (V8 R6 vs V8 R7) ​

V8 R6 ​

  • 参数数量相对较少
  • 参数调整方法相对简单
  • 缺少一些高级参数
  • 参数优化效果相对有限

V8 R7 ​

  • 新增了大量参数，包括并行查询、JSON 支持等
  • 优化了参数的默认值
  • 提供了更多的参数调整选项
  • 支持更多的参数作用范围

最佳实践 ​

1. 了解系统硬件配置 ​

  • 根据 CPU 核心数调整并发参数
  • 根据内存大小调整内存参数
  • 根据存储类型调整 I/O 参数
  • 根据网络带宽调整网络参数

2. 了解业务需求 ​

  • 根据并发度调整 max_connections 参数
  • 根据查询复杂度调整 work_mem 参数
  • 根据写入负载调整 WAL 相关参数
  • 根据监控需求调整日志参数

3. 从小的调整开始 ​

  • 不要一次性调整大量参数
  • 从小的调整开始，逐步优化
  • 记录调整前后的性能变化
  • 验证调整效果

4. 定期监控和调整 ​

  • 定期监控数据库性能
  • 根据业务需求变化，调整参数
  • 定期更新系统统计信息
  • 定期分析慢查询日志

5. 参考最佳实践 ​

  • 参考 KingBaseES 官方文档的最佳实践
  • 参考社区的经验分享
  • 参考类似系统的优化案例

6. 测试调整效果 ​

  • 在测试环境中测试参数调整效果
  • 使用基准测试工具测试性能变化
  • 模拟生产环境的负载进行测试
  • 确保调整不会导致性能下降

常见问题 (FAQ) ​

Q1: 如何查看当前参数设置？ ​

A: 可以使用以下方法查看当前参数设置：

  • 使用 SHOW ALL; 命令查看所有参数
  • 使用 SHOW parameter_name; 命令查看特定参数
  • 查看 kingbase.conf 文件

Q2: 哪些参数需要重启数据库才能生效？ ​

A: 需要重启数据库才能生效的参数包括：

  • shared_buffers
  • max_connections
  • max_worker_processes
  • listen_addresses
  • port

Q3: 如何优化写入性能？ ​

A: 优化写入性能的方法：

  • 调整 WAL 相关参数，如 wal_buffers、max_wal_size 等
  • 调整检查点相关参数，如 checkpoint_timeout、checkpoint_completion_target 等
  • 使用 SSD 存储
  • 优化写入操作，使用批量写入

Q4: 如何优化查询性能？ ​

A: 优化查询性能的方法：

  • 调整查询优化参数，如 random_page_cost、effective_cache_size 等
  • 添加合适的索引
  • 优化查询语句
  • 调整 work_mem 参数

Q5: 如何优化并发性能？ ​

A: 优化并发性能的方法：

  • 调整并发参数，如 max_connections、max_worker_processes 等
  • 使用连接池
  • 优化查询语句，减少锁竞争
  • 调整锁相关参数

总结 ​

参数优化是数据库性能调优的重要组成部分，合理的参数配置可以显著提高数据库的性能和稳定性。在进行参数优化时，应了解系统硬件配置和业务需求，从小的调整开始，逐步优化，定期监控和调整，参考最佳实践，并测试调整效果。

参数优化需要综合考虑多个因素，包括 CPU、内存、磁盘、网络等系统资源，以及并发度、查询类型、数据量等业务需求。通过不断的优化和调整，可以使数据库达到最佳的性能和稳定性。