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 5.6 Optimization Guidance

Discussion in 'Workarounds and Optimization' started by sahostking, Jul 23, 2014.

  1. sahostking

    sahostking Well-Known Member

    Joined:
    May 15, 2012
    Messages:
    300
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Cape Town, South Africa
    cPanel Access Level:
    Root Administrator
    Hi,

    Noticing some heavy load on one of our mysql data disks attached to a VM. I'd really appreciate some help. Thanks

    Here is the my.cnf config we use currently:

    Code:
    [mysqld]
    datadir = /data/mysql
    query_cache_type=1
    max_connections=100
    join_buffer_size=1M
    myisam_use_mmap=1
    myisam_sort_buffer_size=128M
    local-infile=0
    thread_cache_size=25
    key_buffer_size=128M
    read_rnd_buffer_size=1M
    max_allowed_packet=268435456
    open_files_limit=50000
    default-storage-engine=MyISAM
    log-error="/var/log/mysqld.log"
    max_user_connections=30
    table_open_cache=7500
    max_connect_errors = 100000

    Also here is the mysqltuner results:

    Code:
    >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at /http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.16-cll-lve
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 2G (Tables: 20216)
    [--] Data in InnoDB tables: 804M (Tables: 14358)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 0B (Tables: 222)
    [!!] Total fragmented tables: 651
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2h 59m 17s (1M q [130.468 qps], 38K conn, TX: 4B, RX: 529M)
    [--] Reads / Writes: 69% / 31%
    [--] Total buffers: 500.0M global + 2.6M per thread (1024 max threads)
    [OK] Maximum possible memory usage: 3.1G (40% of installed RAM)
    [OK] Slow queries: 0% (248/1M)
    [OK] Highest usage of available connections: 10% (107/1024)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/976.3M
    [OK] Key buffer hit rate: 98.9% (3M cached / 40K reads)
    [OK] Query cache efficiency: 66.8% (664K cached / 994K selects)
    [!!] Query cache prunes per day: 308893
    [OK] Sorts requiring temporary tables: 0% (19 temp sorts / 32K sorts)
    [!!] Joins performed without indexes: 1072
    [!!] Temporary tables created on disk: 32% (17K on disk / 52K total)
    [OK] Thread cache hit rate: 99% (241 created / 38K connections)
    [!!] Table cache hit rate: 14% (7K open / 51K opened)
    [OK] Open file limit used: 18% (9K/50K)
    [OK] Table locks acquired immediately: 99% (494K immediate / 495K locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/804.2M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: /http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/
    Variables to adjust:
        query_cache_size (> 75M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 80M)
        max_heap_table_size (> 25M)
        table_open_cache (> 7500)
        innodb_buffer_pool_size (>= 804M)
     
    #1 sahostking, Jul 23, 2014
    Last edited: Jul 23, 2014
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

Share This Page