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 performance mysqltuner guidance

Discussion in 'Workarounds and Optimization' started by sahostking, Jun 1, 2015.

  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
    Using the following, need some help optimizing please:
    MySQL version 10.0.15-MariaDB-cll-lve

    Code:
    [mysqld]
    slow-query-log=1
    long-query-time=1
    slow-query-log-file="/var/log/mysql-slow.log"
    datadir="/data/mysql"
    local-infile=0
    max_connections=500
    max_user_connections=30
    key_buffer_size=512M
    myisam_sort_buffer_size=64M
    read_buffer_size=1M
    table_open_cache=6000
    thread_cache_size=384
    wait_timeout=6000
    connect_timeout=20
    tmp_table_size=256M
    max_heap_table_size=256M
    max_allowed_packet=268435456
    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=256M
    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
    log-error="/var/log/mysqld.log"
    #log-queries-not-using-indexes
    external-locking=FALSE
    open_files_limit=50000
    innodb_stats_on_metadata=0
    low_priority_updates=1
    innodb_file_per_table
    innodb_buffer_pool_size=134217728
    
    
    [mysqld_safe]
    
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [isamchk]
    key_buffer=384M
    sort_buffer=384M
    read_buffer=256M
    write_buffer=256M
    
    [myisamchk]
    key_buffer=384M
    sort_buffer=384M
    read_buffer=256M
    write_buffer=256M
    
    #### Per connection configuration ####
    sort_buffer_size=1M
    join_buffer_size=1M
    thread_stack=192K
    
    MSQL Tuner results:
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 8h 5m 20s (21M q [104.976 qps], 237K conn, TX: 125B, RX: 4B)
    [--] Reads / Writes: 95% / 5%
    [--] Total buffers: 1.1G global + 4.2M per thread (500 max threads)
    [OK] Maximum possible memory usage: 3.2G (41% of installed RAM)
    [OK] Slow queries: 0% (12K/21M)
    [OK] Highest usage of available connections: 38% (193/500)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/340.7M
    [OK] Key buffer hit rate: 99.2% (97M cached / 814K reads)
    [OK] Query cache efficiency: 42.4% (13M cached / 32M selects)
    [!!] Query cache prunes per day: 104111
    [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 769K sorts)
    [!!] Joins performed without indexes: 18152
    [!!] Temporary tables created on disk: 36% (248K on disk / 681K total)
    [OK] Thread cache hit rate: 99% (193 created / 237K connections)
    [!!] Table cache hit rate: 1% (6K open / 425K opened)
    [OK] Open file limit used: 8% (4K/50K)
    [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/2.9G
    [OK] InnoDB log waits: 0
    -------- 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
        Temporary table size is already large - reduce result set size
        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: bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_size (> 256M) [see warning above]
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_open_cache (> 6000)
        innodb_buffer_pool_size (>= 2G)
    
    
     
    #1 sahostking, Jun 1, 2015
    Last edited by a moderator: Jun 2, 2015
  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
    Hello,

    Note that you can always implement some of the recommendations provided by the MySQL tuner, let MySQL run for another 24 hours, and post the updated results for user-feedback.

    Thank you.
     
Loading...

Share This Page