MySQL Performance

[Troy Settle]

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