作者介紹
分享概要
一、系統性能問題五大特性
二、系統性能排查方略
三、MySQL開發規範和常見調優政策
四、MySQL性能管控體系
五、未來展望
一、系統性能問題五大特性
如果大家了解一些方法論的話，應該聽過兩個原則：一個是海恩法則，強調量變引發質變；另一個是老生常談的墨菲定律，強調會出錯的事總會出錯。針對這兩個原則，我總結了系統性能問題的五大特性。
1）系統響應慢
不論負載情況如何，系統應用程式一直特别慢，響應時間長。
2）時間序列日益緩慢
負載穩定，但系統随着時間推進越來越慢，到達某個門檻值後，系統可能會被鎖定或因大量錯誤出現而崩潰。
3）突發混亂
系統穩定運作，在某一時刻突然出現大量錯誤。
4）局部功能異常
使用者通路部分頁面異常，上圖右下角圖檔是用F12對通路谷歌頁面進行的截圖，從中可以看出，我們通路谷歌時一直逾時，無法通路。
5）随負載變化越來越慢
使用者量增加時，系統明顯變慢，使用者離開系統後，系統恢複原狀。上圖左下角的圖檔展示了CPU的使用情況，其從100%負載恢複到常态化，後續随着使用者增加又逐漸漲至100%負載。
二、系統性能排查方略
1、系統性能排查方略方法論
系統性能排查方略可總結為以下兩點：
1）積極溝通，減小影響
- 利用5W1H原則了解問題現象，即什麼問題、在什麼時間、什麼地點、如何發生、何人處理。同時還要收集現場資訊，包括常見的日志資訊、流量資訊等，盡量做到全面排查。
- 安撫客戶，減小客戶影響。一件小事可能會由于客戶恐慌性的增長釀成大事故。
- 基于曆史經驗緊急應急。
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中間件和資料庫性能兩部分進行詳細分析：
2、java中間件分析
1）通過jca分析javacore
我對比了4個javacore檔案，發現大部分問題集中在無法擷取連接配接池，即連接配接池都已經被占滿且長時間沒有釋放，這時可以結合連接配接池情況快速定位問題。
2）分析oom對象
對于oom對象，上圖可以看出有一個情況是BankFunctionTypePool中，oom大約存了1G空間，換言之，已直接将jvm記憶體耗盡。這種情況下，一般建議heapdump加上javacore共同做分析，這樣可以快速定位問題。
3、資料庫相關問題分析
針對資料庫方面的問題，有如下分析流程。
一般出現問題場景後，首先通過日志分析判斷是不是資料庫無法連接配接。
如果資料庫無法連接配接，就檢查監聽狀态。如果是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）最後需要看資料量的大小，如果超過了規範的門檻值，就要進行分庫分表以及分區政策。
我們将邏輯調整後，再進行相關壓測，當壓測滿意時驗證結束，真正上生産去做處理。
三、MySQL調優政策
1、索引
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等也是比較關鍵的資訊，這裡不再贅述，大家可以參考上圖中的表格。
2、分庫分表
針對分庫分表，首先要關注一個問題，單表資料量達到多少才需要進行分庫？
阿裡手冊中寫到資料量達到500萬進行分庫分表。業界的說法是資料量達到2,000萬進行分庫分表。其源頭是百度的一個DBA進行壓測後，覺得壓到2,000萬沒問題，但是超過2,000萬後性能會出現問題，是以業界流傳的資料量界限是2,000萬。
對于我行來說，MySQL規範建議資料量達3,000萬進行分庫分表。
MySQL索引分為兩種，一種是聚簇索引，即主鍵索引，索引和資料保持在一起。另一種是secondary Index，即輔助索引。
下面簡單介紹一些基礎知識：
- MySQL的表資料是以頁形式存放，預設16k，innodb_page_size值是16384，除以1024正好是16k。
- 一般索引為B+樹，葉子存儲資料，非葉子存儲主鍵和指向頁号，一般是12byte，因為使用bigint會占8位元組，同時lot0types.h中源代碼有一個指針FIL_PAGE_OFFSET，占了4位元組，是以非葉子存儲大約存儲12位元組。
- 資料頁資料僅有15k左右可以存儲資料，因為頁頭、頁目錄和頁尾也會占1k的空間。
- B+樹扇出率較高，15k除以12byte，它每一個節點可以指向1280個葉子。B+樹一般的建議層級是2~4層，保證查找某一鍵值，最多2~4次IO即可。主鍵索引一般也都在3層左右。
- 這裡還涉及到一個iops知識，因為大家之前用機械硬碟，一般進行一次io操作需要0.01秒，而現在大家普遍常見的SSD都是上萬的ops，MySQL的通路效率比以前高很多。
針對以上基礎知識，作以下具體說明：
資料量=扇出值^(B+樹層數-1)*葉子節點存儲行數
例如我們行的行占用大小約為850Byte位元組，每個葉子節點可以存儲18行，資料量為2,900萬左右，這也是3,000萬的分庫分表界限的來源。百度行占用大小是1K，每個葉子節點存儲15行資料，資料量為2400萬左右，是以業界才有2,000萬這一說法。阿裡同理，經過計算強調資料量超過500萬進行分庫分表。
我們要了解規範數字背後的含義，這樣很多問題就會迎刃而解。除此之外，伺服器配置、資料庫版本等因素也會影響查詢速度。
3、鎖問題
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。
4、Google Trends & DB-Engines
MySQL和PostgreSQL這兩個資料庫都很好，但是對于我們國家來說，在Google Trends上MySQL的熱度更高一點，占比大概是89%，PostgreSQL占比是11%左右。我們搜尋關鍵字時，最多的是怎麼編譯MySQL，這說明大陸對源碼的掌握和編譯有較為熱切的需求。從DB-Engines Rank中可以看到MySQL和Oracle一直不相上下，PostgreSQL的熱度也在逐漸上升。
四、MySQL性能管控體系
接下來分享我們行的性能管控體系。
“免費的午餐并不好吃”，随着MySQL的廣泛應用，大家并不注意開發規範，這會導緻慢SQL數量呈爆發式增長。一條慢SQL就可以導緻服務不可用，降低使用者幸福指數。我們為此建構管控體系確定開發合規和性能管控。
1、性能管控體系
1）研發流水線 （DevOps） + QA定期檢查 （線下）
首先我們通過研發流水線（DevOos）和QA定期檢查對整個研發環節進行處理。具體可分為以下環節：
- 設計環節
在設計環節，我們建立了設計指引，做了一些中繼資料管理，并設定了能力提升課程提升大家的資料庫使用能力。我們也會推動一些表結構設計工具和中繼資料管理系統，限定大家局面處理問題，同時我們在這一環節設定了門禁。
- 開發環節
這一環節我們将一些規範做到自動化，包括SQL注入檢查和SQL寫法的規則。SonarQube有SonarLint插件可以做伺服器端的同步，這也有利于在開發環節做性能管控。
- 測試環節
這一環節我們通過安全測試、性能測試和混沌測試進行性能管控。
- 釋出環節
釋出環節會由我們的SRE釋出一些态勢感覺報告，從技術以及安全等層面對業務提出針對性建議及後續整改措施。
- 營運環節
在這一環節我們首先會進行慢SQL的監控治理，逐漸減少大事務資料；大家可以看到上圖某部門有2個應用，慢SQL數量12個，最大耗時246秒，平均耗時11.414秒。
其次，我們會進行生産案例分析，将相關規則沉澱到知識庫，并将技術元件放入技術模型。除此之外，我們還會做一些AIOps根因分析。
最後我們會進行一些慢SQL的監控和清除，将大事務提前扼殺，避免其對系統産生影響。
2）性能運維事件響應及溯源
我們會針對每一個問題檢討并溯源，看到底是哪一環節出現問題，哪些環節可以進行優化。例如判斷：語句是否因為沒有限定時間範圍的存在需求缺失情況？設計功能是否考慮到大表關聯這種設計缺陷？開發環節是否存在代碼缺陷？
檢查開發環節後我們會檢查測試環節是否有測試用例缺失、測試工具漏報等缺陷，最後檢查釋出環節是否有釋出标準等缺陷。
3）能力沉澱
最後我們會進行能力沉澱，例如問題閉環追蹤、根因橫向排查，最後沉澱為知識庫、技術元件、度量模型。
2、MySQL開發規範
1）設計原則
在設計方面，我們有以下三大原則：
① 複用原則
在系統架構時，應考慮将相同或類似作用的資訊使用同一套資料結構來存儲。例如：通用參數表、通用字典表。
② 前瞻性原則
- 設計應基于完整的産品定義和業務要素，而非目前具體功能需求設計表結構；
- 設計應基于完整的生命周期和業務流程設計表結構。如：事件類表，可以适當增加種類、狀态字段以便後續擴充。
③ 中繼資料原則
- 列名應遵循統一的資料标準，即同一類型字段應對應同一個中繼資料；字段類型和長度應相同，如同一産品線下所有表的機構編号應該對應同一個中繼資料；
- 常用的字段應建立應用級的标準定義，指明中繼資料，确定字段命名。如所有表 的“最新維護時間”字段都統一命名為last_modify_time，這樣能夠確定我們後續在資料庫挖掘以及做知識圖譜時，可以将整個鍊路串起來。
2）典型規範示例
① 操作：方法論
方法論是萬物之基石，例如每個表我們必須要建立一個主鍵，如果不顯示設定主鍵，會自動生成一個rowid(6 byte)作為隐藏主鍵，且所有表共用此空間，造成性能下降。
② 量化：精細化的理性思維
我們建議掃描命中比原則上應該是100:1，事物大小方面我們行的要求是10萬，業界一般一萬即可。
③ 避坑：規避 MySQL Bug
大表truncate改為drop + create table，這在5.7中效果非常明顯，但是在8.0中公司已經對其進行了修改優化。
針對以上規範，我們要讓開發人員潛移默化地知其然也知其是以然，避免出現一些問題。
3、品質門禁自動化
我們基于druid，擴充了Sonarqube插件，實作本地檢查規則和雲端雲同步。
我們之前大概定了27條規則，其中包含了常見的一些錯誤，例如有人在update語句的set關鍵字後面，誤将分隔符逗号（“,”）寫成“and”，導緻出現預期之外的結果。
4、大事務清除
大事務的相關問題主要有以下幾點：
- binlog的寫入、傳輸、回放緩慢問題。之前我曾看到一個應用，備庫24小時都未完成回放，萬一主庫出問題，都沒辦法回切，隻能等備庫處理完後再回切；
- 交易寫入堵塞；
- 在主庫故障博弈的情況下，到底切還是不切？
我們行以及業界都采用了自動清除方式。
- 在show engine innodb status中，我們可以進行監控，如果一個事物沒有結束，會提示這個事務更新的記錄數；
- 超過什麼樣的門檻值時，我們可以進行自動kill。對于聯機以及批量來說，門檻值是不一樣的，是以我們自動執行kill時，必須規避一刀切的問題。
我們當時做過兩步操作，第一步是将交易的聯機庫跟批量庫進行區分。對于聯機庫，超過三秒以上的交易可以進行自動清除；對于批量庫，通過小範圍試點，然後做到全面推廣。
後續我們應該會将MySQL的主動同步做到不降級，去掉降級時間，但這一點依賴于我們治理完善、大事務不存在的情況。
五、未來展望
1、全鍊路監控
希望可以做到全套端到端的全鍊路監控，這樣可以快速定位哪個節點出了什麼問題。
2、進一步發展AIOps
希望進一步發展AIOps，實作業界所說的1-5-10目标，1分鐘發現，5分鐘處置，10分鐘恢複。
3、掌握源碼
最後希望各位可以掌握一些開源元件的源碼，做到“他山之石，可以攻玉”，了解其中隐藏的bug風險，有利于我們後續對開源元件進行維護。
Q&A
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多一點，因為我們行引入軟體受到管控，必須要進行登記處理。
更多精彩内容
11月9日晚8點，eBay運維經理-楊勝輝老師将帶大家解決網站運維複雜度高且風險度高、運維工作長尾化且碎片化這一核心沖突，并将運維變更工作引向良性循環；沉澱運維經驗至自動化系統，在提高通用化能力和效率的同時，如何減少自動化運維帶來的風險；突破“自動化開發的速度永遠趕不上新任務出現的速度”怪圈，制定自動化和安全保障機制，并通過基礎架構優化提升高可靠能力。
直播位址：http://z-mz.cn/5tTK7
關于我們
dbaplus社群是圍繞Database、BigData、AIOps的企業級專業社群。資深大咖、技術幹貨，每天精品原創文章推送，每周線上技術分享，每月線下技術沙龍，每季度Gdevops&DAMS行業大會。
關注公衆号【dbaplus社群】，擷取更多原創技術文章和精選工具下載下傳