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. Optimise help needed.

Discussion in 'Workarounds and Optimization' started by howzit, Aug 20, 2012.

  1. howzit

    howzit Member

    Feb 20, 2012
    Likes Received:
    Trophy Points:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Hi, the past two weeks my server started to run on high server load and again I need to look at all the thingies that can be the cause thereof... Please help...

    I notice that the slow queries is not problem!?!?!? ([OK] Slow queries: 0% (11K/31M))
    But found that the slow query log grew to 10Mb in 4 days...?
    Also why would it log something like:
    # Time: 120820 12:59:54
    # User
    # Query_time: 0 Lock_time: 0 Rows_sent: 27 Rows_examined: 54
    use thedb;
    SELECT * FROM bdircats ORDER BY cname ASC;

    What am I doing wrong? looks as follow and below it is contents of my.cnf :

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.95-community-log
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 723M (Tables: 546)
    [!!] Total fragmented tables: 14
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 0h 26m 30s (31M q [120.881 qps], 958K conn, TX: 71M, RX: 337M)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 422.0M global + 1.2M per thread (200 max threads)
    [OK] Maximum possible memory usage: 659.5M (32% of installed RAM)
    [OK] Slow queries: 0% (11K/31M)
    [!!] Highest connection usage: 100%  (201/200)
    [OK] Key buffer size / total MyISAM indexes: 220.0M/229.9M
    [OK] Key buffer hit rate: 100.0% (25B cached / 263K reads)
    [OK] Query cache efficiency: 74.6% (20M cached / 28M selects)
    [!!] Query cache prunes per day: 555390
    [OK] Sorts requiring temporary tables: 0% (15K temp sorts / 3M sorts)
    [!!] Joins performed without indexes: 3654
    [!!] Temporary tables created on disk: 45% (2M on disk / 6M total)
    [OK] Thread cache hit rate: 99% (3K created / 958K connections)
    [!!] Table cache hit rate: 4% (1K open / 21K opened)
    [OK] Open file limit used: 70% (1K/2K)
    [OK] Table locks acquired immediately: 99% (15M immediate / 15M locks)
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce or eliminate persistent connections to reduce connection usage
        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:
        max_connections (> 200)
        wait_timeout (< 1800)
        interactive_timeout (< 28800)
        query_cache_size (> 128M)
        join_buffer_size (> 256.0K, or always use indexes with joins)
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)
        table_cache (> 1024)
    max_connections = 200
    key_buffer_size = 220M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 256K
    read_buffer_size = 256K
    sort_buffer_size = 256K
    table_cache = 1024
    thread_cache_size = 16
    wait_timeout = 1800
    max_allowed_packet = 16M
    query_cache_limit = 1M
    query_cache_size = 128M
    query_cache_type = 1
    tmp_table_size = 64M
    max_heap_table_size = 64M
    open_files_limit = 8192
    max_allowed_packet = 16M
    key_buffer_size = 96M
    sort_buffer_size = 96M
    read_buffer_size = 16M
    write_buffer_size = 16M
    Thanks for the help...

Share This Page