Mysql中show engine innodb status怎么用

152次閱讀

沒有評論

共計 13242 個字符，預計需要花費 34 分鐘才能閱讀完成。

這篇文章主要介紹了 Mysql 中 show engine innodb status 怎么用，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

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

InnoDB 存儲引擎的核心操作大部分都集中在 Mater Thread 后臺線程中，該狀態顯示了后臺線程狀態信息，Master Thread 的主要工作：

主循環 (loop) 主要以每一秒和每十秒的頻率執行刷新日志緩存，合并插入緩存，刷新臟頁緩存，刪除無用 undo 頁等操作

如果當前沒有用戶活動，則進入后臺循環流程(backgroud loop)，主要執行刪除無用的 undo 頁，合并插入緩存

如果沒有什么事情可以做了，便進入了暫停循環(suspend loop)，等待事件循環喚起

-----------------
BACKGROUND THREAD
-----------------
# srv_active  為每秒的循環次數，srv_idle  為每  10  秒的的循環次數，srv_shutdown  為停止的循環，通常為  0 
#  如果每秒循環次數少，每  10  秒次數多，證明當前負載很低；如果每秒循環次數多，每  10  秒次數少，遠大于 10：1，證明當前負載很高
srv_master_thread loops: 2818842 srv_active, 0 srv_shutdown, 411 srv_idle
#  日志緩沖刷盤次數
srv_master_thread log flush and writes: 2819194

———-

SEMAPHORES

———-

WAIT ARRAY INFO: reservation count 335693

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 248 index `PRIMARY`
of table `vip`.`store_customer` trx id 200992143 lock_mode X waiting

Record lock, heap no 141 PHYSICAL
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 00000000; asc
;;

11: len 1; hex 02; asc
;;

12: len 1; hex 00; asc
;;

13: len 4; hex 00000000; asc
;;

14: len 4; hex 00000000; 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

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

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

UPDATE `store_customer` SET `mobile`= 13903404842 ,`store_id`=11,`saleman_id`=314,`pre_order_id`=405131,`is_repeat`=1,`update_time`=1479279248 WHERE
(`id`
= 303168
)

*** (2) HOLDS THE
LOCK(S):

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

Record lock, heap no 141 PHYSICAL
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 00000000; asc
;;

11: len 1; hex 02; asc
;;

12: len 1; hex 00; asc
;;

13: len 4; hex 00000000; asc
;;

14: len 4; hex 00000000; 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 00000000; asc
;;

14: len 4; hex 00000000; 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 n:o 202747617 undo n:o 0 state: running but idle

History list length 2516 — 還有 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/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, 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 合并插入

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 大小 655353 頁 *16k
Free buffers 8505 ——free 列表中頁的數量
Database pages 624627 —–lRu 列表中頁的數量
Old database pages 230411 ——lru 列表中 old list（非熱快數據：5/8- 列表最后）的頁的數量
Modified db pages 26 —— 臟頁，存在于 flush list 中也存在 lru list 中，各司其職
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 —— 內存命中率這個值應該大于 95%，不然 sql 需要優化啊
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

點擊 (此處) 折疊或打開

———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 10989076480; in additional pool allocated 0
Dictionary memory allocated 932159
Buffer pool size 655352 —–innodb buffer size 大小 655353 頁 *16k
Free buffers 8505 ——free 列表中頁的數量
Database pages 624627 —–lru 列表中頁的數量
Old database pages 230411 ——lru 列表中 old list（非熱快數據：5/8- 列表最后）的頁的數量
Modified db pages 26 —— 臟頁，存在于 flush list 中也存在 lru list 中，各司其職
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 —— 內存命中率這個值應該大于 95%，不然 sql 需要優化啊
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

這部分顯示了緩沖池和內存的利用率相關信息?？梢钥吹?Innodb 分配的所有內存(有些時候可能比你設置的還要多點)，以及額外的內存池分配情況 (可以檢查它的大小是否正好)，緩沖池總共有多少個內存頁，有多少空閑內存頁，數據庫分配了多少個內存頁以及有多少個臟內存頁。從這些信息中，就可以判斷內存緩沖池是否設定合理，如果總是有大量空閑內存頁，則不需要設置那么多內存，可以適當減小一點。如果空閑內存頁為 0，這種情況下數據庫內存頁就不一定會和緩沖池的總數一致，因為緩沖池還需要保存鎖信息，自適應哈希索引以及其他系統結構等信息。

等待中的讀寫是指內存緩沖池級別的請求。Innodb 可能會把多個文件級別的請求合并到一個上，因此各不相同。我們還可以看到 Innodb 提交的各種不同類型的 IO，LRU 內存頁中需要刷新的頁 – 臟內存頁，它們不會被長時間存??；刷新列表 –
檢查點進程處理完之后需要刷新的舊內存頁；獨立內存頁 – 獨立的寫內存頁。

我們還可以看到內存頁總共讀寫了多少次。已經創建的內存頁是當前一個內存頁中的內容沒有讀取到內存緩沖池中時，專門為新數據創建的空內存頁。

最后我們可以看到緩沖池的命中率，它預示著緩沖池的效率。1000/1000 相當于 100% 的命中率。不過這樣也很難說明緩沖池的命中率就足夠高了，這要需要根據不同的負載環境而定。通常情況下，950/1000 就夠了，有些時候在 IO 負載較高的環境下，命中率可能為 995/1000。

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“Mysql 中 show engine innodb status 怎么用”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!

正文完