=====================================
2017-08-14 09:05:09 0x7f305b965700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 41 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2169189 srv_active, 0 srv_shutdown, 2477146 srv_idle
srv_master_thread log flush and writes: 4646215
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 96925054
--Thread 139851788347136 has waited at buf0buf.cc line 3497 for 0.00 seconds the semaphore:
Mutex at 0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1
--Thread 139851797931776 has waited at buf0buf.cc line 5815 for 0.00 seconds the semaphore:
Mutex at 0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1
--Thread 139845629937408 has waited at buf0buf.cc line 3497 for 0.00 seconds the semaphore:
Mutex at 0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1
--Thread 139852214482688 has waited at buf0flu.cc line 1422 for 0.00 seconds the semaphore:
Mutex at 0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1
OS WAIT ARRAY INFO: signal count 333660561
RW-shared spins 0, rounds 2190803016, OS waits 15223499
RW-excl spins 0, rounds 4078639377, OS waits 17431914
RW-sx spins 143004, rounds 2685889, OS waits 50982
Spin rounds per wait: 2190803016.00 RW-shared, 4078639377.00 RW-excl, 18.78 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-08-02 14:07:45 0x7f3059d36700
*** (1) TRANSACTION:
TRANSACTION 37769196, ACTIVE 785 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 517 lock struct(s), heap size 57552, 44 row lock(s)
MySQL thread id 1447408, OS thread handle 139845624346368, query id 261597560 10.50.12.12 root updating
update tbl_ec_document set ISSUED=9,ISSUE_ERR_MSG='Error on line -1: Premature end of file.' where FPQQLSH='1t8gN9RLxTQT1AD4kQcX'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 152 page no 188412 n bits 96 index PRIMARY of table `SAJT_DZFP_PT`.`tbl_ec_document` trx id 37769196 lock_mode X locks rec but not gap waiting
Record lock, heap no 29 PHYSICAL RECORD: n_fields 65; compact format; info bits 0
0: len 20; hex 317438674e39524c78545154314144346b516358; asc 1t8gN9RLxTQT1AD4kQcX;;
1: len 6; hex 000002404efb; asc @N ;;
2: len 7; hex 4000004aa8272b; asc @ J '+;;
3: len 10; hex 32303137313630322073; asc 20171602 s;;
4: len 19; hex 323031372d30382d30322031333a30303a3030; asc 2017-08-02 13:00:00;;
5: len 8; hex 8000000000000009; asc ;;
6: len 8; hex 8000000000000001; asc ;;
7: len 30; hex 4572726f72206f6e206c696e65202d313a205072656d617475726520656e; asc Error on line -1: Premature en; (total 40 bytes);
8: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;;
9: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;;
10: SQL NULL;
11: SQL NULL;
12: SQL NULL;
13: SQL NULL;
14: SQL NULL;
15: len 4; hex 31322e30; asc 12.0;;
16: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;;
17: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;;
18: len 30; hex e4b88ae6b5b7e5b882e99d99e5ae89e58cbae58c97e4baace8a5bfe8b7af; asc ; (total 37 bytes);
19: len 11; hex 3032313632313733323132; asc 02162173212;;
20: len 30; hex e5b7a5e8a18ce4b88ae6b5b7e5b882e99d99e5ae89e5afbae694afe8a18c; asc ; (total 50 bytes);
21: len 30; hex e5a48de697a6e5a4a7e5ada6e99984e5b19ee98791e5b1b1e58cbbe999a2; asc ;;
22: len 0; hex ; asc ;;
23: SQL NULL;
24: len 0; hex ; asc ;;
25: len 0; hex ; asc ;;
26: len 0; hex ; asc ;;
27: len 24; hex 313532313131373030313640667564616e2e6564752e636e; asc 15211170016@fudan.edu.cn;;
28: len 1; hex 31; asc 1;;
29: len 0; hex ; asc ;;
30: SQL NULL;
31: SQL NULL;
32: len 9; hex e9bb84e788b1e58d8e; asc ;;
33: SQL NULL;
34: SQL NULL;
35: len 8; hex 8000000000000001; asc ;;
36: len 0; hex ; asc ;;
37: len 0; hex ; asc ;;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;
42: SQL NULL;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: len 9; hex e694afe4bb98e5ae9d; asc ;;
47: len 0; hex ; asc ;;
48: len 0; hex ; asc ;;
49: len 0; hex ; asc ;;
50: len 0; hex ; asc ;;
51: len 0; hex ; asc ;;
52: len 8; hex 3133315130364e4f; asc 131Q06NO;;
53: len 5; hex 999d44de68; asc D h;;
54: len 8; hex 8000000000000000; asc ;;
55: len 1; hex 31; asc 1;;
56: len 30; hex 336637333835383734666466346362623835353064666530653338653762; asc 3f7385874fdf4cbb8550dfe0e38e7b; (total 32 bytes);
57: len 6; hex 5348484c5848; asc SHHLXH;;
58: len 2; hex 5348; asc SH;;
59: len 0; hex ; asc ;;
60: len 13; hex 31353031363533323233303038; asc 1501653223008;;
61: len 1; hex 30; asc 0;;
62: len 1; hex 30; asc 0;;
63: SQL NULL;
64: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 37769491, ACTIVE 753 sec starting index read
mysql tables in use 1, locked 1
517 lock struct(s), heap size 57552, 44 row lock(s)
MySQL thread id 1447405, OS thread handle 139845642184448, query id 261597561 10.50.12.12 root updating
update tbl_ec_document set ISSUED=9,ISSUE_ERR_MSG='Error on line -1: Premature end of file.' where FPQQLSH='1t8gN9RLxTQT1AD4kQcX'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 152 page no 188412 n bits 96 index PRIMARY of table `SAJT_DZFP_PT`.`tbl_ec_document` trx id 37769491 lock mode S locks rec but not gap
Record lock, heap no 29 PHYSICAL RECORD: n_fields 65; compact format; info bits 0
0: len 20; hex 317438674e39524c78545154314144346b516358; asc 1t8gN9RLxTQT1AD4kQcX;;
1: len 6; hex 000002404efb; asc @N ;;
2: len 7; hex 4000004aa8272b; asc @ J '+;;
3: len 10; hex 32303137313630322073; asc 20171602 s;;
4: len 19; hex 323031372d30382d30322031333a30303a3030; asc 2017-08-02 13:00:00;;
5: len 8; hex 8000000000000009; asc ;;
6: len 8; hex 8000000000000001; asc ;;
7: len 30; hex 4572726f72206f6e206c696e65202d313a205072656d617475726520656e; asc Error on line -1: Premature en; (total 40 bytes);
8: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;;
9: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;;
10: SQL NULL;
11: SQL NULL;
12: SQL NULL;
13: SQL NULL;
14: SQL NULL;
15: len 4; hex 31322e30; asc 12.0;;
16: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;;
17: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;;
18: len 30; hex e4b88ae6b5b7e5b882e99d99e5ae89e58cbae58c97e4baace8a5bfe8b7af; asc ; (total 37 bytes);
19: len 11; hex 3032313632313733323132; asc 02162173212;;
20: len 30; hex e5b7a5e8a18ce4b88ae6b5b7e5b882e99d99e5ae89e5afbae694afe8a18c; asc ; (total 50 bytes);
21: len 30; hex e5a48de697a6e5a4a7e5ada6e99984e5b19ee98791e5b1b1e58cbbe999a2; asc ;;
22: len 0; hex ; asc ;;
23: SQL NULL;
24: len 0; hex ; asc ;;
25: len 0; hex ; asc ;;
26: len 0; hex ; asc ;;
27: len 24; hex 313532313131373030313640667564616e2e6564752e636e; asc 15211170016@fudan.edu.cn;;
28: len 1; hex 31; asc 1;;
29: len 0; hex ; asc ;;
30: SQL NULL;
31: SQL NULL;
32: len 9; hex e9bb84e788b1e58d8e; asc ;;
33: SQL NULL;
34: SQL NULL;
35: len 8; hex 8000000000000001; asc ;;
36: len 0; hex ; asc ;;
37: len 0; hex ; asc ;;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;
42: SQL NULL;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: len 9; hex e694afe4bb98e5ae9d; asc ;;
47: len 0; hex ; asc ;;
48: len 0; hex ; asc ;;
49: len 0; hex ; asc ;;
50: len 0; hex ; asc ;;
51: len 0; hex ; asc ;;
52: len 8; hex 3133315130364e4f; asc 131Q06NO;;
53: len 5; hex 999d44de68; asc D h;;
54: len 8; hex 8000000000000000; asc ;;
55: len 1; hex 31; asc 1;;
56: len 30; hex 336637333835383734666466346362623835353064666530653338653762; asc 3f7385874fdf4cbb8550dfe0e38e7b; (total 32 bytes);
57: len 6; hex 5348484c5848; asc SHHLXH;;
58: len 2; hex 5348; asc SH;;
59: len 0; hex ; asc ;;
60: len 13; hex 31353031363533323233303038; asc 1501653223008;;
61: len 1; hex 30; asc 0;;
62: len 1; hex 30; asc 0;;
63: SQL NULL;
64: SQL NULL;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 152 page no 188412 n bits 96 index PRIMARY of table `SAJT_DZFP_PT`.`tbl_ec_document` trx id 37769491 lock_mode X locks rec but not gap waiting
Record lock, heap no 29 PHYSICAL RECORD: n_fields 65; compact format; info bits 0
0: len 20; hex 317438674e39524c78545154314144346b516358; asc 1t8gN9RLxTQT1AD4kQcX;;
1: len 6; hex 000002404efb; asc @N ;;
2: len 7; hex 4000004aa8272b; asc @ J '+;;
3: len 10; hex 32303137313630322073; asc 20171602 s;;
4: len 19; hex 323031372d30382d30322031333a30303a3030; asc 2017-08-02 13:00:00;;
5: len 8; hex 8000000000000009; asc ;;
6: len 8; hex 8000000000000001; asc ;;
7: len 30; hex 4572726f72206f6e206c696e65202d313a205072656d617475726520656e; asc Error on line -1: Premature en; (total 40 bytes);
8: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;;
9: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;;
10: SQL NULL;
11: SQL NULL;
12: SQL NULL;
13: SQL NULL;
14: SQL NULL;
15: len 4; hex 31322e30; asc 12.0;;
16: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;;
17: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;;
18: len 30; hex e4b88ae6b5b7e5b882e99d99e5ae89e58cbae58c97e4baace8a5bfe8b7af; asc ; (total 37 bytes);
19: len 11; hex 3032313632313733323132; asc 02162173212;;
20: len 30; hex e5b7a5e8a18ce4b88ae6b5b7e5b882e99d99e5ae89e5afbae694afe8a18c; asc ; (total 50 bytes);
21: len 30; hex e5a48de697a6e5a4a7e5ada6e99984e5b19ee98791e5b1b1e58cbbe999a2; asc ;;
22: len 0; hex ; asc ;;
23: SQL NULL;
24: len 0; hex ; asc ;;
25: len 0; hex ; asc ;;
26: len 0; hex ; asc ;;
27: len 24; hex 313532313131373030313640667564616e2e6564752e636e; asc 15211170016@fudan.edu.cn;;
28: len 1; hex 31; asc 1;;
29: len 0; hex ; asc ;;
30: SQL NULL;
31: SQL NULL;
32: len 9; hex e9bb84e788b1e58d8e; asc ;;
33: SQL NULL;
34: SQL NULL;
35: len 8; hex 8000000000000001; asc ;;
36: len 0; hex ; asc ;;
37: len 0; hex ; asc ;;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;
42: SQL NULL;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: len 9; hex e694afe4bb98e5ae9d; asc ;;
47: len 0; hex ; asc ;;
48: len 0; hex ; asc ;;
49: len 0; hex ; asc ;;
50: len 0; hex ; asc ;;
51: len 0; hex ; asc ;;
52: len 8; hex 3133315130364e4f; asc 131Q06NO;;
53: len 5; hex 999d44de68; asc D h;;
54: len 8; hex 8000000000000000; asc ;;
55: len 1; hex 31; asc 1;;
56: len 30; hex 336637333835383734666466346362623835353064666530653338653762; asc 3f7385874fdf4cbb8550dfe0e38e7b; (total 32 bytes);
57: len 6; hex 5348484c5848; asc SHHLXH;;
58: len 2; hex 5348; asc SH;;
59: len 0; hex ; asc ;;
60: len 13; hex 31353031363533323233303038; asc 1501653223008;;
61: len 1; hex 30; asc 0;;
62: len 1; hex 30; asc 0;;
63: SQL NULL;
64: SQL NULL;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 41115658
Purge done for trx's n:o < 41115613 undo n:o < 0 state: running but idle
History list length 608
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421327456036272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456039008, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456025328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456035360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456034448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456033536, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456029888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456020768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456028976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456018944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456022592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456017120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456047216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456061808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456057248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456055424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456037184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456044480, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456040832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456039920, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456032624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456031712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456026240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456018032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456038096, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456021680, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456090992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456084608, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456068192, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456059072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456050864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456016208, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456091904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456088256, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456048128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456056336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456054512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456023504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456058160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421327456028064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O //IO Thread
//params: innodb_read_io_threads, innodb_write_io_threads
--------
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 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: complete io for buf page (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 1
76334518114 OS file reads, 73524871 OS file writes, 29682473 OS fsyncs
20542.79 reads/s, 16384 avg bytes/read, 26.02 writes/s, 10.93 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, //已经合并记录页的数量
free list len 239, //空闲列表长度
seg size 241, //当前 Insert Buffer 的大小为 241 * 16K
15758707 merges //合并次数
/*
insert 表示 Insert Buffer
delete mark 表示 Delete Buffer
delete 表示 Purge Buffer
discarded operaations 表示当 Change Buffer 发生 merge 时，表已经被删除，此时就无需再将记录合并到辅助索引中了
*/
merged operations:
insert 18263562, delete mark 339733, delete 19552
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
140.36 hash searches/s, 69.93 non-hash searches/s //自适应哈希索引 AHI
---
LOG //checkpoint 技术，重做日志
---
Log sequence number 66724871713
Log flushed up to 66724871713
Pages flushed up to 66724861111
Last checkpoint at 66724858050
0 pending log flushes, 0 pending chkp writes
15663210 log i/o's done, 4.68 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 1174104
Buffer pool size 8191 //当前 buffer pool size 共有 8191个页，即 8191 * 16k = 约 128 M 的缓冲池
Free buffers 5 //当前 Free 列表中页的数量
Database pages 8174 //LRU 列表中的数量
// Buffer pool size = Free buffers + Database pages + (自适应哈希索引，Lock信息，Insert Buffer 等等)
Old database pages 3033
Modified db pages 17
Pending reads 1
Pending writes: LRU 0, flush list 4, single page 0
Pages made young 280017975, //LRU 列表中页移动到前端的次数
not young 312454897572 //因 innodb_old_blocks_time 的设置而导致页没有从 old 部分移动到 new 部分的页
6.88 youngs/s, 79196.46 non-youngs/s
Pages read 76334728309, created 2784742, written 53910256
20542.86 reads/s, 0.56 creates/s, 19.66 writes/s
Buffer pool hit rate 809 / 1000, //缓冲池命中率
young-making rate 0 / 1000 not 737 / 1000
Pages read ahead 182.65/s, evicted without access 77.27/s, Random read ahead 0.00/s
LRU len: 8174, unzip_LRU len: 0 //LRU 列表中一共有 8174 个页，而 unzip_LRU 列表中有 0 个页，LRU 中的页包含了 unzip_LRU 列表中的页
I/O sum[933451]:cur[10597], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Process ID=27970, Main thread ID=139852104324864, state: sleeping
Number of rows inserted 83061121, updated 5749898, deleted 50570, read 1678275084728
3.59 inserts/s, 1.41 updates/s, 0.02 deletes/s, 465657.94 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================