InnoDB Information Description

InnoDB is the most critical database storage engine ever developed for MySQL database, which not only supports transactional features, but also has rich statistics to make it easy for database managers to understand the state of the recent InnoDB storage engine.

Earlier versions of the InnoDB storage engine were implemented by creating a new internal table named Innodb_monitor, and the InnoDB engine-related statistics were timed out innodb the table was detected internally. The InnoDB storage engine now uses the command show engine InnoDB status to view the statistics of the InnoDB storage engine, performing the show engine InnoDB status\g on the MySQL client, and the results obtained may be as follows:

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

140708 21:35:01 INNODB MONITOR OUTPUT

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

Per second averages calculated from the last seconds

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

BACKGROUND THREAD

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

Srv_master_thread loops:129 1_second, sleeps, 8 10_second, background, flush

Srv_master_thread Log Flush and writes:129

----------

Semaphores

----------

OS WAIT ARRAY info:reservation count 53606, signal count 55906

Mutex spin waits 525951, Rounds 2435436, OS waits 40130

Rw-shared spins 36905, rounds 444653, OS waits 9660

Rw-excl spins 5298, rounds 156489, OS waits 2307

Spin rounds per wait:4.63 mutex, 12.05 rw-shared, 29.54 rw-excl

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

Transactions

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

Trx ID Counter 19FFF

Purge done for Trx ' s N:o < 13696 undo N:o < 0

History list Length 15274

LIST of transactions for each SESSION:

---TRANSACTION 19f65, not started

MySQL tables in use 1, locked 0

MySQL thread ID, OS thread handle 0x7f8bfd1b9700, query ID 1040279 localhost root

SELECT c from Sbtest1 WHERE id=199077

.... ... any other transactions ... ..............................

--------

FILE I/O

--------

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)

.... Other three read threads .........................

I/O thread 6 state:waiting for completed AIO requests (write thread)

.... Other three write threads .........................

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 ' s:0

Pending Flushes (fsync) log:1; Buffer pool:0; Flash Cache 0

296 OS file reads, 47547 OS file writes, 40960 OS Fsyncs

0.00 READS/S, 0 avg bytes/read, 755.62 writes/s, 750.19 fsyncs/s

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

INSERT BUFFER and ADAPTIVE HASH INDEX

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

Ibuf:size 1, free list len 0, seg size 2, 0 merges

Merged operations:

Insert 0, delete mark 0, delete 0

Discarded operations:

Insert 0, delete mark 0, delete 0

Hash table Size 21249841, node heap has 294 buffer (s)

17860.30 Hash searches/s, 13956.39 non-hash searches/s

---

LOG

---

Log Sequence Number 184703155

Log flushed up to 184699426

Last checkpoint at 109334220

1 Pending Log writes, 0 pending CHKP writes

40703 log I/O ' s done, 749.84 log I/O ' S/second

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

BUFFER POOL and MEMORY

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

Total memory Allocated 10994319360; In additional pool allocated 0

Dictionary Memory Allocated 43910

Buffer Pool Size 655359

Free Buffers 646841

Database pages 8224

Old database Pages 3015

Modified db pages 5270

Pending reads 0

Pending WRITES:LRU 0, flush list 0, single page 0

Pages made young 1, isn't young 0

0.00 YOUNGS/S, 0.00 NON-YOUNGS/S

Pages read 149, created 8075, written 6557

0.00 READS/S, 38.13 creates/s, 5.52 writes/s

Buffer Pool hit rate 1000/1000, young-making rate 0/1000 not 0/1000

Pages Read ahead 0.00/s, evicted without access 0.00/s, Random read Ahead 0.00/s

LRU len:8224, Unzip_lru len:0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Async flush:0, Sync flush:0, LRU list flush:0, Flush list flush:6557

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

ROW OPERATIONS

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

0 queries inside InnoDB, 0 queries in queue

Read views open inside InnoDB

Main thread Process No. 30423, id 140238424880896, state:sleeping

Number of rows inserted 451965, updated 103933, deleted 51957, read 21009001

962.26 inserts/s, 1924.65 updates/s, 962.15 deletes/s, 381640.41 reads/s

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

END of INNODB MONITOR OUTPUT

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

II. Description of statistical information

According to the above display, the InnoDB storage engine statistics can be divided into:

1. Basic information

2. Background threads (background thread)

3. Semaphore (semaphores)

4. Transactions (Transactions)

5. I/O

6. Insert Buffer & Adaptive Hash

7. Log

8. Buffer Pool & Memory

9. Row operations

The above categories are described in conjunction with the information shown in the previous section.

Basic information

Per second averages calculated from the last seconds

InnoDB internal statistics are the average for a period of time, by default, 60s a time interval calculation, the 54s shown above is to perform the show Engine InnoDB status command from the last statistical end of 54s, the information shown below is the average in 54s, It is generally believed that this value is only meaningful when it is relatively large, because when the value is relatively small, accidental fluctuations can cause statistical information to vary greatly and not respond well to the internal realities of the InnoDB storage engine.

Background thread

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

BACKGROUND THREAD

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

Srv_master_thread loops:129 1_second, sleeps, 8 10_second, background, flush

Srv_master_thread Log Flush and writes:129

MySQL internal background has a thread responsible for data brush disk and undo cleanup (do not use a separate purge thread) to work, background thread is a dead loop, according to the InnoDB storage engine's busy level to perform data brush disk operations, the main operations such as:

From the background thread information, you can see that the 1s cycle has 129 times, 10s Cycle 8 times, 128 sleep and 76 times into background mode, 76 data brush disk and 129 log brush disk, the entire background The thread information shows that the INNODB internal is in a relatively idle state.

Semaphores

----------

Semaphores

----------

OS WAIT ARRAY info:reservation count 53606, signal count 55906

Mutex spin waits 525951, Rounds 2435436, OS waits 40130

Rw-shared spins 36905, rounds 444653, OS waits 9660

Rw-excl spins 5298, rounds 156489, OS waits 2307

Spin rounds per wait:4.63 mutex, 12.05 rw-shared, 29.54 rw-excl

The semaphore segment shows the internal lock information of the InnoDB storage engine, in the case of large concurrent requests, the INNODB inevitably exists resource competition, at this time, some transactions need to wait through the lock, wait for the related transaction to release the resources before proceeding, the hungry information shown above is InnoDB internal lock waiting information. In addition to the above information, if the InnoDB storage engine has a deadlock state, the corresponding deadlock information will be added below the above information.

Transactions

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

Transactions

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

Trx ID Counter 19FFF

Purge done for Trx ' s N:o < 13696 undo N:o < 0

History list Length 15274

The transaction segment information displays the transactions that are executing within the current InnoDB because the InnoDB storage engine supports concurrent execution of transactions, so there are many transactions within the InnoDB.

The transaction ID is incremented inside the InnoDB, from the current transaction information, the next transaction ID is 0X19FFF, and the current purge to the undo log number No is 13695, there are currently 15,274 transactions waiting for the purge operation, it can be seen from this information, The current InnoDB is in a busy state and the number of transactions waiting for Pruge is increasing.

The above section can be seen as a summary of the transaction, and list of transactions for each SESSION: the specific state of each transaction inside the current InnoDB is shown later:

---TRANSACTION 19f65, not started

MySQL tables in use 1, locked 0

MySQL thread ID, OS thread handle 0x7f8bfd1b9700, query ID 1040279 localhost root

SELECT c from Sbtest1 WHERE id=199077

The above shows the information of a transaction within the current InnoDB, not started indicates that the transaction is inactive, if it is active, active,mysql tables in use describes the number of tables for the current transaction operation, and locked indicates the number of locks on the table ( InnoDB internal support row lock, so the general table lock use less, only in such operations as ALTER TABLE is to lock the table, MySQL thread ID for the current connection assigned Id,os thread handle is the handle of the connection thread, query ID is the ID of the current query, and the specific query SQL is shown later.

/ o

--------

FILE I/O

--------

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)

.... Other three read threads .........................

I/O thread 6 state:waiting for completed AIO requests (write thread)

.... Other three write threads .........................

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 ' s:0

Pending Flushes (fsync) log:1; Buffer pool:0; Flash Cache 0

296 OS file reads, 47547 OS file writes, 40960 OS Fsyncs

READS/S, 0 avg bytes/read, 755.62 writes/s, 750.19 fsyncs/s

I/O segment shows the current INNODB engine internal I/O usage, InnoDB internal asynchronous I/O mode for data read and write operations, by default there are 1 insert buffer threads, 1 log threads, 4 read Data threads, and 4 write data threads.

The following part of the thread information is I/O statistics, a total of 296 reads, 47,547 writes, Fsync is 40,960 times, the average read data is 0, write input is 755.62 times/s, Fsyncs 750.19 times/s, from the above information analysis, The internal transactions of the InnoDB storage engine are basically write transactions, and I/O basically has reached a performance bottleneck.

