MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

PostgreSQL执行计划相关配置项调优

2022-06-204.3k 阅读

PostgreSQL执行计划基础概念

在深入探讨执行计划相关配置项调优之前，我们先来理解一下PostgreSQL执行计划的基础概念。

PostgreSQL的执行计划是数据库为了完成一条SQL语句而生成的操作步骤的详细描述。它包含了从何处获取数据（例如从哪个表、使用何种索引），如何连接表（嵌套循环连接、哈希连接等），以及如何对数据进行排序、聚合等操作的信息。

当我们执行一条SQL语句时，PostgreSQL的查询优化器会分析该语句，考虑多种可能的执行路径，并根据统计信息和配置参数选择一个它认为最优的执行计划。例如，对于下面这条简单的查询语句：

SELECT * FROM employees WHERE department = 'HR';

查询优化器需要决定是全表扫描employees表，还是利用department列上可能存在的索引来快速定位符合条件的数据行。

执行计划可以通过EXPLAIN关键字来查看。例如，在上述查询语句前加上EXPLAIN：

EXPLAIN SELECT * FROM employees WHERE department = 'HR';

PostgreSQL会返回类似如下的执行计划信息：

                    QUERY PLAN
----------------------------------------------------
 Seq Scan on employees  (cost=0.00..241.00 rows=100 width=57)
   Filter: (department = 'HR'::text)
(2 rows)

这里显示的是进行全表扫描（Seq Scan），并给出了估计的成本（cost）、预计返回的行数（rows）等信息。成本是查询优化器用来衡量不同执行路径优劣的一个量化指标，它综合考虑了磁盘I/O、CPU运算等开销。

影响执行计划的配置项概述

PostgreSQL中有多个配置项会影响执行计划的生成，这些配置项大致可以分为以下几类：

  1. 成本相关配置项：用于调整查询优化器对不同操作（如顺序扫描、索引扫描、连接操作等）的成本估算。
  2. 统计信息相关配置项：影响数据库收集和使用表及索引统计信息的方式，而统计信息是查询优化器做出决策的重要依据。
  3. 内存相关配置项：决定了查询执行过程中可用的内存量，这会影响到诸如排序、哈希表构建等操作的执行方式。

下面我们将详细介绍这些配置项及其调优方法。

成本相关配置项调优

  1. seq_page_cost
    • 含义：该配置项表示顺序扫描一个数据页的成本。默认值为1.0，单位通常可以理解为磁盘I/O操作的相对成本。例如，如果将其值增大，查询优化器会更倾向于使用索引扫描而不是顺序扫描，因为顺序扫描的成本被认为更高了。
    • 调优场景：当磁盘I/O性能较高时，顺序扫描相对成本较低，可适当降低该值。例如，使用高速固态硬盘（SSD）存储数据，顺序读取速度很快，此时可以将seq_page_cost设为0.5甚至更低。
    • 修改方法：可以在postgresql.conf文件中修改该配置项，修改后需要重启PostgreSQL服务使配置生效。示例：
seq_page_cost = 0.5

也可以在运行时使用SET命令临时修改，但这种修改只对当前会话有效：

SET seq_page_cost = 0.5;
  1. random_page_cost
    • 含义：此配置项表示随机访问一个数据页的成本。默认值为4.0，通常比seq_page_cost大，因为随机I/O操作相对顺序I/O操作在传统机械硬盘上更为昂贵。
    • 调优场景：如果存储设备对随机I/O操作的支持较好，如使用基于闪存的存储阵列，可适当降低该值，使查询优化器在选择执行计划时对随机访问的成本评估更为合理。
    • 修改方法：同seq_page_cost，可以在postgresql.conf文件中修改并重启服务，或在运行时使用SET命令临时修改。例如：
random_page_cost = 2.0

SET random_page_cost = 2.0;
  1. cpu_tuple_cost
    • 含义：代表处理一个元组（即一行数据）的CPU成本。默认值为0.01，用于评估在CPU上进行元组相关操作（如过滤、排序等）的成本。
    • 调优场景：如果服务器的CPU性能强劲，处理元组的成本相对较低，可以适当降低该值。这样查询优化器在生成执行计划时，会更倾向于选择一些CPU密集型但可能减少I/O的操作。
    • 修改方法：在postgresql.conf文件中修改：
cpu_tuple_cost = 0.005

或者在运行时临时修改：

SET cpu_tuple_cost = 0.005;
  1. cpu_index_tuple_cost
    • 含义：表示处理一个索引元组的CPU成本。默认值为0.005，用于评估与索引相关的CPU操作成本。
    • 调优场景：当索引操作在CPU上的开销相对较小时，比如使用了高效的索引结构且CPU性能优越，可以适当降低该值，让查询优化器更愿意使用索引相关的操作。
    • 修改方法：在postgresql.conf文件中修改：
