Date: Tue, 02 Aug 2005 12:43:40 -0400 From: Troy Settle <troy@psknet.com> To: freebsd-isp@freebsd.org Subject: Re: MySQL Performance Message-ID: <42EFA2BC.4060808@psknet.com> In-Reply-To: <42EF8714.9020409@jpri.com> References: <42EF8206.5000505@psknet.com> <42EF8714.9020409@jpri.com>
next in thread | previous in thread | raw e-mail | index | archive | help
Thanks to all for the feedback. Looking at the status, there were 7 slow queries. I've enabled logging and re-started the server. Now I wait a few weeks to see what, if anything, happens. Of course, by that time, I hope to have a dedicated MySQL server up and running, though I'm not sure I'll want to run my mail server off a remote database. Thanks again, -- Troy Settle Pulaski Networks http://www.psknet.com 866.477.5638 James Ryan wrote: > 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" > > > _______________________________________________ > 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?42EFA2BC.4060808>