Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use show engine innodb status in Mysql, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Mysql > show engine innodb status\ G

* * 1. Row *

Type: InnoDB

Name:

Status:

= =

2016-11-17 10:20:57 7f5fd92a8700 INNODB MONITOR OUTPUT

= =

Per second averages calculated from the last 5 seconds

-

BACKGROUND THREAD

-

Srv_master_thread loops: 263748 srv_active, 0 srv_shutdown, 959366 srv_idle

Srv_master_thread log flush and writes: 1223114

Most of the core operations of the InnoDB storage engine are concentrated in the Mater Thread background thread, which shows the status information of the background thread and the main work of Master Thread:

The main cycle (loop) mainly performs operations such as refreshing log cache, merging insert cache, refreshing dirty page cache, deleting useless undo pages, etc., at the frequency of every second and every ten seconds.

If there is no current user activity, enter the background loop process (backgroud loop), which mainly deletes useless undo pages and merges and inserts the cache.

If there is nothing left to do, enter the suspend loop loop and wait for the event loop to call

-BACKGROUND THREAD-# srv_active is the number of cycles per second, srv_idle is the number of cycles per 10 seconds, srv_shutdown is the stopped cycle, usually 0 # if the number of cycles per second is less, more than every 10 seconds, it proves that the current load is very low If the number of cycles per second is more and the number of times per 10 seconds is less, which is much more than 10:1, it is proved that the current load is very high. Srv_master_thread loops: 2818842 srv_active, 0 srv_shutdown, 411 srv_idle# log buffer flushing times srv_master_thread log flush and writes: 2819194

-

SEMAPHORES

-

OS WAIT ARRAY INFO: reservation count 335693

OS WAIT ARRAY INFO: signal count 7995932

Mutex spin waits 2260302, rounds 4566188, OS waits 73333

RW-shared spins 4335920, rounds 14935679, OS waits 171554

RW-excl spins 209573, rounds 11472909, OS waits 84315

Spin rounds per wait: 2.02 mutex, 3.44 RW-shared, 54.74 RW-excl

-

LATEST DETECTED DEADLOCK

-

2016-11-16 14:54:08 7f5fdcd77700

* * (1) TRANSACTION:

TRANSACTION 200992143, ACTIVE 2 sec fetching rows

Mysql tables in use 4, locked 4

LOCK WAIT 1760 lock struct (s), heap size 210472, 339660 row lock (s), undo log entries 1

MySQL thread id 591553, OS thread handle 0x7f5fdcdb8700, query id 36452144 192.168.10.42 VIPUSER Sending data

UPDATE pre_order

LEFT JOIN `order`ON `order`.pre _ order_id = pre_order.id

LEFT JOIN loan_demand ON loan_demand.id = pre_order.demand_id

LEFT JOIN store_customer ON store_customer.mobile = pre_order.lender_phone

SET pre_order.saleman_id = 224

`order`.saleman _ id = 224

Loan_demand.saleman_charge = 287

Store_customer.saleman_id=224

Store_customer.update_time = 1479279001

WHERE

`store_ customer`.mobile = 18662175906 and pre_order.lender_phone=18662175906 and pre_order.status in (4)

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1558 page no 3636 n bits 248index `PRIMARY` of table `vip`.`store _ customer` trx id 200992143 lock_mode X waiting

Record lock, heap no 141PHYSICAL RECORD: n_fields 18; compact format; info bits 0

0: len 4; hex 00053499; asc 4

1: len 6; hex 00000bfae59a; asc

2: len 7; hex a80000015c012a; asc\ *

3: len 4; hex 0000000b; asc

4: len 4; hex 0000013a; asc:

5: len 4; hex 00062e65; asc .e

6: len 11; hex 3135323334313337313233; asc 15234137123

7: len 1; hex 81; asc

8: len 1; hex 02; asc

9: len 4; hex 582c028e; asc X,

10: len 4; hex 0000000; asc

11: len 1; hex 02; asc

12: len 1; hex 00; asc

13: len 4; hex 0000000; asc

14: len 4; hex 0000000; asc

