From owner-freebsd-questions@FreeBSD.ORG Thu Sep 18 10:06:42 2003 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 7E0DA16A4B3; Thu, 18 Sep 2003 10:06:42 -0700 (PDT) Received: from perrin.nxad.com (internal.nxad.com [69.1.70.251]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5FEF643F3F; Thu, 18 Sep 2003 10:06:41 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.nxad.com (Postfix, from userid 1001) id BC5F22105E; Thu, 18 Sep 2003 10:06:40 -0700 (PDT) Date: Thu, 18 Sep 2003 10:06:40 -0700 From: Sean Chittenden To: Supote Leelasupphakorn Message-ID: <20030918170640.GC34604@perrin.nxad.com> References: <20030918110851.55151.qmail@web40611.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030918110851.55151.qmail@web40611.mail.yahoo.com> X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 3849 3760 1AFE 7B17 11A0 83A6 DD99 E31F BC84 B341 X-Web-Homepage: http://sean.chittenden.org/ User-Agent: Mutt/1.5.4i cc: freebsd-database@freebsd.org cc: freebsd-questions@freebsd.org cc: FreeBSD-Chat@FreeBSD.org Subject: Re: How DBA solved overload problem ? X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 18 Sep 2003 17:06:42 -0000 > 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