Re: Are indexes blown?

Phoenix Kiula wrote:
> On 15/02/2008, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
>> Ah, more new information! This does seem to point to the load,
>> particularly if it's exactly the same query each time. So what do
>> top/vmstat etc show for these "go-slow" periods?
>
> In included top and vmstat info in my other post yesterday, but here
> it is again:

Ah, you had a post yesterday!

(goes away, searches for previous post)
http://archives.postgresql.org/pgsql-general/2008-02/msg00689.php
PG quitting sporadically!!

Right, OK. Firstly, stop worrying about index usage and/or bloat. You
have unexplained process crashes to deal with first. There's no point in
looking at indexes until you figure out what is killing your processes.

Secondly, a single line from vmstat isn't useful, you want to compare
what is happening when things are fine with when they aren't. Leave
vmstat 10 logging to a file so you can catch it.

Thirdly, have you upgraded to the latest 8.2 (8.2.6) yet?

I see you've reduced work_mem, that's good.

Oh, you might as well lower max_connections from 150 too, there's no way
you can support that many concurrent queries anyway.

The fact that you're seeing various strange socket-related problems is
odd. As is the fact that logging doesn't seem to work for you.

Are you sure the two sets of vmstat/top figures are from when PG was
crashing/running queries slow? Everything seems idle to me in those figures.

--
Richard Huxton
Archonet Ltd