mysql going down, even though RAM has lot of memory available

[PankajKumar]

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.

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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

 

[PankajKumar]

Updated /etc/my.cnf to

[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.

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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

 

[PankajKumar]

Still i see that mysql is going down almost once in an hour, any recommendations?

 

[Infopro]

[--] Up for: 19m 54s (15K q [13.205 qps], 536 conn, TX: 164M, RX: 1M)
[--] Up for: 5m 22s (2K q [7.898 qps], 237 conn, TX: 16M, RX: 287K)

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:

MySQL started within last 24 hours - recommendations may be inaccurate

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

 

[PankajKumar]

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.

 

[thinkbot]

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

 

[PankajKumar]

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.

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

 

[ravi9]

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.

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).

 

[PankajKumar]

@thinkbot

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

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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.

 

[thinkbot]

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$ ?

 

[PankajKumar]

Yes, i am paying 49.99 for HG VPS3 level.