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.

my.cnf Optimisation - 24GB RAM

Discussion in 'Workarounds and Optimization' started by tone, Jun 7, 2013.

  1. tone

    tone Member

    Joined:
    Oct 20, 2010
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    I've completed the mysqltuner script and here are the results:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.31-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 307M (Tables: 993)
    [--] Data in InnoDB tables: 885M (Tables: 1685)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 993K (Tables: 24)
    [!!] Total fragmented tables: 137
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 5h 12m 20s (185M q [665.911 qps], 995K conn, TX: 437B, RX: 24B)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 2.0G global + 2.8M per thread (5000 max threads)
    [OK] Maximum possible memory usage: 15.5G (66% of installed RAM)
    [OK] Slow queries: 0% (2K/185M)
    [OK] Highest usage of available connections: 2% (106/5000)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/77.1M
    [OK] Key buffer hit rate: 99.9% (1B cached / 2M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (57 temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 46473
    [!!] Temporary tables created on disk: 43% (206K on disk / 474K total)
    [OK] Thread cache hit rate: 99% (106 created / 995K connections)
    [!!] Table cache hit rate: 0% (400 open / 96K opened)
    [OK] Open file limit used: 0% (92/25K)
    [OK] Table locks acquired immediately: 99% (185M immediate / 185M locks)
    [OK] InnoDB data size / buffer pool: 885.2M/2.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        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_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_cache (> 400)
    
    
    And here is the content of my.cnf

    Code:
    local-infile=0
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    open_files_limit=12424
    max_user_connections=3000
    max_connections=5000
    thread_cache_size=1000
    innodb_buffer_pool_size=2048M
    
    I've attempted to read up on these values and have read a lot of posts on here with a similar question, but I'm still uncertain of the best settings to use. I would appreciate someone with more knowledge giving their input.

    The server is a E3-1240 with 24GB of RAM. The largest database is 900MB.

    Regards,

    Piers
     
  2. tone

    tone Member

    Joined:
    Oct 20, 2010
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    I've updated the my.cnf with the below values as advised by my hosting provider.

    query_cache_size=256M
    table-cache=512M
    tmp_table_size=64M
    join_buffer_size=1M
     
  3. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,146
    Likes Received:
    34
    Trophy Points:
    48
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello,

    Also you can add following values in your my.cnf file

     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,696
    Likes Received:
    656
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Keep in mind that when you make changes, it's a good idea to let MySQL run at least 24 hours before running the tuner script again to ensure more reliable results.

    Thank you.
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    table-cache=512M is ridiculous

    table_cache is buffer for open tables, it can be set max 524288,
    but it's one of the variables which can decrease performance, when set too big, since mysql scallability problem

    so in your case set it as
    table_cache = 2000


    full suggested config
    Code:
    [mysqld]
    skip-name-resolve
    
    wait_timeout = 30
    connect_timeout = 1
    local-infile=0
    
    open_files_limit=12424
    default-storage-engine=MyISAM
    
    max_connections=2000
    
    key_buffer_size = 5G
    max_allowed_packet=32M
    
    query_cache_size=50M
    query_cache_limit=1M
    tmp_table_size=100M
    max_heap_table_size=100M
    thread_cache_size=100
    
    table_open_cache = 2000
    table_definition_cache = 1000
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
    
    #### Per connection configuration ####
    sort_buffer_size=256K
    join_buffer_size=4M
    read_rnd_buffer_size=8M
    
    ##### INNODB Specific Options ######
    innodb_buffer_pool_size=15G
    innodb_buffer_pool_instances = 8
    innodb_log_file_size = 128M
    innodb_flush_log_at_trx_commit=2
    innodb_log_buffer_size=8M
    innodb_flush_method=O_DIRECT
    innodb_open_files=2000
    
    thats all,

    before restarting the server with new config, remove old log files, since the size is changed
    do it like this:
    rm -rf /var/lib/mysql/ib_logfile*

    then remove mysql-slow.log file
    rm -rf /var/lib/mysql/*-slow.log

    and restart mysql

    let it run few hours and generate new mysqltuner and pt-query-digest like that:
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

    in slow.txt you will have slow queries review, which you can later put here
     
  6. tone

    tone Member

    Joined:
    Oct 20, 2010
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1



    Thank you for the replies. I've run mysqltuner.pl again and here are the results. Is the above still suggested?

    Code:
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.31-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 316M (Tables: 1081)
    [--] Data in InnoDB tables: 763M (Tables: 1687)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 745K (Tables: 24)
    [!!] Total fragmented tables: 150
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 19h 35m 33s (24M q [73.971 qps], 565K conn, TX: 447B, RX: 5B)
    [--] Reads / Writes: 67% / 33%
    [--] Total buffers: 1.3G global + 3.6M per thread (5000 max threads)
    [OK] Maximum possible memory usage: 19.0G (81% of installed RAM)
    [OK] Slow queries: 0% (129K/24M)
    [OK] Highest usage of available connections: 1% (63/5000)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/80.7M
    [OK] Key buffer hit rate: 99.8% (39M cached / 93K reads)
    [OK] Query cache efficiency: 92.0% (20M cached / 21M selects)
    [!!] Query cache prunes per day: 124002
    [OK] Sorts requiring temporary tables: 0% (360 temp sorts / 118K sorts)
    [!!] Joins performed without indexes: 51911
    [!!] Temporary tables created on disk: 42% (94K on disk / 220K total)
    [OK] Thread cache hit rate: 99% (63 created / 565K connections)
    [OK] Table cache hit rate: 80% (5K open / 7K opened)
    [OK] Open file limit used: 25% (3K/12K)
    [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
    [OK] InnoDB data size / buffer pool: 763.2M/1.0G
    
    -------- 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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 256M) [see warning above]
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 192M)
        max_heap_table_size (> 24M)
    
     
Loading...

Share This Page