Re: One source of constant annoyance identified

Hello!

Sorry I took so long - I attached the schema as asked.

Actually it seems to be very often the case, that certain operations
suck up more than 25% of available memory and processing capacities.

I managed to customize and install the Full Text Index-option (fti) from
the contrib-directory (substrings are at least 3 characters in length
for us and I defined a list of StopWords to be not included). Right now
I have started filling the fti-table with the substrings; I tried using
the Perl-script supplied, but the results are quite dissatisfactory
because it doesn't exclude any stopwords, nor does it limit itself to
alphanumeric - you may be able to imagine what happens when there's
loads of kiddies posting "!!!!!!!!!!!!!!!!!!" (ad nauseum) and suchlike
in quite a lot of postings. So used the already implemented trigger to
execute the fti-function:

update ct_com_board_message
set state_id=0
where state_id=0
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?

Regards,

Markus

> -----Ursprüngliche Nachricht-----
> Von: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Gesendet: Sonntag, 30. Juni 2002 21:34
> An: Markus Wollny
> Cc: pgsql-general(at)postgresql(dot)org
> Betreff: Re: [GENERAL] One source of constant annoyance identified

> That's what I thought too, but I've tried and failed to reproduce any
> memory leak with lower/LIKE and the same configuration
> options that you
> used. It might be that some other part of the query is the
> problem, or
> maybe I'm not duplicating the setup correctly. Could I
> trouble you for
> the exact schemas of the tables used by the problem query?
> (The output
> of pg_dump -s would be the best thing to send.)
>
> regards, tom lane
>

"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?) 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

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