系統性能排查方略及大型銀行MySQL性能管控

如果大家了解一些方法論的話，應該聽過兩個原則：一個是海恩法則，強調量變引發質變；另一個是老生常談的墨菲定律，強調會出錯的事總會出錯，針對這兩個原則，我總結了系統性能問題的五大特性，

1）系統回應慢

不論負載情況如何，系統應用程式一直特別慢，回應時間長，

2）時間序列日益緩慢

負載穩定，但系統隨著時間推進越來越慢，到達某個閾值后，系統可能會被鎖定或因大量錯誤出現而崩潰，

3）突發混亂

系統穩定運行，在某一時刻突然出現大量錯誤，

4）區域功能例外

用戶訪問部分頁面例外，上圖右下角圖片是用F12對訪問谷歌頁面進行的截圖，從中可以看出，我們訪問谷歌時一直超時，無法訪問，

5）隨負載變化越來越慢

用戶量增加時，系統明顯變慢，用戶離開系統后，系統恢復原狀，上圖左下角的圖片展示了CPU的使用情況，其從100%負載恢復到常態化，后續隨著用戶增加又逐漸漲至100%負載，

系統性能排查方略可總結為以下兩點：

1）積極溝通，減小影響

2）大膽推敲，合理論證

為此，我通過魚骨圖進一步描述問題的排查方式：

1）消除影響

首先需要消除對客戶的影響，其次要消除對系統的影響，可以通過歷史經驗緊急應急或其他方式幫助客戶或系統避開問題，

2）收集現場

這一步強調日志的完備，同時我們需要知道發生問題時的問題資料和系統資料，才可通過資料進行重演，

3）明確問題范圍

判斷發生的是個別交易問題還是普遍性問題，如果是個別交易問題，我們可以很快定位交易當時做過哪些改變；如果是普遍性問題，我們要判斷哪些客戶、客流受到影響，以及這一問題是否會對其他方面造成影響，

4）問題分析

問題分析包括兩個方面，一方面是系統級鏈路分析，從最早的端到端的鏈路進行統一排查；另一方面是交易級鏈路分析，從交易進來后經過中間件到資料庫回傳，對整個交易級鏈路進行分析，

5）問題解決方案

經過之前的一系列步驟，最終我們就可以制定問題的解決方案，在制定解決方案時，一般會進行資料修復和程式修復，在環境中同步驗證，并將修改后的部分歸并至后續版本中，避免導致類似問題重復發生，

6） 問題總結

這一步主要是針對問題進行復盤，從中發現優化點，并從問題的處理方式中總結經驗教訓，然后進行一些橫向排查，沉淀為相關經驗，

下面向大家講述性能問題排查，其中包括兩大方面：系統環境和運行環境，

1）系統環境

我們原則上通過APM工具監控系統環境，業界已經有些很好的開源監控工具，比如Prometheus、Zabbix等，可以利用這些工具監測CPU負荷、lO負荷、記憶體負荷以及網路負荷，

2）運行環境

可以將運行環境的問題大致分為以下三類：

① 資料庫

對于MySQL，首先查看其錯誤日志，通過mysql.err直接查看當時到底有什么問題；如果交易比較緩慢，可以從慢SQL日志（一般是slow-queries.log）中查看，原則上大于10秒的交易都會在這里體現；接下來看事務日志，通過binlog查看當時交易的情況，如果是備庫重演的一些問題，可以看主備中繼日志，通過relaylog查看備庫重演的狀態，

對于Oracle也大體相似，可以通過監聽日志listener.log、lsnrctl status查看監聽器的狀態，Oracle中有一個報警日志，通過alert.log可以查看當時發生的事件，我們還可以進一步打AWR報告和ASH報告，對資料庫進行監控，這一點MySQL不如Oracle，除此之外，Oracle也提供了一些歷史快照資訊表，比如dba_hist_sqlstat和 dba_hist_snapshot，可以通過這兩張快照表獲取需要的任意快照時間的處理資訊，最后，可以通過會話資訊，查看當前會話有哪些中間件正在訪問，以及整個會話的狀態，

進行性能分析時，我們可以查看執行計劃，對于MySQL，我們可以通過explain陳述句看當時的執行計劃，到底有沒有走索引，索引走得好不好，對于Oracle，我們可以通過v$sql_plan和dba_hist _sql_plan查看執行計劃變更的原因，針對執行計劃對索引進行重建，除此之外，我們還要對死鎖進行分析，并處理等待事件，

② 中間件

對于中間件，例如業界使用較多的WAS、Liberty、Tomcat以及國產的東方通等，我們可以查看它的一些執行緒資訊，這里建議大家打出3~5個javacore，一般是1分鐘打一個，這樣可以通過IBM的jca4611.jar工具對比分析問題出于哪個執行緒，或者執行緒卡在何種情況之下，

