1. <tr id="33chb"><label id="33chb"></label></tr>
  2. <pre id="33chb"></pre>
    當前位置:首頁 > 短網址資訊 > 正文內容

    哪些設置會導致短網址的 MySQL 慢查詢



    作者:短鏈接
    團隊:短網址服務提供商

    前言

    無論是開發同學還是DBA,想必大家都遇到慢查詢(select,update,insert,delete 語句慢),影響短網址服務的穩定性。這里說的,有兩個含義一是比正常的慢,有可能正常執行時間是10ms,異常的是100ms 。二是sql執行時間超過設置的慢查詢標準比如500ms。

    本文從短鏈接的架構以及數據庫緯度來分析導致sql執行慢的原因/場景,拋磚引玉,有不足之處還請大家多多提建議。

    二、基礎知識

    分析慢查詢之前,我們先看看sql執行的路徑,理清楚可能會影響sql執行速度的相關因素。

    執行路徑

    app ---[proxy]---db

    app --- db

    目前大部分的數據庫架構基本都是上面的路徑,sql從app的應用服務器發起經過proxy然后到db,db執行sql進過proxy或者直接返回給app應用服務器。分析這個過程我們可以得到幾個會影響sql執行速度的因素

    1. 1 網絡,各個節點之間的網絡


    2. 2 OS系統 ,即數據庫服務器


    3. 3 短網址的MySQL數據庫本身


    三、基礎系統層面

    3.1 網絡層面

    網絡丟包,重傳

    其實這個比較容易理解。當sql 從app端發送到數據庫,執行完畢,數據庫將結果返回給短鏈接的用戶端,這個將數據返回給短鏈接用戶的過程本質是網絡包傳輸。因為鏈路的不穩定性,如果在傳輸過程中發送丟包會導致數據包重傳,進而增加數據傳輸時間。從app端來看,就會覺得sql執行慢。


    網卡滿 比如大字段

    這個場景可能不容易遇到,如果公司業務體量很大,比如平時每天300w訂單的電商平臺,平臺大促(雙十一,618)的時候極有可能出現網卡被打滿。網卡帶寬被占滿類似各種節假日高速公路收費站(網卡)擁堵導致車流(數據包傳輸的速度)行動緩慢。

    網絡鏈路變長,需要使用短網址服務器去優化

    該場景會影響應用緯度的一個事務比如交易下單整體耗時。

    我們知道每個節點之間的數據傳輸是需要時間的,比如同城跨機房(15KM)之間的訪問一般網絡耗時1.5ms左右。

    鏈路1 [app1]--調用--[app2]---[proxy]---[db] 相比 鏈路2[app1] -- [proxy] --[db]

    執行一條sql請求會增加 [app1]--[app2]之間的網絡傳輸耗時大約3ms。如果一個業務事件包含30個sql ,那么鏈路1要比鏈路2 多花至少90ms的時間成本。導致業務整體變慢。

    3.2 受到影響IO的場景

    磁盤io被其他任務占用

    有些備份策略為了減少備份空間的使用,基于xtrabckup備份的時候 使用了 compress 選項將備份集壓縮。當我們需要在數據庫服務器上恢復一個比較大的實例,而解壓縮的過程需要耗費cpu和占用大量io導致數據庫實例所在的磁盤io使用率100%,會影響MySQL 從磁盤獲取數據的速度,導致大量慢查詢。

    raid卡 充放電,raid 卡重置

    RAID卡都有寫cache(Battery Backed Write Cache),寫cache對IO性能的提升非常明顯,因為掉電會丟失數據,所以必須由電池提供支持。電池會定期充放電,一般為90天左右,當發現電量低于某個閥值時,會將寫cache策略從writeback置為writethrough,相當于寫cache會失效,這時如果系統有大量的IO操作,可能會明顯感覺到IO響應速度變慢,cpu 隊列堆積系統load飆高。下面是一個raid充放電導致sql慢查的案例。

    root@rac1#megacli  -FwTermLog dsply -aALL11/08/143:36:58: prCallback: PR completed for pd=0a11/08/143:36:58: PR cycle complete11/08/143:36:58: EVT#14842-11/03/12  3:36:58:  35=Patrol Read complete11/08/143:36:58: Next PR scheduled to start at 11/10/123:01:59 11/08/140:48:04: EVT#14843-11/04/12  0:48:04:  44=Time established as 11/04/12  0:48:04; (25714971 seconds since power on)11/08/1415:30:13: EVT#14844-11/05/12 15:30:13: 195=BBU disabled; changing WB virtual disks to WT  ---問題的原因11/08/1415:30:13: Changein current cache property detected for LD : 0!11/08/1415:30:13: EVT#14845-11/05/12 15:30:13:  54=Policy change on VD 00/0 to [ID=00,dcp=0d,ccp=0c,ap=0,dc=0,dbgi=0,S=0|0] from [ID=00,dcp=0d,ccp=0d,ap=0,dc=0,dbgi=0,S=0|0]

    raid 卡充電將磁盤的寫策略有write back 修改為write through ,io性能急劇下降導致sql慢查,進而影響應用層的邏輯處理。

    raid 卡重置 當raid卡遇到異常時,會進行重置,相當于程序重啟,導致系統io hang。此時也會導致sql慢。下圖是生產中遇到的 RAID卡重置案例。

    io調度算法

    noop(電梯式調度策略):

    NOOP實現了一個FIFO隊列,它像電梯的工作方式一樣對I/O請求進行組織,當有一個新的請求到來時,它將請求合并到最近的請求之后,以此來保證請求同一個介質。NOOP傾向于餓死讀而利于寫,因此NOOP對于閃存設備,RAM以及嵌入式是最好的選擇。

    deadline(介質時間調度策略):

    Deadline確保了在一個截至時間內服務請求,這個截至時間是可調整的,而默認讀期限短于寫期限。這樣就防止了寫操作因為不能被讀取而餓死的現象。Deadline對數據庫類應用是最好的選擇。

    anticipatory(預料I/O調度策略):

    本質上與Deadline一樣,但在最后一次讀操作后,要等待6ms,才能繼續進行對其他I/O請求進行調度。它會在每個6ms中插入新的I/O操作,而會將一些小寫入流合并成一個大寫入流,用寫入延時換取最大的寫入吞吐量。AS適合于寫入較多的環境,比如文件服務器,AS對數據庫環境表現很差。

    3.3 cpu 類型

    cpu 電源策略是控制cpu運行在哪種模式下的耗電策略的,對于數據庫服務器推薦最大性能模式 以下內容摘自 《Red Hat Enterprise Linux7 電源管理指南》:


    指令集  最近遇到的一個性能案例是hw的機器,因為指令集合默認關閉導致性能下降15%。


    自己對CPU并不精通,所以這里的2個點并非CPU優化配置的全部,自建機房的運維朋友依賴官方技術支持的建議或者技術資料的指導來設置cpu相關參數。

    四、數據庫層面

    4.1 沒有索引,或者索引不正確

    這個場景其實比較容易理解。相信每個DBA工作過程中都會或多或少遇到性能案例都和索引設計有關:創建表,沒有索引,sql隨著數據量增大全表掃描而變慢。這個就不額外舉例子了。

    4.2 隱式轉換

    發生隱式轉換時,MySQL選擇執行計劃并不能利用到合適的索引而是選擇全表掃描導致慢查詢。常見的引發隱式轉換的場景如下:

    in 參數包含多個類型,簡單說,就是在IN的入口有一個判斷, 如果in中的字段類型不兼容, 則認為不可使用索引.例如 --圖

    判斷符號左邊是字符串,右邊是數字 ,比如 where a=1;其中a是字符串

    多表join時,where 左右兩邊的字段的字符集類型不一致。

    4.3 執行計劃錯誤

    由于短網址的MySQL優化器本身的不足,選擇執行計劃時會導致錯誤的執行計劃使sql走了錯誤的索引或者沒有做索引。比如

    在檢查某業務數據庫的slowlog 時發現一個慢查詢,查詢時間 1.57s ,檢查表結構 where條件字段存在正確的組合索引,正確的情況下優化器應該選擇組合索引,而非為啥會導致慢查詢呢?

      root@rac1 10:48:11>explain select id,gmt_create, gmt_modified,order_id,service_id, seller_id,seller_nick, sale_type from lol where seller_id= 1501204and service_id= 1and sale_type in(3, 4) and use_status in(3, 4, 5, 6) and process_node_id= 6 order by id desc limit 0,20 \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: lol         type: indexpossible_keys:idx_sellerid,idx_usestatus_saletype,idx_sellerid_saletype,idx_sidustsvidtype          key: PRIMARY  --- 應該選擇 idx_sidustsvidtype      key_len: 8ref: NULL         rows: 3076Extra: Usingwhere1 row inset (0.00 sec)

    推薦閱讀order by 主鍵id導致全表掃描的問題 

    4.4 數據巨大

    比如select count(*) from t1 where a='xxxx'; 盡管字段a有索引,但是如果符合條件的記錄數超高10w,查詢速度還是會比較慢。

    select  count(*) from t1 where app = 'marketing';+----------+| count(*) |+----------+|  2671690 |+----------+1 row inset (0.92 sec)

    4.5 MetaData Lock鎖等待

    MDL鎖這個場景其實蠻多案例的,比如ddl開始時,針對同一個表的長查詢還沒結束,后續的寫操作都會被堵住導致 thread running 飆高。實例整體的sql執行慢。

    案例一 長查詢/mysqldump 阻塞DDL 

    未提交事務阻塞ddl 阻塞查詢

    4.6 并發更新同一行

    常見的秒殺場景:數據庫并發執行update,更新同一行的動作會被其他已經持有鎖的會話堵住,并且需要要進行判斷會不會由于自己的加入導致死鎖,這個時間復雜度O(n),如果有1000個請求,每個線程都要檢測自己和其他999個線程是否死鎖。如果其他線程都沒有持有其他鎖,約比較50w次(計算方式 999+998+...+1)。這個種鎖等待和檢查死鎖沖突帶來巨大的時間成本。對于OLTP 業務高并發大流量訪問的情況下,鎖等待會直接導致thread running飆高,所有的請求會被阻塞并等待innodb引擎層處理,于是sql 會變慢。

    4.7 數據分布不均

    其實和數據分布相關,常見的比如 字段a 是標記狀態0,1,總行數1000w,a=0的值大概幾千條,a=1的有999w多。顯然執行

    select count(*) from tab where a=1 ;

    的查詢效率肯定比查詢a=0的要慢很多。

    select count(*) from tab where a=0 ;

    4.8 sql 姿勢不合理

    常見的分頁查詢 ,使用大分頁深度查詢。

    SELECT * FROM table where kid=1342 and type=1 order id desc limit 149420 ,20;

    該SQL是一個非常典型的排序+分頁查詢:order by col desc limit N,M MySQL 執行此類SQL時需要先掃描到N行,然后再去取 M行。對于此類操作,取前面少數幾行數據會很快,但是掃描的記錄數越多,SQL的性能就會越差,因為N越大,MySQL需要掃描越多的數據來定位到具體的N行,這樣耗費大量的IO 成本和時間成本。

    針對limit 優化有很多種方式:

    1 前端加緩存、搜索,減少落到庫的查詢操作。比如海量商品可以放到搜索里面,使用瀑布流的方式展現數據,很多電商網站采用了這種方式。

    2 優化SQL 訪問數據的方式,直接快速定位到要訪問的數據行。

    3 使用書簽方式 ,記錄上次查詢最新/大的id值,向后追溯 M行記錄。對于第二種方式 我們推薦使用"延遲關聯"的方法來優化排序操作,何謂"延遲關聯" :通過使用覆蓋索引查詢返回需要的主鍵,再根據主鍵關聯原表獲得需要的數據。

    4.9 短網址的表結構設計

    表結構設計是否合理也是影響sql性能的重要因素之一。以下表格展示了字段類型不同帶來的rt性能差異。其中字段c1 為int類型的字段,字段c2則是表名對應的字符串長度類型varchar(200)到varchar(5000) ,還有text字段。

    對于讀請求,單獨查詢c1 int類型的性能并無差異。查詢字段c2時,隨著字段占用的實際字節大小增大,耗費的時間增加,也即rt增大。帶寬逐步增大,text的帶寬147MB 對于千兆網卡已經滿了。

    對于寫請求,因為binlog為row模式,字段長度越大,binlog也越大,網絡傳輸帶寬增加。整體rt也增加。

    4.10 innodb 刷臟頁

    對數據庫運行機制有一定了解的朋友都會知道InnoDB引擎采用Write Ahead Log(WAL)策略,即事務提交時,先寫日志(redo log),再寫磁盤。為了提高IO效率,在寫日志的時候會先寫buffer,然后集中flush buffer pool 到磁盤。 這個過程 我們稱之為刷臟頁。官方文檔中描述:

    With heavy DML activity, flushing can fall behind if it is not aggressive enough, resulting in excessive memory use in the buffer pool; or, disk writes due to flushing can saturate your I/O capacity if that mechanism is too aggressive. 這個過程中就有可能導致平時執行很快的SQL突然變慢。

    推薦閱讀:

    1. https://dev.mysql.com/doc/refman/5.7/en/innodb-lru-background-flushing.html


    2. https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-adaptive_flushing.html

    4.11 undo 沒有被purge/回收

    UNDO 日志是 MVCC 的重要組成部分,當一條數據被修改時,UNDO 日志里面保存了記錄的歷史版本。當事務需要查詢記錄的歷史版本時,可以通過 UNDO 日志構建特定版本的數據。

    每條行記錄上面都有一個指針 DATA_ROLL_PTR,指向最近的 UNDO 記錄。同時每條 UNDO 記錄包含一個指向前一個 UNDO 記錄的指針,這樣就構成了一條記錄的所有 UNDO 歷史的鏈表。當 UNDO 的記錄還存在,那么對應的記錄的歷史版本就能被構建出來。

    當記錄對應的版本通過 DATA_TRX_ID 比對發現不可見時,通過系統列 DATAROLLPTR,找到對應的回滾段記錄,繼續通過上述判斷記錄可見的規則,進行判斷,如果記錄依舊不可見,繼續通過回滾段查找之前的版本,直到找到對應可見的版本。

    所以當有長事務/異常未提交的情況就會因為其他查詢需要構建快照導致undo 不能被及時回收。查詢遍歷的undo越多sql執行的越慢。

    五、小結

    這里總結了我工作經歷中遇到的一部分可能會影響SQL執行效率的場景或者案例,經歷有限,難免有遺漏的案例/場景,拋磚引玉,歡迎各位有興趣的讀者朋友留言說說你們遇到的場景。

    掃描二維碼推送至手機訪問。

    版權聲明:本文由短鏈接發布,如需轉載請注明出處。

    本文鏈接:http://www.virginiabusinesslawupdate.com/article_554.html

    分享給朋友:
    返回列表

    上一篇:短網址API文檔接口說明

    沒有最新的文章了...

    相關文章

    使用60進制的程序仿了一個新浪微博短鏈接生成器

    使用60進制的程序仿了一個新浪微博短鏈接生成器

    與其說仿新浪短網址其實算是個嚼頭,招引人們的眼球,對于常規的進制算法可以去參看數據結構一書 通過取模方式計算出對應的n進制數,t.cn短網址的原理大致如下:Java代碼  int nv =&...

    FT12短網址:現在的信息技術應用只相當于工業革命的蒸汽機時代

    FT12短網址:現在的信息技術應用只相當于工業革命的蒸汽機時代

    【FT12短網址】往后10—20年,對經濟奉獻最大的也許不是新創造的嚴重技能,而是信息技能融入各個工業的新商品、按需供給個性化商品和效勞的新業態、工業鏈跨界交融的新模式。對信息年代而言,信息技能遍及浸透還有很遠的路要走,現在的信息技能運用只...

    為了吸引你的目光,這些家具費盡心機

    家具,不只是家中不可或缺的物件,還是我們居住空間中少不了的擺設,正因為如此,它們也有更多可以挖掘的創意性設計所在。設計師便對常見的家具做了一些不一樣的改變和創新,讓使用者和這些本無生命的家具之間,有了更多的連接。把書本“吊”在木架上顛覆你對...

    又有人要被抓了!新一輪金融整頓開始

    又有人要被抓了!新一輪金融整頓開始原上草有人僥幸地認為,金融反腐和金融整頓清理,搞了這么久,抓了這么多官員,查了這么多企業家,應該快結束了吧?如果真這樣認為,事情就太簡單了。草哥獲得的信息是,事情遠沒有這么快結束,接下來,更大的金融整頓風暴...

    喂不飽的特斯拉,恐怕最后還得攜手AMD自研芯片

    喂不飽的特斯拉,恐怕最后還得攜手AMD自研芯片

    [ FT12短網址 ] 據外媒報道,AMD的半導體代工廠GlobalFoundries的CEO Sanjay Jha在該公司的技術大會上確認,他們正在與特斯拉合作生產用于自動駕駛程序的定制化芯片,并為后者提供晶元生產代工服務。圖片...

    技術漫談:為何KPI毀了索尼,而OKR卻成就了谷歌?

    作者|李運華編輯|小智從技術 leader 的角度出發,看技術人績效考核的痛。大多數公司里面總會因為 KPI 的考核方式而存在各種各樣的問題,OKR 是一個在硅谷互聯網公司比較流行的做法。怎樣去理解 OKR 這個概念,并在技術團隊中推行,從...

    發表評論

    訪客

    ◎歡迎參與討論,請在這里發表您的看法和觀點。
    一本色综合网久久
    1. <tr id="33chb"><label id="33chb"></label></tr>
    2. <pre id="33chb"></pre>