Re: Monitoring postgres slowdowns

Sounds like you might be swapping out a bit much.

If your sort_mem is high, and several people execute a query that needs a
lot of sort_mem, you might start swapping since the sortmem setting is a
per process setting, not the total for the database.

Cranking up shared_buffers is usually ok since it IS a setting that is for
the database.

Try turning down sort_mem to something small like 256 or something and
see if that helps.  Keep in mind that 256*8k is still 2 Megs per process,
and if all 200 users are using sort mem that's 400 Megs right there.

 On 17 Jun 2002, Steve Bacon wrote:

> Hello,
>   is there any way to "look under the hood" when slowdowns occur? We
> have a tomcat / postgres site with each app having it's own server.
> The db machine is a dual CPU / RAID 5 / 2GB RAM box running RedHat
> Linux 7.1 and Postgres 7.1.3
> The two machines are connected via a hub on which no other machines
> are present (i.e. private link). shmall is set to 805306368 and shmmax
> is 536870912
>
> We seem to have daily slowdowns, and the only tools I know of are top
> and ps, which are pretty general and only tell you when something is
> cranking along. I'd like to better be able to 1) determine if indeed
> something strange is happening with our postgres install and 2) what
> it might be. I could find no pointers in the faq.
>
> Out user load isn't very heavy (max of 200 users), yet occasionally
> things just crawl. Looking at the tomcat machine shows most memory
> free low CPU usage, so all signs point to the DB machine - but how to
> tell if something's wrong / what exactly it is doing at the moment?
> It's getting frustrating because when it happens everyone looks at me,
> and I have no idea how to pinpoint what's happening.
>
> (Also, we are doing a nightly vacuum --analyze (we tried doing hourly
> vacuums on 6 of our update-heavy tables, but that slowed things down
> too much))
>
> thanks,
> -Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
"Force has no place where there is need of skill.", "Haste in every
business brings failures.", "This is the bitterest pain among men, to have
much knowledge but no power." -- Herodotus