pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgres backend using huge amounts of ram
4+ messages / 3 participants
[nested] [flat]

* Postgres backend using huge amounts of ram
@ 2004-11-25 20:35 Gary Doades <gpd@gpdnet.co.uk>
  2004-11-26 09:12 ` Re: Postgres backend using huge amounts of ram Richard Huxton <dev@archonet.com>
  2004-11-26 19:25 ` Re: Postgres backend using huge amounts of ram Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Gary Doades @ 2004-11-25 20:35 UTC (permalink / raw)
  To: pgsql-performance

How much RAM can a single postgres backend use?

I've just loaded a moderately sized dataset into postgres and was
applying RI constraints to the tables (using pgadmin on windows). Part
way though I noticed the (single) postgres backend had shot up to using
300+ MB of my RAM!

The two tables are:

create table reqt_dates
(
	reqt_date_id	serial,
	reqt_id     	integer not null,
	reqt_date	date not null,
	primary key (reqt_date_id)
) without oids;

and

create table booking_plan
(
	booking_plan_id		serial,
	reqt_date_id		integer not null,
	booking_id     		integer not null,
	booking_date		date not null,
	datetime_from		timestamp not null,
	datetime_to		timestamp not null,
	primary key (booking_plan_id)
) without oids;

and I was was trying to do:

alter table booking_plan add
	 foreign key
	(
		reqt_date_id
	) references reqt_dates (
		reqt_date_id
	) on delete cascade;

Since I can't get an explain of what the alter table was doing I used this:

select count(*) from booking_plan,reqt_dates where
booking_plan.reqt_date_id = reqt_dates.reqt_date_id

and sure enough this query caused the backend to use 300M RAM. The plan
for this was:

QUERY PLAN
Aggregate  (cost=37.00..37.00 rows=1 width=0) (actual
time=123968.000..123968.000 rows=1 loops=1)
   ->  Hash Join  (cost=15.50..36.50 rows=1000 width=0) (actual
time=10205.000..120683.000 rows=1657709 loops=1)
         Hash Cond: ("outer".reqt_date_id = "inner".reqt_date_id)
         ->  Seq Scan on booking_plan  (cost=0.00..15.00 rows=1000
width=4) (actual time=10.000..4264.000 rows=1657709 loops=1)
         ->  Hash  (cost=15.00..15.00 rows=1000 width=4) (actual
time=10195.000..10195.000 rows=0 loops=1)
               ->  Seq Scan on reqt_dates  (cost=0.00..15.00 rows=1000
width=4) (actual time=0.000..6607.000 rows=2142184 loops=1)
Total runtime: 124068.000 ms

I then analysed the database. Note, there are no indexes at this stage
except the primary keys.

the same query then gave:
QUERY PLAN
Aggregate  (cost=107213.17..107213.17 rows=1 width=0) (actual
time=57002.000..57002.000 rows=1 loops=1)
   ->  Hash Join  (cost=35887.01..106384.32 rows=1657709 width=0)
(actual time=9774.000..54046.000 rows=1657709 loops=1)
         Hash Cond: ("outer".reqt_date_id = "inner".reqt_date_id)
         ->  Seq Scan on booking_plan  (cost=0.00..22103.55 rows=1657709
width=4) (actual time=10.000..19648.000 rows=1657709 loops=1)
         ->  Hash  (cost=24355.92..24355.92 rows=2142184 width=4)
(actual time=9674.000..9674.000 rows=0 loops=1)
               ->  Seq Scan on reqt_dates  (cost=0.00..24355.92
rows=2142184 width=4) (actual time=0.000..4699.000 rows=2142184 loops=1)
Total runtime: 57002.000 ms

This is the same set of hash joins, BUT the backend only used 30M of
private RAM.

Platform is Windows XP, Postgres 8.0 beta 5

shared_buffers = 4000
work_mem = 8192

Any explanations?

Thanks,
Gary.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Postgres backend using huge amounts of ram
  2004-11-25 20:35 Postgres backend using huge amounts of ram Gary Doades <gpd@gpdnet.co.uk>
