Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

Help optimizing MySQL for a 32GB system

Discussion in 'Workarounds and Optimization' started by johnburk, Jan 22, 2015.

  1. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    166
    I am going to upgrade from 16GB ram to 32GB and I looks I am not using the full potential of it. Can you please look at my config and see how you would optimize it?


    MySQL Settings
    Code:
    
    [mysqld]
    innodb_buffer_pool_size=1342177280
    max_allowed_packet=268435456
    
    key_buffer_size = 512M
    local-infile=0
    max_allowed_packet = 32M
    max_connections = 300
    max_heap_table_size = 512MB
    open_files_limit=32000
    query_cache_limit = 64M
    query_cache_size = 256M
    query_cache_type = 1
    table_definition_cache = 8000
    table_open_cache = 16000
    thread_cache_size = 128
    thread_cache_size=100
    tmp_table_size = 512MB
    connect_timeout=40
    interactive_timeout=120
    myisam_sort_buffer_size=64M
    
    ##### SLOW QUERY ######
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.9
    log-queries-not-using-indexes
    
    ##### INNODB ######
    innodb_file_per_table=1
    innodb_log_buffer_size=8M
    innodb_flush_method=O_DIRECT
    innodb_open_files=2400
    
    #### Per connection ####
    join_buffer_size=1M
    read_buffer_size=2M
    read_rnd_buffer_size=4M
    sort_buffer_size=256K
    
    [myisamchk]
    key_buffer = 32M
    sort_buffer = 32M
    read_buffer = 16M
    write_buffer = 16M
    
    
    Mysqltuner.pl output
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.6.22-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 450M (Tables: 2655)
    [--] Data in InnoDB tables: 652M (Tables: 1398)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 0B (Tables: 68)
    [!!] Total fragmented tables: 18
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 0h 21m 14s (74M q [428.119 qps], 939K conn, TX: 702B, RX: 9B)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 2.5G global + 7.5M per thread (300 max threads)
    [OK] Maximum possible memory usage: 4.7G (30% of installed RAM)
    [OK] Slow queries: 0% (717K/74M)
    [OK] Highest usage of available connections: 60% (182/300)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/240.1M
    [OK] Key buffer hit rate: 99.9% (221M cached / 315K reads)
    [OK] Query cache efficiency: 88.1% (59M cached / 67M selects)
    [!!] Query cache prunes per day: 984286
    [OK] Sorts requiring temporary tables: 0% (207 temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 646
    [OK] Temporary tables created on disk: 20% (469K on disk / 2M total)
    [OK] Thread cache hit rate: 99% (369 created / 939K connections)
    [!!] Table cache hit rate: 0% (2K open / 2M opened)
    [OK] Open file limit used: 8% (2K/32K)
    [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
    [OK] InnoDB data size / buffer pool: 652.9M/1.2G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increasing the query_cache size over 128M may reduce performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 256M) [see warning above]
        join_buffer_size (> 1.0M, or always use indexes with joins)
        table_cache (> 16000)
    
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,667
    Likes Received:
    1,788
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello :)

    You could modify the variables based on the "Variables to adjust" results under "Recommendations", however note that you may want to consult with a qualified system administrator for assistance with tuning your MySQL configuration if you do not receive sufficient user-feedback on this thread.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice