Show Engine InnoDB Status interpretation

Mysql> Show engine InnoDB status\g

1. Row ***************************

Type:innodb

Name:

Status:

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

2018-02-24 18:15:52 0x7f2ac00dd700 INNODB MONITOR OUTPUT

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

Per second averages calculated from the last seconds

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

BACKGROUND THREAD

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

Srv_master_thread loops:2 srv_active, 0 srv_shutdown, 23859 srv_idle

Srv_master_thread Log Flush and writes:23860

----------

Semaphores

----------

OS WAIT ARRAY info:reservation count 2

OS WAIT ARRAY info:signal count 2

Rw-shared spins 0, Rounds 4, OS waits 2

Rw-excl spins 0, rounds 0, OS waits 0

Rw-sx spins 0, rounds 0, OS waits 0

Spin rounds per wait:4.00 rw-shared, 0.00 RW-EXCL, 0.00 RW-SX

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

LATEST FOREIGN KEY ERROR

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

2018-01-29 23:23:31 0x7f984875e700 Transaction:

TRANSACTION 171061511, ACTIVE 1 sec inserting

MySQL tables in use 1, locked 1

Ten lock struct (s), heap size 1136, 6 row lock (s), Undo log Entries 5

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

LATEST Detected DEADLOCK # # # If there's a deadlock, there's a record

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

2018-02-24 18:52:09 2b6677e07700--the time when the deadlock occurred

(1) TRANSACTION:

(2) TRANSACTION:--Status of transaction 2

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

Transactions # # # contains statistics about INNODB transactions (transaction)

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

Trx ID Counter 5892

Purge done for Trx's N:o < 5386 undo N:o < 0 state:running but idle

History list Length 3

LIST of transactions for each SESSION:

---TRANSACTION 421297007339344, not started

0 lock struct (s), heap size 1136, 0 row lock (s)

--------

FILE I/O # # #显示了I the status of/O Helper thread D, including some statistics

--------

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)

I/O thread 3 state:waiting for completed AIO requests (read thread)

I/O thread 4 state:waiting for completed AIO requests (read thread)

I/O thread 5 state:waiting for completed AIO requests (read thread)

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

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

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

I/O thread 9 state:waiting for completed AIO requests (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:0

384 OS file reads, OS file writes, 7 OS Fsyncs

0.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 merges

Merged operations:

Insert 0, delete mark 0, delete 0

Discarded operations:

Insert 0, delete mark 0, delete 0

Hash table Size 34679, node heap has 0 buffer (s)

Hash table Size 34679, node heap has 0 buffer (s)

Hash table Size 34679, node heap has 0 buffer (s)

Hash table Size 34679, node heap has 0 buffer (s)

Hash table Size 34679, node heap has 0 buffer (s)

Hash table Size 34679, node heap has 0 buffer (s)

Hash table Size 34679, node heap has 0 buffer (s)

Hash table Size 34679, node heap has 0 buffer (s)

0.00 Hash SEARCHES/S, 0.00 Non-hash SEARCHES/S

---

Log # # #记录了transaction information about the log subsystem

---

Log Sequence Number 370441977

Log flushed up to 370441977

Pages flushed up to 370441977

Last checkpoint at 370441968

0 Pending log flushes, 0 pending CHKP writes

Ten log I/O ' s done, 0.00 log I/O ' S/second

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

Buffer pool and MEMORY # # # #INNODB buffer pool status,

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

Total large Memory allocated 137428992--size allocated to InnoDB, unit byte

Dictionary Memory Allocated 147231--size, unit byte, allocated to InnoDB data dictionary

Buffer pool size 8192--innodb buffer pool, Unit page if page size is set to 16k *16*1024 = Innodb_buffer_pool_si Ze

Free buffers 7914--innodb the number of idle pages in the buffer pool, units page

Database pages 278--innodb The number of non-free pages in the buffer pool, units page

Old database pages 0--old number of pages in the Child list, Unit page

Modified db pages 0-number of pages modified in the current buffer pool, Unit page

Pending reads 0--data read from disk to buffer pool, number of times suspended

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

Pages made young 0, no young 0--pages made the number of times that the old list was moved to the new list; the number of times that the new list moved the table old list

0.00 YOUNGS/S, 0.00 non-youngs/s--youngs/s The average number of pages per second moved from old to new,non-youngs/s the average number of pages per second moved from new to old

Pages read 243, created, written 42-the total number of pages read from buffer pool; The total number of pages created in the InnoDB buffer pool; InnoDB the total number of pages written in the buffer pool

0.00 READS/S, 0.14 creates/s, 42.14 writes/s-average number of pages per second read from InnoDB buffer pool, average InnoDB buffer pool per second how many pages to create, average InnoDB buff per few How many pages of ER pool are written

Buffer pool hit rates 1000/1000, young-making rates 0/1000 not 0/1000-InnoDB buffer pool hits

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

--pages read ahead average number of read ahead per second, evicted without access average number of page outs per second, random read ahead average random read ahead per second

LRU len:80675, Unzip_lru len:0

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

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

Individual BUFFER pool INFO ############ #如设置了innodb_buffer_pool_instances, there are multiple sub-pool

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

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

ROW OPERATIONS

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

0 queries inside InnoDB, 0 queries in queue

0 Read views open inside InnoDB

Process id=2969, Main thread id=139821729601280, state:sleeping

Number of rows inserted 0, updated 0, deleted 0, read 12

0.00 INSERTS/S, 0.00 UPDATES/S, 0.00 DELETES/S, 0.00 READS/S

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

END of INNODB MONITOR OUTPUT

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

1 row in Set (0.00 sec)

Mysql> \s

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

mysql  ver 14.14 distrib 5.7.20, for linux  (x86_64)  using   editline wrapperconnection id:4current database:performance_schemacurrent user:[ Email protected]ssl:not in usecurrent pager:stdoutusing outfile: ' Using  Delimiter:; server version:5.7.20-log mysql community server  (GPL) protocol version :10connection:localhost via unix socketserver characterset:utf8db      characterset:utf8client characterset:utf8conn.  characterset:utf8unix socket:/var/ Lib/mysql/mysql.sockuptime:7 hours 28 min 8 secthreads: 3  questions:  222  slow queries: 0  opens: 201  flush tables: 1   open tables: 194  queries per second avg: 0.008----------- ---

Show Engine InnoDB Status interpretation