Out of memory error in 8.1.0 Win32
I've just started receiving an out of memory error with my most complex
query. It has been running fine for the past 9 months. It's a snapshot
materialized view that I update every night using the functions from
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
The error I'm receiving when I try to execute SELECT
refresh_matview('"tblSummary"'); is
ERROR: out of memory
DETAIL: Failed on request of size 344.
CONTEXT: SQL statement "INSERT INTO "tblSummary" SELECT * FROM
"qrySummary""
PL/pgSQL function "refresh_matview" line 13 at execute statement
I'd post the results from EXPLAIN INSERT INTO "tblSummary" SELECT * FROM
"qrySummary"; but it's just over 700 lines
qrySummary is a view based on a number of other views and a few tables.
It is defined as
========================================================================
=
SELECT "qryPQSetPages"."MachineID", "qryPQSetPages"."PrintCopyID",
"tblColors"."Color", "tblSuppliers"."Supplier", "tblZones"."Zone",
"tblPrinters"."Name" AS "Printer", "qryYield"."Yield",
"qryPrintCopyComments"."PrintCopyComments",
avg("qryPhotopicDensity"."PhotopicDensity") AS "AvgPhotopicDensity",
avg("qryCRMS"."CRMS_Value") AS "AvgCRMS", avg("qryLStar"."AvgOfLstar")
AS "AvgLstar", avg("qryAStar"."AvgOfAstar") AS "AvgAstar",
avg("qryBStar"."AvgOfBstar") AS "AvgBstar", avg("qryABRatio"."ABRatio")
AS "AvgABRatio", max("qryGhosting"."MaxGhosting") AS "MaxGhost",
max("qryBackground"."Background_DeltaE") AS "MaxBkgdDeltaE",
avg("qryMottle_NMF"."Mottle_NMF") AS "AvgMottle_NMF",
max("qryVBS_Horizontal"."VBS_Horizontal") AS "MaxVBS_H",
max("qryVBS_Vertical"."VBS_Vertical") AS "MaxVBS_V",
max("qryReload"."DeltaE") AS "MaxReloadDeltaE"
FROM "tblColors"
JOIN ("qryPQSetPages"
LEFT JOIN "qryCRMS" ON "qryPQSetPages"."ColorID" =
"qryCRMS"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryCRMS"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryCRMS"."MachineID"
LEFT JOIN "qryGhosting" ON "qryPQSetPages"."ColorID" =
"qryGhosting"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryGhosting"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryGhosting"."MachineID"
LEFT JOIN "qryVBS_Horizontal" ON "qryPQSetPages"."ColorID" =
"qryVBS_Horizontal"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryVBS_Horizontal"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryVBS_Horizontal"."MachineID"
LEFT JOIN "qryBStar" ON "qryPQSetPages"."ColorID" =
"qryBStar"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryBStar"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryBStar"."MachineID"
LEFT JOIN "qryBackground" ON "qryPQSetPages"."ColorID" =
"qryBackground"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryBackground"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryBackground"."MachineID"
LEFT JOIN "qryReload" ON "qryPQSetPages"."ColorID" =
"qryReload"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryReload"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryReload"."MachineID"
LEFT JOIN "qryMottle_NMF" ON "qryPQSetPages"."ColorID" =
"qryMottle_NMF"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryMottle_NMF"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryMottle_NMF"."MachineID"
LEFT JOIN "qryAStar" ON "qryPQSetPages"."ColorID" =
"qryAStar"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryAStar"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryAStar"."MachineID"
LEFT JOIN "qryABRatio" ON "qryPQSetPages"."ColorID" =
"qryABRatio"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryABRatio"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryABRatio"."MachineID"
LEFT JOIN "qryLStar" ON "qryPQSetPages"."ColorID" =
"qryLStar"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryLStar"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryLStar"."MachineID"
LEFT JOIN "qryYield" ON "qryPQSetPages"."ColorID" =
"qryYield"."ColorID" AND "qryPQSetPages"."MachineID" =
"qryYield"."MachineID"
LEFT JOIN "qryPrintCopyComments" ON "qryPQSetPages"."ColorID" =
"qryPrintCopyComments"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryPrintCopyComments"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryPrintCopyComments"."MachineID"
LEFT JOIN "qryVBS_Vertical" ON "qryPQSetPages"."ColorID" =
"qryVBS_Vertical"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryVBS_Vertical"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryVBS_Vertical"."MachineID"
LEFT JOIN "qryPhotopicDensity" ON "qryPQSetPages"."ColorID" =
"qryPhotopicDensity"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryPhotopicDensity"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryPhotopicDensity"."MachineID") ON "tblColors"."ColorID" =
"qryPQSetPages"."ColorID"
LEFT JOIN "tblZones" ON "qryYield"."ZoneID" = "tblZones"."ZoneID"
LEFT JOIN "tblPrinters" ON "qryYield"."PrinterID" =
"tblPrinters"."PrinterID"
LEFT JOIN "tblSuppliers" ON "qryYield"."SupplierID" =
"tblSuppliers"."SupplierID"
GROUP BY "qryPQSetPages"."MachineID", "qryPQSetPages"."PrintCopyID",
"tblColors"."Color", "tblSuppliers"."Supplier", "tblZones"."Zone",
"tblPrinters"."Name", "qryYield"."Yield",
"qryPrintCopyComments"."PrintCopyComments"
ORDER BY "qryPQSetPages"."MachineID", "qryPQSetPages"."PrintCopyID",
"tblColors"."Color";
========================================================================
=
For the sake of brevity, I'll only list one of the other views to give
you a rough idea of what they look like. Most of the other views look
similar to the one in the first left join, qryCRMS
========================================================================
=
SELECT "PrintSamples"."MachineID", "PrintSamples"."PrintCopyID",
"tblColors"."ColorID", "ParameterValues"."ParameterValue" AS
"CRMS_Value"
FROM "AnalysisModules"
JOIN ("tblColors"
JOIN ("tblTPNamesAndColors"
JOIN "PrintSamples" ON "tblTPNamesAndColors"."TestPatternName"::text
= "PrintSamples"."TestPatternName"::text) ON "tblColors"."ColorID" =
"tblTPNamesAndColors"."ColorID"
JOIN ("DigitalImages"
JOIN "PrintSampleAnalyses" ON "DigitalImages"."ImageID" =
"PrintSampleAnalyses"."ImageID"
JOIN ("ParameterNames"
JOIN ("Measurements"
JOIN "ParameterValues" ON "Measurements"."MeasurementID" =
"ParameterValues"."MeasurementID") ON "ParameterNames"."ParameterID" =
"ParameterValues"."ParameterID") ON "PrintSampleAnalyses"."psaID" =
"Measurements"."psaID") ON "PrintSamples"."PrintSampleID" =
"DigitalImages"."PrintSampleID") ON "AnalysisModules"."MetricID" =
"Measurements"."MetricID"
WHERE "tblTPNamesAndColors"."ColorID" = 3 AND
"tblTPNamesAndColors"."TestPatternName"::text ~~ '%TP8%'::text AND
("Measurements"."msmTag"::text = 'DAC'::text OR
"Measurements"."msmTag"::text = '<tag>'::text) AND
"AnalysisModules"."AnalysisModuleName"::text = 'MacroUniformity'::text
AND "ParameterNames"."ParameterName"::text = 'CRMS'::text;
========================================================================
=
I'm running this on XP Pro with a P4 3.2ghz, 1.5G memory and a single
SATA Raptor.
My conf settings are:
shared_buffers = 12288
work_mem = 262144
maintenance_work_mem = 131072
effective_cache_size = 10000
random_page_cost = 2.0
Postgres isn't the only app running on this computer that requires
significant resources. I've got another proprietary app on there that
is very CPU and memory intensive. This other app should be allocated
resources before postgres.
Autovacuum is running with the default settings, and I also do a vacuum
full analyze nightly (probably overkill for me, but the server doesn't
see any other activity at night anyway)
How should I resolve this problem? Rewrite qrySummary to be more
efficient (not a job I look forward to)? Get a better server/upgrade
the memory in the one I have? Adjust some settings in my conf file?
Some other solution I haven't thought of? I'm looking for a point in
the right direction.
Mike
""Relyea, Mike"" <Mike.Relyea@xerox.com> wrote
I've just started receiving an out of memory error with my most complex
query.
Can you post the memory usage log after the error the server reports?
Regards,
Qingqing
Is this what you're looking for? If you need something different, just
specify. Remember this is WinXP. This is all of the memory output from
perfmon measured every 15 seconds. The query runs for about 20-30
minutes before the error message comes up. This shows the entire time
it took to run the query and one measurement after the error came up.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Qingqing Zhou
Sent: Wednesday, June 14, 2006 11:13 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Out of memory error in 8.1.0 Win32
""Relyea, Mike"" <Mike.Relyea@xerox.com> wrote
I've just started receiving an out of memory error with my most
complex
query.
Can you post the memory usage log after the error the server reports?
Regards,
Qingqing
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Attachments:
""Relyea, Mike"" <Mike.Relyea@xerox.com> wrote
Is this what you're looking for?
No. I mean per-context memory usage output like this in your log file:
2006-06-08 16:33:09 LOG: autovacuum: processing database "ibox"
TopMemoryContext: 84400 total in 7 blocks; 12696 free (22 chunks); 71704
used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
TopTransactionContext: 8192 total in 1 blocks; 7552 free (10 chunks); 640
used
MessageContext: 8192 total in 1 blocks; 7368 free (1 chunks); 824 used
smgr relation table: 8192 total in 1 blocks; 3904 free (0 chunks); 4288 used
...
Regards,
Qingqing
"Qingqing Zhou" <zhouqq@cs.toronto.edu> wrote
ExecutorState: 550339936 total in 123 blocks; 195005920 free (740144
chunks); 355334016 used
...
HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80 chunks);
290485792 used
TIDBitmap: 2088960 total in 8 blocks; 1012120 free (27 chunks); 1076840
used
...
HashBatchContext: 2080768 total in 7 blocks; 346888 free (11 chunks);
1733880 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 2080768 total in 7 blocks; 325264 free (11 chunks);
1755504 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 41877664 total in 11 blocks; 4845648 free (15 chunks);
37032016 used
TIDBitmap: 2088960 total in 8 blocks; 1012120 free (27 chunks); 1076840
used
[repeated sequenced like this ...]
ERROR: out of memory
DETAIL: Failed on request of size 344.
CONTEXT: SQL statement "INSERT INTO "tblSummary" SELECT * FROM
"qrySummary""
PL/pgSQL function "refresh_matview" line 13 at execute statement
LOG: autovacuum: processing database "iqdb"
LOG: autovacuum: processing database "template1"LOG: autovacuum:
processing database "postgres"
Er, looks like a huge hash-join but not sure if it is a memory leak, Tom?
Regards,
Qingqing
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
ExecutorState: 550339936 total in 123 blocks; 195005920 free (740144
chunks); 355334016 used
...
HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80 chunks);
290485792 used
Er, looks like a huge hash-join but not sure if it is a memory leak, Tom?
A hash join could suck a lot of memory in HashBatchContext, but what
seems wrong about your report is all that memory in ExecutorState.
Please see if you can track where that went.
BTW, what was work_mem set to in this example? In theory the
HashBatchContext shouldn't get much bigger than work_mem.
regards, tom lane
work_mem = 262144
I'm still trying to figure out how to track the memory usage in
ExecutorState
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Sunday, June 18, 2006 11:13 PM
To: Qingqing Zhou
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Out of memory error in 8.1.0 Win32
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
ExecutorState: 550339936 total in 123 blocks; 195005920 free (740144
chunks); 355334016 used
...
HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80
chunks);
290485792 used
Er, looks like a huge hash-join but not sure if it is a memory leak,
Tom?
A hash join could suck a lot of memory in HashBatchContext, but what
seems wrong about your report is all that memory in ExecutorState.
Please see if you can track where that went.
BTW, what was work_mem set to in this example? In theory the
HashBatchContext shouldn't get much bigger than work_mem.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?