mysql show engine_MySQL--SHOW ENGINE INNODB STATUS

最新推荐文章于 2024-04-27 10:45:58 发布

海燕老师

最新推荐文章于 2024-04-27 10:45:58 发布

阅读量239

点赞数

文章标签： mysql show engine

本文链接：https://blog.csdn.net/weixin_30072693/article/details/113276863

版权

=====================================

2017-08-14 09:05:09 0x7f305b965700INNODB MONITOR OUTPUT=====================================Per second averages calculated from thelast 41seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops:2169189 srv_active, 0 srv_shutdown, 2477146srv_idle

srv_master_thread log flush and writes:4646215

----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count96925054

--Thread 139851788347136 has waited at buf0buf.cc line 3497 for 0.00seconds the semaphore:

Mutex at0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1

--Thread 139851797931776 has waited at buf0buf.cc line 5815 for 0.00seconds the semaphore:

Mutex at0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1

--Thread 139845629937408 has waited at buf0buf.cc line 3497 for 0.00seconds the semaphore:

Mutex at0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1

--Thread 139852214482688 has waited at buf0flu.cc line 1422 for 0.00seconds the semaphore:

Mutex at0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1OS WAIT ARRAY INFO: signal count333660561RW-shared spins 0, rounds 2190803016, OS waits 15223499RW-excl spins 0, rounds 4078639377, OS waits 17431914RW-sx spins 143004, rounds 2685889, OS waits 50982Spin rounds perwait: 2190803016.00 RW-shared, 4078639377.00 RW-excl, 18.78 RW-sx------------------------LATEST DETECTED DEADLOCK------------------------

2017-08-02 14:07:45 0x7f3059d36700

*** (1) TRANSACTION:

TRANSACTION37769196, ACTIVE 785sec starting index read

mysql tablesin use 1, locked 1LOCK WAIT517 lock struct(s), heap size 57552, 44row lock(s)

MySQL threadid 1447408, OS thread handle 139845624346368, query id 261597560 10.50.12.12root 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 spaceid 152 page no 188412 n bits 96 index PRIMARY of table `SAJT_DZFP_PT`.`tbl_ec_document` trx id 37769196lock_mode X locks rec but not gap waiting

Record lock, heap no29 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 20171602s;;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 40bytes);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 37bytes);19: len 11; hex 3032313632313733323132; asc 02162173212;;20: len 30; hex e5b7a5e8a18ce4b88ae6b5b7e5b882e99d99e5ae89e5afbae694afe8a18c; asc ; (total 50bytes);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 32bytes);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:

TRANSACTION37769491, ACTIVE 753sec starting index read

mysql tablesin use 1, locked 1

517 lock struct(s), heap size 57552, 44row lock(s)

MySQL threadid 1447405, OS thread handle 139845642184448, query id 261597561 10.50.12.12root 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 spaceid 152 page no 188412 n bits 96 index PRIMARY of table `SAJT_DZFP_PT`.`tbl_ec_document` trx id 37769491lock mode S locks rec but not gap

Record lock, heap no29 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 '+;;

RECORD LOCKS spaceid 152 page no 188412 n bits 96 index PRIMARY of table `SAJT_DZFP_PT`.`tbl_ec_document` trx id 37769491lock_mode X locks rec but not gap waiting

Record lock, heap no29 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 '+;;

History list length 608LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421327456036272, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456039008, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456025328, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456035360, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456034448, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456033536, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456029888, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456020768, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456028976, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456018944, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456022592, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456017120, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456047216, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456061808, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456057248, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456055424, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456037184, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456044480, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456040832, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456039920, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456032624, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456031712, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456026240, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456018032, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456038096, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456021680, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456090992, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456084608, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456068192, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456059072, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456050864, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456016208, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456091904, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456088256, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456048128, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456056336, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456054512, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456023504, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456058160, not started0 lock struct(s), heap size 1136, 0row lock(s)---TRANSACTION 421327456028064, not started0 lock struct(s), heap size 1136, 0row lock(s)--------FILE I/O　　//IO Thread

//params: innodb_read_io_threads, innodb_write_io_threads--------I/O thread 0 state: waiting forcompleted aio requests (insert buffer thread)

I/O thread 1 state: waiting forcompleted aio requests (log thread)

I/O thread 2 state: waiting forcompleted aio requests (read thread)

I/O thread 3 state: waiting forcompleted aio requests (read thread)

I/O thread 4 state: waiting forcompleted aio requests (read thread)

I/O thread 5 state: waiting forcompleted aio requests (read thread)

I/O thread 6 state: waiting for completed aio requests (writethread)

I/O thread 7 state: waiting for completed aio requests (writethread)

I/O thread 8 state: waiting for completed aio requests (writethread)

I/O thread 9 state: complete io for buf page (writethread)

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, 29682473OS fsyncs20542.79 reads/s, 16384 avg bytes/read, 26.02 writes/s, 10.93 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size1, //已经合并记录页的数量

free list len 239, //空闲列表长度

seg size 241, //当前 Insert Buffer 的大小为 241 * 16K

15758707merges //合并次数

insert 表示 Insert Buffer

delete mark 表示 Delete Buffer

delete 表示 Purge Buffer

discarded operaations 表示当 Change Buffer 发生 merge 时，表已经被删除，此时就无需再将记录合并到辅助索引中了

merged operations:

insert18263562, delete mark 339733, delete 19552discarded operations:

insert0, delete mark 0, delete 0Hash table size34673, node heap has 2buffer(s)

Hash table size34673, node heap has 2buffer(s)

Hash table size34673, node heap has 1buffer(s)

Hash table size34673, node heap has 2buffer(s)

Hash table size34673, node heap has 2buffer(s)140.36 hash searches/s, 69.93 non-hash searches/s //自适应哈希索引 AHI---LOG　　//checkpoint 技术，重做日志---Log sequence number66724871713Log flushed up to66724871713Pages flushed up to66724861111Last checkpoint at66724858050

0 pending log flushes, 0pending chkp writes15663210 log i/o's done, 4.68 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated137428992Dictionary memory allocated1174104Buffer pool size8191 //当前 buffer pool size 共有 8191个页，即 8191 * 16k = 约 128 M 的缓冲池Free buffers5　　　//当前 Free 列表中页的数量Database pages8174 //LRU 列表中的数量

// Buffer pool size = Free buffers + Database pages + (自适应哈希索引，Lock信息，Insert Buffer 等等)Old database pages3033Modified db pages17Pending reads1Pending writes: LRU0, flush list 4, single page 0Pages made young280017975, //LRU 列表中页移动到前端的次数

not young 312454897572 //因 innodb_old_blocks_time 的设置而导致页没有从 old 部分移动到 new 部分的页

6.88 youngs/s, 79196.46 non-youngs/s

Pages read76334728309, created 2784742, written 53910256

20542.86 reads/s, 0.56 creates/s, 19.66 writes/s

Buffer pool hit rate809 / 1000, //缓冲池命中率

young-making rate 0 / 1000 not 737 / 1000Pages read ahead182.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 inqueue2read views open inside InnoDB

Process ID=27970, Main thread ID=139852104324864, state: sleeping

Number of rows inserted83061121, 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============================

海燕老师

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql show engine_MySQL--SHOW ENGINE INNODB STATUS

=====================================2017-08-14 09:05:09 0x7f305b965700INNODB MONITOR OUTPUT=====================================Per second averages calculated from thelast 41seconds-----------------B...
复制链接

扫一扫