如果涉及到OOM（記憶體溢位），可以打出heapdump的資訊，再通過IBM的ha457.jar工具進行分析，

我們可以通過GC資訊看是否因為服務器full gc導致系統持續夯住，如果是，可以對vm資訊進行調優，除此之外，中間件還會打一些日志資訊，可以從中發現當時發生的問題，最后可以監控一些中間件的資源資訊，包括資料庫連接池、執行緒池和一些web容器，

③ 應用程式

若發現資料庫和中間件都沒有問題，我們再看應用程式，

對于前臺來說，我們看是不是因為它在前臺做了快取，沒有實時重繪，因此導致新請求獲得老交易，最終出現問題，除此之外可以看請求連接數，瀏覽器的請求連接數實際上是有限的，請求連接數過大也會導致應用程式出問題，最后可以看一下是否因為資源過大導致網路傳輸量較大，這種問題可以通過兩種方式解決，一種是資源壓縮，另一種是將資源部署在CDN上，

對于邏輯層來說，我們可以看它有沒有資源釋放，包括資料庫連接、檔案讀寫、socket、快取等，然后可以看事務問題，比如事務長時間沒有結束，這樣會卡死很多執行緒資訊，回圈處理資料庫也會導致事務的持續時間較長，最后可以看多執行緒資訊中是否包含鎖等待，是否存在資料污染，

綜上所述，系統性能排查有四個關鍵點：查看完備的日志、利用良好的工具、執行計劃和關注邏輯問題，

接下來會對java中間件和資料庫性能兩部分進行詳細分析：

1）通過jca分析javacore

我對比了4個javacore檔案，發現大部分問題集中在無法獲取連接池，即連接池都已經被占滿且長時間沒有釋放，這時可以結合連接池情況快速定位問題，

2）分析oom物件

對于oom物件，上圖可以看出有一個情況是BankFunctionTypePool中，oom大約存了1G空間，換言之，已直接將jvm記憶體耗盡，這種情況下，一般建議heapdump加上javacore共同做分析，這樣可以快速定位問題，

針對資料庫方面的問題，有如下分析流程，

一般出現問題場景后，首先通過日志分析判斷是不是資料庫無法連接，

如果資料庫無法連接，就檢查監聽狀態，如果是Oracle，listener.log并沒有狀態的日志記錄，可以檢查lsnrctl status，然后配置TNS，啟動監聽器，確保資料庫正常訪問，如果是MySQL，可以檢查mysql.err檔案，發現其中有一個access denied報錯，這種情況下我們做好訪問授權并確認防火墻，之后資料庫就可以正常訪問，

如果資料庫可以連接，但是資料庫執行時間過長，這種情況下應該按照以下方法解決，

如果是Oracle，可以列印問題時刻的AWR報告，定位問題陳述句（一般關注Logons、Top 5 events、SQL order by Elapsed time等），然后處理問題，如需進一步查勘，可以列印ASH報告，查看歷史同期問題引進的變化情況，從而快速定位一些問題，如果是MySQL，一般檢查mysql.err的錯誤日志，然后檢查slow-queries.log，如需進一步查勘，可以把performance_schema.events _statements_summary_by_digest表中的資料提取出來進行進一步查勘，

一般來說，資料庫相關問題可分為以下4種：

1）如果有死鎖，需要調整業務邏輯順序，進行壓測，然后驗證結束，

2）如果沒有死鎖，只是執行計劃有問題，例如出現一個全表掃，則在上面增加合適的索引處理，

3）如果有索引，需要判斷它的區分度：如果區分度高并且資料變動頻繁，需要更新統計資訊；如果區分度低，就決定索引是否合適，如果不合適就重建索引，選擇合適的索引進行處理，

4）最后需要看資料量的大小，如果超過了規范的閾值，就要進行分庫分表以及磁區策略，

我們將邏輯調整后，再進行相關壓測，當壓測滿意時驗證結束，真正上生產去做處理，

1）一般建議大家查看執行計劃，從我目前的分析來看，陳述句問題占90%以上；

2）命中索引并不等于ok；

3）執行計劃最少應該達到范圍掃，一般建議達到ref程度，

對于MySQL的執行計劃，有 id、select_type、table等列，其中我一般會關注表中的type，它表示訪問型別，決定了MySQL在表中找到所需要行的方式，

我在上圖右方列出了效率情況：

system (無需磁盤IO)> const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

接下來查看key還有key_len的值，使用索引的位元組長度越短越好，可以根據表定義大概計算出索引的最大可能長度，可用于復合索引的實際使用欄位情況，

