Parse the parameters of InnoDB status

Mysql> show InnoDB status\g;
1. Row ***************************
Type:innodb
Name:
Status:
=====================================
140423 4:15:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated to the last seconds

The parameter values are basically in seconds and you need to select the appropriate time interval sampling.

======================================semaphores======================================

----------
Semaphores

----------

A large number of threads waiting for semaphores means disk I/O or InnoDB connectivity issues. Contention can be initiated by heavy query parallelism or OS thread scheduling. A large number of connections will reduce innodb_thread_concurrency (global, dynamic, 8 by default, 0-1,000). 0=unlimited (buffet, if everyone goes at once it'll take a long time).
InnoDB take multiphase wait policy. First, try spin wait, and the CPU cycles check to see if threads is running. And then upgrade to OS wait, which triggers the context switch to run another thread.
Spin waits = More CPUs, no context switch vs. OS waits.

The number of times to wait for an idle InnoDB mutex before Innodb_sync_spin_loops:thread is suspended, the default value (Global, dynamic, Range is 0-4,294,967,295).
OS WAITS ARRAY shows reservations for-os WAITS vs. how-many signals were actually to sent.

Based on where locking is, we can know what thread is waiting for, buffer or index write. For example, SRV0SRV.C is raised by a long select for UPDATE query waits.

Semaphore is a means of protecting the amount or abstract data types used to form access to restricted shared resources, such as shared memory in a parallel computing environment. is basically implemented in a lock mode.

OS wait ARRAY info:reservation count 182944079, signal count 129485790

The current wait list, if the number of concurrent numbers is large, innodb must frequently wait for the OS (this part does not contain the wait that is resolved by spinlock). Reservation count and signal count indicate how frequently innodb uses an internal synchronized array, which indicates how often innodb needs to wait for the OS.

--thread 140383712171776 has waited at Btr/btr0cur.c Line 378 for 0.00 seconds The semaphore:

btr0pcur.c:b-tree/persistent cursor, index tree persistent cursor
X-lock on Rw-latch at 0x7fad640306a0 created in file dict/dict0dict.c line 1353
A writer (thread ID 140383712171776) has reserved it in mode wait exclusive
Number of readers 1, Waiters flag 1

Waiters flag: Number of currently waiting threads
Last time read locked in file btr/btr0cur.c line 385
Last time write locked in file btr/btr0cur.c line 378

--thread 140383703385856 has waited at Btr/btr0cur.c line 385 for 0.00 seconds The semaphore:
S-lock on Rw-latch at 0x7fad640306a0 created in file dict/dict0dict.c line 1353
A writer (thread ID 140383712171776) has reserved it in mode wait exclusive
Number of readers 1, Waiters flag 1
Last time read locked in file btr/btr0cur.c line 385
Last time write locked in file btr/btr0cur.c line 378

--thread 140382476871424 has waited at IBUF/IBUF0IBUF.C line 738 for 0.00 seconds The semaphore:

Ibuf0ibuf.c:insert buffer/, Insert buffer
X-lock on Rw-latch at 0x7fada8d03fb0 created in file BUF/BUF0BUF.C line 550
A writer (thread ID 140383711905536) has reserved it in mode wait exclusive
Number of readers 1, Waiters flag 1
Last time read locked in file buf/buf0flu.c line 660
Last time write locked in file IBUF/IBUF0IBUF.C line 738

Add: Srv0srv.c:server/server, Server main program
Mutex spin waits 0, Rounds 41511596793, OS waits 84253954

rw-shared spins 92745676, OS waits 40399910; Rw-excl spins 71767529, OS waits 49673121

Spin waits and spin rounds: compared to OS wait, the spin locks is a low cost waiting, but it is an active waiting that wastes CPU resources, so spin waits and spin rounds if it is very large, CPU resources will be wasted. If the value reaches hundreds of thousands of, the CPU is in danger.

Os Waits: Indicates a wait for a mutex OS. OS Waits is relatively slow, and if this value is tens of thousands of per second, there is a problem. Another way to look at this value is through the OS state of the context switch.

This part of the State is related to the following parameters:

Innodb_sync_spin_loops
Innodb_thread_concurrency

==========================latest detected deadlock===========================

------------------------LATEST detected deadlock

------------------------

Heap Size: The memory size used to hold the row locks.

Record the transaction that caused the deadlock. They are in the state of the deadlock, the lock possessed, waiting for the secured lock. InnoDB decide which transaction roll back to resolve deadlock (select the transaction that has the least update operation).

Note: Only a small number of existing locks are printed, along with statements for the last lock rows of each transaction. Heap Size: The memory size used to hold the row locks. 060717 4:16:48
(1) TRANSACTION:
TRANSACTION 0 42313619, ACTIVE sec, Process no 10099, OS thread ID 3771312 starting index read
MySQL tables in use 1, locked 1
Lock wait 3 lock struct (s), Heap size 320
MySQL thread ID 30898, query id 100626 localhost root updating
Update iz set pad= ' a ' where i=2
(1) Waiting for this LOCK to be granted:
Record LOCKS Space ID 0 page no 16403 n bits PRIMARY ' of table ' Test/iz ' Trx ID 0 42313619 lock_mode X LOCKS Rec But not gap waiting
Record lock, Heap No 5 physical record:n_fields 4; Compact format; Info bits 0
0:len 4; Hex 80000002; ASC;; 1:len 6; Hex 00000285a78f; ASC;; 2:len 7; Hex 00000040150110; ASC @;; 3:len 10; Hex 61202020202020202020; ASC A;;
(2) TRANSACTION:
TRANSACTION 0 42313620, ACTIVE sec, Process no 10099, OS thread ID 4078512 starting index read, thread declared inside InnoDB 500
MySQL tables in use 1, locked 1
3 lock struct (s), Heap size 320
MySQL thread ID 30899, query id 100627 localhost root updating
Update iz set pad= ' a ' where i=1
(2) holds the LOCK (S):
Record LOCKS Space ID 0 page no 16403 n bits PRIMARY ' of table ' Test/iz ' Trx ID 0 42313620 lock_mode X LOCKS Rec But not gap
Record lock, Heap No 5 physical record:n_fields 4; Compact format; Info bits 0
0:len 4; Hex 80000002; ASC;; 1:len 6; Hex 00000285a78f; ASC;; 2:len 7; Hex 00000040150110; ASC @;; 3:len 10; Hex 61202020202020202020; ASC A;;
(2) Waiting for this LOCK to be granted:
Record LOCKS Space ID 0 page no 16403 n bits PRIMARY ' of table ' Test/iz ' Trx ID 0 42313620 lock_mode X LOCKS Rec But not gap waiting
Record lock, Heap No 4 physical record:n_fields 4; Compact format; Info bits 0
0:len 4; Hex 80000001; ASC;; 1:len 6; Hex 00000285a78e; ASC;; 2:len 7; Hex 000000003411d9; ASC 4;; 3:len 10; Hex 61202020202020202020; ASC A;;

WE ROLL back TRANSACTION (2)

==========================transactions=======================================

------------
Transactions
------------

MVCC: No transaction is currently using this version, purging occurs in the undo area. Once the operation is complete, you do not need to keep the data and purge will not occur. Current TXN # and last purged Txn # are different.

If there are fewer connections, they are all listed, otherwise they will only be listed without making the output of show INNODB status large.

This section is related to the following parameters:

Innodb_thread_concurrency
Innodb_thread_sleep_delay
Innodb_commit_concurrency
Innodb_concurrency_tickets

Trx ID Counter 0 149268577
Purge for Trx ' s N:o < 0 149201387 undo N:o < 0 0

Transaction ID: Current Transaction identification, incremented with Transaction increment.

Purge for Trx ' s N:o : The number of transactions that have completed purge, Innodb can only purge older versions that are not required in the currently running transactions. The old uncommitted transactions are blocked purge and no longer consume resources. By comparing the counters for the current transaction and the last purged transactions, we can find this, that is, 149268577 and 149201387. Sometimes, purge can't keep up with the speed of update, at this time, need to set a reasonable innodb_max_purge_lag value.

Undo N:o: Represents the number of records currently being purge in the undo log, or 0 if not fired.
History List Length 30800

The number of transactions that have not been purge in the undo space, which increases with the completion of updates and commited transactions, and decreases as purge runs.

LIST of transactions for each session:
---TRANSACTION 0 0, not started, process no 4797, OS thread ID 140383716431616

Not started: Indicates that the connection does not have an active INNODB transaction, otherwise this is active. Note: Multiple statement transaction, even if this connection is a sleep state, it is active.

InnoDB also outputs the OS thread_id and process IDs to help us connect the running mysqld through GDB for troubleshooting or similar purposes.
MySQL thread ID 16797, query ID 3017516449 localhost root
Show InnoDB Status
---TRANSACTION 0 0, not started, process no 4797, OS thread ID 140383079786240
MySQL thread ID 16698, query ID 3017206263 10.66.137.122 hss_devel

---TRANSACTION 0 149268386, not started, process no 4797, OS thread ID 140383731074816
MySQL thread ID 16696, query ID 3017206243 10.66.137.122 hss_devel

---TRANSACTION 0 0, not started, process no 4797, OS thread ID 140383691671296
MySQL thread ID 16689, query ID 3017206165 10.66.136.110 hss_devel
---TRANSACTION 0 149268377, not started, process no 4797, OS thread ID 140383082182400
MySQL thread ID 16688, query ID 3017206161 10.66.136.110 hss_devel
---TRANSACTION 0 149268381, not started, process no 4797, OS thread ID 140383702853376
MySQL thread ID 16671, query ID 3017206189 ds.app.test.eng.nay.redhat.com 10.66.80.134 hss_devel
---TRANSACTION 0 0, not started, process no 4797, OS thread ID 140383692470016
MySQL thread ID 14828, query ID 3017206264 10.66.136.184 hss_devel
---TRANSACTION 0 144974395, not started, process no 4797, OS thread ID 140383072331520
MySQL thread ID 10821, query ID 3017206264 10.66.136.251 hss_devel
---TRANSACTION 0 0, not started, process no 4797, OS thread ID 140383082448640
MySQL thread ID 10820, query ID 3017206264 10.66.136.251 hss_devel
---TRANSACTION 0 149268576, ACTIVE 2 sec, Process no 4797, OS thread ID 140383084046080 waiting in InnoDB queue
MySQL tables in use 1, locked 1
1 lock struct (s), heap size 368, 0 row lock (s), undo log Entries 129
MySQL thread ID 16799, query ID 3017516448 etl-hss-devel.app.eng.bos.redhat.com 10.16.101.22 hss_devel Update
Insert INTO staging. Bugzillas_flags values (360280,437640,10, '? ', 84099,null, ' 2008-03-15 12:55:16 ', ' 2008-03-15 12:55:16 ')
---TRANSACTION 0 149268575, ACTIVE 3 sec, Process no 4797, OS thread ID 140383736665856 waiting in InnoDB queue
MySQL tables in use 1, locked 1
1 lock struct (s), heap size 368, 0 row lock (s), Undo log Entries 146
MySQL thread ID 16798, query ID 3017516447 etl-hss-devel.app.eng.bos.redhat.com 10.16.101.22 hss_devel Update
Insert INTO staging. Bugzillas_flags values (350234,430145,152, ' + ', 193983,null, ' 2008-01-24 14:57:06 ', ' 2008-01-24 14:57:06 ')
---TRANSACTION 0 149268496, ACTIVE 143 sec, Process no 4797, OS thread id 140383711905536 inserting, thread declared Insid E InnoDB 500

1. The transaction state is displayed by the action being performed by the transaction as: fetching Rows,updating or a pair of other values. Thread declared inside InnoDB 500: Indicates that this thread runs in InnoDB kernel and that 500tickets is available. InnoDB attempts to restrict thread concurrency and only allow innodb_thread_concurrency thread peers to run with InnoDB kernel.

MySQL tables in use 1, locked 1
1 lock struct (s), heap size 368, 0 row lock (s), undo log Entries 7777
MySQL thread ID 16723, query ID 3017516369 etl-hss-devel.app.eng.bos.redhat.com 10.16.101.22 hss_devel Update
Insert INTO staging. Bugzillas_bugs_tmp values (67776,111, ' 2.1 ', 35221, ' errata ', '---', ' CLOSED ', ' Medium ', ' Medium ', 84977,84973,null, ', ', ' ', ' with cluster turned off, cluadmin \ ' cluster status\ ' gets error \ ' clugetdisknodesstates failed\ ' ', ' 2002-07-01 17:50:18 ', ' 2008-05-01 11:38:02 ')

---TRANSACTION 0 149268576, ACTIVE 2 sec, Process no 4797, OS thread ID 140383084046080 waiting in InnoDB queue

2. If thread does not run in InnoDB kernel, the status is: Waiting in InnoDB queue or sleeping before joining queue.

To avoid too much thread trying to enter the InnoDB queue at the same time, sleep some time before attempting to wait (no idle slot available), which will make the number of threads active within kernel less than innodb_thread_ Concurrency the specified value, which reduces the amount of time thread waits to enter the queue. Implemented by adjusting parameter innodb_thread_sleep_delay.
MySQL tables in use 1, locked 1

1 lock struct (s), heap size 368, 0 row lock (s), undo log Entries 129
MySQL thread ID 16799, query ID 3017516448 etl-hss-devel.app.eng.bos.redhat.com 10.16.101.22 hss_devel Update
Insert INTO staging. Bugzillas_flags values (360280,437640,10, '? ', 84099,null, ' 2008-03-15 12:55:16 ', ' 2008-03-15 12:55:16 ')
---TRANSACTION 0 149268491, ACTIVE 145 sec, Process no 4797, OS thread ID 140383734535936 waiting in InnoDB queue
MySQL tables in use 1, locked 0

3. mysql tables in use 1, locked 1: The data table used by this transaction and the number of data tables that are locked by this transaction. InnoDB does not lock the table for general operations, so the locked value is typically 0, but LOCK tables will be used for ALTER TABLE or similar statements.
MySQL thread ID 16733, query ID 3017220245 10.66.137.122 hss_devel copying to TMP table
Select M.bug_product product from (SELECT DISTINCT bug_product to Fact.v_open_bug_trend_detail_dt order by Bug_product) M
Trx read view would not the TRX with ID >= 0 149268492, sees < 0 149201352
---TRANSACTION 0 149201352, ACTIVE 17960 sec, Process no 4797, OS thread ID 140383736399616
MySQL thread ID 14829, query ID 3017206264 10.66.136.184 hss_devel
Trx read view would not the TRX with ID >= 0 149201353, sees < 0 149201351

===================================file i/o=========================================

--------
FILE I/O
--------

File IO Helper Threads–insert the state of buffer thread, log thread, read thread and write thread. Corresponds to the following operations:  insert buffer merges, asynchronous log flushes, read-ahead and flushing of dirty Buffers. unix/linux general With 4 helper threads, you can see the thread state of each helper threads, if the thread is ready, its status is: Waiting for I/O request .
I/O thread 0 state:waiting for I/O request (insert buffer thread)
I/O thread 1 state:waiting for I/O request (L) OG thread)
I/O thread 2 state:waiting for I/O request (read thread)
I/O thread 3 state:waiting for I/O request ( Write thread)
Pending normal Aio reads:0, Aio writes:0,
 ibuf aio reads:0, log I/O ' s:0, sync I/o ' s:0
Pending Flushes (fsync) log:1; Buffer pool:0
182899700 os file reads, 111596903 OS file writes, 5072400 os fsyncs
174.20 reads/s, 22611 avg Byte S/read, 130.28 writes/s, 16.71 fsyncs/s

Pending Normal AIO: The number of Pending operations per helper thread, queued and executing execution. The fsync operand of the Pending flushes (Fsync) log:pending.

Writes: Ensures that data is flushed to disk when OS cache is low, typically by Fsync () to complete file modifications.

These variable values represent IO loads, which are not counted when the thread executes the request commit, so they are also seen as 0 when the IO is loaded.

Average IO operations, such as the mean size of the 22611 avg bytes/read:read request, random io typically 16K (page size), and full table Scan/index scan will significantly increase IO size, You can view read-ahead efficiency by this value. ===================== INSERT BUFFER and adaptive HASH index===================

-------------------------------------
INSERT BUFFER and adaptive HASH INDEX
-------------------------------------

innodb_buffer_pool_pages_misc:11% Innodb Buffer Pool
The Innodb_adaptive_hash_index state variable can close adaptive hash index.

When the insert uses the Non-unique Level two index, InnoDB puts the records into the insert buffer and then flush to disk after the transaction is submitted.

If a table is almost completely fits in memory, using the hash indexes is the quickest way to query. InnoDB the index search of the monitor table, if InnoDB notes that the build hash index will make the query benefit automatically created. InnoDB builds hash indexes on indexed pages that are often accessed.

Innodb_adaptive_hash_index:global, static (not dynamic).
Ibuf:size, free list len 1849, seg size 1933,

Insert Buffer Status: Segment size and free list sizes (if there are some records in insert buffer)
296920721 inserts, 296902154 merged RECs, 6883558 merges

Inserts operand, number of records merged, merges operand executed. The ratio merges/inserts represents the efficiency of the insert buffer.

Hash table Size 17393, node heap has 1 buffer (s)

Adaptive hash Index, is built on some pages of the hash index, using hash instead of btree retrieval to improve query speed. The hash table size, the number of cells used, and the number of buffers used by the adaptive Hash Index.
1334.98 Hash searches/s, 9359.05 non-hash searches/s

The hash Index/non-hash index represents the efficiency of the hash index.

We are currently unable to adjust the behavior of Insert BUFFER and Adaptive HASH index only for informational purposes.

=============================log==================================

---

LOG
---

Log subsystem information.
Log Sequence Number 408 205655208

Represents the number of bytes written to the log file since the system table space was created. InnoDB uses the fuzzy checkpointing, this line tracks log sequence, sequence before this number in the buffer pool has been flushed The sequence, which is higher than this number, is recorded only in logs but not flushed. Observe this value to determine if Innodb_log_buffer_size is optimized, and if you find that 30% is not flushed, you need to increase the value of this parameter.
Log flushed up to 408 205652469

Log file has been flush to which point
Last checkpoint at 408 201430671

The amount of data that has not been flush to the log buffer when the last checkpoint
1 Pending Log writes, 0 pending CHKP writes

38582740 log I/O ' s done, 5.07 log I/O ' S/second

The Log/io operation allows the IO associated with the tablespace to be separated from log-related IO, and you can see how much io the log file requires. This is related to the parameter innodb_flush_log_at_trx_commit, and if Innodb_flush_logs_at_trx_commit=2,log file is written to the OS cache, the sequential write logs will be very fast.

==================buffer POOL and memory==========================

----------------------
BUFFER POOL and MEMORY
----------------------
Total memory Allocated 123094498; In additional pool allocated 2097152

Allocate the memory size (sometimes higher than you expect) for the InnoDB, allocating the memory size for the additional pool (you can see if size is correct)

Dictionary Memory Allocated 1949344
Buffer Pool Size 512

Number of page in buffer pool
Free Buffers 0

Page number of free status in buffer pool

Database pages 511

The number of page allocations for the database
Modified db pages 379

Page number for dirty status

The above data can determine whether the buffer pool size is appropriate, and if a large number of free pages persists, it may mean that the active database is less than the allocated buffer pool, you can