From: | Andrew Perrin <andrew_perrin(at)unc(dot)edu> |
---|---|
To: | Doug McNaught <doug(at)wireboard(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Performance tips |
Date: | 2002-01-10 03:37:41 |
Message-ID: | Pine.LNX.4.21L1.0201092234580.5798-100000@nujoma.perrins |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-01-10 04:36:29 | Re: Performance tips |
Previous Message | Doug McNaught | 2002-01-10 02:07:04 | Re: Performance tips |