Heroku Postgres : Out Of Memory Error
I'm trying to run a heavy query in Heroku, using Heroku Postgres Standard0 plan.
The query runs fine locally, on a clone of my remote DB. My local Postgres config has a work_mem of 4mb, the default value.
The database is quite big : almost 9 GB.
Here is the query :
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
I tried to check whether making separate requests would work, and discovered the only failing query was the first one :
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
Whenever I try to run it, I get this error in the heroku logs :
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
Having read the related questions on SO, I assume my query is using too much memory. However, why would it work then locally, considering my low work_mem ? I've also noticed that the query doesn't even appear in Heroku's Database metrics section, and that my error logs don't contain the usual Heroku error codes, like 'H12'.
A more specific question is how could I get around this problem, without upgrading to a higher plan ?
Thank you for your help.
Edit :
Here is a line from the logs returned when I add a 'EXPLAIN (ANALYZE,BUFFERS)' clause, as suggested by Richard Huxton.
[DATABASE] temporary file: path "pg_tblspc/16386/PG_9.5_201510051/pgsql_tmp/pgsql_tmp30904.5", size 1073741824
The temporary file created is around 1GB, the cache limit on the Standard0 plan. As when I narrow the results the query works, I strongly suspect that I hit the plan limit.
Edit 2 :
I had indeed hit Heroku's memory limit. I consequently moved to Digital Ocean & Dokku to be able to tweak postgres and node as needed.
链接地址: http://www.djcxy.com/p/79070.html上一篇: 了解postgres缓存