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

[Steve Swinsburg]

Heres ours from production:

mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

=====================================
100310 14:10:22 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2, signal count 2
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 1280
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 22919, OS thread id 1178020160
MySQL thread id 2576, query id 4779960 localhost sakai
show innodb status
--------
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
25 OS file reads, 3 OS file writes, 3 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, used cells 0, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 43948
Log flushed up to   0 43948
Last checkpoint at  0 43948
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20618000; in additional pool allocated 676096
Buffer pool size   512
Free buffers       493
Database pages     19
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 19, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 22919, id 1170565440, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 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/95712b66/attachment.bin