15: len 1; hex 00; asc

16: len 1; hex 00; asc

17: len 1; hex 02; asc

* * (2) TRANSACTION:

TRANSACTION 200992154, ACTIVE 2 sec starting index read

Mysql tables in use 1, locked 1

13 lock struct (s), heap size 2936, 92 row lock (s), undo log entries

MySQL thread id 592115, OS thread handle 0x7f5fdcd77700, query id 36453180 192.168.10.42 VIPUSER updating

UPDATE `store_ customer` SET `mobile` = '13903404842, `pre_order_ id` = 11, `saleman_ id` = 314,`pre_order_ id` = 405131, `update_ pre_order_ = 1, `update_ time` = 1479279248 WHERE (`id` = 303168)

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 1558 page no 3636 n bits 248index `PRIMARY` of table `vip`.`store _ customer` trx id 200992154 lock_mode X locks rec but not gap

Record lock, heap no 141PHYSICAL RECORD: n_fields 18; compact format; info bits 0

0: len 4; hex 00053499; asc 4

1: len 6; hex 00000bfae59a; asc

2: len 7; hex a80000015c012a; asc\ *

3: len 4; hex 0000000b; asc

4: len 4; hex 0000013a; asc:

5: len 4; hex 00062e65; asc .e

6: len 11; hex 3135323334313337313233; asc 15234137123

7: len 1; hex 81; asc

8: len 1; hex 02; asc

9: len 4; hex 582c028e; asc X,

10: len 4; hex 0000000; asc

11: len 1; hex 02; asc

12: len 1; hex 00; asc

13: len 4; hex 0000000; asc

14: len 4; hex 0000000; asc

15: len 1; hex 00; asc

16: len 1; hex 00; asc

17: len 1; hex 02; asc

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1558 page no 3206 n bits 288 index `PRIMARY` of table `vip`.`store _ customer` trx id 200992154 lock_mode X locks rec but not gap waiting

Record lock, heap no 33 PHYSICAL RECORD: n_fields 18; compact format; info bits 0

0: len 4; hex 0004a040; asc @

1: len 6; hex 00000a77948a; asc w

2: len 7; hex 48000001611c78; asc H a x

3: len 4; hex 0000000b; asc

4: len 4; hex 000000ad; asc

5: len 4; hex 0005888d; asc

6: len 11; hex 3133393033343034383432; asc 13903404842

7: len 1; hex 83; asc

8: len 1; hex 01; asc

9: len 4; hex 581bf514; asc X

10: len 4; hex 581bf621; asc X!

11: len 1; hex 02; asc

12: len 1; hex 00; asc

13: len 4; hex 0000000; asc

14: len 4; hex 0000000; asc

15: len 1; hex 00; asc

16: len 1; hex 00; asc

17: len 1; hex 01; asc

* WE ROLL BACK TRANSACTION (2)

-

TRANSACTIONS

-

Trx id counter 202747662

Purge done for trx's NRO < 202747617 undo NRO < 0 state: running but idle

History list length 2516-and unpurge 2516

LIST OF TRANSACTIONS FOR EACH SESSION:

-TRANSACTION 0, not started

MySQL thread id 626494, OS thread handle 0x7f5fd92a8700, query id 38472637 127.0.0.1 root init

Show engine innodb status

-

FILE I/O

-

I thread 0 state: waiting for completed aio requests (insert buffer thread)

I thread 1 state: waiting for completed aio requests (log thread)

I thread 2 state: waiting for completed aio requests (read thread)

I thread 3 state: waiting for completed aio requests (read thread)

I thread 4 state: waiting for completed aio requests (read thread)

I thread 5 state: waiting for completed aio requests (read thread)

I thread 6 state: waiting for completed aio requests (write thread)

I thread 7 state: waiting for completed aio requests (write thread)

I thread 8 state: waiting for completed aio requests (write thread)

