Re: Out of memory error in 8.1.0 Win32

So what's my next step? How do I track down what is causing this
problem?

-----Original Message-----
From: Qingqing Zhou [mailto:zhouqq@cs.toronto.edu]
Sent: Wednesday, June 21, 2006 11:01 PM
To: Relyea, Mike
Cc: pgsql-general@postgresql.org; Tom Lane
Subject: RE: [GENERAL] Out of memory error in 8.1.0 Win32

On Wed, 21 Jun 2006, Relyea, Mike wrote:

ExecutorState: 550339936 total in 123 blocks; 195003544 free (740135
chunks); 355336392 used
HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80
chunks); 290485792 used
TIDBitmap: 2088960 total in 8 blocks; 924720 free (27 chunks); 1164240
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

The same problem. ExecutorState uses much more memory than we expect --
but not sure where they are from :-(

Regards,
Qingqing

Hi,

I am consistently running into out-of-memory issues in 8.1.4 running on
RHEL3 and 8.0.5 on RHEL4. The logs show entries like this:

AggContext: -2130714624 total in 271 blocks; 9688 free (269 chunks); -2130724312 used
TupleHashTable: 893902872 total in 119 blocks; 1088688 free (449 chunks); 892814184 used

which looks mighty suspicious to me. :-; I can provide a self-contained
test case if anyone wants to look at it.

-- todd

Relyea, Mike wrote:

"Todd A. Cook" <tcook@blackducksoftware.com> writes:

I am consistently running into out-of-memory issues in 8.1.4 running on
RHEL3 and 8.0.5 on RHEL4. The logs show entries like this:

AggContext: -2130714624 total in 271 blocks; 9688 free (269 chunks); -2130724312 used
TupleHashTable: 893902872 total in 119 blocks; 1088688 free (449 chunks); 892814184 used

Misestimated hash aggregation, perhaps? What is the query and what does
EXPLAIN show for it? What have you got work_mem set to?

regards, tom lane

Tom Lane wrote:

Misestimated hash aggregation, perhaps? What is the query and what does
EXPLAIN show for it? What have you got work_mem set to?

oom_test=> \d oom_tab
Table "public.oom_tab"
Column | Type | Modifiers
--------+---------+-----------
val | integer |

oom_test=> explain select val,count(*) from oom_tab group by val;
QUERY PLAN
-------------------------------------------------------------------------
HashAggregate (cost=1163446.13..1163448.63 rows=200 width=4)
-> Seq Scan on oom_tab (cost=0.00..867748.42 rows=59139542 width=4)

The row estimitate for oom_tab is close to the actual value. Most of
the values are unique, however, so the result should have around 59M
rows too.

I've tried it with work_mem set to 32M, 512M, 1G, and 2G. It fails in
all cases, but it hits the failure point quicker with work_mem=32M.

-- todd

"Todd A. Cook" <tcook@blackducksoftware.com> writes:

oom_test=> explain select val,count(*) from oom_tab group by val;
QUERY PLAN
-------------------------------------------------------------------------
HashAggregate (cost=1163446.13..1163448.63 rows=200 width=4)
-> Seq Scan on oom_tab (cost=0.00..867748.42 rows=59139542 width=4)

The row estimitate for oom_tab is close to the actual value. Most of
the values are unique, however, so the result should have around 59M
rows too.

Well, that's the problem right there :-(. Have you ANALYZEd this table?
I think 200 is the default estimate for number of groups in the absence
of any ANALYZE stats, but it should surely not be that far off if it's
got real stats to play with.

If you need to make the query not fail without stats, you could set
enable_hashagg false, but I wouldn't recommend that as a production
choice (unless you set it just for this one query).

regards, tom lane

"Todd A. Cook" <tcook@blackducksoftware.com> writes:

"Todd A. Cook" <tcook@blackducksoftware.com> writes:

QUERY PLAN
-------------------------------------------------------------------------
HashAggregate (cost=1163446.13..1163448.63 rows=200 width=4)
-> Seq Scan on oom_tab (cost=0.00..867748.42 rows=59139542 width=4)

The row estimitate for oom_tab is close to the actual value. Most of
the values are unique, however, so the result should have around 59M
rows too.

ouch. it's estimating 200 distinct values. The estimates for distinct values
are known to be unreliable but they shouldn't be *that* bad. Do you have a few
hundred extremely common values and then a few million other values?

What does this say:

select * from pg_statistic where starelid = (select oid from pg_class where relname = 'oom_tab')

You may need to reanalyze and maybe increase the statistics target
(possibly by a lot). It may be interesting to compare the results of the above
query before and after analyzing too.

--
greg

Tom Lane wrote:

"Todd A. Cook" <tcook@blackducksoftware.com> writes:

oom_test=> explain select val,count(*) from oom_tab group by val;
QUERY PLAN
-------------------------------------------------------------------------
HashAggregate (cost=1163446.13..1163448.63 rows=200 width=4)
-> Seq Scan on oom_tab (cost=0.00..867748.42 rows=59139542 width=4)

The row estimitate for oom_tab is close to the actual value. Most of
the values are unique, however, so the result should have around 59M
rows too.

Well, that's the problem right there :-(. Have you ANALYZEd this table?

My production table and query are more complex. In the original, the
query above was in a sub-select; the work-around was to create a temp
table with the sub-query results, analyze it, and then do the larger
query based off of the temp table.

There have been off and on discussions on the pg lists about out of
memory issues (see http://archives.postgresql.org/pgsql-bugs/2006-03/msg00102.php).
I was just offering my test case as an example in case it might be of
any use in tracking those problems down. :)

-- todd

I've zipped the results of EXPLAIN INSERT INTO "tblSummary" SELECT *
FROM "qrySummary"; for my case. It's a zip file that I've renamed to
.txt in order to get around the attachment being blocked by certain mail
servers.

"Todd A. Cook" <tcook@blackducksoftware.com> writes:

Tom Lane wrote:

Well, that's the problem right there :-(. Have you ANALYZEd this table?

My production table and query are more complex. In the original, the
query above was in a sub-select; the work-around was to create a temp
table with the sub-query results, analyze it, and then do the larger
query based off of the temp table.

Hmm. One of the things that's on my TODO list is to make the planner
smarter about drilling down into sub-selects to extract statistics.
I think that's what's called for here, but your example has eliminated
all the interesting details. Can you show us the actual query, its
EXPLAIN plan, and the definitions of the tables/views involved?

regards, tom lane

"Relyea, Mike" <Mike.Relyea@xerox.com> writes:

I've zipped the results of EXPLAIN INSERT INTO "tblSummary" SELECT *
FROM "qrySummary"; for my case. It's a zip file that I've renamed to
.txt in order to get around the attachment being blocked by certain mail
servers.

Egad, what a mess :-(. By my count you have 89 hash joins, 24 sorts,
and 8 hash aggregations in there. In total these will feel authorized
to use 121 times work_mem. Since you've got work_mem set to 256 meg,
an out-of-memory condition doesn't seem that surprising. You need to
make work_mem drastically smaller for this query. Or else break it down
into multiple steps.

regards, tom lane

On Jun 22, 2006, at 2:23 PM, Tom Lane wrote:

"Relyea, Mike" <Mike.Relyea@xerox.com> writes:

I've zipped the results of EXPLAIN INSERT INTO "tblSummary" SELECT *
FROM "qrySummary"; for my case. It's a zip file that I've renamed to
.txt in order to get around the attachment being blocked by
certain mail
servers.

Egad, what a mess :-(. By my count you have 89 hash joins, 24 sorts,
and 8 hash aggregations in there. In total these will feel authorized
to use 121 times work_mem. Since you've got work_mem set to 256 meg,
an out-of-memory condition doesn't seem that surprising. You need to
make work_mem drastically smaller for this query. Or else break it
down
into multiple steps.

Except won't the sorts pull in all data from their underlying node
before proceeding, which should free the memory from those underlying
nodes? If so, it looks like it's not nearly as bad, only taking about
20x work_mem (which of course still isn't great...)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Tom Lane wrote:

Hmm. One of the things that's on my TODO list is to make the planner
smarter about drilling down into sub-selects to extract statistics.
I think that's what's called for here, but your example has eliminated
all the interesting details. Can you show us the actual query, its
EXPLAIN plan, and the definitions of the tables/views involved?

I've already altered the queries that ran into this problem. I'll
dig the old ones out of CVS and send them to you tomorrow.

-- todd

Thanks Jim and Tom. At least now I've got a direction to head in. I
think for now I'll probably reduce work_mem as a stop-gap measure to get
the query running again. This will buy me some time to redesign it.
I'll probably separate out each sub query and store the results in a
table (would a temp table be a good solution here?) before I pull it all
together with the final query.

Egad, what a mess :-(. By my count you have 89 hash joins, 24 sorts,
and 8 hash aggregations in there. In total these will feel authorized
to use 121 times work_mem. Since you've got work_mem set to 256 meg,
an out-of-memory condition doesn't seem that surprising. You need to
make work_mem drastically smaller for this query. Or else break it
down
into multiple steps.

Except won't the sorts pull in all data from their underlying node
before proceeding, which should free the memory from those underlying
nodes? If so, it looks like it's not nearly as bad, only taking about
20x work_mem (which of course still isn't great...)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Jun 22, 2006, at 4:02 PM, Relyea, Mike wrote:

Thanks Jim and Tom. At least now I've got a direction to head in. I
think for now I'll probably reduce work_mem as a stop-gap measure
to get
the query running again. This will buy me some time to redesign it.
I'll probably separate out each sub query and store the results in a
table (would a temp table be a good solution here?) before I pull
it all
together with the final query.

Yes, it would. It's also possible that you could structure the query
better, to reduce the amount of concurrent sorting/hashing going on.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Ah-hah. I made a table similar to yours (with a lot of dummy data) and
went trawling through the backend memory to try to see where the space
was going. I found two significant inefficiencies in HashAggregate's
space usage:

* It stores a "representative tuple" for each input group, containing
the grouping fields needed to identify the group. Or at least that's
the theory. What I saw in this example (HashAggregate over a direct
table SeqScan) is that what was getting stored was raw disk tuples
including *all* the table columns not only the needed ones. This is
doubtless because the optimization that skips a projection step when
not needed at a table-scan node is firing inappropriately. This was
only costing one extra integer field in my cut-down example, but it
might've accounted for significant overhead in your case, and in the
general case it could be horribly bad. Even if the projection were
being done properly, I think we'd be storing copies of the input
columns used to compute the aggregates, not only the grouping columns.
So there's probably an easy fix here that could be back-patched into
existing releases, and a tenser fix that will save more space.

* dynahash.c is allocating new hashtable entries 32 at a time, without
any awareness for the fact that palloc() rounds small requests up to the
next power-of-2 size. In the example I was looking at, it was asking
for 1280 bytes at a time, resulting in almost 40% of the space used
being completely wasted. This is pretty trivial to fix.

Aside from being just plain inefficient, neither of these effects are
being accounted for in the planner's estimate of space needed for a hash
aggregation, and thus they could be contributing to the problem of
underestimated table size leading to out-of-memory failures.

I'm taking off for the evening but will look into fixing these soon.

regards, tom lane