☰

MySQL 偶尔仅在一个数据库上无响应

tag-icon • tag-icon mysql optimization
MySQL 偶尔仅在一个数据库上无响应

我偶尔会遇到一个问题（每周一两次，不一定是在高峰时段），某个数据库（我最大的网站的数据库）上的查询会突然停止响应约一小时。查询开始堆积，大部分处于“发送数据”和“统计”状态，最终负载上升到 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% 并重建数据库（导出/删除/导入）来解决

相关内容