經(jīng)過(guò)多年的實(shí)際經(jīng)驗(yàn)，整理了一些材料，已Linux環(huán)境下MySQL性能突發(fā)事件問(wèn)題排查技巧分享給大家。

作為DBA在面對(duì)性能上突發(fā)問(wèn)題的時(shí)候，是否出現(xiàn)過(guò)束手無(wú)策，無(wú)從下手的經(jīng)歷。 其實(shí)性能無(wú)非問(wèn)題點(diǎn)在于存儲(chǔ)、操作系統(tǒng)， 應(yīng)用程序，數(shù)據(jù)庫(kù) 等方面。

性能分析問(wèn)題 并沒(méi)有想象的那么難，當(dāng)了解到一些常用的Linux 系統(tǒng)命令和MySQL的基礎(chǔ)排查命令的時(shí)候，所有問(wèn)題點(diǎn)都可以定位到。

先上一個(gè)Linux性能工具圖譜圖，Brendan D. Gregg動(dòng)態(tài)追蹤工具 DTrace 的作者。

有點(diǎn)復(fù)雜，不用太care，只要你理解了下面的常用命令和分析點(diǎn)，那就可以確定絕大數(shù)性能上問(wèn)題。

Linux 平臺(tái)基礎(chǔ)常用的性能收集工具：

1. top — Linux 系統(tǒng)進(jìn)程監(jiān)控

top命令是Linux下常用的性能分析工具，能夠?qū)崟r(shí)顯示系統(tǒng)中各個(gè)進(jìn)程的資源占用狀況，類(lèi)似于Windows的任務(wù)管理器。并且它也是 Linux 系統(tǒng)管理員經(jīng)常使用的監(jiān)控系統(tǒng)性能的工具。Top命令可以定期顯示所有正在運(yùn)行和實(shí)際運(yùn)行并且更新到列表中，它顯示出 CPU 的使用、內(nèi)存的使用、交換內(nèi)存、緩存大小、緩沖區(qū)大小、過(guò)程控制、用戶(hù)和更多命令。它也會(huì)顯示內(nèi)存和 CPU 使用率過(guò)高的正在運(yùn)行的進(jìn)程。

 

2. vmstat — 虛擬內(nèi)存統(tǒng)計(jì)

vmstat 命令是用于顯示虛擬內(nèi)存、內(nèi)核線(xiàn)程、磁盤(pán)、系統(tǒng)進(jìn)程、I/O 模塊、中斷、CPU 活躍狀態(tài)等更多信息。

 

3. lsof — 打開(kāi)文件列表

lsof 命令對(duì)于很多 Linux/Unix 系統(tǒng)都可以使用，主要以列表的形式顯示打開(kāi)的文件和進(jìn)程。打開(kāi)的文件主要包括磁盤(pán)文件、網(wǎng)絡(luò)套接字、管道、設(shè)備和進(jìn)程。這個(gè)命令很容易看出哪些文件正在使用。

 

4. tcpdump — 網(wǎng)絡(luò)數(shù)據(jù)包分析器

tcpdump 是一種使用最廣泛的命令行網(wǎng)絡(luò)數(shù)據(jù)包分析，將網(wǎng)絡(luò)中傳送的數(shù)據(jù)包完全截獲下來(lái)提供分析。它支持針對(duì)網(wǎng)絡(luò)層、協(xié)議、主機(jī)、網(wǎng)絡(luò)或端口的過(guò)濾 并提供and、or、not等邏輯語(yǔ)句來(lái)幫助你去掉無(wú)用的信息。

 

包可通過(guò)tcpdump命令解析，也可以保存成后綴為pcap的文件，使用wireshark等軟件進(jìn)行查看。

 

3. netstat — 網(wǎng)絡(luò)統(tǒng)計(jì)

netstat 命令是一個(gè)監(jiān)控網(wǎng)絡(luò)數(shù)據(jù)包傳入和傳出的統(tǒng)計(jì)界面的命令行工具。它對(duì)于許多系統(tǒng)管理員去監(jiān)控網(wǎng)絡(luò)性能和解決網(wǎng)絡(luò)相關(guān)問(wèn)題是一個(gè)非常有用的工具。

 

4. iostat — 輸入/輸出統(tǒng)計(jì)

iostat 是收集和展示系統(tǒng)輸入和輸出存儲(chǔ)設(shè)備統(tǒng)計(jì)的簡(jiǎn)單工具。這個(gè)工具通常用于查找存儲(chǔ)設(shè)備性能問(wèn)題，包括設(shè)備、本地磁盤(pán)、例如 NFS 遠(yuǎn)程磁盤(pán)。

 

除了上述 還有 其他一些Linux 常用的工具sar，htop， IPTraf ， iotop ，iftop ，iptraf 等。

MySQL常用性能突發(fā)事件分析命令：

1. SHOW PROCESSLIST; —當(dāng)前MySQL數(shù)據(jù)庫(kù)的運(yùn)行的所有線(xiàn)程

 

2. INNODB_TRX; — 當(dāng)前運(yùn)行的所有事務(wù)

## 當(dāng)前運(yùn)行的所有事務(wù) ，還有具體的語(yǔ)句

 

3. INNODB_LOCKS; — 當(dāng)前出現(xiàn)的鎖

## 當(dāng)前事務(wù)出現(xiàn)的鎖的語(yǔ)句信息

 

4. INNODB_LOCK_WAITS; — 鎖等待的對(duì)應(yīng)關(guān)系計(jì)

## 鎖等待的對(duì)應(yīng)關(guān)系

 

5. SHOW OPEN TABLES where In_use >0; — 當(dāng)前打開(kāi)表

查看哪些表在使用中，In_use列表示有多少線(xiàn)程正在使用某張表，Name_locked表示表名是否被鎖，這一般發(fā)生在Drop或Rename命令操作這張表時(shí)。所以這條命令不能幫助解答我們常見(jiàn)的問(wèn)題：當(dāng)前某張表是否有死鎖，誰(shuí)擁有表上的這個(gè)鎖等。

 

下面比較重點(diǎn)部分，請(qǐng)注意!

6. SHOW ENGINE INNODB STATUS \G; —Innodb狀態(tài)

顯示除了大量的內(nèi)部信息，輸出內(nèi)容比較復(fù)雜難懂,輸出內(nèi)容中包含了一些平均值的統(tǒng)計(jì)信息，這些平均值是自上次輸出結(jié)果生成以來(lái)的統(tǒng)計(jì)數(shù)。

 

具體分析如下：

①.Header

這部分簡(jiǎn)單的打印，輸出的時(shí)間，以及自從上次輸出的間隔時(shí)間。

 

②.BACKGROUND THREAD

 

 

 

③. SEMAPHORES 信號(hào)量

 

OS WAIT ARRAY INFO 操作系統(tǒng)等待數(shù)組的信息，它是一個(gè)插槽數(shù)組，innodb使用了多少次操作系統(tǒng)的等待

保留統(tǒng)計(jì)(reservation count)顯示了innodb分配插槽的頻度

信號(hào)計(jì)數(shù)(signal count) 衡量的是線(xiàn)程通過(guò)數(shù)組得到信號(hào)的頻度

RW-shared spins：#這行顯示讀寫(xiě)的共享鎖的計(jì)數(shù)器

RW-excl spins：#這行顯示讀寫(xiě)的排他鎖的計(jì)數(shù)器

RW-sx spins：#這行顯示共享排它鎖計(jì)數(shù)器

*備注：5.7.2增加了一種新的讀寫(xiě)鎖類(lèi)型稱(chēng)為SX共享排他鎖

鎖的擁有則可以讀表中的任何數(shù)據(jù)，如果在相應(yīng)的行上能夠獲得X鎖，則可以修改該行。

④. TRANSACTIONS

包含Innodb 事務(wù)(transactions)的統(tǒng)計(jì)信息，還有當(dāng)前活動(dòng)的事務(wù)列表。

 

transaction id： 這個(gè)ID是一個(gè)系統(tǒng)變量隨時(shí)每次新的transaction產(chǎn)生而增加。

Purge done：正在進(jìn)行清空(purge)操作的transaction ID。你可以通過(guò)查看第transaction id和第Purge done ID的區(qū)別，明白沒(méi)有被purge的事務(wù)落后的情況。

History listlength：記錄了undo spaces內(nèi)unpurged的事務(wù)的個(gè)數(shù)。

⑤. FILE I/O

顯示了I/O Helper thread的狀態(tài)，包括一些統(tǒng)計(jì)信息

 

pending operations, pending的log和buffer pool thread的fsync()調(diào)用

