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

Discussion in 'Workarounds and Optimization' started by david.cocos, May 19, 2014.

  1. david.cocos

    david.cocos Member

    Joined:
    May 19, 2014
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,
    I have a problem with my VPM server. It runs on a 4 core processor and 32GB of RAM. The server has a single domain hosted on it with aprox 500 real time users on it. The website freezes for 3-5 minutes, and this happens 5-10 times per day.

    MySQL Tunner log:
    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 13G (Tables: 39)
    [--] Data in InnoDB tables: 2G (Tables: 233)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 41
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 19h 31m 4s (39M q [161.618 qps], 3M conn, TX: 107B, RX: 31B)
    [--] Reads / Writes: 56% / 44%
    [--] Total buffers: 8.6G global + 14.6M per thread (151 max threads)
    [OK] Maximum possible memory usage: 10.8G (33% of installed RAM)
    [OK] Slow queries: 0% (20K/39M)
    [OK] Highest usage of available connections: 63% (96/151)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/2.9G
    [OK] Key buffer hit rate: 99.9% (658M cached / 346K reads)
    [OK] Query cache efficiency: 41.2% (5M cached / 12M selects)
    [!!] Query cache prunes per day: 321735
    [OK] Sorts requiring temporary tables: 0% (8K temp sorts / 2M sorts)
    [!!] Temporary tables created on disk: 47% (14M on disk / 30M total)
    [OK] Thread cache hit rate: 99% (96 created / 3M connections)
    [!!] Table cache hit rate: 0% (536 open / 253K opened)
    [OK] Open file limit used: 0% (178/50K)
    [OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
    [OK] InnoDB buffer pool / data size: 8.0G/2.3G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increasing the query_cache size over 128M may reduce performance
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_size (> 256M) [see warning above]
        table_cache (> 4096)
    
    my.cnf:
    Code:
    [mysqld]
    innodb_file_per_table=1
    open_files_limit=50000
    thread_cache_size = 16K
    tmp_table_size = 256M
    max_heap_table_size = 256M
    query_cache_size = 256M
    query_cache_limit = 8M
    table_cache = 4K
    table_definition_cache = 8K
    key_buffer_size = 128M
    join_buffer_size = 12M
    innodb_buffer_pool_size = 8G
    # log_queries_not_using_indexes
    slow_query_log=1
    slow_query_log_file=/var/log/mysql-slow-query.log
    long_query_time=1
    log_error = /var/log/mysql/error.log
    
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    
    Please help with a solution,
    Thanks
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,766
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    To clarify, does the entire website fail to load, or is it only pages that utilize MySQL? Do you notice any error messages in the MySQL error log when this happens?

    Thank you.
     
  3. david.cocos

    david.cocos Member

    Joined:
    May 19, 2014
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    The entire VPS freezes. I didn't find anything specific in the error log. Which error log would be most useful?
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,766
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    If it's an OpenVZ/Virtuozzo VPS, check the /proc/user_beancounters file to see if the VPS is reaching any resource limits imposed from the node.

    Thank you.
     
  5. david.cocos

    david.cocos Member

    Joined:
    May 19, 2014
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    It is a Virtuozzo VPS. This is what the /proc/user_beancounters file is showing:
    Code:
    Version: 2.5
           uid  resource                     held              maxheld              barrier                limit              failcnt
        99837:  kmemsize                237096228            244813824            773094113            858993459                    0
                lockedpages                     0                    0                 2059                 2059                    0
                privvmpages               3134756              3193840              7549747              8388608                    0
                shmpages                     1950                 1950               524288               524288                    0
                dummy                           0                    0  9223372036854775807  9223372036854775807                    0
                numproc                       526                  597                 1600                 1600                    0
                physpages                 7416304              7469372              7549747              8388608                    0
                vmguarpages                     0                    0              4194304  9223372036854775807                    0
                oomguarpages              1525539              1527215              4194304  9223372036854775807                    0
                numtcpsock                    329                  370                 1550                 1550                    0
                numflock                      288                  301                 1000                 1100                    0
                numpty                          0                    0                  102                  102                    0
                numsiginfo                      0                   48                 1024                 1024                    0
                tcpsndbuf                 3412184              7202544             14880000             22320000                  677
                tcprcvbuf                 1857760              2025976             14880000             22320000                    0
                othersockbuf               339752               650432             14400000             21600000                    0
                dgramrcvbuf                     0                13872             13440000             13440000                    0
                numothersock                  351                  404                 1700                 1700                    0
                dcachesize              128847168            128849018            115964116            128849018                    0
                numfile                      5745                 6825                38496                38496                    0
                dummy                           0                    0  9223372036854775807  9223372036854775807                    0
                dummy                           0                    0  9223372036854775807  9223372036854775807                    0
                dummy                           0                    0  9223372036854775807  9223372036854775807                    0
                numiptent                      70                   70  9223372036854775807  9223372036854775807                    0
    
    Thank you

    - - - Updated - - -

    This is the original file.
     

    Attached Files:

  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,766
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Notice the fail count for the "tcpsndbuf" value. Please report this to your VPS hosting provider and have them check to see if they can increase that limit from the VPS hardware node.

    Thank you.
     
  7. david.cocos

    david.cocos Member

    Joined:
    May 19, 2014
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    My VPS Hosting Provider said: "the "tcpsndbuf" value is already the highest value we can offer". So it wasn't very helpful. The specs of the server are: Intel(R) Xeon(R) CPU E5-2620 v2 8 cores, 2,1 GHz, 32 GB RAM, 500 GB SSD. I want to specify that the server uses very few memory. Also max_connections is set to 151. Could this also be a problem?
    Thank you for your help
     
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,766
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You are welcome to gather user-feedback for MySQL optimization on this thread. I'm not sure what else would cause your VPS to stop responding. You may want to have you provider address the issue with the VPS failing to respond if it continues to be an issue, or try reviewing the output of additional logs if it happens again (/var/log/messages or /var/log/dmesg).

    Thank you.
     
  9. david.cocos

    david.cocos Member

    Joined:
    May 19, 2014
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have also found this with the "sar -P ALL" command:
    Code:
    Average:        CPU     %user     %nice   %system   %iowait    %steal     %idle
    Average:        all     66.34      0.22     14.09      0.01      0.42     18.92
    Average:          0      0.00      0.00      0.00      0.00      0.00    100.00
    Average:          1     74.88      0.26     16.07      0.02      0.52      8.25
    Average:          2      0.00      0.00      0.00      0.00      0.00    100.00
    Average:          3     74.89      0.24     15.98      0.01      0.46      8.42
    Average:          4      0.00      0.00      0.00      0.00      0.00    100.00
    Average:          5     74.90      0.23     15.82      0.02      0.47      8.56
    Average:          6      0.00      0.00      0.00      0.00      0.00    100.00
    Average:          7     75.04      0.25     15.78      0.01      0.46      8.47
    Average:          8      0.00      0.00      0.00      0.00      0.00      0.00
    
    I notice that for 4 of my cores %idle is 100%. Is this ok and if not how can I solve this?
     
Loading...

Share This Page