Re: Performance tips

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

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.

Thanks,
Andy

----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin(at)unc(dot)edu - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
269 Hamilton Hall, CB#3210, Chapel Hill, NC 27599-3210 USA

On 9 Jan 2002, Doug McNaught wrote:

> Andrew Perrin <andrew_perrin(at)unc(dot)edu> writes:
>
> > The computer is a 1Ghz PIII (IBM NetVista) running debian linux
> > (woody) and PostgreSQL 7.1.3. There's 512M of RAM in it, and top shows
> > that swap rarely gets used, so one possibility is to try to have pg keep
> > more workspace in RAM at once. I could also potentially buy more RAM for
> > the machine.
>
> Do try to keep it out of swap, but you may have scope for increasing
> the number of shmem buffers. More RAM will always help, as will
> getting more and faster disks and spreading the I/O load over them.
> Take a look at 'vmstat' output and your CPU usage while you're running
> a query to see where your bottlenecks might be.
>
> Unless you have enough RAM to cache the whole thing, a database is
> usually I/O bound, which means your disk subsystem is probably a good
> place to improve.
>
> Also: VACUUM ANALYZE (are you running it)? Does EXPLAIN show
> reasonable plans for all your queries?
>
> -Doug
> --
> Let us cross over the river, and rest under the shade of the trees.
> --T. J. Jackson, 1863
>