@ 2004-11-26 09:12 ` Richard Huxton <dev@archonet.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Richard Huxton @ 2004-11-26 09:12 UTC (permalink / raw)
  To: Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance

Gary Doades wrote:
> How much RAM can a single postgres backend use?
> 
> I've just loaded a moderately sized dataset into postgres and was
> applying RI constraints to the tables (using pgadmin on windows). Part
> way though I noticed the (single) postgres backend had shot up to using
> 300+ MB of my RAM!

Oops - guess that's why they call it a Beta. My first guess was a queue 
of pending foreign-key checks or triggers etc. but then you go on to say...

> Since I can't get an explain of what the alter table was doing I used this:
> 
> select count(*) from booking_plan,reqt_dates where
> booking_plan.reqt_date_id = reqt_dates.reqt_date_id
> 
> and sure enough this query caused the backend to use 300M RAM. The plan
> for this was:
[snip]
> I then analysed the database. Note, there are no indexes at this stage
> except the primary keys.
> 
> the same query then gave:
[snip]

> This is the same set of hash joins, BUT the backend only used 30M of
> private RAM.

I'm guessing in the first case that the default estimate of 1000 rows in 
a table means PG chooses to do the join in RAM. Once it knows there are 
a lot of rows it can tell not to do so.

However, I thought PG was supposed to spill to disk when the memory 
required exceeded config-file limits. If you could reproduce a simple 
test case I'm sure someone would be interested in squashing this bug.

-- 
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Postgres backend using huge amounts of ram
  2004-11-25 20:35 Postgres backend using huge amounts of ram Gary Doades <gpd@gpdnet.co.uk>
@ 2004-11-26 19:25 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-11-26 19:42   ` Re: Postgres backend using huge amounts of ram Gary Doades <gpd@gpdnet.co.uk>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2004-11-26 19:25 UTC (permalink / raw)
  To: Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance

Gary Doades <gpd@gpdnet.co.uk> writes:
> I've just loaded a moderately sized dataset into postgres and was
> applying RI constraints to the tables (using pgadmin on windows). Part
> way though I noticed the (single) postgres backend had shot up to using
> 300+ MB of my RAM!

> Since I can't get an explain of what the alter table was doing I used this:

[ looks in code... ]  The test query for an ALTER ADD FOREIGN KEY looks
like

	 	SELECT fk.keycols FROM ONLY relname fk
	 	 LEFT OUTER JOIN ONLY pkrelname pk
	 	 ON (pk.pkkeycol1=fk.keycol1 [AND ...])
	 	 WHERE pk.pkkeycol1 IS NULL AND
	 	 (fk.keycol1 IS NOT NULL [AND ...])

It's also worth noting that work_mem is temporarily set to
maintenance_work_mem, which you didn't tell us the value of:

	/*
	 * Temporarily increase work_mem so that the check query can be
	 * executed more efficiently.  It seems okay to do this because the
	 * query is simple enough to not use a multiple of work_mem, and one
	 * typically would not have many large foreign-key validations
	 * happening concurrently.	So this seems to meet the criteria for
	 * being considered a "maintenance" operation, and accordingly we use
	 * maintenance_work_mem.
	 */

> I then analysed the database. ...
> This is the same set of hash joins, BUT the backend only used 30M of
> private RAM.

My recollection is that hash join chooses hash table partitions partly
on the basis of the estimated number of input rows.  Since the estimate
was way off, the actual table size got out of hand a bit :-(

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Postgres backend using huge amounts of ram
  2004-11-25 20:35 Postgres backend using huge amounts of ram Gary Doades <gpd@gpdnet.co.uk>
  2004-11-26 19:25 ` Re: Postgres backend using huge amounts of ram Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-11-26 19:42   ` Gary Doades <gpd@gpdnet.co.uk>
  0 siblings, 0 replies; 4+ messages in thread

From: Gary Doades @ 2004-11-26 19:42 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom Lane wrote:
> 
> It's also worth noting that work_mem is temporarily set to
> maintenance_work_mem, which you didn't tell us the value of:
> 
It's left at the default. (16384).

This would be OK if that is all it used for this type of thing.

> 
> 
> My recollection is that hash join chooses hash table partitions partly
> on the basis of the estimated number of input rows.  Since the estimate
> was way off, the actual table size got out of hand a bit :-(

A bit!!

The really worrying bit is that a normal (ish) query also exhibited the 
same behaviour. I'm a bit worried that if the stats get a bit out of 
date so that the estimate is off, as in this case, a few backends trying 
to get this much RAM will see the server grind to a halt.

Is this a fixable bug? It seems a fairly high priority, makes the server 
go away, type bug to me.

If you need the test data, I could zip the two tables up and send them 
somewhere....

Thanks,
Gary.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-11-26 19:42 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-25 20:35 Postgres backend using huge amounts of ram Gary Doades <gpd@gpdnet.co.uk>
2004-11-26 09:12 ` Richard Huxton <dev@archonet.com>
2004-11-26 19:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-26 19:42   ` Gary Doades <gpd@gpdnet.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox