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 going down, even though RAM has lot of memory available

Discussion in 'Workarounds and Optimization' started by PankajKumar, Nov 30, 2013.

  1. PankajKumar

    PankajKumar Member

    Joined:
    Nov 30, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi,

    My server load remains in the range of ~0.5 and sometimes go to 3-4 and my RAM always remains ~400MB free.

    Still from last few days my MySQL server is going down almost once in an hour. I have a cronjob running to monitor the MySql process and start it if down, so mostly users are not affected.

    I ran mysqltuner.pl and here is the output.

    Code:
     >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 1G (Tables: 604)
    [--] Data in InnoDB tables: 190M (Tables: 70)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 11)
    [!!] Total fragmented tables: 128
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 19m 54s (15K q [13.205 qps], 536 conn, TX: 164M, RX: 1M)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (75% of installed RAM)
    [OK] Slow queries: 0% (1/15K)
    [OK] Highest usage of available connections: 2% (4/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/360.8M
    [OK] Key buffer hit rate: 99.5% (247K cached / 1K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3K sorts)
    [!!] Temporary tables created on disk: 28% (902 on disk / 3K total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 50% (400 open / 798 opened)
    [OK] Open file limit used: 16% (705/4K)
    [OK] Table locks acquired immediately: 100% (16K immediate / 16K locks)
    [!!] InnoDB data size / buffer pool: 190.9M/128.0M
    
    -------- 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
        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
    Variables to adjust:
        query_cache_size (>= 8M)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        innodb_buffer_pool_size (>= 190M)
    
    I restart mysql with command "/etc/rc.d/init.d/mysql restart", I don't have much knowledge on MySql configuration settings, can someone help me with the recommended settings and explain what the report is saying?

    Thanks,
    Pankaj
     
  2. PankajKumar

    PankajKumar Member

    Joined:
    Nov 30, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Updated /etc/my.cnf to

    Code:
    [mysqld]
    open_files_limit=4262
    log-slow-queries=/var/lib/mysql/slow.log
    query_cache_size=8M
    tmp_table_size=18M
    max_heap_table_size=18M
    thread_cache_size=8
    innodb_buffer_pool_size=192M
    key_buffer_size=368M
    
    and then when I ran mysqltuner.pl, it gives following output.

    Code:
     >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 72M (Tables: 295)
    [--] Data in InnoDB tables: 170M (Tables: 64)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 65
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5m 22s (2K q [7.898 qps], 237 conn, TX: 16M, RX: 287K)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 602.0M global + 2.8M per thread (151 max threads)
    [!!] Maximum possible memory usage: 1017.2M (132% of installed RAM)
    [OK] Slow queries: 0% (0/2K)
    [OK] Highest usage of available connections: 1% (3/151)
    [OK] Key buffer size / total MyISAM indexes: 368.0M/31.6M
    [OK] Key buffer hit rate: 97.1% (16K cached / 473 reads)
    [OK] Query cache efficiency: 70.1% (1K cached / 1K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 173 sorts)
    [OK] Temporary tables created on disk: 25% (64 on disk / 248 total)
    [OK] Thread cache hit rate: 98% (3 created / 237 connections)
    [OK] Table cache hit rate: 89% (62 open / 69 opened)
    [OK] Open file limit used: 2% (91/4K)
    [OK] Table locks acquired immediately: 100% (776 immediate / 776 locks)
    [OK] InnoDB data size / buffer pool: 170.0M/192.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
    
     
  3. PankajKumar

    PankajKumar Member

    Joined:
    Nov 30, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Still i see that mysql is going down almost once in an hour, any recommendations?
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,456
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    The server needs to be up for at least 24 hours to get accurate results of the test. As it says in what you posted:
    Question, what leads you to believe you need to tweak my.cnf at all?

    Go here:
    WHM » Server Status » Daily Process Log
    At top of that page, are dates that can be clicked thru. Do you see any accounts showing up in red here on the dates the server is crashing?

    More about that page, here:
    Daily Process Log - cPanel Documentation
     
  5. PankajKumar

    PankajKumar Member

    Joined:
    Nov 30, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    That is the strange thing... even when load average is <1 and there is ~400MB free RAM available, I am not sure why mysql is going down every 15-20 mins...

    When I run the mysqltuner, it gave the top results and variables to adjust, so I changed my.cnf to try if it works but it seems that it has no effect at all. Should I revert the my.cnf changes and see how it goes?

    - - - Updated - - -

    Daily Process logs doesnt show anything alarming in MySql Processes or CPU usage etc.
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    based on your current results it seems like you are using VPS with 768MB of memory
    Is this guaranteed RAM memory ? describe your spec in details

    I guess that OS is killing your mysql since you are using more RAM then possible on your VPS

    please adjust your my.cnf like this

    [mysqld]
    skip-name-resolve
    max_connections = 75
    max_user_connections = 50

    open_files_limit=4262
    log-slow-queries=/var/lib/mysql/slow.log
    query_cache_size=8M
    tmp_table_size=5M
    max_heap_table_size=5M
    thread_cache_size=8
    innodb_buffer_pool_size=192M
    key_buffer_size=50M
    sort_buffer_size=256K
     
  7. PankajKumar

    PankajKumar Member

    Joined:
    Nov 30, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Yes, I have HostGator VPS Level 3 account with 768MB of RAM. Most of the times, I see that RAM is only 50% used, so I am not sure if memory is the issue here.

    OK, I have changed the settings as suggested and restarted MySql server. I will update if the problem gets solved or not.

     
  8. ravi9

    ravi9 Well-Known Member

    Joined:
    Oct 31, 2013
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Website Owner
    Have to contacted HostGator about this. I have read many bad review about HG in recent times (after EIG acquired HG). This may be a problem from node (like oversold node).
     
  9. PankajKumar

    PankajKumar Member

    Joined:
    Nov 30, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    @thinkbot

    After using the setting given by you, mysql is working fine for ~24hours now. Here is the output of mysqltuner now, anymore recommendations?

    Code:
     >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 70M (Tables: 295)
    [--] Data in InnoDB tables: 170M (Tables: 64)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 70
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 23h 38m 17s (291K q [3.425 qps], 15K conn, TX: 2B, RX: 35M)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 271.0M global + 1.0M per thread (75 max threads)
    [OK] Maximum possible memory usage: 346.0M (45% of installed RAM)
    [OK] Slow queries: 0% (1/291K)
    [OK] Highest usage of available connections: 18% (14/75)
    [OK] Key buffer size / total MyISAM indexes: 50.0M/31.5M
    [OK] Key buffer hit rate: 100.0% (3M cached / 734 reads)
    [OK] Query cache efficiency: 81.9% (201K cached / 246K selects)
    [!!] Query cache prunes per day: 28020
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
    [OK] Temporary tables created on disk: 22% (5K on disk / 24K total)
    [OK] Thread cache hit rate: 99% (21 created / 15K connections)
    [OK] Table cache hit rate: 26% (400 open / 1K opened)
    [OK] Open file limit used: 14% (639/4K)
    [OK] Table locks acquired immediately: 99% (56K immediate / 56K locks)
    [!!] Connections aborted: 9%
    [OK] InnoDB data size / buffer pool: 170.0M/192.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        query_cache_size (> 8M)
    
    - - - Updated - - -

    @ravi9

    I have contacted HostGator about they asked me to check the load and let them know the timing when load goes high so that they can analyze the issue.

    I am running a script to check the load at regular interval of 5 secs, will check with them if load goes high. As of know load is fine and mysql is also fine after @thinkbot recommendations.
     
  10. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Yep, that was most likely memory issue
    Now it looks good
    [OK] Maximum possible memory usage: 346.0M (45% of installed RAM)

    btw. you are paying for that hostgator vps with cpanel 49.99$ ?
     
  11. PankajKumar

    PankajKumar Member

    Joined:
    Nov 30, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Yes, i am paying 49.99 for HG VPS3 level.
     
Loading...

Share This Page