Innodb監(jiān)控

Innodb由于支持事務(wù)操作，是mysql中使用最多的存儲引擎，所以如何監(jiān)控Innodb存儲引擎以進行性能優(yōu)化是在使用mysql過程中遇到最多的，那么如何進行監(jiān)控呢？

show engine

--?顯示innodb存儲引擎狀態(tài)的統(tǒng)計和配置信息
show?engine?innodb?status;

展示的主要內(nèi)容有
-----------------
BACKGROUND?THREAD??--后臺線程
-----------------
srv_master_thread?loops:?19610306?srv_active,?0?srv_shutdown,?9705136?srv_idle??--統(tǒng)計Innodb啟動后的活動
srv_master_thread?log?flush?and?writes:?29312902??--寫入和刷新日志的次數(shù)
----------
SEMAPHORES??--信號量包含了線程等待互斥鎖或讀寫鎖的信息
----------
OS?WAIT?ARRAY?INFO:?reservation?count?52795642??--os等待數(shù)組信息，分配插槽的次數(shù)
OS?WAIT?ARRAY?INFO:?signal?count?57522728??--os等待數(shù)組信息，線程通過數(shù)組得到信號的次數(shù)
RW-shared?spins?0,?rounds?77349143,?OS?waits?9180114??--共享鎖期間，讀寫鎖存器上自旋等待個數(shù)，自旋循環(huán)迭代次數(shù)以及操作系統(tǒng)調(diào)用的等待個數(shù)
RW-excl?spins?0,?rounds?179767865,?OS?waits?2534243??--排他鎖期間，讀寫鎖存器上自旋等待個數(shù)，自旋循環(huán)迭代次數(shù)以及操作系統(tǒng)調(diào)用的等待個數(shù)
RW-sx?spins?2068750,?rounds?40171680,?OS?waits?844522??--共享排他鎖期間，讀寫鎖存器上自選等待個數(shù)，自旋循環(huán)迭代次數(shù)以及操作系統(tǒng)調(diào)用的等待個數(shù)
Spin?rounds?per?wait:?77349143.00?RW-shared,?179767865.00?RW-excl,?19.42?RW-sx??--對于每一個互斥鎖，操作系統(tǒng)調(diào)用等待的每一個自旋循環(huán)迭代個數(shù)
------------
TRANSACTIONS?--包含所有當前正在執(zhí)行的事務(wù)的信息
------------
Trx?id?counter?1888483436??--當前事務(wù)id
Purge?done?for?trx?s?n:o?<?1888483436?undo?n:o?<?0?state:?running?but?idle?--所有編號小于1888483436的事務(wù)都已經(jīng)從歷史記錄列表中清除了，清除舊的MVCC行時所用的事務(wù)id，這個值與當前事務(wù)ID進行比較，就可以知道有多少老版本的數(shù)據(jù)未被清除
History?list?length?17?--歷史列表的長度，位于Innodb數(shù)據(jù)文件的撤銷空間里的頁面的數(shù)目，如果事務(wù)執(zhí)行了更新并提交，該數(shù)目就會增加，當清理進程移除舊版本數(shù)據(jù)時，該數(shù)目會減少
LIST?OF?TRANSACTIONS?FOR?EACH?SESSION:?當前事務(wù)列表
---TRANSACTION?422068961001072,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068960999248,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961005632,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961013840,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961012016,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961010192,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961001984,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961000160,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961017488,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961011104,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961012928,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961004720,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961002896,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961003808,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
---TRANSACTION?422068961007456,?not?started
0?lock?struct(s),?heap?size?1136,?0?row?lock(s)
--------
FILE?I/O??--各種IO操作的Innodb內(nèi)部線程以及執(zhí)行了多少次IO操作
--------
--?有四個線程
--?insert?buffer?thread?負責(zé)插入緩沖合并
--?log?thread?負責(zé)異步刷日志
--?read?thread?執(zhí)行預(yù)讀操作以嘗試預(yù)先讀取Innodb預(yù)感需要的數(shù)據(jù)
--?write?thread?刷臟緩沖
I/O?thread?0?state:?waiting?for?completed?aio?requests?(insert?buffer?thread)?--IO線程的狀態(tài)
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?--?掛起操作的信息，aio是指異步io
83934578288?OS?file?reads,?282688772?OS?file?writes,?190348192?OS?fsyncs?--innodb啟動后的總統(tǒng)計信息
984.40?reads/s,?16384?avg?bytes/read,?10.15?writes/s,?9.12?fsyncs/s?--最后一次顯示后的總統(tǒng)計信息
-------------------------------------
INSERT?BUFFER?AND?ADAPTIVE?HASH?INDEX??--插入緩沖區(qū)與自適應(yīng)散列統(tǒng)計信息
-------------------------------------
Ibuf:?size?1,?free?list?len?3078,?seg?size?3080,?8815726?merges?--在頁中插入緩沖索引樹的當前大小，空閑列表的長度，在包含插入緩沖樹與頭信息的文件段中已分配頁的個數(shù)，被合并頁的個數(shù)
merged?operations:
?insert?6898371,?delete?mark?38430046,?delete?1226485??--通過類型區(qū)分，索引頁被執(zhí)行合并操作的次數(shù)
discarded?operations:
?insert?1019,?delete?mark?0,?delete?0?--無須合并丟棄操作的數(shù)量
Hash?table?size?34673,?node?heap?has?1?buffer(s)
Hash?table?size?34673,?node?heap?has?74?buffer(s)
Hash?table?size?34673,?node?heap?has?1?buffer(s)
Hash?table?size?34673,?node?heap?has?1?buffer(s)
Hash?table?size?34673,?node?heap?has?1?buffer(s)
Hash?table?size?34673,?node?heap?has?2?buffer(s)
Hash?table?size?34673,?node?heap?has?28?buffer(s)
Hash?table?size?34673,?node?heap?has?7?buffer(s)
5203.54?hash?searches/s,?128.14?non-hash?searches/s?--成功使用自適應(yīng)散列索引查找的數(shù)量，當不能使用自適應(yīng)索引時向下搜索B樹的次數(shù)
---
LOG????--Innodb日志中活動信息
---
Log?sequence?number?319041331834?--當前日志序號
Log?flushed?up?to???319041331699?--?日志已經(jīng)刷到的位置
Pages?flushed?up?to?319033170877?
Last?checkpoint?at??319033170877??--?上一個檢查點，當前日志序列號LSN
0?pending?log?flushes,?0?pending?chkp?writes?
169033177?log?i/o's?done,?8.92?log?i/o's/second?--掛起的日志寫入次數(shù)，掛起的檢查點寫入個數(shù)，innodb啟動后的IO操作個數(shù)，從最近一次顯示之后的每秒IO操作個數(shù)
----------------------
BUFFER?POOL?AND?MEMORY???--Innodb緩沖池與內(nèi)存使用情況
----------------------
Total?large?memory?allocated?137428992?--分配的內(nèi)存
Dictionary?memory?allocated?1204989?--被數(shù)據(jù)字典表與索引對象所占空間的字節(jié)數(shù)
Buffer?pool?size???8191?--緩沖池個數(shù)
Free?buffers???????1024?--空閑緩沖區(qū)個數(shù)
Database?pages?????7052?--當前緩沖區(qū)LRU隊列的長度(分配用來存儲數(shù)據(jù)庫頁的頁數(shù))
Old?database?pages?2583?--舊的LRU隊列的長度
Modified?db?pages??530?--需要刷新的頁面的數(shù)量(臟數(shù)據(jù)庫頁數(shù))
Pending?reads??????0?--掛起讀操作的個數(shù)
Pending?writes:?LRU?0,?flush?list?0,?single?page?0?--通過LRU算法，等待刷新的頁數(shù)
Pages?made?young?983912385,?not?young?304833753259?--因為最近第一次被訪問時，變成新頁面的數(shù)目和沒有變成新頁面的數(shù)目
1.54?youngs/s,?16246.04?non-youngs/s?--?上述兩個值每秒的速率
Pages?read?83934649301,?created?4135172,?written?103030852?--讀操作的頁面數(shù)目，在緩沖區(qū)中創(chuàng)建但是沒有讀取的頁面數(shù)目，寫操作的頁面數(shù)目
984.40?reads/s,?0.17?creates/s,?1.15?writes/s?--?上述值美妙的速率
Buffer?pool?hit?rate?972?/?1000,?young-making?rate?0?/?1000?not?478?/?1000?--讀取到的頁面數(shù)與獲得的緩沖池頁面的比例，變?yōu)樾马撁娴捻撁鏀?shù)與獲得緩沖池頁面的比例，沒有變?yōu)樾马撁娴捻撁鏀?shù)與獲得緩沖池頁面的比例
Pages?read?ahead?913.79/s,?evicted?without?access?5.60/s,?Random?read?ahead?0.00/s?--預(yù)讀的速率與不通過訪問剔除的預(yù)讀頁面的個數(shù)
LRU?len:?7052,?unzip_LRU?len:?0?--LRU列表的長度，unzip_LRU列表的長度
I/O?sum[4121]:cur[0],?unzip?sum[0]:cur[0]?--IO操作的次數(shù)：當前間隔的IO
--------------
ROW?OPERATIONS???--行操作
--------------
0?queries?inside?InnoDB,?0?queries?in?queue?--當前有多少個正在執(zhí)行的查詢，在innodb_thread_concurrency隊列中的查詢個數(shù)
0?read?views?open?inside?InnoDB?--只讀視圖的數(shù)量
Process?ID=1543,?Main?thread?ID=140593683990272,?state:?sleeping?--線程id以及狀態(tài)
Number?of?rows?inserted?56092883,?updated?133093048,?deleted?40729879,?read?477150639699?--從innodb啟動后，插入、更新、刪除、讀取的行數(shù)
0.19?inserts/s,?7.73?updates/s,?0.00?deletes/s,?138100.85?reads/s?--?速率

