Re: MySQL Performance

From: James Ryan (james_at_jpri.com)
Date: 08/02/05

  • Next message: Troy Settle: "Re: MySQL Performance" 
    Date: Tue, 02 Aug 2005 09:45:40 -0500
To: freebsd-isp@freebsd.org

    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"

  • Next message: Troy Settle: "Re: MySQL Performance"

    Relevant Pages

    • Re: mysql slowdown under load
      ... Enable query caching in MySQL ... Enable thread caching in MySQL ... You can tweek the memory use (increase stuff like key/index cache, ... Subject: mysql slowdown under load ...
      (freebsd-stable)
    • Re: Problem with SQL and Recordset
      ... This assumes that the name of the subform control on the parent form is the ... NextSuffix field from the "query" to be entered into the Suffix field on ... Dim mySQL As String ...
      (microsoft.public.access.modulesdaovba)
    • Re: [Q]Problems related to the MySQL linked Server.
      ... The error messge when I query data with following SQL. ... select @v_intUserNo = uno ... where userid = 'testid' ... I have two questions about the linked server for the MySQL. ...
      (microsoft.public.sqlserver.odbc)
    • Re: Performance MySQL
      ... schicke erst den Query an die Datenbank ab, ... Möglichkeit von Concurrent Inserts nur MyISAM Storages ein. ... > MySQL bis jetzt noch nichts zu bieten. ... Welcher Tabellentyp? ...
      (de.comp.datenbanken.mysql)
    • Re: where to get mySQL questions answered?
      ... After resolving some performance issues with mysql in building initial ... soon as I add an OR to my WHERE x LIKE '%blah%' type queries. ... My php code dynamically builds an sql query. ...
      (comp.lang.php)