【讨论中】mysql隔几个月就当掉

ccchallenge

mysql 数据库隔几个月就当掉，下面是错误log.是由于死锁引起的吗？

1. 
   
2. select * from table123
   
3. Trx read view will not see trx with id >= 0 3611757250, sees < 0 3611754257
   
4. ---TRANSACTION 0 3611757091, ACTIVE 52 sec, process no 8304, OS thread id 2916936592
   
5. mysql tables in use 1, locked 0
   
6. MySQL thread id 32608, query id 780248056 localhost root Sending data
   
7. select * from table123
   
8. Trx read view will not see trx with id >= 0 3611757092, sees < 0 3611736852
   
9. ---TRANSACTION 0 3611737563, COMMITTED IN MEMORY, process no 8304, OS thread id 2888760208 committing, thread declared inside InnoDB 498
   
10. mysql tables in use 1, locked 1
    
11. , undo log entries 1
    
12. MySQL thread id 30043, query id 780228528 localhost root end
    
13. update `ignore` set `i_count`=2249 , `updatetime`=1371097812
    
14. --------
    
15. FILE I/O
    
16. --------
    
17. I/O thread 0 state: waiting for i/o request (insert buffer thread)
    
18. I/O thread 1 state: waiting for i/o request (log thread)
    
19. I/O thread 2 state: waiting for i/o request (read thread)
    
20. I/O thread 3 state: waiting for i/o request (write thread)
    
21. Pending normal aio reads: 0, aio writes: 0,
    
22. ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    
23. Pending flushes (fsync) log: 1; buffer pool: 0
    
24. 92 OS file reads, 2583849 OS file writes, 2423491 OS fsyncs
    
25. 0.00 reads/s, 0 avg bytes/read, 0.04 writes/s, 0.04 fsyncs/s
    
26. -------------------------------------
    
27. INSERT BUFFER AND ADAPTIVE HASH INDEX
    
28. -------------------------------------
    
29. Ibuf: size 1, free list len 0, seg size 2,
    
30. 0 inserts, 0 merged recs, 0 merges
    
31. Hash table size 34679, used cells 5193, node heap has 7 buffer(s)
    
32. 0.00 hash searches/s, 0.00 non-hash searches/s
    
33. ---
    
34. LOG
    
35. ---
    
36. Log sequence number 0 917197117
    
37. Log flushed up to   0 917196041
    
38. Last checkpoint at  0 917195741
    
39. 1 pending log writes, 0 pending chkp writes
    
40. 1789840 log i/o's done, 0.04 log i/o's/second
    
41. ----------------------
    
42. BUFFER POOL AND MEMORY
    
43. ----------------------
    
44. Total memory allocated 18326854; in additional pool allocated 1039488
    
45. Buffer pool size   512
    
46. Free buffers       353
    
47. Database pages     152
    
48. Modified db pages  4
    
49. Pending reads 0
    
50. Pending writes: LRU 0, flush list 0, single page 0
    
51. Pages read 116, created 36, written 532066
    
52. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    
53. Buffer pool hit rate 1000 / 1000
    
54. --------------
    
55. ROW OPERATIONS
    
56. --------------
    
57. 1 queries inside InnoDB, 0 queries in queue
    
58. 19 read views open inside InnoDB
    
59. Main thread process no. 8304, id 2996677520, state: flushing log
    
60. Number of rows inserted 67316, updated 75290, deleted 0, read 1762953908
    
61. 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 5435.60 reads/s
    
62. ----------------------------
    
63. END OF INNODB MONITOR OUTPUT
    
64. ============================
    
65. InnoDB: ###### Diagnostic info printed to the standard error stream
    
66. 
    
67. Number of processes running now: 0
    
68. 130628 21:35:03  mysqld restarted
    
69. 130628 21:35:05 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
    
70. 130628 21:35:05 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
    
71. InnoDB: The log sequence number in ibdata files does not match
    
72. InnoDB: the log sequence number in the ib_logfiles!
    
73. 130628 21:35:06  InnoDB: Database was not shut down normally!
    
74. InnoDB: Starting crash recovery.
    
75. InnoDB: Reading tablespace information from the .ibd files...
    
76. InnoDB: Restoring possible half-written data pages from the doublewrite
    
77. InnoDB: buffer...
    
78. 130628 21:35:07  InnoDB: Started; log sequence number 0 938296578
    
79. 130628 21:35:07 [Note] /usr/libexec/mysqld: ready for connections.
    
80. Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
    
81. 
    
82. Number of processes running now: 0
    
83. 130628 23:35:27  mysqld restarted
    
84. 130628 23:35:32 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
    
85. 130628 23:35:32 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
    
86. InnoDB: The log sequence number in ibdata files does not match
    
87. InnoDB: the log sequence number in the ib_logfiles!
    
88. 130628 23:35:33  InnoDB: Database was not shut down normally!
    
89. InnoDB: Starting crash recovery.
    