I thread 9 state: waiting for completed aio requests (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's: 0

Pending flushes (fsync) log: 0; buffer pool: 1

117165 OS file reads, 4860463 OS file writes, 3532584 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 9.00 writes/s, 9.20 fsyncs/s

-

INSERT BUFFER AND ADAPTIVE HASH INDEX

-

Ibuf: size 1, free list len 75, seg size 77,839 merges-insert buffer merge insert

Merged operations:

Insert 373, delete mark 904, delete 130

Discarded operations:

Insert 0, delete mark 0, delete 0

Hash table size 4425293, node heap has 7459 buffer (s)

37262.35 hash searches/s, 269.75 non-hash searches/s

-

LOG

-

Log sequence number 9856475404

Log flushed up to 9856475404

Pages flushed up to 9856471068

Last checkpoint at 9856470666

0 pending log writes, 0 pending chkp writes

1186853 log i/o's done, 2.40 log i/o

--

BUFFER POOL AND MEMORY

--

Total memory allocated 10989076480; in additional pool allocated 0

Dictionary memory allocated 932159

Buffer pool size 655352-innodb buffer size size 655353 pages * 16k

Free buffers 8505-number of pages in the free list

Database pages 624627-number of pages in the lRu list

Old database pages 230411-the number of old list pages in the lru list (non-thermal fast data: 5max 8-last on the list)

Modified db pages 26-dirty pages, which exist in flush list and lru list, each doing its own job

Pending reads 0

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

Pages made young 8034747, not young 844963

23.47 youngs/s, 0.00 non-youngs/s

Pages read 78033, created 1790917, written 492746631

0.00 reads/s, 0.47 creates/s, 42.94 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000-the memory hit ratio should be greater than 95%, otherwise sql needs to be optimized.

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

LRU len: 624627, unzip_LRU len: 0

--

INDIVIDUAL BUFFER POOL INFO

--

-BUFFER POOL 0

Buffer pool size 16384

Free buffers 1025

Database pages 14428

Old database pages 5305

Modified db pages 1

Pending reads 0

Pending writes: LRU 0, flush list 1, single page 0

Pages made young 111884, not young 729335

0.00 youngs/s, 0.00 non-youngs/s

Pages read 14143, created 18647, written 441843

0.00 reads/s, 0.00 creates/s, 0.40 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: 14428, unzip_LRU len: 0

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

-BUFFER POOL 1

Buffer pool size 16384

Free buffers 1026

Database pages 14430

Old database pages 5306

Modified db pages 0

Pending reads 0

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

Pages made young 106938, not young 556784

0.00 youngs/s, 0.00 non-youngs/s

Pages read 13932, created 18419, written 259908

0.00 reads/s, 0.00 creates/s, 0.20 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: 14430, unzip_LRU len: 0

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

-BUFFER POOL 2

Buffer pool size 16384

Free buffers 1025

Database pages 14414

Old database pages 5300

Modified db pages 2

Pending reads 0

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

Pages made young 111161, not young 408726

0.00 youngs/s, 0.00 non-youngs/s

Pages read 14114, created 18430, written 406694

0.00 reads/s, 0.00 creates/s, 0.60 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: 14414, unzip_LRU len: 0

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

-BUFFER POOL 3

Buffer pool size 16384

Free buffers 1025

Database pages 14420

Old database pages 5303

Modified db pages 4

Pending reads 0

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

Pages made young 113167, not young 708499

0.00 youngs/s, 0.00 non-youngs/s

Pages read 14242, created 18731, written 409053

0.00 reads/s, 0.00 creates/s, 0.40 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: 14420, unzip_LRU len: 0

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

-BUFFER POOL 4

Buffer pool size 16384

Free buffers 1027

Database pages 14440

Old database pages 5310

Modified db pages 0

Pending reads 0

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

Pages made young 110331, not young 461397

0.00 youngs/s, 0.00 non-youngs/s

Pages read 14224, created 18645, written 278405

0.00 reads/s, 0.00 creates/s, 0.20 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: 14440, unzip_LRU len: 0

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

-BUFFER POOL 5

Buffer pool size 16384

Free buffers 1024

Database pages 14425

Old database pages 5304

Modified db pages 2

Pending reads 0

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

Pages made young 108069, not young 370734

0.00 youngs/s, 0.00 non-youngs/s

Pages read 13755, created 18494, written 233833

0.00 reads/s, 0.00 creates/s, 0.40 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: 14425, unzip_LRU len: 0

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

-BUFFER POOL 6

Buffer pool size 16384

Free buffers 1027

Database pages 14426

Old database pages 5305

Modified db pages 4

Pending reads 0

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

Pages made young 106842, not young 789185

0.00 youngs/s, 0.00 non-youngs/s

Pages read 13862, created 18461, written 351585

0.00 reads/s, 0.00 creates/s, 0.60 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: 14426, unzip_LRU len: 0

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

-BUFFER POOL 7

Buffer pool size 16384

Free buffers 1025

Database pages 14426

Old database pages 5305

Modified db pages 5

Pending reads 0

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

Pages made young 109852, not young 628401

0.00 youngs/s, 0.00 non-youngs/s

Pages read 14021, created 18798, written 336451

0.00 reads/s, 0.00 creates/s, 0.60 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: 14426, unzip_LRU len: 0

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

-

ROW OPERATIONS

-

0 queries inside InnoDB, 0 queries in queue

0 read views open inside InnoDB

Main thread process no. 13133, id 140049721313024, state: sleeping

Number of rows inserted 2464227, updated 569725, deleted 1790, read 56368048059

1.40 inserts/s, 1.80 updates/s, 0.00 deletes/s, 206769.85 reads/s

-

END OF INNODB MONITOR OUTPUT

= = 1 row in set (0.00 sec) ERROR:

No query specified

Click (here) to collapse or open

--

BUFFER POOL AND MEMORY

--

Total memory allocated 10989076480; in additional pool allocated 0

Dictionary memory allocated 932159

Buffer pool size 655352-innodb buffer size size 655353 pages * 16k

Free buffers 8505-number of pages in the free list

Database pages 624627-number of pages in the lru list

Old database pages 230411-the number of old list pages in the lru list (non-thermal fast data: 5max 8-last on the list)

Modified db pages 26-dirty pages, which exist in flush list and lru list, each doing its own job

Pending reads 0

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

Pages made young 8034747, not young 844963

23.47 youngs/s, 0.00 non-youngs/s

Pages read 78033, created 1790917, written 492746631

0.00 reads/s, 0.47 creates/s, 42.94 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000-the memory hit ratio should be greater than 95%, otherwise sql needs to be optimized.

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

LRU len: 624627, unzip_LRU len: 0

This section shows information about buffer pool and memory utilization. You can see all the memory allocated by Innodb (sometimes more than you set), as well as the additional memory pool allocation (you can check whether it is the right size), how many memory pages there are in the buffer pool, how many free memory pages are available, how many memory pages are allocated by the database, and how many dirty memory pages are there. From this information, you can judge whether the memory buffer pool is set properly, and if there are always a large number of free memory pages, you don't need to set up so much memory, you can reduce it a little bit. If the free memory page is 0, the database memory page is not necessarily the same as the total number of buffer pools, because the buffer pool also needs to hold lock information, adaptive hash indexing, and other system structure information.

Read and write while waiting refers to requests at the memory buffer pool level. Innodb may merge multiple file-level requests into one, so they are different. We can also see the pages that need to be refreshed among the different types of IO,LRU memory pages submitted by Innodb-dirty memory pages, which will not be accessed for a long time; refresh list-

Old memory pages that need to be refreshed after the checkpoint process is finished; stand-alone memory pages-separate write memory pages.

We can also see how many times the memory page has been read and written. The memory page that has been created is an empty memory page created specifically for new data when the contents of the current memory page are not read into the memory buffer pool.

Finally, we can see the hit rate of the buffer pool, which indicates the efficiency of the buffer pool. 1000 is equivalent to a 100% hit rate. However, it is difficult to show that the hit rate of the buffer pool is high enough, depending on the load environment. In general, 950amp 1000 is enough, and sometimes in environments with high IO loads, the hit rate may be 995Comp1000.

Thank you for reading this article carefully. I hope the article "how to use show engine innodb status in Mysql" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.