가끔(피크 시간대가 아닌 일주일에 한두 번) 특정 DB(내 사이트 중 가장 큰 DB)에 대한 쿼리가 갑자기 약 한 시간 동안 응답을 중지하는 문제가 있습니다. 대부분 "데이터 전송 중" 및 "통계" 상태에서 쿼리가 쌓이기 시작하고 결국 로드가 40 이상으로 올라가 전체 서버가 정지됩니다.
이 문제가 발생하는 동안 이 사이트를 비활성화하면 동일한 서버의 다른 모든 사이트와 DB가 정상적으로 실행됩니다.
응답이 없을 때 "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%로 늘리고 데이터베이스를 재구축(내보내기/드롭/가져오기)하여 문제를 해결했습니다.