阿裡資料庫性能診斷的利器——SQL執行干預

来源:https://www.cnblogs.com/mq44944/archive/2019/05/05/sqlfilter.html
-Advertisement-
Play Games

在業務資料庫性能問題診斷中，如果發現一個業務性能很差跟某個SQL有關，應用連接池幾乎被該SQL占滿，同時資料庫伺服器上也不堪重負。此時情況很緊急，業務改SQL重發佈已經來不及了，運維能選擇的操作不多。 阿裡資料庫(AliSQL和OceanBase)都支持在運行中干預SQL的執行計劃，以及對問題SQL... ...


概述

在業務資料庫性能問題診斷中，如果發現一個業務性能很差跟某個SQL有關，應用連接池幾乎被該SQL占滿，同時資料庫伺服器上也不堪重負。此時情況很緊急，業務改SQL重發佈已經來不及了，運維能選擇的操作不多。如批量重啟應用、資料庫切換或者重啟。此時業務中斷一下，很可能很快壓力又上來，問題依然在那個SQL。前篇文章《阿裡資料庫性能診斷的利器——SQL全量日誌》分享瞭如何定位問題SQL，本文就分享阿裡資料庫們如何處理這個問題SQL。

ORACLE在SQL性能診斷方面的功能非常豐富，一直被其他資料庫模仿。其中Outline和SQL Profile功能就非常有用。當業務SQL在生產環境執行計划走錯後(如表連接順序或演算法不對，或者索引選擇的不對等等)，此時可以在該問題SQL上使用hint先生成一個正確的執行計劃，然後用Outlines存儲這個執行計劃，然後再跟執行計劃緩存里在用的執行計划進行交換，從而讓業務SQL回到正確的執行計划上，性能問題也就迎刃而解了。雖然這個過程有點複雜，也有很多註意事項，但終究是一個不錯的選擇。ORACLE在10g後將Outlines功能進一步改進推出SQL Profiles功能,更容易生成、更改和控制SQL執行計劃。詳情參考後面文章。

MySQL資料庫在SQL性能優化方面能力一般，資料庫性能又嚴重依賴主機IO和CPU能力。面對這個問題社區版的MySQL資料庫只有選擇切換或重啟。

 

在互聯網業務中，資料庫請求數QPS非常高，當SQL有性能問題時，很快就會將資料庫的某個資源(CPU或IO)耗盡，進而拖慢其他正常的業務SQL。而應用伺服器集群里每個APP的連接池也會相應被耗盡，從而可能出現應用相繼掛掉，引起雪崩。阿裡資料庫AliSQL和OceanBase針對這個場景都有一個SQL干預手段，即SQL執行計劃修改或者限流。

  

阿裡資料庫內核的SQL執行干預功能

 

AliSQL的SQL執行計劃干預和SQL限流

AliSQL是阿裡巴巴資料庫內核團隊曾經維護的一個開源MySQL的分支，針對MySQL內核做了很多加強和優化。其中一個獨特的功能就是SQL執行計劃干預和SQL限流。

AliSQL的SQL執行計劃干預也是利用hint先生成正確的執行計劃，然後再替換掉實際運行中的執行計劃。能修改的也只是索引。並且其替換並不像ORACLE那樣嚴格的使用SQL ID去替換，而是可以根據SQL特征去匹配替換。這個功能的關鍵字是sql_hints。其原理是在語法解析後，sql優化前，根據設定的規則，對語法解析樹進行修改。模擬在語法解析中，解析到了index hint。

功能：可在Server端設定指定規則，為指定sql動態添加索引hint，以干預其執行計劃。
語法：

Set sql_hints=’+,<schema_name>,<table_name>,<index_name>,<keyword1>~<keyword2>~<keywordn>’;
Example: 
Set sql_hints=’+,test,t1,idx_id1,select id from~orderid=~status=’;

 

這個功能也有些限制就是只能針對單表select限制，特征指定(keyword)不能太簡單，db名稱不能模糊匹配。在電商MySQL場景完全夠用了。

示例設置規則：

root@(none) 01:03:17>set global sql_hints='+,xxx_xxxxxx_0000,xxxxxx_template_refer_,idx_refer,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` =';

 

示例查看規則,可以看到規則命中多少次，失敗多少次。