--?展示Innodb的互斥體信息
show?engine?innodb?mutex;

Type???Name????????????????????????Status
InnoDB?rwlock:?dict0dict.cc:2782????waits=4
InnoDB?rwlock:?dict0dict.cc:1228????waits=80
InnoDB?rwlock:?log0log.cc:846??????waits=75
InnoDB?sum?rwlock:?buf0buf.cc:1460??waits=11

--?name列顯示了創(chuàng)建互斥體的源文件和行號
--?status列顯示了互斥體在操作系統(tǒng)上的等待次數(shù)

show status

通過查看日志文件

show?status?like?'innodb%log%'

Variable_name??????????????Value
Innodb_log_waits?????????????0?????日志文件太小時，操作必須等待日志刷新的等待時間計數(shù)器，該值如果長期大于0，可以適當增加日志文件大小
Innodb_log_write_requests???4539????日志寫入請求的數(shù)量
Innodb_log_writes????????????22?????數(shù)據(jù)被寫入日志的次數(shù)
Innodb_os_log_fsyncs????????1020???操作系統(tǒng)文件同步的數(shù)量(fsync()方法調(diào)用)
Innodb_os_log_pending_fsyncs?0?????阻塞的文件同步請求的數(shù)量，如果該值開始增長并長期大于0，需要檢查磁盤訪問問題
Innodb_os_log_pending_writes?0?????阻塞的日志寫請求的次數(shù)，如果該值開始增長并長期大于0，需要檢查磁盤訪問問題
Innodb_os_log_written??????2855424??寫入日志中的字節(jié)總量
Innodb_available_undo_logs?128

緩沖池信息

緩沖池是Innodb緩存頻繁訪問數(shù)據(jù)的地方，對緩沖池內(nèi)數(shù)據(jù)的任何更新也會被緩存

--?可以查看存儲引擎的統(tǒng)計信息，其中包含有緩沖池的信息
show?engine?innodb?status;

截取出緩沖池的信息來進行分析
----------------------
BUFFER?POOL?AND?MEMORY
----------------------
Total?large?memory?allocated?137428992
Dictionary?memory?allocated?223164
Buffer?pool?size???8191
Free?buffers???????7374???空的且可用于緩沖數(shù)據(jù)的緩沖段個數(shù)
Database?pages?????809???
Old?database?pages?299
Modified?db?pages??0???發(fā)生變化的頁數(shù)
Pending?reads??????0???等待中的讀請求個數(shù)
Pending?writes:?LRU?0,?flush?list?0,?single?page?0
Pages?made?young?0,?not?young?0
0.00?youngs/s,?0.00?non-youngs/s
Pages?read?503,?created?306,?written?2534
0.00?reads/s,?0.00?creates/s,?0.00?writes/s
No?buffer?pool?page?gets?since?the?last?printout
Pages?read?ahead?0.00/s,?evicted?without?access?0.00/s,?Random?read?ahead?0.00/s
LRU?len:?809,?unzip_LRU?len:?0
I/O?sum[0]:cur[0],?unzip?sum[0]:cur[0]

在查看一下緩沖區(qū)相關(guān)的變量

show?status?like?'innodb%buf%'


Variable_name??????????????????????Value
Innodb_buffer_pool_pages_data????????809????含有數(shù)據(jù)的頁數(shù)，包括不變和改變的頁
Innodb_buffer_pool_bytes_data??????13254656?含有數(shù)據(jù)的字節(jié)數(shù)
Innodb_buffer_pool_pages_dirty???????0??????改變的字節(jié)數(shù)
Innodb_buffer_pool_bytes_dirty???????0??????改變的頁的數(shù)目
Innodb_buffer_pool_pages_flushed????2525????緩沖池頁面被刷新的次數(shù)
Innodb_buffer_pool_pages_free????????7374????空頁面的數(shù)目
Innodb_buffer_pool_pages_misc?????????8??????用于管理工作的頁數(shù)，公式為'Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free-Innodb_buffer_pool_pages_data'
Innodb_buffer_pool_pages_total??????8191????緩沖池中的總頁數(shù)
Innodb_buffer_pool_read_ahead_rnd?????0??????掃描大塊數(shù)據(jù)時發(fā)生隨機讀頭的數(shù)量
Innodb_buffer_pool_read_ahead?????????0??????
Innodb_buffer_pool_read_ahead_evicted?0
Innodb_buffer_pool_read_requests???107632???邏輯讀請求的次數(shù)
Innodb_buffer_pool_reads?????????????504????直接從磁盤中邏輯讀取的次數(shù)(沒有從緩沖池中讀)
Innodb_buffer_pool_wait_free??????????0?????如果緩沖池繁忙且沒有空頁，innodb需要等待頁面刷新，該值表示等待次數(shù)，若始終大于0，可適當增加緩沖池大小
Innodb_buffer_pool_write_requests????47403???寫入innodb緩沖池的次數(shù)

線程和連接統(tǒng)計信息

使用show status like '變量'來查詢，這些變量用來跟蹤嘗試的連接、退出的連接、網(wǎng)絡(luò)流量和線程統(tǒng)計

• Connections
• Max_used_connections
• Threads_connected
• Aborted_clients
• Aborted_connects 如果不為0，表示有人嘗試連接失敗
• Bytes_received
• Bytes_sent
• Slow_launch_threads
• Threads_cached
• Threads_created
• Threads_running

二進制日志狀態(tài)

• Binlog_cache_use和Binlog_cache_disk_use表示在二進制日志緩存中有多少事務(wù)被存儲過，以及多少事務(wù)因為超過二進制日志緩存而被存儲到一個臨時文件中
• Binlog_stmt_cache_use和Binlog_stmt_cache_disk_use表示非事務(wù)語句對應(yīng)的度量值

命令計數(shù)器

Com_*變量統(tǒng)計了每種類型的SQL發(fā)起的次數(shù)

臨時文件和臨時表

通過Create_tmp%來查看隱式臨時文件和臨時表的統(tǒng)計

select類型

select_*變量統(tǒng)計select查詢的計數(shù)器

• Select_full_join 交叉連接或并沒有條件匹配表中行的連接的數(shù)目，如果存在，需要檢查sql語句
• Select_full_range_join 使用在表t1中的一個值來從表t2中通過參考索引的區(qū)間內(nèi)獲取行所做的連接數(shù)，比Select_scan開銷大些
• Select_range 掃描表的一個索引區(qū)間的連接數(shù)目
• Select_range_check 在表t2中重新評估表t1中的每一行的索引是否開銷最小所做的連接數(shù)，意味著表t2中對該連接而言并沒有使用索引，這種查詢應(yīng)該避免，開銷很大
• Select_scan 掃描整張表的連接數(shù)目

排序

• Sort_merge_passes 依賴于sort_buffer_size服務(wù)器變量，sort_buffer_size來容納排序的行塊，當完成排序后，會將這些排序后的行合并到結(jié)果集中，此時就會增加Sort_merge_passes值
• Sort_scan和Sort_range 當mysql從文件排序結(jié)果中讀取已經(jīng)排好序的行并返回給客戶端會導(dǎo)致這兩個變量的增長，如果是當Select_scan增加時Sort_scan增加；如果是Select_range增加時Sort_range增加

information_schema數(shù)據(jù)庫中關(guān)于innodb的表

information_schema數(shù)據(jù)庫中有幾個對于innodb的特殊表，可以用于監(jiān)控壓縮、事務(wù)和鎖

• INNODB_CMP表 顯示壓縮表的詳細信息和統(tǒng)計信息
• INNODB_CMP_RESET表 與INNODB_CMP信息相同，但是會在查詢表時將重置統(tǒng)計信息，可以定期跟蹤統(tǒng)計信息
• INNODB_CMPMEM表 顯示在緩沖池中使用壓縮的詳細信息和統(tǒng)計信息
• INNODB_CMPMEM_RESET表 與INNODB_CMPMEM信息相同，但是會在查詢表時將重置統(tǒng)計信息，可以定期跟蹤統(tǒng)計信息
• INNODB_TRX表 顯示所有事務(wù)的詳細信息和統(tǒng)計信息，包括事務(wù)狀態(tài)和當前正在運行的查詢信息
• INNODB_LOCKS表 顯示事務(wù)請求的鎖的詳細信息和統(tǒng)計信息，描述每個鎖的狀態(tài)、模式、類型等信息
• INNODB_LOCK_WAITS表 顯示被阻塞的事務(wù)請求的鎖的詳細信息和統(tǒng)計信息，描述每個鎖的狀態(tài)、模式、類型和阻塞事務(wù)

https://zhhll.icu/2021/數(shù)據(jù)庫/關(guān)系型數(shù)據(jù)庫/MySQL/進階/29.Innodb監(jiān)控/