這篇文章主要介紹了MySQL中show engine innodb status怎么用,具有一定借鑒價(jià)值,感興趣的朋友可以參考下,希望大家閱讀完這篇文章之后大有收獲,下面讓小編帶著大家一起了解一下。
創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供連江網(wǎng)站建設(shè)、連江做網(wǎng)站、連江網(wǎng)站設(shè)計(jì)、連江網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、連江企業(yè)網(wǎng)站模板建站服務(wù),十多年連江做網(wǎng)站經(jīng)驗(yàn),不只是建網(wǎng)站,更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
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 存儲(chǔ)引擎的核心操作大部分都集中在 Mater Thread 后臺(tái)線程中,該狀態(tài)顯示了后臺(tái)線程狀態(tài)信息,Master Thread 的主要工作:
主循環(huán)(loop)主要以每一秒和每十秒的頻率執(zhí)行刷新日志緩存,合并插入緩存,刷新臟頁(yè)緩存,刪除無(wú)用 undo 頁(yè)等操作
如果當(dāng)前沒有用戶活動(dòng),則進(jìn)入后臺(tái)循環(huán)流程(backgroud loop),主要執(zhí)行刪除無(wú)用的 undo 頁(yè),合并插入緩存
如果沒有什么事情可以做了,便進(jìn)入了暫停循環(huán)(suspend loop),等待事件循環(huán)喚起
----------------- BACKGROUND THREAD ----------------- # srv_active 為每秒的循環(huán)次數(shù),srv_idle 為每 10 秒的的循環(huán)次數(shù),srv_shutdown 為停止的循環(huán),通常為 0 # 如果每秒循環(huán)次數(shù)少,每 10 秒次數(shù)多,證明當(dāng)前負(fù)載很低;如果每秒循環(huán)次數(shù)多,每 10 秒次數(shù)少,遠(yuǎn)大于10:1,證明當(dāng)前負(fù)載很高 srv_master_thread loops: 2818842 srv_active, 0 srv_shutdown, 411 srv_idle # 日志緩沖刷盤次數(shù) 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 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
13 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 donefor 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頁(yè)*16k
Free buffers 8505 ------free 列表中頁(yè)的數(shù)量
Database pages 624627 -----lRu列表中頁(yè)的數(shù)量
Old database pages 230411 ------lru列表中old list(非熱快數(shù)據(jù):5/8-列表最后)的頁(yè)的數(shù)量
Modified db pages 26 ------臟頁(yè),存在于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 ------內(nèi)存命中率這個(gè)值應(yīng)該大于95%,不然sql需要優(yōu)化啊
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
點(diǎn)擊(此處)折疊或打開
----------------------
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頁(yè)*16k
Free buffers 8505 ------free 列表中頁(yè)的數(shù)量
Database pages 624627 -----lru列表中頁(yè)的數(shù)量
Old database pages 230411 ------lru列表中old list(非熱快數(shù)據(jù):5/8-列表最后)的頁(yè)的數(shù)量
Modified db pages 26 ------臟頁(yè),存在于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 ------內(nèi)存命中率這個(gè)值應(yīng)該大于95%,不然sql需要優(yōu)化啊
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
這部分顯示了緩沖池和內(nèi)存的利用率相關(guān)信息??梢钥吹絀nnodb分配的所有內(nèi)存(有些時(shí)候可能比你設(shè)置的還要多點(diǎn)),以及額外的內(nèi)存池分配情況 (可以檢查它的大小是否正好),緩沖池總共有多少個(gè)內(nèi)存頁(yè),有多少空閑內(nèi)存頁(yè),數(shù)據(jù)庫(kù)分配了多少個(gè)內(nèi)存頁(yè)以及有多少個(gè)臟內(nèi)存頁(yè)。從這些信息中,就可以判斷 內(nèi)存緩沖池是否設(shè)定合理,如果總是有大量空閑內(nèi)存頁(yè),則不需要設(shè)置那么多內(nèi)存,可以適當(dāng)減小一點(diǎn)。如果空閑內(nèi)存頁(yè)為 0,這種情況下數(shù)據(jù)庫(kù)內(nèi)存頁(yè)就不一定會(huì)和緩沖池的總數(shù)一致,因?yàn)榫彌_池還需要保存鎖信息,自適應(yīng)哈希索引以及其他系統(tǒng)結(jié)構(gòu)等信息。
等待中的讀寫是指內(nèi)存緩沖池級(jí)別的請(qǐng)求。Innodb可能會(huì)把多個(gè)文件級(jí)別的請(qǐng)求合并到一個(gè)上,因此各不相同。我們還可以看到Innodb提交的各 種不同類型的IO,LRU內(nèi)存頁(yè)中需要刷新的頁(yè) - 臟內(nèi)存頁(yè),它們不會(huì)被長(zhǎng)時(shí)間存??;刷新列表 -
檢查點(diǎn)進(jìn)程處理完之后需要刷新的舊內(nèi)存頁(yè);獨(dú)立內(nèi)存頁(yè) - 獨(dú)立的寫內(nèi)存頁(yè)。
我們還可以看到內(nèi)存頁(yè)總共讀寫了多少次。已經(jīng)創(chuàng)建的內(nèi)存頁(yè)是當(dāng)前一個(gè)內(nèi)存頁(yè)中的內(nèi)容沒有讀取到內(nèi)存緩沖池中時(shí),專門為新數(shù)據(jù)創(chuàng)建的空內(nèi)存頁(yè)。
最后我們可以看到緩沖池的命中率,它預(yù)示著緩沖池的效率。1000/1000 相當(dāng)于 100% 的命中率。不過這樣也很難說明緩沖池的命中率就足夠高了,這要需要根據(jù)不同的負(fù)載環(huán)境而定。通常情況下,950/1000 就夠了,有些時(shí)候在IO負(fù)載較高的環(huán)境下,命中率可能為 995/1000。
感謝你能夠認(rèn)真閱讀完這篇文章,希望小編分享的“Mysql中show engine innodb status怎么用”這篇文章對(duì)大家有幫助,同時(shí)也希望大家多多支持創(chuàng)新互聯(lián),關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道,更多相關(guān)知識(shí)等著你來學(xué)習(xí)!
分享標(biāo)題:Mysql中showengineinnodbstatus怎么用
文章分享:http://aaarwkj.com/article28/gjghjp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供自適應(yīng)網(wǎng)站、做網(wǎng)站、軟件開發(fā)、網(wǎng)站策劃、品牌網(wǎng)站制作、電子商務(wù)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)