capturing/viewing sort_mem utilization on a per query basis

[Lonni J Friedman]

Greetings,
I've got a pesky J2EE based app that is using PostgreSQL-7.4.x on the
backend. Lately i've been getting a disturbing large number of
PostgreSQL out of memory exceptions on the J2EE side when running
queries that have some huge joins in them. I've tried increasing the
sort_mem value for the DB , but that hasn't had any noticable impact.
Rather than fumbling blindly in the dark, i'd like to get a better
idea of how much memory this query really needs.

Is there a way for me to capture that in the log?

Note, i'm not the person who wrote this app or the query, i'm just the
guy responsible for the DB. I've already castigated the folks who
wrote this resource hog.

thanks,
Lonni

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~
L. Friedman [email]netllama@gmail.com[/email]
LlamaLand [url]http://netllama.linux-sxs.org[/url]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Lonni J Friedman]

On Wed, 02 Feb 2005 12:58:49 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Lonni J Friedman <netllama@gmail.com> writes:

> > On Wed, 02 Feb 2005 12:13:59 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> >> Hmm, looks like a hash join ran out of memory. What PG version is this
> >> again, and what do you have sort_mem set to? Can you show an EXPLAIN
> >> for the query that is failing like this?

>

> > I've attached the explain for the query that is blowing up.

>
> One of the three Hash steps must be absorbing a whole lot more rows than
> the planner is expecting, but all of them look like fairly
> straightforward estimation situations:
>
> -> Hash (cost=108.96..108.96 rows=28 width=24)
> -> Index Scan using mntr_subscr_usrevt on mntr_subscription sfmain_monitoringsubscriptio0 (cost=0.00..108.96 rows=28 width=24)
> Index Cond: (((user_id)::text = 'user1187'::text) AND ((event_operation)::text = 'update'::text))
>
> -> Hash (cost=701.44..701.44 rows=34444 width=24)
> -> Seq Scan on field_value tracker_artifact_group0 (cost=0.00..701.44 rows=34444 width=24)
>
> -> Hash (cost=5.74..5.74 rows=1 width=80)
> -> Index Scan using project_path on project tracker_artifact_extension_f1 (cost=0.00..5.74 rows=1 width=80)
> Index Cond: (("path")::text = 'projects.meeting_broker_v3'::text)
> Filter: ((("path")::text = 'projects.meeting_broker_v3'::text) OR (("path")::text ~~ 'projects.meeting_broker_v3.%'::text))
>
> Perhaps one of these tables hasn't been vacuumed/analyzed and is way

I'm doing a 'vacuumdb -a -z' every 15 minutes, and a full vacuum once/day.

> bigger than the planner thinks? Can you check into how many rows
> actually meet the identified conditions?

I'm afraid i'm not clear on what i'm supposed to be checking here.
Which conditions should I be looking at? thanks.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~
L. Friedman [email]netllama@gmail.com[/email]
LlamaLand [url]http://netllama.linux-sxs.org[/url]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Lonni J Friedman <netllama@gmail.com> writes:

> OK, i think i see what you're requesting. How's this:

What about field_value, does it in fact have circa 34444 rows?

These tables are surely nowhere near big enough to bloat a hash table to
upwards of 2Gb, as your log shows happened. I'm thinking that you must
have found some sort of bug, or memory leak maybe. Is there a way I can
get access to your data set to trace through the behavior with a debugger?

(We should probably take the discussion offlist at this point.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend