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.

Need help to reduce mysqld very high loading

Discussion in 'Workarounds and Optimization' started by pachiko, Nov 11, 2013.

  1. pachiko

    pachiko Active Member

    Joined:
    Nov 11, 2013
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello,
    M server's load is very high, i have noticed that MYSQL process causing high cpu usage.
    by runing MySQL tuner script , it suggests to adjust those variables:
    Code:
    Variables to adjust:
        query_cache_size (> 200M) [see warning above]
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_cache (> 10000)
        innodb_buffer_pool_size (>= 468M)
    
    This is my my.cnf configuration:

    Code:
    [myisamchk]
    read_buffer=2M
    sort_buffer_size=256M
    write_buffer=2M
    key_buffer_size=256M
    [mysqld]
    myisam_sort_buffer_size=64M
    read_rnd_buffer_size=8M
    log-slow-queries
    query_cache_size=200M
    thread_cache_size=8
    max_allowed_packet=32M
    table_open_cache=10000
    local-infile=0
    max_connections=400
    key_buffer_size=384M
    read_buffer_size=2M
    server-id=1
    datadir="/home/var_mysql/mysql"
    thread_concurrency=4
    sort_buffer_size=2M
    port=3306
    bind-address=127.0.0.1
    skip-external-locking
    socket="/var/lib/mysql/mysql.sock"
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    open_files_limit=50000
    [client]
    port=3306
    socket="/var/lib/mysql/mysql.sock"
    [mysqldump]
    max_allowed_packet=16M
    quick
    [mysql]
    no-auto-rehash
    [mysqlhotcopy]
    interactive-timeout
    Thanks.
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Post the rest of mysqltuner result
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Check the MySQL process list using the "mysqladmin processlist" command to see if you can find any databases using excessive resources.

    Thank you.
     
  4. pachiko

    pachiko Active Member

    Joined:
    Nov 11, 2013
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello,
    I executed the commad : mysqladmin processlist, and of course there is a database of one client that use excessive ressouces of the server
    any recommendations to resolve the problem with this constumer?
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You may need to work with the client and see if there is anything they can do to reduce their MySQL usage. It could be an issue with the specific script they are using, or it could indicate the client needs to upgrade to their own VPS or dedicated server.

    Thank you.
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You obiously have wrong config of mysql
    especially becouse of
    innodb_buffer_pool_size (>= 468M)



    But you didnt post FULL result of mysqltuner, once you post complete result, I can help you adjust my.cnf
     
Loading...

Share This Page