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 Optimization needed

Discussion in 'Workarounds and Optimization' started by zlati, Jan 31, 2016.

  1. zlati

    zlati Registered

    Joined:
    Jan 31, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Romania
    cPanel Access Level:
    Root Administrator
    Hello,

    Im have a dedicated server with 32GB ram and Intel(R) Xeon(R) CPU D-1520 @ 2.20GHz with 8 Cores. Have hosted around 130 websites on it and 80% of them are using mysql. I have the problem with the load, load average: 8.06, 7.83, 7.94 .
    I saw in htop that mysql uses 200-600 % CPU, it's very strange.
    My my.cnf look like:
    Code:
    [mysqld]
    local-infile=0
    max_connections = 400
    key_buffer_size = 512M
    myisam_sort_buffer_size = 64M
    read_buffer_size = 1M
    table_open_cache = 5000
    thread_cache_size = 384
    wait_timeout = 5
    connect_timeout = 10
    tmp_table_size = 256M
    max_heap_table_size = 128M
    max_allowed_packet = 64M
    net_buffer_length = 16384
    max_connect_errors = 10
    concurrent_insert = 2
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 5M
    query_cache_size = 128M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65535
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    max_write_lock_count = 8
    slow_query_log
    log-error
    external-locking=FALSE
    open_files_limit=50000
    
    [mysqld_safe]
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [isamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    [myisamchk]
    key_buffer = 1G
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    #### Per connection configuration ####
    sort_buffer_size = 1M
    join_buffer_size = 1M
    thread_stack = 192K
    
    It's this optimization okay ?

    i forgot to tell that i use cloudlinux with cPanel and db_governor
     
    #1 zlati, Jan 31, 2016
    Last edited by a moderator: Feb 1, 2016
  2. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,146
    Likes Received:
    34
    Trophy Points:
    48
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello :),

    I will recommend you to use mysqltuner.pl on the server so to optimize mysql services and when you attempt this make sure the mysql service is running last 24 hours
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,734
    Likes Received:
    661
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page