我正在 postgresql 表(在 RDS 实例上)上运行 select 语句,以便对 200 万行中的值进行求和。select 语句有大约 100 个参数(主要是我们想要求和的不同国家/地区)。
当我从应用程序运行此查询一次时,它将在大约 500 毫秒内执行。然而,当我连续执行几次时,查询延迟会在运行 8 或 9 次后突然增加10-20 倍,达到大约 5 到 10 秒。(这些测量是来自我启用的 RDS 日志的服务器端log_min_duration_statement
)。
什么可能导致执行时间的跳跃?-- 我预计查询在初次运行时会变慢,但我惊讶地发现在几次连续运行后延迟会增加。
一些关键的观察结果:
- 连接池:确切的连接池似乎并不重要。无论我使用HikariCP还是C3P0,问题仍然存在
- 单个连接:如果我将连接池配置为只有一个连接,则不会发生此问题(因此应用程序逻辑没有错误)
- PSQL:如果我随后使用执行查询来对查询进行基准测试,也不会发生此问题
psql
。 - 查询参数:当我将查询参数的数量减少到 ~60 时,我没有看到任何问题。只有当我查询 90 多个地区时,我才会遇到这种行为。
- 池重置:当我重新启动应用程序(并因此重置连接池)时,查询会恢复到快速状态(0.5 秒),进行另外 8-9 个查询,然后再次变慢(5 秒)。
我尝试寻找常见的可疑点,例如 RDS IOPS 突发限制、数据库负载、内存消耗、可用连接,但在这方面看起来没有任何可疑之处。
我还确保查询正在使用的索引和从表返回的行被缓存。
我正在运行的查询具有以下形状。我截断了 90 个国家/地区 ID 中的一些:
SELECT d.primary_title_no,
SUM(d.cume) AS lifetime
FROM schema.my_data AS d
WHERE d.currency = 'USD'
AND d.date >= '2020-01-01'::date + (7 * (d.week - 1))
AND d.date < '2021-01-01'::date + (7 * (d.week - 1))
AND d.is_lifetime = TRUE
AND d.ter_id IN ('AE','AM', ... (100 odd values) ...,'WA','ZA')
GROUP BY d.primary_title_no
ORDER BY lifetime DESC
LIMIT 250;
重要的是,只有当查询具有 90 多个国家/地区 ID 时,才会出现此问题。我可以毫无问题地在 50 个国家/地区运行它。
explain analyze
作为参考,以下是执行时间较快的查询的输出:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit (cost=1218692.98..1218693.23 rows=100 width=12) (actual time=369.146..369.167 rows=100 loops=1) |
| -> Sort (cost=1218692.98..1218697.34 rows=1742 width=12) (actual time=369.145..369.154 rows=100 loops=1) |
| Sort Key: (sum(cume)) DESC |
| Sort Method: top-N heapsort Memory: 33kB |
| -> GroupAggregate (cost=1218595.34..1218626.41 rows=1742 width=12) (actual time=366.250..368.653 rows=1959 loops=1) |
| Group Key: primary_title_no |
| -> Sort (cost=1218595.34..1218599.89 rows=1820 width=12) (actual time=366.240..366.844 rows=5739 loops=1) |
| Sort Key: primary_title_no |
| Sort Method: quicksort Memory: 462kB |
| -> Index Scan using idx_dailies_currency_ter_id_primary_title_no_lifetime on dailies d (cost=0.43..1218496.79 rows=1820 width=12) (actual time=1.093..364.406 rows=5739 loops=1) |
| Index Cond: (((currency)::text = 'USD'::text) AND ((ter_id)::text = ANY ('{AE,AM,AR,AT,AU,AZ,BA,BE,BG,BH,BO,BR,BY,CH,CL,CN,CO,CR,CU,CZ,DE,DK,DO,EC,EE,EG,ES,FI,FR,GE,GR,GT,HK,HN,HR,HU,IL,IN,IQ,IS,IT,JP,KG,KR,KW,KZ,LB,LT,LU,LV,MD,MX,MY,MZ,NI,NL,NO,NZ,OM,PA,PE,PH,PL,PT,PY,QA,RO,RS,RU,SA,SE,SG,SI,SK,SV,TH,TJ,TM,TR,TT,TW,UK,UP,UY,UZ,WA,ZA}'::text[]))) |
| Filter: (((date - (7 * (week - 1))) >= '2020-01-01'::date) AND ((date - (7 * (week - 1))) < '2020-06-01'::date)) |
| Rows Removed by Filter: 314494 |
| Planning Time: 0.613 ms |
| Execution Time: 369.213 ms |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
对于查询执行缓慢的情况,explain analyze
输出是相同的,但最里面的Index Scan
步骤花费了 20 倍的计算量。
如果有人对我如何解决这个问题有任何指示,我会洗耳恭听。
在进一步研究了参数问题后,我发现以下stackoverflow 问题表明:
在Postgresql的文档中,plan_cache_mode的记录如下:
当我使用 select 语句在事务中执行以下语句时,它解决了问题:
更新:我认为这个问题是与设置
work_mem
为 4mb 相关的。我的假设是通用计划占用更多内存,导致大量交换。将此限制提高到 16mb 也可以解决该问题: