Re: Performance tips

On Wed, Jan 09, 2002 at 10:37:41PM -0500, Andrew Perrin wrote:
> 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:

Well now, that's very interesting. You may need to type "vmstat 1" so it
prints status every second, but the interesting columns are the "bi" and
"bo" columns (blocks in and blocks out). It appears that your disk system is
basically idling and the last five columns indicate that your CPU is also.
The second column indicates one process in "uninterruptable sleep", which is
bad if it stays that way.

Looks like a process was accessing an NFS mount and the server died, but
that's probably not the case. What could possibly be jamming your machine
so?

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

Really, really slow disk? Are you getting any disk errors? timeouts?

HTH,

> 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
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.