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

Discussion in 'Workarounds and Optimization' started by abdelhost77, Aug 4, 2012.

  1. abdelhost77

    abdelhost77 Well-Known Member

    Joined:
    Apr 25, 2012
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Hello ,

    anu suggestions please for Mysql Tuning , below are inputs :



    Here is the complete Mysqltuner.pl output :


    Code:
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 48162)
    [--] Data in InnoDB tables: 1G (Tables: 19138)
    [--] Data in MEMORY tables: 3M (Tables: 332)
    [!!] Total fragmented tables: 22701
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5d 23h 10m 49s (71M q [139.181 qps], 1M conn, TX: 356B, RX: 9B)
    [--] Reads / Writes: 92% / 8%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (18% of installed RAM)
    [OK] Slow queries: 0% (730/71M)
    [OK] Highest usage of available connections: 44% (221/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/745.8M
    [OK] Key buffer hit rate: 95.4% (903M cached / 41M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 1% (85K temp sorts / 7M sorts)
    [!!] Joins performed without indexes: 1310986
    [!!] Temporary tables created on disk: 31% (2M on disk / 6M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 15M opened)
    [OK] Open file limit used: 5% (126/2K)
    [OK] Table locks acquired immediately: 99% (71M immediate / 71M locks)
    [!!] InnoDB data size / buffer pool: 1.5G/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
        Set thread_cache_size to 4 as a starting value
        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)
        thread_cache_size (start at 4)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 1G)

    ################################"

    my.cnf

    [mysqld]
    local-infile=0
    set-variable = max_connections=500
    log-slow-queries=/var/lib/mysql/slow.log
    safe-show-database

    - - - Updated - - -
     
  2. abdelhost77

    abdelhost77 Well-Known Member

    Joined:
    Apr 25, 2012
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    No feedbacks :(
     
Loading...

Share This Page