實用QPS和TPS高的高效分析方法

zping發表於2021-11-24

 現在主庫的MySQL的QPS一直在3K/s左右，想知道其到底執行了那些SQL，或者是那些SQL執行的次數比較多：

  騰訊雲的後臺監控：

    開啟騰訊雲的SQL審計後，下載幾分鐘SQL日誌檔案, 下列語句在MySQL建表，如我們下載了6分鐘的單例項審計日誌：   

實用QPS和TPS高的高效分析方法 
CREATE TABLE `ex` (
  `AffectRows` varchar(255)  DEFAULT NULL,
  `ErrCode` varchar(255)  DEFAULT NULL,
  `SqlType` varchar(255)  DEFAULT NULL,
  `TableName` varchar(255)  DEFAULT NULL,
  `PolicyName` varchar(255)  DEFAULT NULL,
  `DBName` varchar(255)  DEFAULT NULL,
  `Sql` text CHARACTER SET utf8mb4,
  `Host` varchar(255)  DEFAULT NULL,
  `User` varchar(255)  DEFAULT NULL,
  `ExecTime` varchar(255)  DEFAULT NULL,
  `CpuTime` varchar(255)  DEFAULT NULL,
  `LockWaitTime` varchar(255)  DEFAULT NULL,
  `CheckRows` varchar(255)  DEFAULT NULL,
  `SentRows` varchar(255)  DEFAULT NULL,
  `ThreadId` varchar(255)  DEFAULT NULL,
  `NsTime` varchar(255)  DEFAULT NULL,
  `IoWaitTime` varchar(255)  DEFAULT NULL,
  `TrxLivingTime` varchar(255)   DEFAULT NULL,
  `Timestamp` varchar(255)  DEFAULT NULL,
  `Result` varchar(255)  DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx_sql` (`Sql`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
View Code

  將sql檔案匯入到表中，執行下列SQL：

select tab,op,count(*) num from (
select case when `sql` like 'insert%' then 'insert'
when `sql` like 'replace%' then 'replace'
when `sql` like 'update%' then 'update'
when `sql` like 'delete%' then 'delete'
when `sql` like 'select%' then 'select' else '' end op,
case when `sql` like 'insert%' then substring_index(substring_index(LOWER(`sql`), '(', 1),'into',-1)
when `sql` like 'replace%' then substring_index(substring_index(LOWER(`sql`), '(', 1),'into',-1)
when `sql` like 'update%' then substring_index(substring_index(LOWER(`sql`), 'set', 1),'update',-1)
when `sql` like 'delete%' then substring_index(substring_index(LOWER(`sql`), 'where', 1),'from',-1)
when `sql` like 'select%' then substring_index(substring_index(LOWER(`sql`), 'where', 1),'from',-1) else '' end tab,`sql`
from ex where `sql` like 'insert%' or
`sql` like 'update%' or `sql` like 'delete%' or `sql` like 'select%' or `sql` like 'replace%' ) t
GROUP BY tab,op order by 3 desc

 執行結果：

 

 

 初步得到執行排序，初步計算其QPS：

    前2個查詢命令，QPS =550000/60/6=1527次/秒，其每秒3000多次/秒，這2個查詢佔用幾乎一半左右，大體知道其SQL呼叫情況。

    如果要查某個表可以使用全文索引：     

--查詢sys_user表，沒有join關聯欄位,有"select"查詢關鍵字的SQL
select * from ex where MATCH(`sql`) AGAINST ('+sys_user -join +select' IN BOOLEAN MODE);

  立馬就可以查詢到明細情況。

  這裡查詢了增刪改查，如果只查TPS，可以把SQL查詢語句的 " or `sql` like 'select%' "  去掉，就可以查DML的呼叫次數

  總結：

     1，該方法在百萬級別的SQL審計中，能快速分析出哪個表的呼叫表次數多

     2，結合mysql的fullindex全文索引，快速定位到具體的SQL

     3，主要用來分析QPS高的原因

 

相關文章