Re: Performance tips - Mailing list pgsql-general

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

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@unc.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@unc.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
>