Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL Optimization Required

Discussion in 'Workarounds and Optimization' started by ckbapps, Dec 31, 2012.

  1. ckbapps

    ckbapps Registered

    Dec 31, 2012
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    I am facing with slow website.. it is only loading and loading and nothing happening.. However the same file and DB my developer using on their server is running fine.

    My Server was running in Cloud VPS with 4GB RAM, 6Core CPU and Centos, CPanel/WHM.

    Here is my my.cnf settings

    max_connections = 300
    key_buffer = 10M
    myisam_sort_buffer_size = 10M
    join_buffer_size = 5M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 3500
    thread_cache_size = 286
    interactive_timeout = 25
    wait_timeout = 1000
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 16M
    query_cache_size = 50M
    query_cache_type = 1
    tmp_table_size = 200M
    max_heap_table_size = 200M
    key_buffer_size = 100M
    open_files_limit = 8500
    max_allowed_packet = 16M
    key_buffer = 32M
    sort_buffer = 32M
    read_buffer = 16M
    write_buffer = 16M
    Here is MySQL Tuner Script Report:

    >>  MySQLTuner 1.2.0 - Major Hayden <>
     >>  Bug reports, feature requests, and downloads at
     >>  Run with '--help' for additional options and output filtering
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.66-cll
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 11M (Tables: 270)
    [--] Data in InnoDB tables: 224K (Tables: 14)
    [!!] Total fragmented tables: 3
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 58m 55s (436K q [4.849 qps], 8K conn, TX: 2B, RX: 616M)
    [--] Reads / Writes: 65% / 35%
    [--] Total buffers: 360.0M global + 8.5M per thread (300 max threads)
    [OK] Maximum possible memory usage: 2.8G (74% of installed RAM)
    [OK] Slow queries: 0% (0/436K)
    [OK] Highest usage of available connections: 11% (34/300)
    [OK] Key buffer size / total MyISAM indexes: 100.0M/2.5M
    [OK] Key buffer hit rate: 99.7% (1M cached / 4K reads)
    [OK] Query cache efficiency: 84.7% (313K cached / 370K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
    [!!] Joins performed without indexes: 298
    [OK] Temporary tables created on disk: 22% (9K on disk / 42K total)
    [OK] Thread cache hit rate: 99% (34 created / 8K connections)
    [OK] Table cache hit rate: 30% (546 open / 1K opened)
    [OK] Open file limit used: 8% (737/8K)
    [OK] Table locks acquired immediately: 99% (99K immediate / 99K locks)
    [!!] Connections aborted: 13%
    [OK] InnoDB data size / buffer pool: 224.0K/8.0M
    -------- 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
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        join_buffer_size (> 5.0M, or always use indexes with joins)

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice