From owner-freebsd-isp@FreeBSD.ORG Tue Aug 2 14:46:08 2005 Return-Path: X-Original-To: freebsd-isp@freebsd.org Delivered-To: freebsd-isp@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 84D6716A41F for ; Tue, 2 Aug 2005 14:46:08 +0000 (GMT) (envelope-from james@jpri.com) Received: from gandalf.jpri.com (gandalf.jpri.com [69.57.152.82]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2F32843D53 for ; Tue, 2 Aug 2005 14:46:03 +0000 (GMT) (envelope-from james@jpri.com) Received: (qmail 3153 invoked by uid 89); 3 Aug 2005 05:13:56 -0000 Received: from cpe240.txcyber.com (HELO ?192.168.0.155?) (james@jpri.com@208.21.199.240) by gandalf.jpri.com with SMTP; 3 Aug 2005 05:13:56 -0000 Message-ID: <42EF8714.9020409@jpri.com> Date: Tue, 02 Aug 2005 09:45:40 -0500 From: James Ryan User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: freebsd-isp@freebsd.org References: <42EF8206.5000505@psknet.com> In-Reply-To: <42EF8206.5000505@psknet.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: Re: MySQL Performance X-BeenThere: freebsd-isp@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Internet Services Providers List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 02 Aug 2005 14:46:08 -0000 Try enabling slow query logging, as well as maybe try to isolate if the load is coming from any particular query... If you are performing joins on the 1k+ rows table, maybe MySQL has ran out of memory and is copying the query results to a tmp table on the disk. Sometimes this can cause a jump in CPU usage... You can determine if this is the case by mysqladmin processlist; it will say something like "Copying to tmp table". Check for slow queries though... Cheers, James Troy Settle wrote: > > I've a 4-STABLE box on a Dual Xeon w/4GB and U320 RAID: > > 4.11-STABLE FreeBSD 4.11-STABLE #7: Fri Mar 11 20:24:25 EST 2005 > 10:05AM up 55 days, 14:38, 3 users, load averages: 1.07, 1.10, 1.13 > > Running on this box, is MySQL 4.0.23a (a little outdated, I know). This > MySQL server seems to run fine as long as I watch it, but as soon as I > turn my head for a moment, it jumps up to ~97% CPU usage: > > 508 mysql 63 0 99M 51660K CPU0 0 306.9H 97.46% 97.46% mysqld > > It doesn't appear to be an IO issue, 300 samples from iostat show a max > of 0.31 MB/s. > > It also doesn't appear to be a memory issue: > > 867M Active, 2354M Inact, 320M Wired, 192M Cache, 199M Buf, 40M Free > Swap: 4096M Total, 16K Used, 4096M Free > > Through a number of samples from sockstat(1), I see ~20 connections to > mysql at any given time. 5 for Courier's authdaemon, 3 for Exim, and > ~12 for Apache/PHP. There are intermitant connections from Windows > clients using MyODBC, but none are persistant. Restarting any/all of > these processes does not cause mysql to ease up on the CPU. Only > restarting MySQL will buy releif, but after a few weeks, it's right back > to 97% CPU usage. > > The machine itself is completely responsive, mysql is completely > responsive. The tables in question are flat, with only a few thousand > entries in the largest. Queries range from simple to semi-complex. > > I can't identify the source of the load. Can anyone help? What should > I be looking at? > > Thanks, > > -- > Troy Settle > Pulaski Networks > http://www.psknet.com > 866.477.5638 > _______________________________________________ > freebsd-isp@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-isp > To unsubscribe, send any mail to "freebsd-isp-unsubscribe@freebsd.org"