MySQL Performance

[James Ryan]

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 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"