root@(none) 01:03:14>show sql_hints; 
+---------+-----------------------+--------------------------+-----------+---------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rule_id | db | table | index | key_num | hits | errors | cmd_str | 
+---------+-----------------------+--------------------------+-----------+---------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| 6 | xxxxxx_xxxxxx_0000 | xxxxxx_template_refer_ | idx_refer | 5 | 452076 | 0 | xxxxxx_xxxxxx_0000,xxxxxx_template_refer_,idx_refer,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` =                           | 
| 2 | xxxxxx_xxxxxx_0000 | xxxxxx_template_refer_ | idx_refer | 3 | 2181691 | 0 | xxxxxx_xxxxxx_0000,xxxxxx_template_refer_,idx_refer,`xxxxxx_template_refer`.`refer_user_id` =~`xxxxxx_template_refer`.`user_id` =~`xxxxxx_template_refer`.`template_id` = | 
+---------+-----------------------+--------------------------+-----------+---------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
2 rows in set (0.00 sec)

 

當SQL性能問題不是執行計划走偏導致的時候，上面方法就沒有用。這類往往是新發業務，業務上線前沒有嚴格測試性能。此時需要業務改SQL，但是應用修改發佈時間沒那麼快。所以AliSQL還有個功能就是針對SQL限流功能，即限制SQL併發數。

設置sql限流時可以針對select/update/delete 設置限流命令：

set global sql_select_filter='+,併發數,sql特征1~sql特征2';
set global sql_update_filter='+,併發數,sql特征1~sql特征2';
set global sql_delete_filter='+,併發數,sql特征1~sql特征2';

查看當前的sql限流設置命令：

show sql_filters;

舉例，限制SQL併發數為4：

root@(none) 01:23:15>set global sql_select_filter='+,4,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` ='; 
Query OK, 0 rows affected (0.00 sec) 
root@(none) 01:24:34>show sql_filters; 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| type | item_id | cur_conc | max_conc | key_num | key_str | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| SELECT | 1 | 0 | 4 | 5 | +,4,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` = | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

 

備註：

  1. 其中的cur_conc 列顯示當前讀併發數。如果一直為0，表示規則沒有命中問題sql。

  2. sql特征不能太泛（誤命中其他sql），也不能太具體（漏掉部分sql）。

  3. 限制的併發數（自然數）不能太高（超過16意義不大），也不能太低（太低容易限制過死，導致該sql的qps嚴重低於正常值，很容易引起業務訪問量下跌），也要恰到好處。

  4. 如果特征中包含中文（不推薦），務必確保中文能正確的被mysql接收。os的session變數export.UTF-8 ,同時把終端字元集設置為 UTF-8。 詳情參見 資料庫字元亂碼問題分析。

 

該限流設置還可以撤銷。首先查看sql限流規則的 item_id 值，然後通過相同的設置命令取消該項規則。

命令：

set global sql_select_filter='-,規則id';
set global sql_update_filter='-,規則id';
set global sql_delete_filter='-,規則id'; 

舉例：

root@(none) 01:24:34>show sql_filters; 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| type | item_id | cur_conc | max_conc | key_num | key_str | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| SELECT | 1 | 0 | 4 | 5 | +,4,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` = | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 
root@(none) 01:24:41>set global sql_select_filter='-,1'; 
Query OK, 0 rows affected (0.00 sec) 
root@(none) 01:24:49>show sql_filters; 
Empty set (0.00 sec)

 

AliSQL的這個SQL執行計劃修改和限流功能，在開源的AliSQL(地址：https://github.com/alibaba/AliSQL)里已經包含了。

OceanBase的SQL Outlines功能

OceanBase是阿裡巴巴和螞蟻金服完全自主研發的通用的分散式關係型資料庫，其在SQL執行和性能診斷方面的邏輯大量參考了ORACLE的設計思路。OceanBase也支持SQL Outline功能，能夠修改線上運行的SQL執行計劃。同時也支持SQL限流功能。

Outline的用法也是通過SQL Hint固定SQL的執行計劃，可以調整表連接演算法、使用的索引等等。
創建大綱的語法如下：

CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ];

  1. 其中stmt為一個帶有HINT的DML語句。限流或固定計劃，通過stmt中的HINT來區分。

  2. 如果期望對含有HINT的語句進行限流和固定計劃，則需要TO target_stmt來指明相應的SQL。create outline outline_name on stmt1 to stmt2;的語意是說對stmt2創建outline,讓stmt2使用stmt1中的hint。

  3. 指定OR REPLACE後，可以對已經存在執行計劃或限流規則進行replace。（註：限流規則和執行計劃間可以彼此替換）

  4. 在使用target_stmt時，嚴格要求stmt與target_stmt在去掉hint後完全匹配（實現中為去掉hint的signature相同）。若是在創建限流時使用target_stmt，則同時要求fix_param完全匹配。

 

舉例說明：

OceanBase (root@oceanbase)> create outline ol_1 on select /*+index(t1 c2)*/ * from t1 where c1 =1;
Query OK, 0 rows affected (0.07 sec)

