Skip site navigation (1)Skip section navigation (2)
Date:      Thu, 18 Sep 2003 10:06:40 -0700
From:      Sean Chittenden <sean@chittenden.org>
To:        Supote Leelasupphakorn <pjn0211@yahoo.com>
Cc:        FreeBSD-Chat@FreeBSD.org
Subject:   Re: How DBA solved overload problem ?
Message-ID:  <20030918170640.GC34604@perrin.nxad.com>
In-Reply-To: <20030918110851.55151.qmail@web40611.mail.yahoo.com>
References:  <20030918110851.55151.qmail@web40611.mail.yahoo.com>

next in thread | previous in thread | raw e-mail | index | archive | help
>    As a newly DBA, I really don't know how I deal with this
> problem. My problem is not so long ago, my database server seem to
> overloaded. It take me a time to find the cause of problem. I
> realize that some program don't queried wiht inappropriated SQL
> statement. I mean they're not efficient one.
> 
> AS DBA how do you solved this problem?

In PostgreSQL, I flip on the following settings and then periodically
scan PostgreSQL's the log file (/var/log/pgsql):

log_duration = true
log_pid = true
log_statement = true
log_timestamp = true

It's tedious, but with grep on your side, it's not an impossible task
or even a super hard one... just a bit tedious if you don't keep up
with your developers.  I've found doing this on the devel machines
produces better bang for the buck than on the production DB's (though
I still do it there occasionally).  Once a query is found, I typically
launch a big 'ole fashion egrep -r with a reasonably unique part of
the query and am normally pretty successful in finding the culprit.
Sometimes you'll have to use sockstat to find what machine and what
PID you're dealing with, but that's something I have to do rarely.
-sc


PS Don't spam so many lists in the future, just -databases or just
-questions would've been sufficient (the more topical the list the
better).

-- 
Sean Chittenden



Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20030918170640.GC34604>