From: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Markus Wollny <Markus(dot)Wollny(at)computec(dot)de>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: One source of constant annoyance identified |
Date: | 2002-07-03 08:28:48 |
Message-ID: | Pine.LNX.4.21.0207030921470.2828-100000@ponder.fairway2k.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 2 Jul 2002, Tom Lane wrote:
> "Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de> writes:
> > Sorry I took so long - I attached the schema as asked.
>
> Thanks. But I'm still unable to reproduce the memory bloat you see on
> SELECTs. This seems very peculiar. You said you were running SuSE 7.3
> --- how recent is that? Which glibc version is it running? (I've been
> reduced to speculating about memory leakage inside libc, which is a
> pretty long shot but...)
>
> > So used the already implemented trigger to
> > execute the fti-function:
>
> > update ct_com_board_message
> > set state_id=3D0
> > where state_id=3D0
> > and to_char(last_reply, 'yyyymmdd') between '20020301' and '20020830';
>
> > I took a quick look at top: Even this humble query causes memory- and
> > processor-load like a giant: 266M RAM, 38.3% processor time, 26.4%
> > memory usage. Okay, it's calling the trigger for each row which in turn
> > inserts some new tuples into ct_com_board_fti, but is it expected to
> > cause so much load?
>
> Wouldn't surprise me. Since you're using an AFTER trigger, the pending
> trigger events have to be saved up for commit time, so the list of
> pending events is going to grow quite large. (How many rows do you have
> in ct_com_board_message, anyway? How many did that query try to
> update?)
Whoa, that's what I was trying to remember. I had problems at one time
when loading a large amount of data into a table, with a txtidx type column. It
might not have been a memory problem I had though it could just have been slow
loading. I was loading with COPY in a transaction and ended up just doing the
COPY outside of a transaction. It still took a while but then it's only a low
powered machine.
If that wasn't the process footprint growing huge then that problem was
occuring for me when doing selects. I can't remember what it was that I did
that fixed it though. I wonder if it's in the list's archives since the issue
was raised here.
> This however does not explain your problem with SELECT, since
> selects don't fire triggers.
>
> Could I see the output of EXPLAIN for that problem SELECT on your
> machine?
>
> regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
>
--
Nigel J. Andrews
Director
---
Logictree Systems Limited
Computer Consultants
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Wollny | 2002-07-03 08:34:53 | Re: One source of constant annoyance identified |
Previous Message | frbn | 2002-07-03 08:16:38 | Re: Name of encodings: in which system catalog? |