Thread: 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