BiTel

*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
130824 21:25:16 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 17 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 13726176, signal count 13406553
Mutex spin waits 0, rounds 709989747, OS waits 10329516
RW-shared spins 1174704, OS waits 390911; RW-excl spins 3205412, OS waits 454686
------------------------
LATEST DETECTED DEADLOCK
------------------------
130824 19:32:03
*** (1) TRANSACTION:
TRANSACTION 1 2665739631, ACTIVE 0 sec, process no 2403, OS thread id 139833664968448 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 368, 2 row lock(s)
MySQL thread id 1552395, query id 855486269 192.168.80.182 wifi Updating
UPDATE wifi_cl SET old_test=UNIX_TIMESTAMP() WHERE ip="192.168.249.202" AND mac="e0:63:e5:22:61:29"
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 246 page no 3 n bits 128 index `PRIMARY` of table `bgbilling`.`wifi_cl` trx id 1 2665739631 lock_mode X waiting
Record lock, heap no 15 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 80000926; asc    &;; 1: len 6; hex 00018b4dce63; asc    M c;; 2: len 7; hex 00000034f811d7; asc    4   ;; 3: len 8; hex 3434393933393833; asc 44993983;; 4: len 8; hex 8000000000000000; asc         ;; 5: len 8; hex 8000000000000000; asc         ;; 6: SQL NULL; 7: SQL NULL; 8: len 8; hex 80000000523c1aaf; asc     R<  ;;

*** (2) TRANSACTION:
TRANSACTION 1 2665739583, ACTIVE 0 sec, process no 2403, OS thread id 139834983663360 starting index read, thread declared inside InnoDB 493
mysql tables in use 2, locked 2
222 lock struct(s), heap size 30704, 4910 row lock(s), undo log entries 4678
MySQL thread id 1387745, query id 855486225 localhost 127.0.0.1 bill preparing
UPDATE agent SET wifi_c=0 WHERE cid NOT IN (SELECT cid FROM wifi_cl WHERE old_test > (UNIX_TIMESTAMP()-300))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 246 page no 3 n bits 128 index `PRIMARY` of table `bgbilling`.`wifi_cl` trx id 1 2665739583 lock mode S locks rec but not gap
Record lock, heap no 15 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 80000926; asc    &;; 1: len 6; hex 00018b4dce63; asc    M c;; 2: len 7; hex 00000034f811d7; asc    4   ;; 3: len 8; hex 3434393933393833; asc 44993983;; 4: len 8; hex 8000000000000000; asc         ;; 5: len 8; hex 8000000000000000; asc         ;; 6: SQL NULL; 7: SQL NULL; 8: len 8; hex 80000000523c1aaf; asc     R<  ;;

Record lock, heap no 44 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 80001273; asc    s;; 1: len 6; hex 00016ccf943b; asc   l  ;;; 2: len 7; hex 8000000109012e; asc       .;; 3: len 8; hex 3136373435323731; asc 16745271;; 4: len 8; hex 8000000000000000; asc         ;; 5: len 8; hex 8000000000000000; asc         ;; 6: SQL NULL; 7: SQL NULL; 8: len 8; hex 800000005236677f; asc     R6g ;;

Record lock, heap no 52 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 80002435; asc   $5;; 1: len 6; hex 00016b60b2b4; asc   k`  ;; 2: len 7; hex 8000000109012e; asc       .;; 3: len 8; hex 3638303638363532; asc 68068652;; 4: len 8; hex 8000000000000000; asc         ;; 5: len 8; hex 8000000000000000; asc         ;; 6: SQL NULL; 7: SQL NULL; 8: len 8; hex 8000000052360d26; asc     R6 &;;

Record lock, heap no 56 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 80001397; asc     ;; 1: len 6; hex 0000ec9e9852; asc      R;; 2: len 7; hex 8000001afe012e; asc       .;; 3: len 8; hex 3634313937363434; asc 64197644;; 4: len 8; hex 8000000000000000; asc         ;; 5: len 8; hex 8000000000000000; asc         ;; 6: SQL NULL; 7: SQL NULL; 8: len 8; hex 80000000521db453; asc     R  S;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 246 page no 3 n bits 128 index `PRIMARY` of table `bgbilling`.`wifi_cl` trx id 1 2665739583 lock mode S locks rec but not gap waiting
Record lock, heap no 54 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 8000038f; asc     ;; 1: len 6; hex 000107793576; asc    y5v;; 2: len 7; hex 8000000136012e; asc     6 .;; 3: len 8; hex 3131353635323330; asc 11565230;; 4: len 8; hex 8000000000000000; asc         ;; 5: len 8; hex 8000000000000000; asc         ;; 6: SQL NULL; 7: SQL NULL; 8: len 8; hex 800000005222d9a6; asc     R"  ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 1 2677731775
Purge done for trx's n:o < 1 2677727817 undo n:o < 0 0
History list length 1684
... truncated...
-TRANSACTION 1 2677729099, not started, process no 2403, OS thread id 139834963298048
MySQL thread id 1578325, query id 866574985 localhost agent
******
---TRANSACTION 1 2677715095, not started, process no 2403, OS thread id 139837771106048
MySQL thread id 6, query id 866573839 localhost 127.0.0.1 bill
--------
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
526864 OS file reads, 290199782 OS file writes, 962078 OS fsyncs
2.18 reads/s, 16384 avg bytes/read, 945.65 writes/s, 2.41 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 65, seg size 67,
77738 inserts, 77738 merged recs, 34940 merges
Hash table size 2212699, node heap has 5158 buffer(s)
3588.61 hash searches/s, 3913.83 non-hash searches/s
---
LOG
---
Log sequence number 200 4245877953
Log flushed up to   200 4245862385
Last checkpoint at  200 4059956335
0 pending log writes, 0 pending chkp writes
288849329 log i/o's done, 939.06 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1266087826; in additional pool allocated 16776704
Dictionary memory allocated 2693592
Buffer pool size   65536
Free buffers       20
Database pages     60358
Modified db pages  7497
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1617193, created 128641, written 4026971
2.18 reads/s, 0.65 creates/s, 15.12 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 process no. 2403, id 139835282646784, state: sleeping
Number of rows inserted 52991132, updated 328626443, deleted 50736178, read 50811755739
409.39 inserts/s, 1060.76 updates/s, 398.86 deletes/s, 76668.20 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================