innodb status
=================================================
innodb-format ROW
tx_isolation RR
=================================================
Type Name Status
------ ------ -------------------------------------------------------------------
InnoDB
=====================================
2018-07-25 02:14:16 7f58eded5700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 42 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 80293830 srv_active, 0 srv_shutdown, 243403 srv_idle
srv_master_thread log flush and writes: 80536795
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 650316888
OS WAIT ARRAY INFO: signal count 289438451
Mutex spin waits 5289054099, rounds 10779531188, OS waits 59288323
RW-shared spins 126996788, rounds 11959961197, OS waits 255435937
RW-excl spins 169909774, rounds 15755893235, OS waits 330800444
Spin rounds per wait: 10.84 mutex, 94.18 RW-shared, 92.73 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-07-21 00:48:13 7f58ed8e6700
*** (1) TRANSACTION:
TRANSACTION 18795255815, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 452007776, OS thread handle 0x7f58eded5700, query id 45141102517 10.0.0.7 7nuuser updating
UPDATE box_time SET box_id = v_box_id, box_key = v_box_key , start_time = v_start_time , push_status = 0 WHERE user_id = v_user_id
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 587 page no 33799 n bits 648 index `IX_box_push` of table `fission_luck`.`box_time` trx id 18795255815 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 18795255808, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
1419 lock struct(s), heap size 161320, 4209 row lock(s)
MySQL thread id 452007913, OS thread handle 0x7f58ed8e6700, query id 45141102496 10.0.0.83 7nuuser Sending data
UPDATE box_time bt, luck_detail ld SET bt.push_status = 1
WHERE bt.`box_id` = ld.`id`
AND DATE_ADD(bt.start_time,INTERVAL (ld.luck_time_num + ld.push_time) SECOND) < NOW()
AND ld.push_status = 1 AND bt.`push_status` = 0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 587 page no 33799 n bits 648 index `IX_box_push` of table `fission_luck`.`box_time` trx id 18795255808 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 587 page no 39543 n bits 1000 index `IX_box_push` of table `fission_luck`.`box_time` trx id 18795255808 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 18958871741
Purge done for trx's n:o < 18958871716 undo n:o < 0 state: running but idle
History list length 4122
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 454956649, OS thread handle 0x7f58eded5700, query id 45618813015 10.0.0.89 admin init
show engine innodb status
---TRANSACTION 18958871342, not started
MySQL thread id 454956652, OS thread handle 0x7f58ed78f700, query id 45618811715 10.0.0.25 7nuuser cleaning up
---TRANSACTION 18958871616, not started
MySQL thread id 454956929, OS thread handle 0x7f58ecc18700, query id 45618812642 10.0.0.12 7nuuser cleaning up
---TRANSACTION 18958871739, not started
MySQL thread id 454950670, OS thread handle 0x7f58caac0700, query id 45618813006 10.0.0.155 7nuuser cleaning up
---TRANSACTION 18958871701, not started
MySQL thread id 169903475, OS thread handle 0x7f58eccab700, query id 45618812898 10.0.0.156 admin cleaning up
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
30857821 OS file reads, 2701927344 OS file writes, 229714872 OS fsyncs
0.07 reads/s, 16384 avg bytes/read, 19.02 writes/s, 3.55 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 450, seg size 452, 341485 merges
merged operations:
insert 359838, delete mark 23770482, delete 31819
discarded operations:
insert 0, delete mark 0, delete 0
4832.00 hash searches/s, 1080.47 non-hash searches/s
---
LOG
---
Log sequence number 1621249806625
Log flushed up to 1621249806075
Pages flushed up to 1620959920982
Last checkpoint at 1620959831491
Max checkpoint age 2608479868
Checkpoint age target 2526964873
Modified age 289885643
Checkpoint age 289975134
0 pending log writes, 0 pending chkp writes
2388270253 log i/o's done, 13.19 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 49450844160; in additional pool allocated 0
Total memory allocated by read views 15624
Internal hash tables (constant factor + variable factor)
Adaptive hash index 2480661728 (764990584 + 1715671144)
Page hash 5977288 (buffer pool 0 only)
Dictionary cache 192586000 (191248976 + 1337024)
File system 932200 (812272 + 119928)
Lock system 119583104 (119530904 + 52200)
Recovery system 0 (0 + 0)
Dictionary memory allocated 1337024
Buffer pool size 2949112
Buffer pool size, bytes 48318251008
Free buffers 8195
Database pages 2836201
Old database pages 2126988
Modified db pages 62619
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1246402285, not young 5758391461
0.12 youngs/s, 0.31 non-youngs/s
Pages read 30679387, created 12460494, written 261314786
0.07 reads/s, 0.02 creates/s, 4.74 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2836201, unzip_LRU len: 0
I/O sum[1680]:cur[40], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 368639
Buffer pool size, bytes 6039781376
Free buffers 1025
Database pages 354536
Old database pages 265882
Modified db pages 9062
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 157482623, not young 814926827
0.05 youngs/s, 0.00 non-youngs/s
Pages read 4298297, created 1559597, written 37866789
0.00 reads/s, 0.00 creates/s, 0.43 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 354536, unzip_LRU len: 0
I/O sum[210]:cur[5], unzip sum[0]:cur[0]
。。。。。。。。。。。。。。
。。。。。。。。。。。。。。
---BUFFER POOL 7
Buffer pool size 368639
Buffer pool size, bytes 6039781376
Free buffers 1024
Database pages 354462
Old database pages 265826
Modified db pages 7482
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 155443826, not young 706835226
0.00 youngs/s, 0.07 non-youngs/s
Pages read 3803092, created 1557068, written 30173184
0.02 reads/s, 0.00 creates/s, 0.67 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 354462, unzip_LRU len: 0
I/O sum[210]:cur[5], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
Main thread process no. 61879, id 140020091574016, state: sleeping
Number of rows inserted 2139169467, updated 3142049602, deleted 172336099, read 70565160031534
6.36 inserts/s, 13.62 updates/s, 0.05 deletes/s, 83373.28 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
