mysql tried to restart itself 3 or more times, but it failed finally

Question

it happended the second time, could be the same reason cause it.

mysql tried to restart itself 3 or more times, but it failed finally.

here is mysql log

130706 09:43:27 mysqld_safe Number of processes running now: 0
130706 09:43:27 mysqld_safe mysqld restarted
130706  9:43:28  InnoDB: Initializing buffer pool, size = 8.0M
130706  9:43:28  InnoDB: Completed initialization of buffer pool
130706  9:43:29  InnoDB: Started; log sequence number 0 2552936
130706  9:43:29 [Note] Event Scheduler: Loaded 0 events
130706  9:43:29 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.69-log'  socket: '/var/www/data/mysql.sock'  port: 3306  Source distribution
130706 09:43:34 mysqld_safe Number of processes running now: 0
130706 09:43:34 mysqld_safe mysqld restarted130706  9:43:36  InnoDB: Initializing buffer pool, size = 8.0M
130706  9:43:36  InnoDB: Completed initialization of buffer pool
130706  9:43:36  InnoDB: Started; log sequence number 0 2552936
130706  9:43:36 [Note] Event Scheduler: Loaded 0 events
130706  9:43:36 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.69-log'  socket: '/var/www/data/mysql.sock'  port: 3306  Source distribution
130706 09:43:38 mysqld_safe Number of processes running now: 0
130706 09:43:39 mysqld_safe mysqld restarted
130706  9:43:41  InnoDB: Initializing buffer pool, size = 8.0M
130706  9:43:41  InnoDB: Completed initialization of buffer pool
130706  9:43:41  InnoDB: Started; log sequence number 0 2552936
130706 09:43:42 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

and following is content of my.cnf

there are 1.5G mem on this server

[mysqld]
datadir=/var/www/data
socket=/var/www/data/mysql.sock
user=mysql
    symbolic-links=0
query_cache_size=128M
    max_connections=512
key_buffer=512M 
back_log=200
max_allowed_packet=256M
table_cache=512  
sort_buffer_size=4M
myisam_sort_buffer_size=64M
thread_cache=128
wait_timeout=300
    thread_concurrency=4
log_output=FILE 
slow-query-log=1
    slow-query-log-file=/var/www/logs/log-slow-query.log
long_query_time=1

[mysqld_safe]
log-error=/var/www/logs/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

but i can easily start it using "service mysqld start"

anyone knows what was happened? please help!

Answer 1

Check the syslogs; on CentOS, I think the file you want is /var/log/messages.

The symptoms in your MySQL error log file suggest that something is doing the equivalent of a kill -9 on the MySQL process. It's not impossible but it's extremely unlikely for MySQL to just fail to start and leave not a trace in the log files.

130706 09:43:34 mysqld_safe Number of processes running now: 0

That message is not from mysqld but rather from mysqld_safe, which is a wrapper for mysqld and is responsible for trying to restart mysqld if it stops without removing its pidfile (which it would do in the event of a clean shutdown).

I expect you'll find your system is crunched for memory and there will be references to oom-killer in the syslog.

# egrep -i "oom|kill|mysql" /var/log/messages

See if that gets you anything. If not, manually review the syslogs for the time around which the crashes occurred.

This is not the precise scenario that I identified in this answer or this one ... but the end result similar enough that it bears investigating. If it is the same ultimate cause, the delayed onset may be related to the fact that your InnoDB Buffer Pool is tiny compared to those mentioned in the other questions, so they ran into the problem at a different point during startup.

---

update: diagnosis confirmed, so the next question is how to avoid the problem.

Looking at the fact that you have 1.5 GB of RAM (I assume you mean "total") and then looking at your my.cnf, there are definitely problems, aside from the fact that it's generally not a good idea to run your web server and your MySQL server on the same machine for the simple reason that neither MySQL nor Apache is designed to be tightly constrained to a fixed allocation of memory.

But a couple of aspects of your configuration seem particularly unreasonable:

query_cache_size=128M
key_buffer=512M 

These two are fixed allocations of memory that you probably can't afford... right here, at startup, MySQL is allocating 128MB for the query cache and 512MB for the MySQL key cache (key_buffer). Reducing these dramatically will of course reduce your performance, but you don't have the memory to spare for these two things. Set them to small values, such as 8M.

Of course, I don't think 8M would ordinarily be a reasonable value on a dedicated server, but again, these are fixed allocations at startup, and we're dealing with system that is very constrained for memory.

Those two are the big ones, and shrinking them down to size will probably get you going again depending on how severely constrained for memory you are.

There are a few others that I would say are less important but still seem out-of-range given the size of the server and the fact that it isn't dedicated to MySQL only.

max_connections=512

Surely not. Reducing this isn't going to save much if anything at startup because the allocations don't happen until those connections occur... but if you really have a need for this, then you're really, really dealing with a server that's far too small.

max_allowed_packet=256M

The default for max_allowed_packet is 1M. Unless you changed this for a reason, it's higher than necessary. Reducing this won't likely save anything unless you have a workload that requires this, but this amount of memory could theoretically be allocated by every client thread at the same time which of course wouldn't fly very far.

thread_cache=128

I'm not finding thread_cache in the documentation, so what is this? thread_cache_size I assume, so this is also too large and should probably be lowered to a very small number (such as "8"). No real harm done except that the threads used by recently-disconnected clients linger in memory after they're not needed, taking up space that you can't really spare.

See also: How MySQL Uses Memory