OceanBase (root@oceanbase)> select * from __all_outline\G;
*************************** 1. row ***************************
gmt_create: 2016-06-08 16:09:39.058537
gmt_modified: 2016-06-08 16:09:39.058537
tenant_id: 1
outline_id: 1099511628777
database_id: 1099511627777
schema_version: 1465373379055176
name: ol_1
signature: select * from t1 where c1 =?
outline_content: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c2") END_OUTLINE_DATA */
sql_text: select /*+index(t1 c2)*/ * from t1 where c1 =1
owner: root
used: 0
version: 60768-local-78cf62842644724e437542cd12c2cc1e76805ee0
compatible: 1
enabled: 1
format: 01 row in set (0.07 sec)

OceanBase (root@oceanbase)> create table t1(c1 int, c2 int, c3 int, key(c2), key(c3,c2));
Query OK, 0 rows affected (0.13 sec)

OceanBase (root@oceanbase)> CREATE OUTLINE ol_1 ON select /*+index(t1 c3)*/ c3,c2 from t1 TO select c3,c2 from t1;
Query OK, 0 rows affected (0.02 sec)
OceanBase (root@oceanbase)> select * from oceanbase.gv$outline\G
*************************** 1. row ***************************
tenant_id: 1
database_id: 1099511627777
outline_id: 1099511628777
database_name: oceanbase
outline_name: ol_1
visible_signature: select c3,c2 from t1
sql_text: select /*+index(t1 c3)*/ c3,c2 from t1
outline_target: select c3,c2 from t1
outline_sql: select /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c3") END_OUTLINE_DATA*/ c3,c2 from t1
1 row in set (0.00 sec)

 

創建完ol_1後，所有**select from t1 where c1 =?語句都會按照/+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1""oceanbase.t1"@"SEL$1" "c2") END_OUTLINE_DATA */固定的計劃執行。其中?可以是任意值。

這裡著重介紹下遷移outline過程中會用到的oceanbase.gv$outline視圖中的列：

  1. database_name: outline所屬的database的名字

  2. outline_name: outline自己的名字

  3. outline_sql: outline_sql是將原始查詢和固定計劃的hint拼接在一起的字元串。用於還原outline 創建語句，上面這個例子中，就是將select c3,c2 from t1 和 固定計劃的 /+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "c3") END_OUTLINE_DATA/拼接而成的，

  4. outline_target: outline_target是使用CREATE OUTLINE ol_name ON on_stmt TO to_stmt這種語法創建outline時的to_stmt, 用於支持在帶有hint的DML語句上創建outline; 如果使用CREATE OUTLINE ol_name ON on_stmt ；語句創建outline，則outline_target內容為空。

 

OceanBase Outline更多的時候是用在資料庫Failover、分散式資料庫集群擴容(增加實例和分庫等)等場景中，將正常資料庫的SQL執行計劃遷移到新的租戶中，可以確保SQL性能不會發生意外的變化。

當create outline時，如果指定MAX_CONCURRENT(NUM)，將會對當前SQL進行限流。指定限流規則後會控制一個observer中可以併發執行的限流SQL個數。併發度控制的是限流SQL對應的physical_plan在單一observer可以併發執行的個數；也就是說對於整個集群，併發執行的限流SQL個數大於HINT中指定的併發度。

示例：

OceanBase (root@oceanbase)> create outline ol_1 on select /*+max_concurrent(1)*/ * from t2 where c1 = 1 and c2 = ?;
Query OK, 0 rows affected (0.04 sec)

 

創建完ol_1後，形如 select * from t2 where c1 = 1 and c2 = ? 的sql在單台observer中可執行的併發度為1；
c2 = ? 表明問號的位置可以被任意的const值代替，例如下麵的sql都會被限流：

select * from t2 where c1 = 1 and c2 = 1;
select * from t2 where c1 = 1 and c2 = 2;
select * from t2 where c1 = 1 and c2 = "2";
select * from t2 where c1 = 1 and c2 = true;

 

註意：

限流和固定計劃的使用方法類似，均是通過指定HINT的形式來實現；限流的HINT為MAX_CONCURRENT(NUM)，其中NUM為併發度。當被限流的SQL達到最大併發個數後，再有新的限流SQL執行時會報，server會返回 SQL reach max concurrent num 錯誤。

當前限流和固定計劃功能均通過create outline語句來實現，為了確保語意的正確性和清晰性，我們規定create outline中限流HINT和其他HINT不能同時存在。同時存在時只執行限流規則，不會固定執行計劃。

高級用法：

  1. 同一個outline_name可以對應多個具有相同signature的限流規則。

  2. 當同一條SQL可以匹配多個限流規則時，會選擇併發度最小的進行限流。

OceanBase (root@oceanbase)> create outline ol_1 on select /*+max_concurrent(1)*/ * from t1 where c1 =1 and c2 = 1;
Query OK, 0 rows affected (0.07 sec)

OceanBase (root@oceanbase)> alter outline ol_1 add select /*+max_concurrent(1)*/ * from t1 where c1 =1 and c2 = ?;
Query OK, 0 rows affected (0.09 sec)