cpu_index_tuple_cost = 0.003

或者在运行时临时修改：

SET cpu_index_tuple_cost = 0.003;
  1. cpu_operator_cost
    • 含义：代表执行一个操作符（如比较操作符=、<等）的CPU成本。默认值为0.0025，用于计算在CPU上执行各种操作符的开销。
    • 调优场景：如果CPU处理操作符的速度非常快，可以适当降低该值，使查询优化器在评估执行计划时对包含操作符的操作成本评估更低。
    • 修改方法：在postgresql.conf文件中修改：
cpu_operator_cost = 0.001

或者在运行时临时修改：

SET cpu_operator_cost = 0.001;

统计信息相关配置项调优

  1. autovacuum
    • 含义：autovacuum是PostgreSQL的自动清理和分析机制。它会定期自动对表进行VACUUM（清理垃圾元组）和ANALYZE（更新统计信息）操作。默认是开启的（值为on）。
    • 调优场景：如果数据库中的数据更新频率很高，自动分析的频率可能不足以保证统计信息的及时性，导致查询优化器使用不准确的统计信息生成执行计划。此时可以适当调整自动分析的参数，或者在数据发生大量变化后手动执行ANALYZE。
    • 相关参数：
      • autovacuum_naptime：表示自动清理和分析的周期，默认值为60秒。如果数据变化频繁，可以适当缩短该时间，例如设为30秒：
autovacuum_naptime = 30

    - **autovacuum_vacuum_threshold**：指定在自动清理之前表中需要积累的最小垃圾元组数量，默认值为50。如果表非常大，可以适当增大该值，以减少不必要的自动清理操作。例如，设为100：

autovacuum_vacuum_threshold = 100

    - **autovacuum_analyze_threshold**：表示在自动分析之前表中需要积累的最小新元组或更新元组数量，默认值为50。同样，如果表很大且更新频繁，可以调整该值，比如设为200：

autovacuum_analyze_threshold = 200
  1. default_statistics_target
    • 含义：该配置项决定了ANALYZE操作收集统计信息时的详细程度。默认值为100，值越高，收集的统计信息越详细，但同时也会增加ANALYZE的执行时间和存储统计信息的空间。
    • 调优场景：对于经常用于复杂查询条件的列，适当提高该值可以让查询优化器获得更准确的统计信息，从而生成更好的执行计划。例如，对于包含大量不同值且经常用于WHERE子句过滤的列，可以将default_statistics_target设为200。
    • 修改方法：在postgresql.conf文件中修改：
default_statistics_target = 200

也可以针对特定的表或列在运行时修改，例如针对employees表的department列：

ALTER TABLE employees ALTER COLUMN department SET STATISTICS 200;
  1. track_counts
    • 含义：该配置项用于控制是否跟踪表的行数变化。默认值为on，开启后，PostgreSQL会在内部维护表的行数信息，这对于查询优化器估计结果集大小很有帮助。
    • 调优场景：如果关闭track_counts，查询优化器在估计行数时可能会不准确，导致执行计划偏差。但在一些特殊情况下，如某些只读表的数据量固定且已知，关闭该选项可以节省一些系统资源。不过这种情况比较少见，一般建议保持默认开启状态。
    • 修改方法：在postgresql.conf文件中修改：
track_counts = off

或者在运行时临时修改：

SET track_counts = off;

内存相关配置项调优

  1. work_mem
    • 含义：work_mem定义了在排序操作（如ORDER BY、DISTINCT等）和哈希表构建（如哈希连接操作）时，每个查询可以使用的内存量。默认值通常为4MB。如果内存量不足，PostgreSQL会将数据临时写入磁盘，这会显著降低查询性能。
    • 调优场景：对于包含复杂排序或连接操作的查询，如果服务器有足够的内存，可以适当增大work_mem的值。例如，对于一个涉及多表连接并需要排序结果的查询，将work_mem设为16MB或更高可能会提高查询性能。
    • 修改方法：在postgresql.conf文件中修改：
work_mem = 16MB

或者在运行时临时修改：

SET work_mem = 16MB;
  1. maintenance_work_mem
    • 含义：此配置项决定了在执行维护操作（如VACUUM、CREATE INDEX等）时可以使用的最大内存量。默认值通常为64MB。与work_mem不同，它是针对维护操作的内存限制。
    • 调优场景：当执行大规模的VACUUM操作或创建大型索引时，如果服务器内存充足，可以增大maintenance_work_mem的值，以加快这些操作的执行速度。例如，在对一个非常大的表进行VACUUM FULL操作时，将其设为512MB可能会显著缩短操作时间。
    • 修改方法：在postgresql.conf文件中修改：
