From owner-freebsd-isp@FreeBSD.ORG Tue Aug 2 14:39:39 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 B6E0916A41F for ; Tue, 2 Aug 2005 14:39:39 +0000 (GMT) (envelope-from volfman@keystreams.com) Received: from mailbox.keystreams.com (mailbox.keystreams.com [207.158.28.37]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5E9FA43D5C for ; Tue, 2 Aug 2005 14:39:39 +0000 (GMT) (envelope-from volfman@keystreams.com) Received: (qmail 84179 invoked by uid 1012); 2 Aug 2005 07:35:24 -0700 Received: from 10.8.0.6 by mail.keystreams.com (envelope-from , uid 1009) with qmail-scanner-1.25-st-qms (ClamAV 0.86.1. spamassassin: 3.0.4. perlscan: 1.25-st-qms. Clear:RC:0(10.8.0.6):SA:0(-5.9/5.0):. Processed in 3.072924 secs); 02 Aug 2005 14:35:24 -0000 X-Spam-Status: No, hits=-5.9 required=5.0 X-Antivirus-Keystreams-Mail-From: volfman@keystreams.com via mail.keystreams.com X-Antivirus-Keystreams: 1.25-st-qms (Clear:RC:0(10.8.0.6):SA:0(-5.9/5.0):. Processed in 3.072924 secs Process 84168) Received: from unknown (HELO ?10.8.0.6?) (volfman@keystreams.com@10.8.0.6) by mailbox.keystreams.com with AES256-SHA encrypted SMTP; 2 Aug 2005 07:35:20 -0700 Message-ID: <42EF85A7.2000103@keystreams.com> Date: Tue, 02 Aug 2005 07:39:35 -0700 From: Roman Volf User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Troy Settle , 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 Cc: 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:39:39 -0000 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" Have you tried compiling WITH_LINUXTHREADS=yes BUILD_OPTIMIZED=yes BUILD_STATIC=yes (if you are compiling from ports). I had similar issues on FreeBSD 5.3 and this helped alot. Also, are you logging slow queries? Maybe there is a single query causing the problem? -- Roman Volf Keystreams Internet Solutions volfman@keystreams.com