From owner-freebsd-isp@FreeBSD.ORG Tue Aug 2 14:24:10 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 31DD516A41F for ; Tue, 2 Aug 2005 14:24:10 +0000 (GMT) (envelope-from troy@psknet.com) Received: from psknet.com (kennedy.psknet.com [63.171.251.9]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9206243D6B for ; Tue, 2 Aug 2005 14:24:07 +0000 (GMT) (envelope-from troy@psknet.com) Received: from pool-70-110-6-88.roa.east.verizon.net ([70.110.6.88] helo=[192.168.1.102]) by psknet.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.43 (FreeBSD)) id 1Dzxgc-0005WV-GU for freebsd-isp@freebsd.org; Tue, 02 Aug 2005 10:24:06 -0400 Message-ID: <42EF8206.5000505@psknet.com> Date: Tue, 02 Aug 2005 10:24:06 -0400 From: Troy Settle User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050723) X-Accept-Language: en-us, en MIME-Version: 1.0 To: freebsd-isp@freebsd.org Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: 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:24:10 -0000 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