Mysql Server Crashing Regularly

Question

Error Log: MYSQLD log

140925 20:47:19 mysqld_safe Number of processes running now: 0
140925 20:47:19 mysqld_safe mysqld restarted    
140925 20:47:26  InnoDB: Initializing buffer pool, size = 56.0M    
140925 20:47:26  InnoDB: Completed initialization of buffer pool    
InnoDB: **The log sequence number in ibdata files does not match**    
InnoDB: **the log sequence number in the ib_logfiles!**    
140925 20:47:26  InnoDB: Database was not shut down normally!    
InnoDB: Starting crash recovery.    
InnoDB: Reading tablespace information from the .ibd files...    
InnoDB: Restoring possible half-written data pages from the doublewrite    
InnoDB: buffer...    
140925 20:47:32  InnoDB: Started; log sequence number 4 3219462995    
140925 20:47:32 [Note] Event Scheduler: Loaded 0 events    
140925 20:47:32 [Note] /usr/libexec/mysqld: ready for connections.    
Version: '5.1.71'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution    
Messages Log :

Sep 25 08:15:02 s1 kernel: Out of memory: Kill process 28796 (php-cgi) score 18 or sacrifice child        
Sep 25 08:15:02 s1 kernel: Killed process 28796, UID 528, (php-cgi) total-vm:340760kB, anon-         rss:40048kB, file-rss:21088kB    
Sep 25 08:15:03 s1 kernel: php-cgi invoked oom-killer: gfp_mask=0x280da, order=0, oom_adj=0,     oom_score_adj=0    
Sep 25 08:15:03 s1 kernel: php-cgi cpuset=/ mems_allowed=0   

Server Details:
Operating system: Centos 6.2 linux server,
Memory: 4GB
Swap Size: 2GB

Steps taken to resolve the Issue:

• Enabled innodb_force_recovery=4 to commit the .ibd files and to mysql server.
• Tried installing MYSQLTUNER to monitor query activities.However cudnt install it.

Behavior

Have to manually restart it after it shuts down. Not able to restart it automatically using mysqlsafe
Have been seeing this issue since past few days and also a suspect spike in number of hits which reflects in my AW stats.
Will appreciate if someone help me know where to look for solutions and help me configure the mysql config file for a proper performance and memory usage.

My.conf settings:

[mysqld] datadir=/var/lib/        
mysql socket=/var/lib/mysql/mysql.sock    
 user=mysql    
 symbolic-links=0    
 innodb_file_per_table = 1    
 innodb_buffer_pool_size = 56M    
 thread_concurrency = 8    
 query_cache_size = 32M    
 thread_cache_size = 8    
 myisam_sort_buffer_size = 64M    
 read_rnd_buffer_size = 8M    
 read_buffer_size = 2M    
 sort_buffer_size = 2M    
 table_cache = 512    
 max_allowed_packet = 1M    
 key_buffer = 384M    
 [mysqld_safe] log-error=/var/log/mysqld.log    
 pid-file=/var/run/mysqld/mysqld.pid 

Answer 1

Something is eating your Ram, possibly the mysql recover. Add more swap (dd if=/dev/zero count=1000 bs=1M of=swapfile; mkswap swapfile; swapon swapfile) go to singleuser mode (init 1) so that nothing else is running and start your mysql recover. Watch with top or htop for memory usage.

Answer 2

Can you give the o/p of

free -m
top

I see that it is OOM killer. It means that the server is using too much memory and it has to kill the process which is using the most. As MySQL was using more, kernal killed MySQL. Our goal is to minimize MySQL memory.

I think it is not dedicated MySQL database server.

We can reduce the memory usage of mysql server by doing this

1) Remove the per thread buffers read_buffer_size and read_rnd_buffer_size and from my.cnf (use default value)

Rather than increasing these variables, try to improve the queries

2) Check how much of data is using InnoDB

select sum(data_length+index_length) from information_schema.tables where engine='InnoDB';

select sum(data_length) from information_schema.tables where engine='MyISAM';

I see that the innodb_buffer_pool_size is already on the lower side. If the size of MyISAM is less, you can decrease the value of key_buffer

3) You can also disable query cache.

query_cache_size = 0

Answer 3

You might have memory that MySQL is not able to address because of a memory leak. Running the following command as Root will free unused pagecache, dentries and inodes.

free -h && sync && echo 3 > /proc/sys/vm/drop_caches && free -h