Re: MySQL Heavy Load Problem (5.0.67)
One more thing I have noticed:
When the bad thing happens, MySQL memory use grows dramatically: it take about 90 megs more just within half a minute. Usually it does about 10 megs a minute after clean start.
It goest to 150% percents of CPU usage and stays there. Log says nothing.
Here is a part of show engine innodb status:
**** lots of hanging transactions like this ***
---TRANSACTION 0 3985928, ACTIVE 465 sec, OS thread id 3452957696 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 80379, query id 645203 verter 10.0.0.1 superhabr Sending data
SELECT
variant_id,
user_id
FROM question_votes
WHERE
post_id = '36743'
Trx read view will not see trx with id >= 0 3985929, sees < 0 3985586
--------
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 (write thread)
Pending normal aio reads: 0, 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
49884 OS file reads, 137365 OS file writes, 83438 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.53 writes/s, 0.94 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
465 inserts, 465 merged recs, 334 merges
Hash table size 5188061, used cells 1559546, node heap has 2737 buffer(s)
6219.63 hash searches/s, 3356.33 non-hash searches/s
---
LOG
---
Log sequence number 0 2937754805
Log flushed up to 0 2937754805
Last checkpoint at 0 2937754805
0 pending log writes, 0 pending chkp writes
72337 log i/o's done, 0.53 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2895644424; in additional pool allocated 16777216
Buffer pool size 160000
Free buffers 99729
Database pages 57534
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 57404, created 130, written 77819
0.00 reads/s, 0.00 creates/s, 1.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 87 queries in queue
95 read views open inside InnoDB
Main thread id 3169326080, state: sleeping
Number of rows inserted 8271, updated 31601, deleted 88, read 156493518
0.00 inserts/s, 0.18 updates/s, 0.00 deletes/s, 9914.30 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================