Date: Tue, 02 Aug 2005 09:45:40 -0500 From: James Ryan <james@jpri.com> To: freebsd-isp@freebsd.org Subject: Re: MySQL Performance Message-ID: <42EF8714.9020409@jpri.com> In-Reply-To: <42EF8206.5000505@psknet.com> References: <42EF8206.5000505@psknet.com>
next in thread | previous in thread | raw e-mail | index | archive | help
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"
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?42EF8714.9020409>