> 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