Re: database running slow

> Since our application involves high inserts/deletes,
> we vacuum/analyze major tables thrice a day. Apart
> from this

Be sure you really need the vacuum before you do it.
++ yes i normally vacuum/analyze only tables with huge
inserts/deletes and others once a week.

Remember, vacuum destroys your buffers, so it's not
free.  On certain kinds of tables, it can be worth it
to do a vacuum very often (we have some we
do once an hour).

> 1. how to identify run-away sql backends

Define "run-away".  You can look for long-running
transactions; that might be helpful.

++ i meant a backend process taking most of the cpu
cycles and running for ever. so i wanted to figure out
what those processes were doing and what made them to
hog the cpu cycles.

how to look for long-running transactions ?

> 2. find out the bad sqls thats causing trouble

Look for queries that take a long time.  EXPLAIN
ANALYSE is your friend.

++ how to identify those queries. once i have the
queries identified then i can do explain analyze and
tune accordingly. Is there equivalent of oracle
sql_trace in postgres ?

thanks for you help

Shankar

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com