之后查看rows，一般情況下建議rows值越小越好，其他例如filtered和Extra等也是比較關鍵的資訊，這里不再贅述，大家可以參考上圖中的表格，

針對分庫分表，首先要關注一個問題，單表資料量達到多少才需要進行分庫？

阿里手冊中寫到資料量達到500萬進行分庫分表，業界的說法是資料量達到2,000萬進行分庫分表，其源頭是百度的一個DBA進行壓測后，覺得壓到2,000萬沒問題，但是超過2,000萬后性能會出現問題，所以業界流傳的資料量界限是2,000萬，

對于我行來說，MySQL規范建議資料量達3,000萬進行分庫分表，

MySQL索引分為兩種，一種是聚簇索引，即主鍵索引，索引和資料保持在一起，另一種是secondary Index，即輔助索引，

下面簡單介紹一些基礎知識：

針對以上基礎知識，作以下具體說明：

資料量=扇出值^(B+樹層數-1)*葉子節點存盤行數

例如我們行的行占用大小約為850Byte位元組，每個葉子節點可以存盤18行，資料量為2,900萬左右，這也是3,000萬的分庫分表界限的來源，百度行占用大小是1K，每個葉子節點存盤15行資料，資料量為2400萬左右，所以業界才有2,000萬這一說法，阿里同理，經過計算強調資料量超過500萬進行分庫分表，

我們要了解規范數字背后的含義，這樣很多問題就會迎刃而解，除此之外，服務器配置、資料庫版本等因素也會影響查詢速度，

MySQL官方對鎖有較詳細的介紹，一般常見型別是讀鎖和寫鎖，讀鎖包含兩種鎖：記錄鎖和間隙鎖，我行用READ-COMMITTED規避間隙鎖，

大家通過mysql.err看日志表現，可以看到有lock_mode X和locks rec but not gap，這是記錄鎖的含義，

這里需要關注以下兩點：

1）鎖競爭

5.7版本中我們從locks、locks_waits表查看鎖，但是8.0版本從infomation_spchema遷至performance_schema，下面舉一個例子進行說明，

事務1是start transaction，更新同一個id=1的值，事務也對它進行更新，50秒后，它會拋一個1205錯誤，直接顯示鎖等待超時，我們建議一個鎖等待超時的時間是5~10秒，從而避免對事務造成較大影響，

2）死鎖檢測

死鎖檢測本質是哲學家的問題：2個及以上事務，雙方都在等待對方釋放已經持有的資源，最后造成等待回圈，形成死鎖，

針對MySQL實作機制，大家看lock0lock.cc，它本質是進行深度優先機制，如果發現環，則認為是一個死鎖，同時回滾undo log量小的事務，

如果大家查看mysql.err，可以發現它第一步有一個deadlock detected，然后事務1會等待另外一個記錄鎖去釋放，事務2也會等待事務1的記錄鎖去釋放，最后因為事務2回滾量較小，所以回滾了事務2，

MySQL和PostgreSQL這兩個資料庫都很好，但是對于我們國家來說，在Google Trends上MySQL的熱度更高一點，占比大概是89%，PostgreSQL占比是11%左右，我們搜索關鍵字時，最多的是怎么編譯MySQL，這說明我國對原始碼的掌握和編譯有較為熱切的需求，從DB-Engines Rank中可以看到MySQL和Oracle一直不相上下，PostgreSQL的熱度也在逐步上升，

接下來分享我們行的性能管控體系，

“免費的午餐并不好吃”，隨著MySQL的廣泛應用，大家并不注意開發規范，這會導致慢SQL數量呈爆發式增長，一條慢SQL就可以導致服務不可用，降低用戶幸福指數，我們為此構建管控體系確保開發合規和性能管控，

1）研發流水線 （DevOps） +  QA定期檢查 （線下）

首先我們通過研發流水線（DevOos）和QA定期檢查對整個研發環節進行處理，具體可分為以下環節：

在設計環節，我們建立了設計指引，做了一些元資料管理，并設定了能力提升課程提升大家的資料庫使用能力，我們也會推動一些表結構設計工具和元資料管理系統，限定大家局面處理問題，同時我們在這一環節設定了門禁，

這一環節我們將一些規范做到自動化，包括SQL注入檢查和SQL寫法的規則，SonarQube有SonarLint插件可以做服務器端的同步，這也有利于在開發環節做性能管控，

這一環節我們通過安全測驗、性能測驗和混沌測驗進行性能管控，

發布環節會由我們的SRE發布一些態勢感知報告，從技術以及安全等層面對業務提出針對性建議及后續整改措施，

在這一環節我們首先會進行慢SQL的監控治理，逐步減少大事務資料；大家可以看到上圖某部門有2個應用，慢SQL數量12個，最大耗時246秒，平均耗時11.414秒，