Insert Buffer & Adaptive Hash

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

INSERT BUFFER and ADAPTIVE HASH INDEX

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

Ibuf:size 1, free list len 0, seg size 2, 0 merges

Merged operations:

Insert 0, delete mark 0, delete 0

Discarded operations:

Insert 0, delete mark 0, delete 0

The above section shows the insert buffer information, starting with MySQL 5.5, insert buffer not only statistics Insert information also includes delete and update information, called Change buffer (delete mark is update information, InnoDB internal update is implemented by setting the record delete flag to true.

From the above information, the change buffer does not involve the need to merge the insert/update/delete operation, the insert Buffer function is not available.

Hash table Size 21249841, node heap has 294 buffer (s)

17860.30 Hash searches/s, 13956.39 non-hash searches/s

InnoDB internal to a certain number of pages query, the internal will be a hash of the page index, innodb that when a page is frequently used for a period of time, then the probability of being used later will be very high, so a hash index for the page can speed up the search for the page. The hash table size shows the size of the internal adaptive hash table, and hash search and Non-hash search show the number of times per second used by adaptive hashing and normal index lookups for InnoDB internal lookups, and from the above information, the adaptive hash lookup accounted for 55%. The adaptive hash effect is obvious.

Log

---

LOG

---

Log Sequence Number 184703155

Log flushed up to 184699426

Last checkpoint at 109334220

1 Pending Log writes, 0 pending CHKP writes

40703 log I/O ' s done, 749.84 log I/O ' S/second

InnoDB log information shows the current internal redo log (redo log) write, InnoDB internally through the LSN to achieve redo log and transaction correspondence, log sequence number indicates that the current system internal transaction execution LSN is 184703155, While log flushed up to indicate that redo log has been saved to disk with an LSN of 184699426, the last checkpoint at indicates that the most recent data and redo log are saved to disk with an LSN of 109334220.

The information above will tell you that if some kind of accident occurs at this time, the transaction of LSN > 184699426 may be lost, and the transactions between LSN 109334220 and 184699426 will be recovered by redo log. LSN < 109334220 data, because it has been saved to disk, so it will not be lost due to downtime.

Buffer Pool & Memory

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

BUFFER POOL and MEMORY

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

Total memory Allocated 10994319360; In additional pool allocated 0

Dictionary Memory Allocated 43910

Buffer Pool Size 655359

Free Buffers 646841

Database pages 8224

Old database Pages 3015

Modified db pages 5270

Pending reads 0

Pending WRITES:LRU 0, flush list 0, single page 0

Pages made young 1, isn't young 0

0.00 YOUNGS/S, 0.00 NON-YOUNGS/S

Pages read 149, created 8075, written 6557

0.00 READS/S, 38.13 creates/s, 5.52 writes/s

Buffer Pool hit rate 1000/1000, young-making rate 0/1000 not 0/1000

Pages Read ahead 0.00/s, evicted without access 0.00/s, Random read Ahead 0.00/s

LRU len:8224, Unzip_lru len:0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Async flush:0, Sync flush:0, LRU list flush:0, Flush list flush:6557

InnoDB caches data internally using buffer pool, and for the InnoDB storage engine, the larger the buffer pool, the more data is cached in memory and the better the performance of the system. The above information shows that InnoDB occupies about 10G of memory,

The size of the buffer is approximately 655359 (unit 16K, same as below), while Idle is approximately 646841, the page used is 8224 + 3015, where the dirty page is 8224 and 3015 is a non-dirty page.

From the above information shows that InnoDB memory is too large, the data all fall in memory.

Buffer Pool hits rate 1000/1000 memory hit ratio 100%, the page waiting for Flush is 6557

Row operations

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

ROW OPERATIONS

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

0 queries inside InnoDB, 0 queries in queue

Read views open inside InnoDB

Main thread Process No. 30423, id 140238424880896, state:sleeping

Number of rows inserted 451965, updated 103933, deleted 51957, read 21009001

962.26 inserts/s, 1924.65 updates/s, 962.15 deletes/s, 381640.41 reads/s

Row operations shows the internal logging operation information for the INNODB storage engine, opens 61 read views, shows the main thread information and status, and finally shows all the insert/update/delete/since startup. Read quantity and average per second.

This article from the NetEase cloud community, by the author Shong authorized release. Original: InnoDB Information description

InnoDB Information Description