Re: Performance tips

Andrew Perrin <andrew_perrin(at)unc(dot)edu> writes:

> Well, here's the output from vmstat:
>
> aperrin(at)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