Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

From the past few days, our MySQL database instance has crashed and had to be restarted a few times. My MySQL version is 5.1.57-log. The CPU and memory usage was way below 50% during the crash time. I am not a MySQL database expert but have been trying to make sense of the logs (full log available on pastebin):

    [-]InnoDB: Warning: a long semaphore wait:--Thread 1258785088 has waited at ./include/trx0sys.ic line 120 for 241.00 seconds
 the semaphore:X-lock (wait_ex) on RW-latch at 0x2aaaac992958 created in file buf/buf0buf.c line 682a writer (thread id 1258785088) has reserved it in 
mode wait exclusivenumber of readers 1, waiters flag 0, lock_word: ffffffffffffffffLast time read locked in file buf/buf0flu.c line 1169
Last time write locked in file ./include/trx0sys.ic line 120InnoDB: Warning: a long semaphore wait:--Thread 1245137216 has waited at trx/trx0trx.c 
line 708 for 241.00 seconds the semaphore:Mutex at 0x107e1ea8 created file srv/srv0srv.c line 943, lock var 1waiters flag 1InnoDB: Warning: a long semaphore wait:--Thread 1206896960 has waited at srv/srv0srv.c 
line 2148 for 241.00 seconds the semaphore:Mutex at 0x107e1ea8 created file srv/srv0srv.c line 943, lock var 1waiters flag 1InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:InnoDB: Pending preads 0, pwrites 0=====================================121204 15:46:04 
INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 30 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 1354451 1_second, 1354448 sleeps, 132169 10_second, 33616 background, 33616 flushsrv_master_thread log flush and writes: 1363178----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 180507, signal count 179839--Thread 1258785088 has waited at ./include/trx0sys.ic line 120 for 242.00 seconds the semaphore:X-lock (wait_ex) on RW-latch at 0x2aaaac992958 created in file buf/buf0buf.c line 682a writer (thread id 1258785088) has reserved it in mode wait exclusivenumber of readers 1, waiters flag 0, lock_word: ffffffffffffffffLast time read locked in file buf/buf0flu.c line 1169Last time write locked in file ./include/trx0sys.ic line 120--Thr
    ...
    ...
    ...
    rv/srv0srv.c line 2148 for 242.00 seconds the semaphore:Mutex at 0x107e1ea8 created file srv/srv0srv.c line 943, lock var 1waiters flag 1--Thread 1261394240 has waited at trx/trx0trx.c line 199 for 237.00 seconds the semaphore:Mutex at 0x107e1ea8 created file srv/srv0srv.c line 943, lock var 1waiters flag 1--Thread 1249954112 has waited at trx/trx0trx.c line 708 for 232.00 seconds the semaphore:Mutex at 0x107e1ea8 created file srv/srv0srv.c line 943, lock var 1waiters flag 1--Thread 1098996032 has waited at trx/trx0trx.c line 708 for 229.00 seconds the semaphore:Mutex at 0x107e1ea8 created file srv/srv0srv.c line 943, lock var 1waiters flag 1--Thread 1257982272 has waited at trx/trx0trx.c line 708 for 229.00 seconds the semaphore:Mutex at 0x107e1ea8 created file srv/srv0srv.c line 943, lock var 1waiters flag 1--Thread 1260992832 has waited at trx/trx0trx.c line 199 for 227.00 seconds the semaphore:Mutex at 0x107e1ea8 created file srv/srv0srv.c line 943, lock var 1waiters flag 1--Thread 1239116096 has waited at trx/trx0trx.c line 344 for 221.00 seconds the semaphore:Mutex at 0x107e1ea8 created file srv/srv0srv.c line 943, lock var 1waiters flag 1Mutex spin waits 18559608, rounds 5933417, OS waits 37160RW-shared spins 134274, OS waits 133041; RW-excl spins 259, OS waits 10205Spin rounds per wait: 0.32 mutex, 30.00 RW-shared, 1184.19 RW-exclFAIL TO OBTAIN KERNEL MUTEX, SKIP LOCK INFO PRINTING--------FILE I/O--------I/O thread 0 state: waiting for i/o request (insert buffer thread)I/O thread 1 state: waiting for i/o request (log thread)I/O thread 2 state: waiting for i/o request (read thread)I/O thread 3 state: waiting for i/o request (read thread)I/O thread 4 state: waiting for i/o request (read thread)I/O thread 5 state: waiting for i/o request (read thread)I/O thread 6 state: waiting for i/o request (write thread)I/O thread 7 state: waiting for i/o request (write thre

A first look at it says that a thread has locked the resources and other threads are waiting for that resource to be released. Looks like an update statement has locked the table and other queries are waiting on it. But even if that is the case then why has MySQL crashed? Can some one please help me out and provide some more detail and give some pointers in how should I go about debugging this case?

share|improve this question
1  
Can you paste the full log (or pastebin link) that includes the actual crash or restart entries? Also you list your mysql server is 5.1.57, but tag it as mysql-5.5. I assume the 5.1.57 is correct, and if so, please remove the mysql-5.5 tag. –  Derek Downey Dec 6 '12 at 15:52
    
I had retagged this as mysql-5.1. I agree with @DTest: We need to see the actual logs entries showing actual crash and subsequent restart. –  RolandoMySQLDBA Dec 6 '12 at 18:03
    
pastebin.com/XyVzTmsS –  Egalitarian Dec 6 '12 at 18:18
    
@egalitarian this just looks like output showing a long wait time, not a 'crash'..why do you think it's crashing/restarting? –  Derek Downey Dec 6 '12 at 18:22
1  
And what I'm asking for is the actual 'restart' messages. You aren't providing those. Your similar question appears that it eats up all the max_connections so you can't connect to the server. Is this what's happening? –  Derek Downey Dec 6 '12 at 20:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.