[Building Sakai] MySQL table lock; could you check innodb status for me?

[Steve Swinsburg]

And from my local dev server:

mysql  Ver 14.14 Distrib 5.1.40, for apple-darwin9.5.0 (i386) using readline 5.1

=====================================
100310 14:22:57 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 34 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 299, signal count 299
Mutex spin waits 0, rounds 320, OS waits 11
RW-shared spins 272, OS waits 135; RW-excl spins 155, OS waits 153
------------
TRANSACTIONS
------------
Trx id counter 0 471726
Purge done for trx's n:o < 0 471699 undo n:o < 0 0
History list length 27
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 471715, not started, OS thread id 4346785792
MySQL thread id 359, query id 399438 localhost 127.0.0.1 sakai
---TRANSACTION 0 471725, not started, OS thread id 4346511360
MySQL thread id 358, query id 399869 localhost 127.0.0.1 sakai
---TRANSACTION 0 0, not started, OS thread id 4347060224
MySQL thread id 360, query id 395527 localhost 127.0.0.1 sakai
---TRANSACTION 0 0, not started, OS thread id 4353638400
MySQL thread id 350, query id 399870 localhost root
show innodb status
---TRANSACTION 0 0, not started, OS thread id 4347334656
MySQL thread id 349, query id 393622 localhost root
---TRANSACTION 0 0, not started, OS thread id 4327256064
MySQL thread id 348, query id 393251 localhost 127.0.0.1 root
--------
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
1415 OS file reads, 3163 OS file writes, 2496 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, node heap has 1 buffer(s)
0.00 hash searches/s, 0.74 non-hash searches/s
---
LOG
---
Log sequence number 0 59092760
Log flushed up to   0 59092760
Last checkpoint at  0 59092760
0 pending log writes, 0 pending chkp writes
2153 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 24097928; in additional pool allocated 1048576
Dictionary memory allocated 2341320
Buffer pool size   512
Free buffers       0
Database pages     511
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1834, created 24, written 916
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 4351791104, state: sleeping
Number of rows inserted 552, updated 1318, deleted 262, read 34685
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1.24 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================





On 10/03/2010, at 1:50 PM, Charles Hedrick wrote:

> I'd like to ask for some help in debugging the table lock problem. Could a few sites with mysql 5.0 and 5.1 try "show innodb status" and look for the section with current transactions:
> 
> ---TRANSACTION 0 151485, not started, OS thread id 4451168256
> MySQL thread id 7499, query id 1904644 localhost 127.0.0.1 sakaiuser
> ---TRANSACTION 0 151183, not started, OS thread id 4450893824
> MySQL thread id 7498, query id 1904622 localhost 127.0.0.1 sakaiuser
> 
> 
> The question is whether you have indications of current locks, such as this:
> 
> ---TRANSACTION 0 128440, ACTIVE 291 sec, OS thread id 165
> 2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
> MySQL thread id 154, query id 8867 kissaki.oirt.rutgers.edu 128.6.31.98 sakaiuser
> ---TRANSACTION 0 128436, ACTIVE 291 sec, OS thread id 161
> 2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
> MySQL thread id 150, query id 8854 kissaki.oirt.rutgers.edu 128.6.31.98 sakaiuser
> ---TRANSACTION 0 128432, ACTIVE 291 sec, OS thread id 157
> 2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
> MySQL thread id 146, query id 8841 kissaki.oirt.rutgers.edu 128.6.31.98 sakaiuser
> ---TRANSACTION 0 128429, ACTIVE 291 sec, OS thread id 155
> 
> I see nothing like this on our production system (4.1) or my own development system (5.0). It's pretty clear that idle connections shouldn't be holding open locks, and that if they do, eventually there will be trouble. I'm wondering whether this is specific to 5.1 or whether it is also present in 5.0. If 5.0 is OK, I'd probably prefer to move from 4.1 to 5.0. We need to solve it for 5.1, but at least 5.0 is more current, and should have better performance with multiple cores.
> 
> So I'd appreciate having a few sites tell me whether they see these hanging locks, and what version of Mysql you're running.
> 
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> 
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 3689 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20100310/4e787d7f/attachment.bin