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 load problems (15 CPU load..)

Discussion in 'General Discussion' started by AresXP, Apr 9, 2005.

  1. AresXP

    AresXP Member

    Joined:
    May 26, 2004
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    Hello,
    Whenever I start MySQL, load climbs to 9-25!! I've tried many things. MySQL version is 4.1.10a.
    My.cnf:
     
  2. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
    Really need the information from top posted within CODE tags to tell, but at a guess I'd say they you're running out of memory and swapping to disk which is causing the high load. especially with query_cache_size=150M if you d not have that much RAM free for it.
     
  3. AresXP

    AresXP Member

    Joined:
    May 26, 2004
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    Swap isnt used and memory usage is around 40%

    3-4 of those processes
     
  4. thehostinghut

    thehostinghut Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    232
    Likes Received:
    0
    Trophy Points:
    16
    I don't think anyone knows what this is because it never gets answered. I need to know what this is also.

    I wonder if we can find out?

    Tracy
     
  5. DigitalN

    DigitalN Well-Known Member

    Joined:
    Sep 23, 2004
    Messages:
    420
    Likes Received:
    1
    Trophy Points:
    18
    Try installing MyTop http://jeremy.zawodny.com/mysql/mytop/ - that may shed some light on what is using up the resources with MySQL.

    Check the disk performance using

    # hdparm -t -T /dev/hda

    Enable dma if it isn't enabled on your drives.
     
    #5 DigitalN, Apr 16, 2005
    Last edited: Apr 16, 2005
  6. Sinewy

    Sinewy Well-Known Member

    Joined:
    May 15, 2004
    Messages:
    367
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Sydney, Australia
    cPanel Access Level:
    DataCenter Provider
    Some of those settings are rather high, which is probably why you're getting high loads.

    The following is more optimal:

    [mysqld]
    skip-locking
    max_connections=300
    connect_timeout=15
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    key_buffer=16M
    join_buffer=1M
    record_buffer=1M
    sort_buffer=2M
    table_cache=1028
    thread_cache_size=286
    max_allowed_packet=5M
    wait_timeout=15
    thread_concurrency=2
     
  7. gr8ex

    gr8ex Member

    Joined:
    Sep 30, 2004
    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    1
    Guten Tag

    Mysql persistant connections can also cause load. Just set mysql persistant connection to off in php.ini

    Also reduce the query cache size to 50MB..I think this can help you. :)
     
  8. Sinewy

    Sinewy Well-Known Member

    Joined:
    May 15, 2004
    Messages:
    367
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Sydney, Australia
    cPanel Access Level:
    DataCenter Provider
    It should be set based on how much ram you have etc.

    Cheers,
    Alan
     
  9. thehostinghut

    thehostinghut Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    232
    Likes Received:
    0
    Trophy Points:
    16
    I did this and nothing out of the ordanary shows..... It does not show those processes.

    Tracy
     
  10. DigitalN

    DigitalN Well-Known Member

    Joined:
    Sep 23, 2004
    Messages:
    420
    Likes Received:
    1
    Trophy Points:
    18
    You could try commenting out the limits set in /etc/my.cnf and stop/start mysql, to see if that helps.

    There may be bugs in that version of MySQL, you could upgrade to the latest 4.1.11

    http://dev.mysql.com/downloads/mysql/4.1.html

    or downgrade back to 4.0.x

    Downgrading may require backups and recompiling apache/php if you do that.
    I'd try the limits one by one in my.cnf first then upgrade to the latest version.
     
  11. Secret Agent

    Secret Agent Guest

    I'm having the same problem

    /etc/my.cnf

    Code:
    [mysqld]
    skip-locking
    max_connections=300
    connect_timeout=15
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    key_buffer=16M
    join_buffer=1M
    record_buffer=1M
    sort_buffer=2M
    table_cache=1028
    thread_cache_size=286
    max_allowed_packet=5M
    wait_timeout=15
    thread_concurrency=2
    
    [mysqld_safe]
    open_files_limit = 8192
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [isamchk]
    key_buffer_size = 64M
    sort_buffer_size = 64M
    read_buffer_size = 4M
    write_buffer_size = 4M
    
    [myisamchk]
    key_buffer_size = 64M
    sort_buffer_size = 64M
    read_buffer_size = 4M
    write_buffer_size = 4M
    
    [mysqlhotcopy]
    interactive-timeout
    
    
    TOP shows mysql using the load generally, but can't trace it.

    6.5% cpu usage while these below happened

    Code:
    19139 mysql     18   0 33296  22M  1944 R    26.6  4.6   0:12   0 mysqld
    18979 nobody    15   0 10772  10M  3608 S     1.4  2.0   0:00   0 httpd
    18753 nobody    16   0 10688  10M  3752 S     0.9  2.0   0:01   0 httpd
        7 root      15   0     0    0     0 SW    0.8  0.0   0:08   1 kswapd
    14650 nobody    15   0 20660 8784  2020 D     0.6  1.7   0:02   1 spamd
    18447 nobody    15   0  9180 9024  3568 S     0.6  1.7   0:00   1 httpd
        8 root      15   0     0    0     0 SW    0.3  0.0   0:13   1 kscand
    15309 nobody    15   0 10508  10M  3720 S     0.2  2.0   0:02   0 httpd
    19136 mysql     15   0 33296  22M  1944 D     0.2  4.6   0:09   0 mysqld
    18441 nobody    15   0  7360 7204  3520 S     0.1  1.4   0:00   0 httpd
    18940 root      15   0  1240 1240   896 R     0.1  0.2   0:00   1 top
    
    ps auxf shows:

    Code:
    mysql    19055  2.1  4.2 100084 21696 ?      S    16:30   0:00 /usr/sbin/mysqld
    
    as the only lines with any thing around 2.0 cpu usage
     
  12. WeMasterz5

    WeMasterz5 Well-Known Member

    Joined:
    Feb 24, 2003
    Messages:
    361
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Miami
    Michael from cPanel helped me out with something like this

    we were seeing something like this jam up the load on the server

    %CPU 99.9 usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/pub1.*****.com.pid --skip-locking

    %CPU 99.6 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/pub1.********.com.pid --skip-locking

    %CPU 97.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/pub1.*********.com.pid --skip-locking

    we done this and it seems to be helping so far

    alter table `YOURDB_statz`.`mnmstats_info` add index `ip`(`ip`);

    The cpu-intensive queries seem to have been coming from the YOURBD_statz database. I've added an index on the "ip" field of the "mnmstats_info" so the queries don't have to scan the whole table every time, and the mysql load seems to have gone down
     
  13. WeMasterz5

    WeMasterz5 Well-Known Member

    Joined:
    Feb 24, 2003
    Messages:
    361
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Miami
    you will need to find which database it is that is overloading
     
  14. AndyReed

    AndyReed Well-Known Member
    PartnerNOC

    Joined:
    May 29, 2004
    Messages:
    2,222
    Likes Received:
    3
    Trophy Points:
    38
    Location:
    Minneapolis, MN
    We have seen this problem happening with few of our clients. The main reason was an insecure Php and/or cgi script that was not coded properly. I am referring to scripts written by individuals, or freedly distributed programs with the CPanel including PhpBB, PhpNume, and osCommerce. You'll have to upgrade these programs to the latest release, and/or ask the client to fix their own scripts.
     
  15. SteveD3

    SteveD3 Member

    Joined:
    Jul 3, 2003
    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    1
    I'm not seeing where I need to index anything. the DB that is causing the issues has no such record for mnmstats, nor is one names statz

    Can you shed a little more information on this resolution

    Thanks

    Steve
     
Loading...

Share This Page