Movatterモバイル変換

The Lock Monitor is the same as the Standard Monitor except that it includes additional lock information. Enabling either monitor for periodic output turns on the same output stream, but the stream includes extra information if the Lock Monitor is enabled. For example, if you enable the Standard Monitor and Lock Monitor, that turns on a single output stream. The stream includes extra lock information until you disable the Lock Monitor.

Standard Monitor output is limited to 1MB when produced using theSHOW ENGINE INNODB STATUS statement. This limit does not apply to output written to server standard error output (stderr).

Example Standard Monitor output:

mysql> SHOW ENGINE INNODB STATUS\G*************************** 1. row ***************************  Type: InnoDB  Name:Status:=====================================2018-04-12 15:14:08 0x7f971c063700 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 4 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 15 srv_active, 0 srv_shutdown, 1122 srv_idlesrv_master_thread log flush and writes: 0----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 24OS WAIT ARRAY INFO: signal count 24RW-shared spins 4, rounds 8, OS waits 4RW-excl spins 2, rounds 60, OS waits 2RW-sx spins 0, rounds 0, OS waits 0Spin rounds per wait: 2.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx------------------------LATEST FOREIGN KEY ERROR------------------------2018-04-12 14:57:24 0x7f97a9c91700 Transaction:TRANSACTION 7717, ACTIVE 0 sec insertingmysql tables in use 1, locked 14 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3MySQL thread id 8, OS thread handle 140289365317376, query id 14 localhost root updateINSERT INTO child VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6)Foreign key constraint fails for table `test`.`child`:,  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE  CASCADE ON UPDATE CASCADETrying to add in child table, in index par_ind tuple:DATA TUPLE: 2 fields; 0: len 4; hex 80000003; asc     ;; 1: len 4; hex 80000003; asc     ;;But in parent table `test`.`parent`, in index PRIMARY,the closest match we can find is record:PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000004; asc     ;; 1: len 6; hex 000000001e19; asc       ;; 2: len 7; hex 81000001110137; asc       7;;------------TRANSACTIONS------------Trx id counter 7748Purge done for trx's n:o < 7747 undo n:o < 0 state: running but idleHistory list length 19LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421764459790000, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 7747, ACTIVE 23 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)MySQL thread id 9, OS thread handle 140286987249408, query id 51 localhost root updatingDELETE FROM t WHERE i = 1------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`trx id 7747 lock_mode X waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000000202; asc       ;; 1: len 6; hex 000000001e41; asc      A;; 2: len 7; hex 820000008b0110; asc        ;; 3: len 4; hex 80000001; asc     ;;------------------TABLE LOCK table `test`.`t` trx id 7747 lock mode IXRECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`trx id 7747 lock_mode X waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000000202; asc       ;; 1: len 6; hex 000000001e41; asc      A;; 2: len 7; hex 820000008b0110; asc        ;; 3: len 4; hex 80000001; asc     ;;--------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 (read thread)I/O thread 4 state: waiting for i/o request (read thread)I/O thread 5 state: waiting for i/o request (read thread)I/O thread 6 state: waiting for i/o request (write thread)I/O thread 7 state: waiting for i/o request (write thread)I/O thread 8 state: waiting for i/o request (write thread)I/O thread 9 state: waiting for i/o request (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: 0833 OS file reads, 605 OS file writes, 208 OS fsyncs0.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 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 553253, node heap has 0 buffer(s)Hash table size 553253, node heap has 1 buffer(s)Hash table size 553253, node heap has 3 buffer(s)Hash table size 553253, node heap has 0 buffer(s)Hash table size 553253, node heap has 0 buffer(s)Hash table size 553253, node heap has 0 buffer(s)Hash table size 553253, node heap has 0 buffer(s)Hash table size 553253, node heap has 0 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number          19643450Log buffer assigned up to    19643450Log buffer completed up to   19643450Log written up to            19643450Log flushed up to            19643450Added dirty pages up to      19643450Pages flushed up to          19643450Last checkpoint at           19643450129 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 2198863872Dictionary memory allocated 409606Buffer pool size   131072Free buffers       130095Database pages     973Old database pages 0Modified db pages  0Pending reads      0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 810, created 163, written 4040.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 973, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]----------------------INDIVIDUAL BUFFER POOL INFO-------------------------BUFFER POOL 0Buffer pool size   65536Free buffers       65043Database pages     491Old database pages 0Modified db pages  0Pending reads      0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 411, created 80, written 2100.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 491, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 1Buffer pool size   65536Free buffers       65052Database pages     482Old database pages 0Modified db pages  0Pending reads      0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 399, created 83, written 1940.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 482, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBProcess ID=5772, Main thread ID=140286437054208 , state=sleepingNumber of rows inserted 57, updated 354, deleted 4, read 44210.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================