maintenance_work_mem = 512MB
  1. shared_buffers
    • 含义：shared_buffers是PostgreSQL用于缓存数据库页面的共享内存区域大小。它是影响数据库性能的关键配置项之一。增大该值可以提高数据的缓存命中率，减少磁盘I/O操作。
    • 调优场景：根据服务器的内存总量来合理设置shared_buffers。一般建议将其设置为服务器物理内存的25% - 40%。例如，如果服务器有32GB内存，可以将shared_buffers设为8GB到12GB之间。
    • 修改方法：在postgresql.conf文件中修改：
shared_buffers = 8GB

修改shared_buffers后需要重启PostgreSQL服务使配置生效。

综合调优示例

假设我们有一个电商数据库，其中有products表、orders表和customers表。我们经常执行如下复杂查询：

SELECT p.product_name, o.order_date, c.customer_name
FROM products p
JOIN orders o ON p.product_id = o.product_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE p.price > 100 AND o.status = 'completed'
ORDER BY o.order_date DESC;
  1. 成本相关调优：
    • 假设数据库服务器使用的是高性能SSD存储，顺序I/O和随机I/O性能都很好。我们可以适当降低seq_page_cost和random_page_cost。在postgresql.conf文件中修改：
seq_page_cost = 0.5
random_page_cost = 1.0

- 服务器CPU性能也很强劲，我们可以降低`cpu_tuple_cost`、`cpu_index_tuple_cost`和`cpu_operator_cost`：

cpu_tuple_cost = 0.005
cpu_index_tuple_cost = 0.003
cpu_operator_cost = 0.001
  1. 统计信息相关调优：
    • 由于数据更新频繁，我们缩短autovacuum_naptime为30秒，增大autovacuum_analyze_threshold为200：
autovacuum_naptime = 30
autovacuum_analyze_threshold = 200

- 对于`products`表的`price`列、`orders`表的`status`列和`order_date`列，我们提高`default_statistics_target`到200。首先在`postgresql.conf`文件中全局修改：

default_statistics_target = 200

然后针对特定列再次确认设置：

ALTER TABLE products ALTER COLUMN price SET STATISTICS 200;
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 200;
ALTER TABLE orders ALTER COLUMN order_date SET STATISTICS 200;
  1. 内存相关调优：
    • 考虑到这个查询涉及多表连接和排序操作，我们增大work_mem到16MB：
work_mem = 16MB

- 由于数据库规模较大，在进行维护操作时可能需要更多内存，我们增大`maintenance_work_mem`到512MB：

maintenance_work_mem = 512MB

- 服务器有64GB内存，我们将`shared_buffers`设置为20GB：

shared_buffers = 20GB

通过上述综合调优后，再次执行EXPLAIN查看该复杂查询的执行计划，会发现查询优化器生成的执行计划在成本、预计行数等方面都有更合理的评估，实际查询性能也会得到显著提升。

执行计划调优的注意事项

  1. 测试环境验证：在对配置项进行修改并应用到生产环境之前，一定要在测试环境进行充分的验证。不同的配置项修改可能会对不同类型的查询产生不同的影响，有些修改甚至可能导致某些查询性能下降。通过在测试环境模拟生产数据和查询负载，可以确保配置项调优的安全性和有效性。
  2. 监控与调整：调优不是一次性的工作，而是一个持续的过程。随着数据库中数据量的变化、查询模式的改变以及硬件环境的调整，之前优化的配置可能不再是最优的。因此，需要定期监控数据库的性能指标（如查询响应时间、磁盘I/O使用率、CPU使用率等），根据监控结果适时调整配置项。
  3. 了解系统负载特性：不同的数据库应用场景具有不同的负载特性。例如，在线事务处理（OLTP）系统和数据分析（OLAP）系统对配置项的需求就有很大差异。OLTP系统通常对响应时间要求较高，处理的是大量短小的事务；而OLAP系统则更注重处理复杂的查询和大数据量的聚合操作。在调优之前，深入了解系统的负载特性，有助于针对性地调整配置项。
  4. 配置项相互影响：PostgreSQL的各个配置项之间并不是孤立的，它们相互影响。例如，增大shared_buffers可能会减少磁盘I/O，从而影响seq_page_cost和random_page_cost的实际效果；调整work_mem可能会影响排序和连接操作的方式，进而影响到查询优化器对成本的评估。在进行配置项调优时，需要综合考虑这些相互影响的因素，避免顾此失彼。
  5. 版本兼容性：不同版本的PostgreSQL在配置项的名称、默认值以及功能实现上可能会有所不同。在进行调优时，要确保参考的文档和方法与所使用的PostgreSQL版本相匹配，以免出现错误的配置或达不到预期的优化效果。

通过深入理解PostgreSQL执行计划相关的配置项，并结合实际应用场景进行合理的调优，我们可以显著提升数据库的性能，为应用程序提供更高效的数据访问支持。同时，遵循上述注意事项，能够保证调优过程的稳健性和可持续性。