399 OS file：行顯示了reads, writes, and fsync()調(diào)用次數(shù)。

0.00 reads/s…… : 顯示了每秒的統(tǒng)計(jì)信息

備注：“aio”表示“ 異步I/O(asynchronous I/O).”

⑥. INSERT BUFFER AND ADAPTIVE HASH INDEX

 

Ibuf：insertbuffer的一些信息，包括free list, segment size

Hash table：顯示了hash table的一些信息***一行顯示了每秒進(jìn)行了多少次hash搜索，以及非hash搜索

⑦. LOG

 

Log sequence number表示的是redo log buffer中的lsn

Log flushed up to表示的是redo log file中的lsn

Pages flushed up to表示的緩沖池最舊臟頁(yè)的lsn

Last checkpoint at 指的就是最近一個(gè)物理頁(yè)刷新到磁盤(pán)時(shí)，它的fil_page_lsn的變量值。

⑧. BUFFER POOL AND MEMORY

當(dāng)前內(nèi)存使用狀態(tài)

 

Pages read ahead：顯示了每秒線(xiàn)性預(yù)讀跟隨機(jī)預(yù)讀的次數(shù)

備注：InnoDB 提供了兩種預(yù)讀的方式，一種是 Linear read ahead，由參數(shù)innodb_read_ahead_threshold控制，當(dāng)你連續(xù)讀取一個(gè) extent 的 threshold 個(gè) page 的時(shí)候，會(huì)觸發(fā)下一個(gè) extent 64個(gè)page的預(yù)讀。另外一種是Random read-ahead，由參數(shù)innodb_random_read_ahead控制，當(dāng)你連續(xù)讀取設(shè)定的數(shù)量的page后，會(huì)觸發(fā)讀取這個(gè)extent的剩余page。InnoDB 的預(yù)讀功能是使用后臺(tái)線(xiàn)程異步完成。

⑨. ROW OPERATIONS

 

0 queries inside InnoDB, 0 queries in queue：顯示了有多少線(xiàn)程在Innodb內(nèi)核

read views open inside InnoDB：顯示了有多少read view被打開(kāi)了，一個(gè)read view是一致性保證的MVCC “snapshot”

備注：innodb多版本并發(fā)(MVCC)通過(guò)read view來(lái)確定一致性讀時(shí)的數(shù)據(jù)庫(kù)snapshot, innodb的read view確定一條記錄能否看到，

在RC隔離級(jí)別下，是每個(gè)SELECT都會(huì)獲取***的read view;

在RR隔離級(jí)別下，則是當(dāng)事務(wù)中的***個(gè)SELECT請(qǐng)求才創(chuàng)建read view

7. SHOW STATUS LIKE 'innodb_row_lock_%'; — 鎖性能狀態(tài)

查看當(dāng)前鎖性能狀態(tài)

 

解釋如下：

Innodb_row_lock_current_waits：當(dāng)前等待鎖的數(shù)量

Innodb_row_lock_time：系統(tǒng)啟動(dòng)到現(xiàn)在、鎖定的總時(shí)間長(zhǎng)度

Innodb_row_lock_time_avg：每次平均鎖定的時(shí)間

Innodb_row_lock_time_max：最長(zhǎng)一次鎖定時(shí)間

Innodb_row_lock_waits：系統(tǒng)啟動(dòng)到現(xiàn)在、總共鎖定次數(shù)

8. SQL語(yǔ)句EXPLAIN; — 查詢(xún)優(yōu)化器

EXPLAIN執(zhí)行計(jì)劃部分，略過(guò)(后續(xù)專(zhuān)題分享)

作為一個(gè)DBA，問(wèn)題排查技巧是每個(gè)工程師都需要掌握的核心技能。

作者介紹：崔虎龍，云和恩墨-開(kāi)源架構(gòu)部-MySQL技術(shù)顧問(wèn)，長(zhǎng)期服務(wù)于數(shù)據(jù)中心(金融，游戲，物流)行業(yè)，熟悉數(shù)據(jù)中心運(yùn)營(yíng)管理的流程及規(guī)范，自動(dòng)化運(yùn)維 等方面。擅長(zhǎng)MySQL，Redis，MongoDB 數(shù)據(jù)庫(kù)高可用設(shè)計(jì) 和 運(yùn)維故障處理，備份恢復(fù)，升級(jí)遷移，性能優(yōu)化 。