我遇到一個問題，偶爾（每週一次或兩次，不一定是在高峰時段）對某個資料庫（我最大的網站的資料庫）的查詢突然停止回應大約一個小時。查詢開始堆積，大部分處於「發送資料」和「統計」狀態，最終負載上升到> 40，整個伺服器停止運作。
當發生此問題時，如果我停用此站點，同一伺服器上的所有其他站點和資料庫都會正常運作。
當它沒有響應時，我有“SHOW ENGINE INNODB STATUS”的輸出，我想知道是否有人可以告訴我它是否顯示任何異常 - 我已經刪除了列出所有待處理查詢的“TRANSACTIONS”部分，我可以添加如果有幫助的話。
5.1.70-cll MySQL 社群伺服器，無 Innodb 插件，CentOS 5.9
提前致謝。
=====================================
130901 17:10:26 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 25977, signal count 14540
--Thread 140248378771776 has waited at btr/btr0cur.c line 467 for 1.00 seconds the semaphore:
S-lock on RW-latch at 0x7f8e273f8ae8 created in file buf/buf0buf.c line 550
a writer (thread id 140248378771776) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0sea.c line 794
Last time write locked in file buf/buf0buf.c line 1820
--Thread 140248434157888 has waited at btr/btr0cur.c line 467 for 3.00 seconds the semaphore:
S-lock on RW-latch at 0x7f8e272877c8 created in file buf/buf0buf.c line 550
a writer (thread id 140248434157888) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0sea.c line 794
Last time write locked in file buf/buf0buf.c line 1820
Mutex spin waits 0, rounds 550914, OS waits 8104
RW-shared spins 34629, OS waits 16634; RW-excl spins 2759, OS waits 586
--------
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: doing file i/o (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 172, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
105909 OS file reads, 8365 OS file writes, 4079 OS fsyncs
1 pending preads, 1 pending pwrites
106.42 reads/s, 19475 avg bytes/read, 9.50 writes/s, 4.68 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 20, free list len 70, seg size 91,
1099 inserts, 8617 merged recs, 2154 merges
Hash table size 1888559, node heap has 2598 buffer(s)
8189.64 hash searches/s, 941.80 non-hash searches/s
---
LOG
---
Log sequence number 196 2706819157
Log flushed up to 196 2706818752
Last checkpoint at 196 2705105219
0 pending log writes, 0 pending chkp writes
3880 log i/o's done, 4.42 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1061750976; in additional pool allocated 1048576
Dictionary memory allocated 1530760
Buffer pool size 58240
Free buffers 0
Database pages 55642
Modified db pages 1543
Pending reads 172
Pending writes: LRU 120, flush list 0, single page 0
Pages read 127164, created 59, written 5866
126.52 reads/s, 0.08 creates/s, 5.82 writes/s
Buffer pool hit rate 996 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 27 queries in queue
17 read views open inside InnoDB
Main thread process no. 2504, id 140248394586432, state: sleeping
Number of rows inserted 963, updated 4397, deleted 5, read 43384960
1.56 inserts/s, 4.92 updates/s, 0.00 deletes/s, 17291.99 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
答案1
透過將 innodb 緩衝池增加到 ram 的 60% 並重建資料庫（匯出/刪除/匯入）來解決