Обсуждение: database running slow

hi there,

I'm looking for some advice here to troubleshoot
performance issues as and when i get reports of
'database running slow'

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

1. how to identify run-away sql backends

2. find out the bad sqls thats causing trouble

and what would one do to quickly identify the real
cause of performance issues.

please advice.

Shankar

sorry to crosspost in both admin/performance list.

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

On Mon, May 12, 2003 at 11:55:11AM -0700, Shankar K wrote:

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

> 2. find out the bad sqls thats causing trouble

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

A
--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

> 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

On Tue, May 13, 2003 at 10:19:55AM -0700, Shankar K wrote:
>
> 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.

You can use the pid of a process to look it up in the system tables.
There's no simple way to identify long-running ones, except to look
for back ends that have been hanging around for a long time (but if
you're using pooling, it won't help you).  Tools like top will be
helpful in identifying processor hogs.

> ++ how to identify those queries. once i have the

If you can identify the process, then you can identify the pid, and
use the pid in the stats tables to get the query.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110