数据库参数调优
更新时间：2024-07-01 14:39:44
一般情况下，安装完 AntDB 后，我们都会把数据库各类参数调整为一个普适的值，大多数情况下，性能是可以满足需求的。 当您通过数据库统计信息、慢 SQL 等信息了解数据库当前的状态以及存在的问题后，可以针对发现的问题，进行调整和优化，以下这些参数可以关注。
参数修改请参考运维手册中的【数据库参数配置】。
资源消耗参数
| 参数名 | 参数说明 |
|---|---|
| shared_buffers | AntDB 的共享内存，建议设置为实际内存 * 25%~40% |
| work_mem | 排序、哈希可用的内存，每个连接单独占用，所以要考虑连接数 |
| huge_pages | 有高并发查询，且机器内存足够大的情况下，可以考虑设为 on，需要配合 OS 的 hugepage 同步设置 |
| synchronous_commit | 复制的自适应参数，控制事务是否等待 WAL 日志刷盘之后再返回成功。设为 off 的话，会显著提升数据库的写性能，但是有丢数据的风险 |
| checkpoint_completion_target | 控制 checkpoint 的频率 |
| maintenance_work_mem | 指定在维护性操作（例如 VACUUM、CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY）中使用的最大的内存量。因为在一个数据库会话中，一个时刻只有一个这样的操作可以被执行，并且一个数据库安装通常不会有太多这样的操作并发执行， 把这个数值设置得比 work_mem 大很多是安全的。 更大的设置可以改进清理和恢复数据库转储的性能。注意当自动清理运行时，可能会分配最多达这个内存的 autovacuum_max_workers 倍，因此要小心不要把该默认值设置得太高。 通过独立地设置 autovacuum_work_mem 可能会对控制这种情况有所帮助。 |
| autovacuum_work_mem | 指定每个自动清理工作者进程能使用的最大内存量。其默认值为 -1，表示使用 maintenace_work_mem 的值。 |
| temp_file_limit | 指定一个进程能用于临时文件（如排序和哈希临时文件，或者用于保持游标的存储文件）的最大磁盘空间量。一个试图超过这个限制的事务将被取消。这个值以千字节计，-1意味着没有限制。 |
| max_worker_processes | 设置系统能够支持的后台进程的最大数量。这个参数只能在服务器启动时设置。 |
| max_parallel_workers | 设置系统为并行操作所支持的工作者的最大数量。要注意将这个值设置得大于 max_worker_processes 将不会产生效果。注意并行查询可能消耗比非并行查询更多的资源，因为每一个工作者进程时一个完全独立的进程，它对系统产生的影响大致和一个额外的用户会话相同。 |
| max_parallel_workers_per_gather | 设置单个 Gather 或者 Gather Merge 节点能够开始的工作者的最大数量。并行工作者会从 max_worker_processes 建立的进程池获取，数量由 max_parallel_workers 限制。 |
| max_parallel_maintenance_workers | 设置 CREATE INDEX 并行工作的最大数量。 |
autovacuum 相关参数
| 参数 | 说明 |
|---|---|
| autovacuum_max_workers | 指定能同时运行的自动清理进程（除了自动清理启动器之外）的最大数量。默认值为 3。该参数只能在服务器启动时设置。 |
| autovacuum_naptime | 指定自动清理在任意给定数据库上运行的最小延迟。在每一轮中后台进程检查数据库并根据需要为数据库中的表发出 VACUUM 和 ANALYZE 命令。延迟以秒计，且默认值为 1 分钟（1min）。 |
| autovacuum_vacuum_threshold | 指定能在一个表上触发 VACUUM 的被插入、被更新或被删除元组的最小数量。默认值为 50 个元组。可以通过修改表存储参数来覆盖该设置。 |
| autovacuum_analyze_threshold | 指定能在一个表上触发 ANALYZE 的被插入、被更新或被删除元组的最小数量。默认值为 50 个元组。可以通过修改表存储参数来覆盖该设置。 |
| autovacuum_vacuum_scale_factor | 指定一个表尺寸的分数，在决定是否触发 VACUUM 时将它加到 autovacuum_vacuum_threshold 上。默认值为 0.05（表尺寸的5%）。可以通过修改表存储参数来覆盖该设置。 |
| autovacuum_analyze_scale_factor | 指定一个表尺寸的分数，在决定是否触发 ANALYZE 时将它加到 autovacuum_analyze_threshold 上。默认值为 0.1（表尺寸的10%）。可以通过修改表存储参数来覆盖该设置。 |
| autovacuum_vacuum_cost_delay | 指定用于自动 VACUUM 操作中的代价延迟值。如果指定 -1（默认值），则使用 vacuum_code_delay 值。可以通过修改表存储参数来覆盖该设置。 |
| autovacuum_vacuum_cost_limit | 指定用于自动 VACUUM 操作中的代价限制值。如果指定 -1（默认值），则使用 vacuum_cost_limit 值。注意该值被按比例地分配到运行中的自动清理工作者上（如果有多个），因此每一个工作者的限制值之和不会超过这个变量中的值。可以通过修改表存储参数来覆盖该设置。 |
查询规划参数
以下参数不建议全局修改，当您在测试特定的查询时，在当前会话中修改。
| 参数 | 参数说明 |
|---|---|
| enable_bitmapscan | 允许或禁止查询规划器使用位图扫描计划类型。 |
| enable_hashagg | 允许或禁用查询规划器使用哈希聚集计划类型。 |
| enable_hashjoin | 允许或禁止查询规划器使用哈希连接计划类型。 |
| enable_indexscan | 允许或禁止查询规划器使用索引扫描计划类型。 |
| enable_indexonlyscan | 允许或禁止查询规划器使用只用索引扫描计划类型。 |
| enable_seqscan | 允许或禁止查询规划器使用顺序扫描计划类型。它不可能完全禁止顺序扫描，但是关闭这个变量将使得规划器尽可能优先使用其他方法。 |
| enable_sort | 允许或禁止查询规划器使用显式排序步骤。它不可能完全禁止显式排序，但是关闭这个变量将使得规划器尽可能优先使用其他方法。 |
| enable_mergejoin | 允许或禁止查询规划器使用归并连接计划类型。 |
| enable_nestloop | 允许或禁止查询规划器使用嵌套循环连接计划。它不可能完全禁止嵌套循环连接，但是关闭这个变量将使得规划器尽可能优先使用其他方法。 |
| enable_parallel_append | 允许或禁止查询规划器使用并行追加计划类型。 |
| enable_parallel_hash | 允许或禁止查询规划器对并行哈希使用哈希连接计划类型。如果哈希连接计划也没有启用，这个参数没有效果。 |
规划器代价参数
| 参数 | 参数说明 |
|---|---|
| seq_page_cost | 设置规划器计算一次顺序磁盘页面抓取的开销。默认值是 1.0。 |
| random_page_cost | 设置规划器对一次非顺序获取磁盘页面的代价估计。默认值是 4.0。 |
| cpu_tuple_cost | 设置规划器对一次查询中处理每一行的代价估计。默认值是 0.01。 |
| cpu_index_tuple_cost | 设置规划器对一次索引扫描中处理每一个索引项的代价估计。默认值是 0.005。 |
| cpu_operator_cost | 设置规划器对于一次查询中处理每个操作符或函数的代价估计。默认值是 0.0025 |
| parallel_setup_cost | 设置规划器对启动并行工作者进程的代价估计。默认是 1000。 |
| parallel_tuple_cost | 设置规划器对于从一个并行工作者进程传递一个元组给另一个进程的代价估计。默认是 0.1。 |
| min_parallel_table_scan_size | 为必须扫描的表数据量设置一个最小值，扫描的表数据量超过这一个值才会考虑使用并行扫描。对于并行顺序扫描，被扫描的表数据量总是等于表的尺寸，但是在使用索引时，被扫描的表数据量通常会更小。默认值是 8MB。 |
| min_parallel_index_scan_size | 为必须扫描的索引数据量设置一个最小值，扫描的索引数据量超过这一个值时才会考虑使用并行扫描。注意并行索引扫描通常并不会触及整个索引，它是规划器认为该扫描会实际用到的相关页面的数量。默认值是 512KB。 |
问题反馈