Re: weird run-times with pg_autovacuum

Hi All!
I tried to find out more details about this weird problem, and I must
say, I am stumped.

here are the details:
We run a software module on a 64-bit opteron with a 32 Gig RAM, RedHat
AS3 update 3, Linux 2.4.21. postgres 7.4.6

I dumped the postgres query logs and here are the numbers:
SELECTs :2064 (2%)
INSERT : 14932 (12%)
UPDATE:105672(86%)

I ran the module twice, and the run times were 3 and 28 minutes!!!

I did some processing of the query logs using a Postgres Query
Analyzer library I found on the net, and the main difference was in
the execution of the selects.
(in fact, the execution times of the inserts and the updates remained
almost the same, and was even lesser in the slow run for some
queries.)

In particular, look at the following query:
SELECT fs_MapFormRul.CollectionGID, fs_MapFormRul.ObjectGID,
fs_MapFormRul.col_key, fs_MapFormRul.col_order FROM fs_MapFormRul
WHERE fs_MapFormRul.CollectionGID IN ( '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '' -- a large number of comma separated instance
values )
This query took 33 millisecs in the fast run as opposed to 9.6 seconds
on an average in the slow run.

I ran the module once more and got another slow run (I have no Idea
what's causing them so can't reproduce for my own benefit)
I opened up a parallel psql prompt and saw that the table
fs_mapfromrul had around 90k rows and the explain output of one of
these selects was:
Seq Scan on fs_mapformrul (cost=0.00..48003.02 rows=57419 width=219)
Filter: (((collectiongid)::text =
'YZZZZZSB1X1FUYSBZ3ZJHFP5ZYQLQVZZ'::text) OR ((collectiongid)::text =
'YZZZZZSB1X1FUYSBZ3ZJHFP5ZYZEO1ZZ'::text) OR ((collectiongid)::text

-- A LARGE SET OF THESE LINES

= 'YZZZZZSB1X1FUYSBZ3ZJHFP5ZYPJWXZZ'::text))

I am also confused why the slow runs appear only when I am running the
setup with pg_autovacuum and not with explicit vacuum analyzes.

Does any one know what do to here?
ps

On Tue, 21 Dec 2004 09:24:09 +0000, Richard Huxton <dev(at)archonet(dot)com> wrote:
> Postgres Learner wrote:
> > Hi all!
> > I recently started using pg_autovacuum instead of scheduling vacuum
> > analyzes in a system that needs to be taken to production soon.
> >
> > However, I have noticed something funny that happens while using this.
> >
> > sometimes, some database operations take an unreasonably long time to finish.
> > I have not been able to pin the problem down to any specific cause and
> > the problem is also not reproducible( atleast I don't know how to make
> > sure it happens again ) but what happens is that some queries take an
> > unreasonably long time to finish.
> >
> > pg_stat_actiivity showed some selects running on a table with ~90k
> > rows at one such time of weird behavior(they ran for a long long
> > time).
> >
> > Is it possible that pg_autovacuum is auto vacuuming that same table at
> > that time and there is some unnecessary waiting involved because the
> > table is locked?
>
> A simple vacuum shouldn't lock tables, although a vacuum full will. It
> could be that on a busy system, the vacuum is pushing disk activity to
> the limit. You can monitor overall activity with "vmstat 1" from the
> command-line in Linux.
>
> One solution might be to vacuum more often. Although a little
> counter-intuitive, this means each run does less work.
>
> --
> Richard Huxton
> Archonet Ltd
>