90. InnoDB: Reading tablespace information from the .ibd files...
    
91. InnoDB: Restoring possible half-written data pages from the doublewrite
    
92. InnoDB: buffer...
    
93. 130628 23:35:36  InnoDB: Started; log sequence number 0 938303752
    
94. 130628 23:35:39 [Note] /usr/libexec/mysqld: ready for connections.
    
95. Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
    
96. 
    
97. Number of processes running now: 0
    
98. 130629 00:27:24  mysqld restarted
    
99. 130629  0:27:35 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
    
100. 130629  0:27:35 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
     
101. 130629 00:34:59  mysqld ended
     
102. 
     
103. 130629 09:38:36  mysqld started
     
104. 130629  9:38:37 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
     
105. 130629  9:38:37 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
     
106. InnoDB: The log sequence number in ibdata files does not match
     
107. InnoDB: the log sequence number in the ib_logfiles!
     
108. 130629  9:38:38  InnoDB: Database was not shut down normally!
     
109. InnoDB: Starting crash recovery.
     
110. InnoDB: Reading tablespace information from the .ibd files...
     
111. InnoDB: Restoring possible half-written data pages from the doublewrite
     
112. InnoDB: buffer...
     
113. 130629  9:38:39  InnoDB: Started; log sequence number 0 938303882
     
114. 130629  9:38:40 [Note] /usr/libexec/mysqld: ready for connections.
     
115. Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
     
116. 
     
117. Number of processes running now: 0
     
118. 130629 12:10:54  mysqld restarted
     
119. 130629 12:11:08 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
     
120. 130629 12:11:08 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
     
121. InnoDB: The log sequence number in ibdata files does not match
     
122. InnoDB: the log sequence number in the ib_logfiles!
     
123. 130629 12:11:19  InnoDB: Database was not shut down normally!
     
124. InnoDB: Starting crash recovery.
     
125. InnoDB: Reading tablespace information from the .ibd files...
     
126. InnoDB: Restoring possible half-written data pages from the doublewrite
     
127. InnoDB: buffer...
     
128. 130629 12:12:25  InnoDB: Started; log sequence number 0 938327306
     
129. 130629 12:16:09  mysqld ended
     
130. 
     
131. 130629 13:24:50  mysqld started
     
132. 130629 13:24:50 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
     
133. 130629 13:24:50 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
     
134. InnoDB: The log sequence number in ibdata files does not match
     
135. InnoDB: the log sequence number in the ib_logfiles!
     
136. 130629 13:24:50  InnoDB: Database was not shut down normally!
     
137. InnoDB: Starting crash recovery.
     
138. InnoDB: Reading tablespace information from the .ibd files...
     
139. InnoDB: Restoring possible half-written data pages from the doublewrite
     
140. InnoDB: buffer...
     
141. 130629 13:24:51  InnoDB: Started; log sequence number 0 938327345
     
142. 130629 13:24:51 [Note] /usr/libexec/mysqld: ready for connections.
     
143. Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
     
144. 130629 13:27:29 [Note] /usr/libexec/mysqld: Normal shutdown
     
145. 
     
146. 130629 13:27:29  InnoDB: Starting shutdown...
     
147. 130629 13:27:30  InnoDB: Shutdown completed; log sequence number 0 938327345
     
148. 130629 13:27:30 [Note] /usr/libexec/mysqld: Shutdown complete
     
149. 
     
150. 130629 13:27:30  mysqld ended
     
151. 
     
152. 130629 13:28:54  mysqld started
     
153. 130629 13:28:54 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
     
154. 130629 13:28:54 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
     
155. 130629 13:28:55  InnoDB: Started; log sequence number 0 938327345
     
156. 130629 13:28:55 [Note] /usr/libexec/mysqld: ready for connections.
     
157. Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
     

复制代码

小版主杀手

'max_join_size' ---这里优化下  sql慢语句查看下  

ccchallenge

回复 2# 小版主杀手

    多谢回复。
    重启应该不是max_join_size引起的吧。因为max_join_size这个错误出来之前就已经开始重启了。

Shaquile

92 OS file reads, 2583849 OS file writes, 2423491 OS fsyncs

这么多文件写！

你的这个SQL要更新多少条记录？很多吗？if yes， 试一下分批更新

1. update `ignore` set `i_count`=2249 , `updatetime`=1371097812

复制代码

cenalulu

你这边所有导入日志都显示mysql是正常shutdown： 130629 13:27:30  mysqld ended
有没有再往上一点日志？

ccchallenge

回复 5# cenalulu


    mysqld.zip (380.9 KB, 下载次数: 1)

2013-07-03 08:54 上传

点击文件名下载附件
log

多谢回复，这是全部的日志。

cenalulu

从日志来看mysql连最基础的trace都没打出来，不像是遇到bug什么突然推出的。感觉是操作系统级别的kill或者bug造成。
最好能结合宕机时的现场环境分析。