MySQL Lock monitor

according to MySQL lock monitor This article to operate.

Readiness condition: More than 5.6.16 version of MySQL, login MySQL

Open

Set global Innodb_status_output=on;set global innodb_status_output_locks=on;

Add and Modify tables.

Mysql>Show CREATE TABLE My_test1_0\g***************************1. Row ***************************table:my_test1_0create table:create Table ' my_test1_0 ' (' ID 'int( One) not NULL, ' col2 'int( One) DEFAULT NULL, ' col3 ' varchar ( -) DEFAULT NULL, ' col4 ' varchar (Ten) DEFAULT NULL, ' col5 ' varchar ( -DEFAULT null,primary key (' ID '), key ' Idx_col2 ' (' col2 '), key ' Idx_col3_col4 ' (' col3 ', ' col4 ') ENGINE=innodb DEFAULT charset=latin11Rowinch Set(0.08sec) insert into MY_TEST1_0 (ID) VALUES (1),(2),(3); Update My_test1_0Setcol5='Jiji' whereId=1;

View

Show Engine InnoDB Status\g

Results:

Mysql>Show engine InnoDB status\g***************************1. Row ***************************type:innodb name:status:===================================== .- One- -  the: +: $149c INNODB MONITOR OUTPUT=====================================Per second averages calculated fromThe last0seconds-----------------BACKGROUND THREAD-----------------Srv_master_thread Loops:3Srv_active,0Srv_shutdown,1528929Srv_idlesrv_master_thread log Flush and writes:1528919----------semaphores----------OS WAIT ARRAY info:reservation Count +OS WAIT ARRAY info:signal Count -Mutex Spin Waits142, Rounds276, OS waits7RW-shared spins9, Rounds221, OS waits6RW-excl spins1, Rounds Max, OS waits5Spin rounds per wait:1.94Mutex24.56Rw-shared,150.00rw-excl------------Transactions------------Trx ID Counter22306Purge Done forTrx'S N:o < 22306 undo N:o < 0 state:running but idleHistory list Length446LIST of transactions for each SESSION:---TRANSACTION0, not startedmysql thread ID One, OS thread handle0x149c, Query ID239localhost::1Rootinchtshow engine InnoDB status--------FILE I/O--------I/O Thread0state:wait Windows aio (insert buffer thread) I/O Thread1state:wait Windows aio (log thread) I/O Thread2state:wait Windows AIO (read thread) I/O Thread3state:wait Windows AIO (read thread) I/O Thread4state:wait Windows AIO (read thread) I/O Thread5state:wait Windows AIO (read thread) I/O Thread6state:wait Windows aio (write thread) I/O Thread7state:wait Windows aio (write thread) I/O Thread8state:wait Windows aio (write thread) I/O Thread9state:wait Windows aio (write thread) Pending normal AIO reads:0[0,0,0,0], Aio writes:0[0,0,0,0], Ibuf Aio reads:0, log I/O's:0, sync I/O'S0Pending Flushes (fsync) log:0; Buffer pool:0480OS file reads, -OS file writes, -OS Fsyncs0.00READS/S,0Avg Bytes/read,0.00WRITES/S,0.00fsyncs/s-------------------------------------INSERT BUFFER and ADAPTIVE HASH INDEX-------------------------------------ibuf:size1, the free list len0, SEG size2,0mergesmerged Operations:insert0, delete mark0, delete0Discarded Operations:insert0, delete mark0, delete0Hash Table Size553253, node Heap has1buffer (s)0.00Hash searches/s,0.00Non-hash searches/s---LOG---Log Sequence number2555311Log flushed up to2555311Pages flushed to2555311Last checkpoint at25553110Pending Log writes,0Pending CHKP writes -Log I/O's done, 0.00 log I/O's/Second----------------------BUFFER POOL and MEMORY----------------------Total Memory Allocated136052736;inchAdditional Pool Allocated0Dictionary Memory Allocated101103Buffer Pool Size8192Free buffers7737Database pages454Old Database pages0Modified db pages0Pending reads0Pending Writes:lru0, flush List0, single page0Pages made young0, not young00.00YOUNGS/S,0.00non-youngs/Spages Read448, created6, written -0.00READS/S,0.00CREATES/S,0.00writes/sNo Buffer Pool page gets since the last printoutpages read ahead0.00/s, evicted without access0.00/s, Random read ahead0.00/LRU Len:454, Unzip_lru Len:0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0Queries inside InnoDB,0QueriesinchQueue0read views open inside Innodbmain thread ID3908, State:sleepingnumber of rows inserted3, updated1, deleted0, read10.00INSERTS/S,0.00UPDATES/S,0.00DELETES/S,0.00reads/s----------------------------END of INNODB MONITOR OUTPUT============================1Rowinch Set(0.08sec) MySQL>

MySQL Lock monitor