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 for a 16GB ram server.

Discussion in 'Workarounds and Optimization' started by johnburk, Feb 25, 2014.

  1. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Server is a 8-core Xeon with 16GB of ram running multiple wordpress blogs.

    Highest usage of available connections based on the last 6 weeks has been 382.

    Any suggestions on how I can improve and optimize my.cnf?


    Mysqltuner output
    Fragmentation of 60 to 100 happens within 2 to 4 hours of optimizing the database.

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.35-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 4G (Tables: 2018)
    [--] Data in InnoDB tables: 262M (Tables: 720)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 3M (Tables: 34)
    [!!] Total fragmented tables: 91
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 17h 26m 49s (35M q [109.964 qps], 474K conn, TX: 889B, RX: 8B)
    [--] Reads / Writes: 76% / 24%
    [--] Total buffers: 2.6G global + 14.2M per thread (400 max threads)
    [OK] Maximum possible memory usage: 8.1G (51% of installed RAM)
    [OK] Slow queries: 0% (2K/35M)
    [OK] Highest usage of available connections: 10% (43/400)
    [OK] Key buffer size / total MyISAM indexes: 1.2G/1.2G
    [OK] Key buffer hit rate: 99.9% (540M cached / 344K reads)
    [OK] Query cache efficiency: 60.5% (17M cached / 29M selects)
    [!!] Query cache prunes per day: 240109
    [OK] Sorts requiring temporary tables: 0% (73 temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 2285
    [OK] Temporary tables created on disk: 23% (369K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (43 created / 474K connections)
    [!!] Table cache hit rate: 0% (5K open / 3M opened)
    [OK] Open file limit used: 4% (6K/128K)
    [OK] Table locks acquired immediately: 99% (14M immediate / 14M locks)
    [OK] InnoDB data size / buffer pool: 262.7M/512.0M
    
    -------- 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 (> 512M) [see warning above]
        join_buffer_size (> 2.0M, or always use indexes with joins)
        table_cache (> 64000)
    My.cnf
    Code:
    [mysqld]
    connect_timeout=360
    default-storage-engine=MyISAM
    innodb_additional_mem_pool_size=40M
    innodb_buffer_pool_size=512M
    innodb_commit_concurrency=16
    innodb_file_per_table=1
    innodb_flush_method=O_DIRECT
    innodb_log_buffer_size=12M
    innodb_max_dirty_pages_pct=90
    innodb_thread_concurrency=16
    interactive_timeout=2400
    join_buffer_size=2M
    key_buffer_size=1280M
    local-infile=0
    log-slow-queries
    long_query_time=1
    max_allowed_packet=32M
    max_connections=400
    max_heap_table_size=256M
    open_files_limit=100000
    query_cache_limit=32M
    query_cache_min_res_unit=512
    query_cache_size=512M
    read_buffer_size=4M
    read_rnd_buffer_size=2M
    sort_buffer_size=6M
    table_cache=64K
    table_definition_cache=8K
    table_open_cache=64000
    thread_cache_size=4M
    tmp_table_size=256M
    wait_timeout=2400
     
    #1 johnburk, Feb 25, 2014
    Last edited: Feb 25, 2014
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,811
    Likes Received:
    671
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    lower table_cache to something like 6000
    since it scales bad on high numbers

    table_definition_cache to 2000

    Many of the values you put there are too big, it seems like it was copied from somewhere without thought

    The best thing would be to add caching to wordpress
     
  4. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    I will give it a try, but I am not comfortable with adding my mysql root password in a plain text environment.

    - - - Updated - - -

    All sites already work with caching.

    The values are based on advice of tools such as mysqltuner.pl and tuning-primer.sh which both seem to love giving the advice to increase values.
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Exactly, tools always suggest to increase and increase :)

    put there this, it's cleaned up version of your my.cnf
    with enabled slow query log, after it gatherers some slow queries for few days, you can run pt-query-digest and review them

    Also install Munin module from WHM, you will have there server resource monitoring nicely displayed on graphs

    Code:
    [mysqld]
    local-infile=0
    
    connect_timeout=360
    wait_timeout=2400
    interactive_timeout=2400
    default-storage-engine=MyISAM
    
    max_connections = 400
    max_user_connections = 100
    
    key_buffer_size=1400M
    join_buffer_size=2M
    sort_buffer_size=256K
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1		
    
    query_cache_type = 1
    query_cache_size = 150M
    query_cache_limit = 1M
    
    max_allowed_packet=32M
    tmp_table_size=256M
    max_heap_table_size=256M
    open_files_limit=100000
    
    table_definition_cache=2000
    table_open_cache=6000
    
    thread_cache_size=64
    
    innodb_buffer_pool_size=512M
    innodb_file_per_table=1
    
     
  6. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Thank you.

    Unfortunately cPanel has not updated Munin for a few years. They still use 1.4.7 which has some bugs in term of memory usage.
     
Loading...

Share This Page