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.

What should we change (mysqltuner)

Discussion in 'Workarounds and Optimization' started by hostatree, Mar 25, 2011.

  1. hostatree

    hostatree Member

    Joined:
    May 6, 2009
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Based on the following results, can someone tell me some helpful changes we should make to our mysql. Server has 6gb ram.

    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

    >> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.92-community-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 17527)
    [--] Data in InnoDB tables: 2G (Tables: 1718)
    [--] Data in MEMORY tables: 3M (Tables: 57)
    [!!] Total fragmented tables: 1368

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 15h 31m 31s (10M q [73.109 qps], 300K conn, TX: 39B, RX: 1B)
    [--] Reads / Writes: 78% / 22%
    [--] Total buffers: 514.0M global + 8.2M per thread (500 max threads)
    [OK] Maximum possible memory usage: 4.5G (77% of installed RAM)
    [OK] Slow queries: 0% (263/10M)
    [OK] Highest usage of available connections: 11% (59/500)
    [OK] Key buffer size / total MyISAM indexes: 384.0M/728.2M
    [OK] Key buffer hit rate: 99.6% (226M cached / 890K reads)
    [OK] Query cache efficiency: 71.8% (5M cached / 7M selects)
    [!!] Query cache prunes per day: 574440
    [OK] Sorts requiring temporary tables: 0% (3K temp sorts / 759K sorts)
    [!!] Joins performed without indexes: 9664
    [!!] Temporary tables created on disk: 32% (187K on disk / 570K total)
    [OK] Thread cache hit rate: 99% (59 created / 300K connections)
    [!!] Table cache hit rate: 0% (2K open / 469K opened)
    [OK] Open file limit used: 84% (3K/4K)
    [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
    [!!] InnoDB data size / buffer pool: 2.3G/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better 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
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 50M)
    join_buffer_size (> 3.0M, or always use indexes with joins)
    tmp_table_size (> 70M)
    max_heap_table_size (> 70M)
    table_cache (> 2048)
    innodb_buffer_pool_size (>= 2G)
     
  2. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hey!

    You need to make the below given changes in the my.cnf

    query_cache_size=50M
    join_buffer_size=3.0M
    tmp_table_size=70M
    max_heap_table_size=70M
    table_cache=2048
    innodb_buffer_pool_size=2G

    After that you need to optimize the databases using the below given command.

    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

    Restart MySQL after you've done the above:

    /etc/init.d/mysql restart

    Please wait 24 hours and then re-run mysqltuner.pl again on the machine to see any other variable changes to make.
     
    #2 LinuxTechie, Mar 26, 2011
    Last edited: Mar 26, 2011
  3. hostatree

    hostatree Member

    Joined:
    May 6, 2009
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    What about now?

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better 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
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 50M)
    join_buffer_size (> 3.0M, or always use indexes with joins)
    tmp_table_size (> 70M)
    max_heap_table_size (> 70M)
    table_cache (> 2048)
    innodb_buffer_pool_size (>= 2G)
     
Loading...

Share This Page