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 help

Discussion in 'Workarounds and Optimization' started by nyte_spawn, Dec 30, 2013.

  1. nyte_spawn

    nyte_spawn Registered

    Joined:
    Dec 30, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    can anyone take a look at the following mysql tuner report and tell me what i should add to my.cnf file to make it run smoother or any other things i should be doing to optimize.

    Code:
    >>  MySQLTuner 1.2.0 - Major Hayden 
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials passed on the command line
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.34-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 280M (Tables: 185)
    [--] Data in InnoDB tables: 1M (Tables: 63)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 32
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 11d 14h 3m 48s (874K q [0.873 qps], 78K conn, TX: 13B, RX: 170M)
    [--] Reads / Writes: 42% / 58%
    [--] Total buffers: 176.0M global + 2.8M per thread (200 max threads)
    [OK] Maximum possible memory usage: 726.0M (17% of installed RAM)
    [OK] Slow queries: 0% (0/874K)
    [OK] Highest usage of available connections: 4% (9/200)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/66.6M
    [OK] Key buffer hit rate: 99.9% (8M cached / 8K reads)
    [OK] Query cache efficiency: 50.1% (181K cached / 362K selects)
    [!!] Query cache prunes per day: 694
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 21K sorts)
    [OK] Temporary tables created on disk: 13% (1K on disk / 12K total)
    [OK] Thread cache hit rate: 99% (29 created / 78K connections)
    [OK] Table cache hit rate: 37% (400 open / 1K opened)
    [OK] Open file limit used: 50% (517/1K)
    [OK] Table locks acquired immediately: 99% (438K immediate / 438K locks)
    [OK] InnoDB data size / buffer pool: 1.0M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
    Variables to adjust:
        query_cache_size (> 8M)
    This is the current my.cnf.

    Code:
    [mysqld]
    max_connections = 200
    innodb_file_per_table = 1
    query_cache_size = 8M
    query_cache_limit = 1M
    table_cache = 400
    thread_cache_size = 4 
    interactive_timeout = 1000
    connect_timeout = 1000
    wait_timeout = 1000
    log-slow-queries=/var/lib/mysql/slow.log
    Thanks in advance
     
  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
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You dont have much queries, so there is not much to optimize

    You can add
    key_buffer_size = 100M
    long_query_time=0.1

    query_cache_size change to 30M
    table_cache change to 1000

    besides that, it's all good

    before restarting remove old slow log file /var/lib/mysql/slow.log, so new one will be created and catch queries longer than 0.1
     
  4. nyte_spawn

    nyte_spawn Registered

    Joined:
    Dec 30, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi i have a new xen vps now and this is the mysqltuner report

    Code:
    >>  MySQLTuner 1.2.0 - Major Hayden 
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials passed on the command line
    
    -------- 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: 35M (Tables: 265)
    [--] Data in MRG_MYISAM tables: 807K (Tables: 6)
    [--] Data in InnoDB tables: 1M (Tables: 70)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 26
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 11h 51m 42s (209K q [0.693 qps], 14K conn, TX: 3B, RX: 30M)
    [--] Reads / Writes: 66% / 34%
    [--] Total buffers: 176.0M global + 2.8M per thread (200 max threads)
    [OK] Maximum possible memory usage: 726.0M (18% of installed RAM)
    [OK] Slow queries: 0% (15/209K)
    [OK] Highest usage of available connections: 3% (6/200)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/8.7M
    [OK] Key buffer hit rate: 99.9% (940K cached / 623 reads)
    [OK] Query cache efficiency: 50.7% (60K cached / 119K selects)
    [!!] Query cache prunes per day: 211
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 14K sorts)
    [OK] Temporary tables created on disk: 8% (253 on disk / 3K total)
    [OK] Thread cache hit rate: 99% (6 created / 14K connections)
    [OK] Table cache hit rate: 57% (400 open / 694 opened)
    [OK] Open file limit used: 28% (597/2K)
    [OK] Table locks acquired immediately: 99% (96K immediate / 96K locks)
    [OK] InnoDB data size / buffer pool: 1.3M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
    Variables to adjust:
        query_cache_size (> 8M)
    The current my.cnf

    Code:
    [mysqld]
    innodb_file_per_table=1
    local-infile=0
     
    max_connections = 200
    query_cache_size = 8M
    query_cache_limit = 1M
    thread_cache_size = 4 
    tmp_table_size = 16M
    max_heap_table_size = 16M
    interactive_timeout = 1000
    connect_timeout = 1000
    wait_timeout = 1000
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    open_files_limit=2112
     
  5. cPanelPeter

    cPanelPeter Technical Analyst III
    Staff Member

    Joined:
    Sep 23, 2013
    Messages:
    569
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
Loading...

Share This Page