其次，我們會進行生產案例分析，將相關規則沉淀到知識庫，并將技術組件放入技術模型，除此之外，我們還會做一些AIOps根因分析，

最后我們會進行一些慢SQL的監控和查殺，將大事務提前扼殺，避免其對系統產生影響，

2）性能運維事件回應及溯源

我們會針對每一個問題反省并溯源，看到底是哪一環節出現問題，哪些環節可以進行優化，例如判斷：陳述句是否因為沒有限定時間范圍的存在需求缺失情況？設計功能是否考慮到大表關聯這種設計缺陷？開發環節是否存在代碼缺陷？

檢查開發環節后我們會檢查測驗環節是否有測驗用例缺失、測驗工具漏報等缺陷，最后檢查發布環節是否有發布標準等缺陷，

3）能力沉淀

最后我們會進行能力沉淀，例如問題倍訓追蹤、根因橫向排查，最后沉淀為知識庫、技術組件、度量模型，

1）設計原則

在設計方面，我們有以下三大原則：

① 復用原則

在系統架構時，應考慮將相同或類似作用的資訊使用同一套資料結構來存盤，例如：通用引數表、通用字典表，

② 前瞻性原則

③ 元資料原則

2）典型規范示例

① 操作：方法論

方法論是萬物之基石，例如每個表我們必須要建立一個主鍵，如果不顯示設定主鍵，會自動生成一個rowid(6 byte)作為隱藏主鍵，且所有表共用此空間，造成性能下降，

② 量化：精細化的理性思維

我們建議掃描命中比原則上應該是100:1，事物大小方面我們行的要求是10萬，業界一般一萬即可，

③ 避坑：規避 MySQL Bug

大表truncate改為drop + create table，這在5.7中效果非常明顯，但是在8.0中公司已經對其進行了修改優化，

針對以上規范，我們要讓開發人員潛移默化地知其然也知其所以然，避免出現一些問題，

我們基于druid，擴展了Sonarqube插件，實作本地檢查規則和云端云同步，

我們之前大概定了27條規則，其中包含了常見的一些錯誤，例如有人在update陳述句的set關鍵字后面，誤將分隔符逗號（“,”）寫成“and”，導致出現預期之外的結果，

大事務的相關問題主要有以下幾點：

我們行以及業界都采用了自動查殺方式，

我們當時做過兩步操作，第一步是將交易的聯機庫跟批量庫進行區分，對于聯機庫，超過三秒以上的交易可以進行自動查殺；對于批量庫，通過小范圍試點，然后做到全面推廣，

后續我們應該會將MySQL的主動同步做到不降級，去掉降級時間，但這一點依賴于我們治理完善、大事務不存在的情況，

希望可以做到全套端到端的全鏈路監控，這樣可以快速定位哪個節點出了什么問題，

希望進一步發展AIOps，實作業界所說的1-5-10目標，1分鐘發現，5分鐘處置，10分鐘恢復，

最后希望各位可以掌握一些開源組件的原始碼，做到“他山之石，可以攻玉”，了解其中隱藏的bug風險，有利于我們后續對開源組件進行維護，

Q1：貴司在MySQL調優程序中，會用到相關輔助工具嗎？老師能簡單分享一下嗎？

A1：沒有用到輔助工具，我們更多還是通過explain直接查看執行計劃，然后進行一些分析，

Q2：MySQL規范已經在貴司普及了嗎？落地一整套規范需要多長時間？

A2：我們大概從17年開始建立MySQL規范，因為我們當時引入MySQL5.7時，必須建立方法論這套基石，我們建立規范后，在SonarQube上建立檢查組件，進而做到門禁，實作規范的落地，在只有規范，沒有落地的情況下，我們很難把控，所以必須要通過硬性方式進行把控，

Q3：貴司是采用什么方式對MySQL進行監控的？

A3：包括兩種層面，第一層面，我們在MyBatis上做了擴展，會對陳述句進行審核，判斷陳述句是否有問題，第二層面，對MySQL的performance schema 和Information schema相關表進行監控，查找并處理其中的慢SQL，

Q4：老師，自動查殺的準確率能達到多高？

A4：自動查殺的準確率其實可以達到100%，大事務很容易就可以監控出來，但很多時候不敢查殺，我們把聯機跟批量分離完以后，對聯機大事務查殺的準確率就相當于是100%了，

Q5：老師能推薦個好用的開發工具嗎？比如Workbench？這塊總行有要求嗎？

A5：業界其實有很多工具，例如收費的Navicat、免費的MySQL Workbench等，我一般會用Workbench多一點，因為我們行引入軟體受到管控，必須要進行登記處理，

作者：魏亞東