Re: Performance tips

Andrew Perrin <andrew_perrin@unc.edu> writes:

> Well, here's the output from vmstat:
>
> aperrin@hm269-26876:~/afshome/papers/authoritarian$ vmstat
>    procs                      memory    swap          io     system
> cpu
>  r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us
> sy  id
>  0  1  0   3052   2132  10460 413284   0   0    11    14    6     5   6
> 3  17

Sorry, I should have told you to do 'vmstat 5' which will keep
printing lines of numbers (every 5 seconds) until you interrupt it.
One line isn't too useful.  But hold off on that for now, see below...

> I can't say I understand it though.... I've got a query running through
> psql that's been chugging away for nearly 2 hours now:
>
> auth=# select count(patternid) from patterns where patternid in (select
> o_patternid from
> auth(# letters, pattern_occurrences where letters.letterid =
> pattern_occurrences.o_letterid
> auth(# and letters.datecat in (1,2));
>
>
> patterns has approx. 3,000,000 records, pattern_occurrences 5,000,000,
> letters 10,000, of which 8,000 or so are datecat 1 or 2.
>
> Last time I tried to vacuum the database it was still hung 12 hours later
> so I cancelled.  Haven't tried vacuum analyze or explain but will do so.

Yow.  There are two possibilities:

1) VACUUM actually ran that long (possible)
2) You had something else holding a transaction open, which prevents
   VACUUM from running.  Do you have any clients running that hold
   connections open?

You *really* need to VACUUM ANALYZE, especially if your tables have
been active with updates and deletes.  Once that's done, do an EXPLAIN
on your long-running queries, post the output along with your schema
and maybe we can help you speed things up.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863