OceanBase (root@oceanbase)> alter outline ol_1 add select /*+max_concurrent(10)*/ * from t1 where c1 =? and c2 = 1;
Query OK, 0 rows affected (0.04 sec)

OceanBase支持的Hints

  • 語句級別的hint

FROZEN_VERSION
QUERY_TIMEOUT
READ_CONSISTENCY
LOG_LEVEL
QB_NAME
ACTIVATE_BURIED_POINT
TRACE_LOG
MAX_CONCURRENT

  • 計劃相關的hint

FULL
INDEX
LEADING
USE_MERGE
USE_HASH
USE_NL
ORDERED
NO_REWRITE

  • 存儲outline的schema信息的系統表

oceanbase.__all_outline
oceanbase.__all_outline_history

  • 固定計劃相關虛擬表和試圖

展示的均是當前租戶的信息：

oceanbase.__tenant_virtual_outline 用於outline遷移使用的虛擬表，同時顯示固定計劃的信息。
oceanbase.gv$outline 在__tenant_virutal_outline基礎上創建的視圖。
information_schema.dba_outlines 在__all_table上創建的視圖。

  • 限流相關虛擬表和試圖

下表展示的均是當前租戶的信息：

oceanbase.__tenant_virtual_concurrent_limit_sql： 展示限流信息
oceanbase.gv$concurrent_limit_sql： 在__tenant_virtual_concurrent_limit_sql上創建的視圖。

 

參考

 

總結

阿裡資料庫(AliSQL和OceanBase)都支持在運行中干預SQL的執行計劃，以及對問題SQL併發進行限流，以快速將資料庫和應用從某個問題SQL的影響中恢復出來。

推薦關註

 更多分享，推薦關註公眾號：obpilot


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 微信公眾號開發：伺服器配置
    前言： 如果讓大家說出一款國內比較熱門的社交軟體，那無疑就是QQ和微信了，說到微信，無不例外的會想到微信公眾號和小程式，所以現在它們已經是很多企業流量及品牌推廣的主要途徑， 而作為一個開發者而言呢，如果想要開發打造一款屬於自己或企業的公眾號，就是需要對微信公眾號平臺API文檔的熟悉。 你可以花上半天 ...
  • [20190502]給顯示輸出加入時間戳.txt
    [20190502]給顯示輸出加入時間戳.txt--//有別人問我執行腳本中timestamp.pl的代碼，實際上有些文章裡面有源代碼，有一些忘記寫上了。--//貼上：$ cat /usr/local/bin/timestamp.pl#!/usr/bin/perlwhile (<>) {($sec, ...
  • CentOS6.9 Python環境配置（python2.7、pip、virtualenv）
    python2.7 setuptools pip 建立軟鏈接 virtualenv virtualenvwrapper ...
  • 基於macOS+VMware的GNS3內VM上公網
    筆者經常需要做網路實驗，GNS3就是筆者最喜歡用的模擬器，為了便於實驗，需要能從macos上直接ssh登陸模擬出來的vm，並且vm需要上公網。經過研究，已解決此問題，並以此分享出來 ...
  • 基於STM32之UART串口通信協議（一）詳解
    一、前言 1、簡介 寫的這篇博客，是為了簡單講解一下UART通信協議，以及UART能夠實現的一些功能，還有有關使用STM32CubeMX來配置晶元的一些操作，在後面我會以我使用的STM32F429開發板來舉例講解（其他STM32系列晶元大多數都可以按照這些步驟來操作的），如有不足請多多指教。 2、U ...
  • 《Linux就該這麼學》—學習記錄—第0章
    稻盛和夫先生在《活法》中有段一直激勵著我的話，現在轉送給正在閱讀本書的你： “工作馬馬虎虎，只想在興趣和游戲中尋覓快活，充其量只能獲得一時的快感，絕不能嘗到從心底涌出的驚喜和快樂，但來自工作的喜悅並不像糖果那樣—放進嘴裡就甜味十足，而是需要從苦勞與艱辛中滲出，因此當我們聚精會神，孜孜不倦，剋服艱辛後 ...
  • Linux下zookeeper集群搭建
    Linux下zookeeper集群搭建 部署前準備 1. 下載zookeeper的安裝包 http://zookeeper.apache.org/releases.html 我下載的版本是zookeeper 3.4.10。 2. 準備三台伺服器 ip地址為： 3. 檢查jdk版本，安裝jdk環境，j ...
  • mysql 查詢存在A表中而不存在B表中的數據
    有兩張表，學生信息表infolist： 學生姓名錶namelist： 現要查詢出，存在infolist中，而不存在namelist中的學生，語句如下： 查詢結果如下： age有些奇葩，不用在意。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類