Re: Performance tips - Mailing list pgsql-general
From | Andrew Perrin |
---|---|
Subject | Re: Performance tips |
Date | |
Msg-id | Pine.LNX.4.21L1.0201092234580.5798-100000@nujoma.perrins Whole thread Raw |
In response to | Re: Performance tips (Doug McNaught <doug@wireboard.com>) |
Responses |
Re: Performance tips
Re: Performance tips |
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 >
pgsql-general by date: