MySQL Performance

[Roman Volf]

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 at freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-isp
> To unsubscribe, send any mail to "freebsd-isp-unsubscribe at 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 at keystreams.com