Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    73

    SQL Taking Up Too Much Resources

    Hello everyone,

    One of my servers keep overloading due to a SQL process.

    The process is:

    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/zeus1.forcium.net.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

    It takes up pretty much 90-95% of the cpu and memory at times if I do not kill the process. But even after I kill the process it comes back and immediately hogs up cpu load again causing it to go into loads of 8.00 or higher ( I have 8cpus ).

    Any solution to this?

    Thanks in advance!

  2. #2
    Join Date
    Jan 2006
    Location
    Athens, Greece
    Posts
    1,481
    Use one of the .cnf files that came with mysql. Possibly my-medium.cnf
    I would also suggest keeping mysql threads to 4.

    If that doesn't do anything, then there is a script that does some heavy processing.

  3. #3
    Join Date
    Apr 2008
    Posts
    73
    where can I go to do this?

  4. #4
    Join Date
    Apr 2008
    Posts
    73
    I can't seem to spot any single process that can be taking up all that resources with SQL.

    It seems like this process would build up overtime and eventually overload the server.

    Currently Looking at Today's CPU Usage:

    mysql 59.90 2.37 0.0

    Top Process %CPU 148 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/zeus1.forcium.net.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock

    Top Process %CPU 125 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/zeus1.forcium.net.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock

    Top Process %CPU 124 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/zeus1.forcium.net.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock

    Then I go to: Main ]] SQL Services ]] Show MySQL Processes

    I see the following:

    1330 hentaifo_blu localhost hentaifo_smf Sleep 3 NULL
    1407 womenfoo_footbal localhost womenfoo_football Sleep 1 NULL
    1442 hentaifo_blu localhost hentaifo_smf Sleep 5 NULL
    1484 hentaifo_blu localhost hentaifo_smf Sleep 1 NULL
    1522 bwcbob_scftane localhost bwcbob_scftanet Sleep 5 NULL
    1523 bwcbob_scftane localhost bwcbob_scftanet Sleep 5 NULL
    1540 fhosting_s2k localhost fhosting_status2k Sleep 2 NULL
    1544 eximstats localhost eximstats Sleep 2 NULL
    1559 root localhost NULL Query 0 NULL SHOW PROCESSLIST

    Any input on this would be appreciated.

    Thanks

  5. #5
    Join Date
    Oct 2007
    Location
    9.9N 76.2E , Planet Earth
    Posts
    1,003
    Install MTOP (http://mtop.sourceforge.net/) and monitor the MySQL server

    You may also want to log slow mysql queries
    A U T O M 8 N . C O M
    High Available webstack for cPanel
    Active-Active redundancy and High Availability plugin for cPanel

  6. #6
    Join Date
    Aug 2001
    Location
    Brea, CA
    Posts
    541
    You could also do something like:
    strace -f -p <process id>

    That would let you watch the system calls MySQL is making. It'll often scroll pretty fast, but you can use some of the options to log to a file and then examine it.

    It's hit and miss, but it often gives some insight.
    Jeff Standen, Software Architect, Cerb
    Cerb - web-based teamwork and workflow automation - https://cerb.ai/

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,431
    Hi

    Firstly I would look at the configuration of the mysql startup as suggested earlier. There are numerous sites out there that have tuning scripts that will tell your where the bottleneck is.

    if you don't know about my.cnf there is a good chance its a default one.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •