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.

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:
    16
    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:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    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.
     
Loading...

Share This Page