Re: MySQL Performance

From: Troy Settle (troy_at_psknet.com)
Date: 08/02/05

  • Next message: Thomas Eisenbarth: "Re: MySQL Performance" 
    Date: Tue, 02 Aug 2005 12:43:40 -0400
To: freebsd-isp@freebsd.org

    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"
_______________________________________________
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"
  • Next message: Thomas Eisenbarth: "Re: MySQL Performance"

    Relevant Pages

    • Re: [PHP] Command-line PHP script CPU usage goes sky-high, stays there--why?
      ... run indefinitely with a predictable CPU usage and memory footprint. ... connections, processes incoming data, stores results in a MySQL ... years on an Xserve dual-G5 2GHz w/ OS X Server 10.4.11). ... One question about the "listener" program, does it maintain a connection to the DB or does it ...
      (php.general)
    • RE: MySQL/PHPMyAdmin on FC3 Connection Problem
      ... // You can disable a server config entry by setting host to ''. ... MySQL server ... MySQL control user settings ... table to describe the display fields ...
      (Fedora)
    • Re: Using Access for web application?
      ... Any suggestions as to which newsgroup would be more ... The server that the web app will use in this case, ... > which technology you will decide to use as the Web server (Linux or Windows) ... Instead MySQL is being ...
      (microsoft.public.access.dataaccess.pages)
    • [UNIX] phpMyAdmin PHP Code Injection (left.php)
      ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... phpMyAdmin is "web-based MySQL ... does not prevent a malicious user from altering the servers configuration ... server configurations to the list of servers configuration by adding ...
      (Securiteam)
    • Re: Using MySQL as the backend
      ... I need to learn MySQL, ... > the impression you're not familiar with the concept of a server DB. ... > doesn't need to have Access installed to access the database, ... >> install the server or maybe I have it installed but I just don't know how to ...
      (microsoft.public.access.tablesdbdesign)