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 CHO66, Nov 19, 2012.

  1. CHO66

    CHO66 Registered

    Joined:
    Nov 19, 2012
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    CentOS - VPS with 20GB ram

    I been having some server load issue, I have Varnish installed on the server.


    Code:
    >>  MySQLTuner 1.2.0 - Major Hayden 
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  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.27-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 1G (Tables: 13905)
    [--] Data in InnoDB tables: 1G (Tables: 17026)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 5M (Tables: 40)
    [!!] Total fragmented tables: 17964
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 21m 25s (729K q [149.358 qps], 85K conn, TX: 2B, RX: 188M)
    [--] Reads / Writes: 59% / 41%
    [--] Total buffers: 2.4G global + 6.5M per thread (500 max threads)
    [OK] Maximum possible memory usage: 5.6G (28% of installed RAM)
    [OK] Slow queries: 0% (1/729K)
    [OK] Highest usage of available connections: 21% (106/500)
    [OK] Key buffer size / total MyISAM indexes: 192.0M/469.6M
    [OK] Key buffer hit rate: 100.0% (39M cached / 13K reads)
    [!!] Query cache efficiency: 16.9% (66K cached / 396K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 5% (266 temp sorts / 4K sorts)
    [!!] Joins performed without indexes: 162
    [!!] Temporary tables created on disk: 30% (1K on disk / 3K total)
    [OK] Thread cache hit rate: 99% (106 created / 85K connections)
    [OK] Table cache hit rate: 99% (1K open / 1K opened)
    [OK] Open file limit used: 16% (1K/8K)
    [OK] Table locks acquired immediately: 95% (717K immediate / 748K locks)
    [!!] Connections aborted: 6%
    [OK] InnoDB data size / buffer pool: 1.5G/2.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        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
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        query_cache_limit (> 4M, or use smaller result sets)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)

    my.conf

    Code:
    [client]
    port=3306
    socket="/var/lib/mysql/mysql.sock"
    
    [mysqld]
    ft_min_word_len = 3
    max_connections = 500
    myisam_sort_buffer_size = 64M
    key_buffer = 192M
    read_buffer_size = 2M
    sort_buffer_size = 2M
    join_buffer_size = 2M
    table_open_cache = 4096
    thread_cache_size = 384
    wait_timeout = 7200
    connect_timeout = 10
    max_allowed_packet = 64M
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 64M
    max_connect_errors = 1000
    query_cache_limit = 4M
    query_cache_size =128M
    query_cache_type = 1
    innodb_buffer_pool_size = 2G
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    nice = -5
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M

    http.conf

    Code:
    PidFile logs/httpd.pid
    LockFile logs/accept.lock
    # Defined in /var/cpanel/cpanel.config: apache_port
    Listen 0.0.0.0:82
    User nobody
    Group nobody
    ExtendedStatus On
    ServerAdmin myname @gmail.com
    ServerName server3.hostlatte.com
    LogLevel warn
    
    # These can be set in WHM under 'Apache Global Configuration'
    Timeout 60
    TraceEnable Off
    ServerSignature Off
    ServerTokens ProductOnly
    FileETag None
    StartServers 5
    <IfModule prefork.c>
    MinSpareServers 5
    MaxSpareServers 10
    </IfModule>
    ServerLimit 128
    MaxClients 500
    MaxRequestsPerChild 5000
    KeepAlive On
    KeepAliveTimeout 5
    MaxKeepAliveRequests 100

    Any suggestions on fixing my server load issue?
     
  2. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    Whats your sever load at? When does it become a problem? As for the Mysql side of tings. I have a default answer from another post.

    1. Wait the 24 hours before running the tests, so that they are not inaccurate.
    2. Here is a video about this. MySQL OptimizationcPanel Videos | cPanel Videos
     
  3. CHO66

    CHO66 Registered

    Joined:
    Nov 19, 2012
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Code:
    >>  MySQLTuner 1.2.0 - Major Hayden 
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  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.27-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 1G (Tables: 13991)
    [--] Data in InnoDB tables: 1G (Tables: 17029)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 11M (Tables: 42)
    [!!] Total fragmented tables: 17978
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10h 59m 31s (7M q [188.927 qps], 735K conn, TX: 23B, RX: 1B)
    [--] Reads / Writes: 60% / 40%
    [--] Total buffers: 2.4G global + 6.5M per thread (500 max threads)
    [OK] Maximum possible memory usage: 5.6G (28% of installed RAM)
    [OK] Slow queries: 0% (47/7M)
    [OK] Highest usage of available connections: 23% (119/500)
    [OK] Key buffer size / total MyISAM indexes: 192.0M/486.3M
    [OK] Key buffer hit rate: 99.9% (177M cached / 113K reads)
    [OK] Query cache efficiency: 24.4% (986K cached / 4M selects)
    [!!] Query cache prunes per day: 474561
    [OK] Sorts requiring temporary tables: 0% (680 temp sorts / 76K sorts)
    [!!] Joins performed without indexes: 1935
    [!!] Temporary tables created on disk: 39% (28K on disk / 72K total)
    [OK] Thread cache hit rate: 99% (119 created / 735K connections)
    [!!] Table cache hit rate: 3% (4K open / 125K opened)
    [OK] Open file limit used: 81% (7K/8K)
    [OK] Table locks acquired immediately: 96% (6M immediate / 6M locks)
    [OK] InnoDB data size / buffer pool: 1.5G/2.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        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 (> 128M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)
        table_cache (> 4096)
    

    Here an update after 24 hours, anyone have and suggestions? The server load keep spiking every now and then.

    Load Averages: 2.79 2.20 2.31

    And it spikes all the way up to 6-10 every now and then though out the day.
     
    #3 CHO66, Nov 21, 2012
    Last edited: Nov 21, 2012
  4. sahostking

    sahostking Well-Known Member

    Joined:
    May 15, 2012
    Messages:
    299
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Cape Town, South Africa
    cPanel Access Level:
    Root Administrator
    How many websites have you got on one server? You may have reached your maximum.
    Also it may be PHP and not necessarily MySQL?
     
  5. mobilitysd

    mobilitysd Registered

    Joined:
    Nov 23, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Is your system optimized? You can try to optimize your system by make.conf. You can re-build your MySQL, PHP, Varnish and it will work faster. In my case, home page was generating for 22sec after cleaning cache, when I optimizaed whole system and all applications it became 8sec. It's pretty good as for me. Here describes how to make optimization by make.conf
    /http://basicuse.net/articles/os/bsd/freebsd/optimization_by_make_conf_in_freebsd
     
  6. CHO66

    CHO66 Registered

    Joined:
    Nov 19, 2012
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Its a shared server, but I have a large website. Just recently out of no where the server load just started spiking.

    heres an update of mysql tuner

    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.27-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 2G (Tables: 13830)
    [--] Data in InnoDB tables: 1G (Tables: 16319)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 5M (Tables: 42)
    [!!] Total fragmented tables: 17286
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2h 41m 2s (1M q [174.853 qps], 162K conn, TX: 6B, RX: 400M)
    [--] Reads / Writes: 60% / 40%
    [--] Total buffers: 2.3G global + 6.5M per thread (500 max threads)
    [OK] Maximum possible memory usage: 5.5G (28% of installed RAM)
    [OK] Slow queries: 0% (7/1M)
    [OK] Highest usage of available connections: 20% (101/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/508.5M
    [OK] Key buffer hit rate: 99.9% (68M cached / 98K reads)
    [OK] Query cache efficiency: 26.4% (242K cached / 916K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (313 temp sorts / 22K sorts)
    [!!] Joins performed without indexes: 446
    [!!] Temporary tables created on disk: 40% (6K on disk / 17K total)
    [OK] Thread cache hit rate: 61% (63K created / 162K connections)
    [!!] Table cache hit rate: 10% (4K open / 38K opened)
    [OK] Open file limit used: 49% (4K/8K)
    [OK] Table locks acquired immediately: 96% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 1.5G/2.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        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:
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)
        table_cache (> 4096)
    
     
    #6 CHO66, Nov 25, 2012
    Last edited: Nov 25, 2012
Loading...

Share This Page