Heroku Postgres：内存不足错误

2018-06-28 07:16:06

我试图用Heroku Postgres Standard0计划在Heroku中运行一个沉重的查询。

查询在我的远程数据库的克隆上本地运行良好。我的本地Postgres配置有一个4mb的默认值work_mem。

该数据库相当大：几乎9 GB。

这是查询：

WITH 
AVG AS (
SELECT application_id,country_id,collection_id,category_id,COUNT(id) AS number_appearances,AVG(application_history.ranking) AS avg_ranking 
FROM application_history
WHERE application_history.date::date BETWEEN date (CURRENT_DATE - INTERVAL '1 month') AND CURRENT_DATE
GROUP BY application_id,country_id,collection_id,category_id
ORDER BY application_id ASC
),
OLD AS (
SELECT application_id AS appIdOld, country_id,collection_id,category_id,ranking AS old_ranking
FROM application_history
WHERE date::date = date_trunc('day', NOW() - interval '1 month')
),
NEW AS(
SELECT application_id AS appIdNew, country_id,collection_id,category_id,ranking AS todays_ranking 
FROM application_history
WHERE date::date = date_trunc('day',NOW())
)
SELECT AVG.application_id,AVG.country_id,AVG.collection_id,AVG.category_id,number_appearances,avg_ranking,old_ranking,todays_ranking, 
SUM(old_ranking-avg_ranking) AS delta_ranking
FROM AVG
JOIN OLD 
ON AVG.application_id = OLD.appIdOld 
AND AVG.country_id = OLD.country_id
AND AVG.collection_id = OLD.collection_id 
AND AVG.category_id = OLD.category_id
JOIN NEW
ON AVG.application_id = NEW.appIdNew 
AND AVG.country_id = NEW.country_id
AND AVG.collection_id = NEW.collection_id 
AND AVG.category_id = NEW.category_id
GROUP BY avg.application_id,avg.country_id,avg.collection_id,avg.category_id,avg.number_appearances,avg.avg_ranking,old.old_ranking,
new.todays_ranking

我试图检查是否有单独的请求会起作用，并发现唯一失败的查询是第一个：

SELECT application_id,country_id,collection_id,category_id,COUNT(id) AS number_appearances,AVG(application_history.ranking) AS avg_ranking 
FROM application_history
WHERE application_history.date::date BETWEEN date (CURRENT_DATE - INTERVAL '1 month') AND CURRENT_DATE
GROUP BY application_id,country_id,collection_id,category_id
ORDER BY application_id ASC

每当我尝试运行它时，我都会在heroku日志中看到这个错误：

2016-04-29T12:25:13Z app[postgres.15]: [DATABASE] checkpoint starting: time
2016-04-29T12:25:13Z app[postgres.15]: [DATABASE] checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 10 recycled; write=0.001 s, sync=0.000 s, total=0.026 s; sync files=0, longest=0.000 s, average=0.000 s; distance=163840 kB, estimate=225537 kB
2016-04-29T12:24:47+00:00 app[heroku-postgres]: source=DATABASE sample#current_transaction=106252061.0 sample#db_size=9551544492.0bytes sample#tables=8 sample#active-connections=3 sample#waiting-connections=0 sample#index-cache-hit-rate=0.99985 sample#table-cache-hit-rate=0.94637 sample#load-avg-1m=0.38 sample#load-avg-5m=0.49 sample#load-avg-15m=0.26 sample#read-iops=1809.2 sample#write-iops=0 sample#memory-total=3786352.0kB sample#memory-free=142060kB sample#memory-cached=3482828.0kB sample#memory-postgres=21940kB
2016-04-29T12:26:23+00:00 app[heroku-postgres]: source=DATABASE sample#current_transaction=106252061.0 sample#db_size=9551544492.0bytes sample#tables=8 sample#active-connections=4 sample#waiting-connections=0 sample#index-cache-hit-rate=0.99985 sample#table-cache-hit-rate=0.94637 sample#load-avg-1m=0.515 sample#load-avg-5m=0.495 sample#load-avg-15m=0.27 sample#read-iops=1572.7 sample#write-iops=0.010417 sample#memory-total=3786352.0kB sample#memory-free=401448kB sample#memory-cached=2274420.0kB sample#memory-postgres=972772kB
2016-04-29T12:27:39Z app[postgres.30904]: [DATABASE] out of memory
Detail: Failed on request of size 40.
Query: SELECT application_id,country_id,collection_id,category_id,COUNT(id) AS number_appearances,AVG(application_history.ranking) AS avg_ranking 
FROM application_history
WHERE application_history.date::date BETWEEN date (CURRENT_DATE - INTERVAL '1 month') AND CURRENT_DATE
GROUP BY application_id,country_id,collection_id,category_id
ORDER BY application_id ASC

在阅读了关于SO的相关问题之后，我假设我的查询使用了太多内存。然而，考虑到我的低工作量，为什么它会在当地工作？我也注意到查询甚至不出现在Heroku的数据库指标部分，并且我的错误日志不包含常见的Heroku错误代码，如'H12'。

一个更具体的问题是我怎么能解决这个问题，而不升级到更高的计划？

感谢您的帮助。

编辑：

按照Richard Huxton的建议，当我添加'EXPLAIN（ANALYZE，BUFFERS）'子句时，这是从日志中返回的一行。

[DATABASE] temporary file: path "pg_tblspc/16386/PG_9.5_201510051/pgsql_tmp/pgsql_tmp30904.5", size 1073741824

创建的临时文件大约为1GB，是Standard0计划的缓存限制。当我缩小查询结果的结果时，我强烈怀疑我达到了计划限制。

编辑2：

我的确击中了Heroku的内存限制。随后，我转向Digital Ocean＆Dokku，以便根据需要调整postgres和节点。

链接地址: http://www.djcxy.com/p/79069.html

上一篇: Heroku Postgres : Out Of Memory Error

下一篇: Copy staging database from heroku to nitrous development