The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySql overload

Discussion in 'Workarounds and Optimization' started by mm1250, Jun 13, 2008.

  1. mm1250

    mm1250 Well-Known Member

    Joined:
    Nov 10, 2006
    Messages:
    108
    Likes Received:
    0
    Trophy Points:
    16
    Hello all,

    I have noticed over the last 2 weeks MySQL is been always on the top and using lots of CPU it seems. Even when I goto WHM "CPU/Memory/MySQL Usage" the MySQL is a yello color stating it's getting pretty heavy loaded.

    However, I am not very adverse with MySQL and I dont' know which user(s) are hammering the MySQL server. There are over 250 websites on this server. What is the process to investigate which sites are loading up the server?

    I talked with my server admin company who says there is no way to find out which site(user) is hammering the mysql. I find this very hard to believe.
     
  2. mm1250

    mm1250 Well-Known Member

    Joined:
    Nov 10, 2006
    Messages:
    108
    Likes Received:
    0
    Trophy Points:
    16
    Here is a copy/paste from the CPU/user/memory usage

    mysql 10.71 1.11 0.0
    Top Process %CPU 10.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/hostname.server.com.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock
    Top Process %CPU 10.7 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/hostname.server.com.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock
     
  3. Kailash1

    Kailash1 Well-Known Member

    Joined:
    Nov 27, 2006
    Messages:
    252
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    There is an option in WHM "Show MySQL Process" through which you will get which user is using high mysql resource.

    Additionally you can install mytop on your server.
    http://jeremy.zawodny.com/mysql/mytop/

    Regards,

    Kailash
     
  4. mm1250

    mm1250 Well-Known Member

    Joined:
    Nov 10, 2006
    Messages:
    108
    Likes Received:
    0
    Trophy Points:
    16
    MySQL Process List

    Id User Host db Command Time State Info
    1267 eximstats localhost eximstats Sleep 99 NULL
    1396143 cphulkd localhost cphulkd Sleep 34 NULL
    1396236 cphulkd localhost cphulkd Sleep 1 NULL
    1396239 root localhost NULL Query 0 NULL SHOW PROCESSLIST
     
  5. Kailash1

    Kailash1 Well-Known Member

    Joined:
    Nov 27, 2006
    Messages:
    252
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    From the process list, it doesn't seem that any user is using high mysql resource. Try to optimize the settings in my.cnf. I think that will help you.

    Kailash
     
Loading...

Share This Page