記錄一次資料庫CPU被打滿的排查過程

来源:https://www.cnblogs.com/Jcloud/archive/2022/08/31/16642188.html
-Advertisement-
Play Games

資料庫CPU使用率100%報警頻繁起來。第一個想到的就是慢Sql,我們對未合理運用索引的表加入索引後,問題依然沒有得到解決,深入排查時,發現在 order by id asc limit n時,即使where條件已經包含了覆蓋索引,優化器還是選擇了錯誤的索引導致。 ...


1 前言

近期隨著數據量的增長,資料庫CPU使用率100%報警頻繁起來。第一個想到的就是慢Sql,我們對未合理運用索引的表加入索引後,問題依然沒有得到解決,深入排查時,發現在 order by id asc limit n時,即使where條件已經包含了覆蓋索引,優化器還是選擇了錯誤的索引導致。通過查詢大量資料,問題得到瞭解決。這裡將解決問題的思路以及排查過程分享出來,如果有錯誤歡迎指正。

2 正文

2.1 環境介紹

2.2 發現問題

22日開始,收到以下圖1報警變得頻繁起來,由於資料庫中會有大數據推數動作,資料庫CPU偶爾報警並沒有引起對該問題的重視,直到通過圖2對整日監控數據分析時,才發現問題的嚴重性,從0點開始,資料庫CPU頻繁被打滿。

圖1:報警圖

圖2:整日CPU監控圖

2.3 排查問題

發現問題後,開始排查慢Sql,發現很多查詢未添加合適的索引,經過一輪修複後,問題依然沒有得到解決,在深入排查時發現了一個奇怪現象,SQL代碼如下(表名已經替換),比較簡單的一個單表查詢語句。

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
ORDER BY
id
LIMIT 500;

 

看似比較簡單的查詢,但執行時長平均在90s以上,並且調用頻次較高。如圖3所示。

圖3:慢Sql平均執行時長

開始檢查表信息,可以看到表數據量在2100w左右。

圖4:數據表情況

排查索引情況,主鍵為id,並且有business_day與full_ps_code的聯合索引。

PRIMARY KEY (`id`) USING BTREE,
KEY `idx_business_day_full_ps_code` (`business_day`,`full_ps_code`)
==========以下索引可以忽略========
KEY `idx_erp_month_businessday` (`erp`,`month`,`business_day`),
KEY `idx_business_day_erp` (`business_day`,`erp`),
KEY `idx_erp_month_ps_plan_id` (`erp`,`month`,`ps_performance_plan_id`),
......

 

通過Explain查看執行計劃時發現,possible_keys中包含上面的聯合索引,而Key卻選擇了Primary主鍵索引,掃描行數Rows為1700w,幾乎等於全表掃描。

圖5:執行計劃情況

2.4 解決問題

第一次,我們分析是,由於Where條件中包含了ID,查詢分析器認為主鍵索引掃描行數會少,同時根據主鍵排序,使用主鍵索引會更加合理,我們試著添加以下索引,想要讓查詢分析器命中我們新加的索引。

ADD INDEX `idx_test`(`business_day`, `full_ps_code`, `id`) USING BTREE;

 

再次通過Explain語句進行分析,發現執行計劃完全沒變,還是走的主鍵索引。

explain
SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
ORDER BY
id
LIMIT 500;

 

圖6:執行計劃情況

第二次,我們通過強制指定索引方式 force index (idx_test)方式,再次分析執行情況,得到圖7的結果,同樣的查詢條件同樣的結果,查詢時長由90s->0.49s左右。問題得到解決

圖7:強制指定索引後執行計劃情況

第三次,我們懷疑是where條件中有ID導致直接走的主鍵索引,where條件中去掉id,Sql調整如下,然後進行分析。依然沒有命中索引,掃描rows變成111342,查詢時間96s

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
ORDER BY
id
LIMIT 500

 

第四次,我們把order by去掉,SQL調整如下,然後進行分析。命中了idx_business_day_full_ps_code之前建立的聯合索引。掃描行數變成154900,查詢時長變為0.062s,但是發現結果與預想的不一致,發生了亂序

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
LIMIT 500;

 

第五次,經過前幾次的分析可以確定,order by 導致查詢分析器選擇了主鍵索引,我們在Order by中增加排序欄位,將Sql調整如下,同樣可以命中我們之前的聯合索引,查詢時長為0.034s,由於先按照主鍵排序,結果是一致的。相比第四種方法多了一份filesort,問題得解決。

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
ORDER BY
id,full_ps_code
LIMIT 500;

 

第六次,我們考慮是不是Limit導致的問題,我們將Limit 500 調整到 1000,Sql調整如下,奇跡發生了,命中了聯合索引,查詢時長為0.316s,結果一致,只不過多返回來500條數據。問題得到瞭解決。經過多次實驗Limit 大於695時就會命中聯合索引,查詢條件下的數據量是79963,696/79963大概占比是0.0087,猜測當獲取數據比超過0.0087時,會選擇聯合索引,未找到源代碼驗證此結論。

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
ORDER BY
id
LIMIT 1000;

 

經過我們的驗證,其中第2、5、6三種方法都可以解決性能問題。為了不影響線上,我們立即修改代碼,並選擇了force index 的方式,上線觀察一段時間後,資料庫CPU恢復正常,問題得到瞭解決。

3 事後分析

上線後問題得到瞭解決,同時也留給我了很多疑問。

  • 為什麼明明where條件中包含了聯合索引,卻未能命中,反而選擇了性能較慢的主鍵索引?
  • 為什麼在order by中增加了一個索引其他欄位,就可以命中聯合索引了呢?
  • 為什麼我僅僅是將limit限制條件由原來的500調大後,也能命中聯合索引呢?

這一切的答案都來自MySQL的查詢優化器。

3.1 查詢優化器

查詢優化器是專門負責優化查詢語句的優化器模塊,通過計算分析收集的各種系統統計信息,為查詢給出最優的執行計劃——最優的數據檢索方式。

優化器決定如何執行查詢的方式是基於一種稱為基於代價的優化的方法。5.7在代價類型上分為IO、CPU、Memory。記憶體的代價收集了,但是並沒有參與最終的代價計算。Mysql中引入了兩個系統表,mysql.server_cost和mysql.engine_cost,server_cost對應CPU的代價,engine_cost代表IO的代價。

server_cost(CPU代價)
  • row_evaluate_cost (default 0.2) 計算符合條件的行的代價,行數越多,此項代價越大
  • memory_temptable_create_cost (default 2.0) 記憶體臨時表的創建代價
  • memory_temptable_row_cost (default 0.2) 記憶體臨時表的行代價
  • key_compare_cost (default 0.1) 鍵比較的代價,例如排序
  • disk_temptable_create_cost (default 40.0) 內部myisam或innodb臨時表的創建代價
  • disk_temptable_row_cost (default 1.0) 內部myisam或innodb臨時表的行代價

由上可以看出創建臨時表的代價是很高的,尤其是內部的myisam或innodb臨時表。

engine_cost(IO代價)
  • io_block_read_cost (default 1.0) 從磁碟讀數據的代價,對innodb來說,表示從磁碟讀一個page的代價
  • memory_block_read_cost (default 1.0) 從記憶體讀數據的代價,對innodb來說,表示從buffer pool讀一個page的代價

這些信息都可以在資料庫中配置,當資料庫中未配置時,從MySql源代碼(5.7)中可以看到以上預設值情況

3.2 代價配置

--修改io_block_read_cost值為2
UPDATE mysql.engine_cost
SET cost_value = 2.0
WHERE cost_name = 'io_block_read_cost';
--FLUSH OPTIMIZER_COSTS 生效,只對新連接有效,老連接無效。
FLUSH OPTIMIZER_COSTS;

 

3.3 代價計算

代價是如何算出來的呢,通過讀MySql的源代碼,可以找到最終的答案

3.3.1 全表掃描(table_scan_cost)

以下代碼摘自MySql Server(5.7分支),全表掃描時,IO與CPU的代價計算方式。

double scan_time=
cost_model->row_evaluate_cost(static_cast<double>(records)) + 1;
// row_evaluate_cost 核心代碼
// rows * m_server_cost_constants->row_evaluate_cost()
// 數據行數 * 0.2 (row_evaluate_cost預設值) + 1 = CPU代價
Cost_estimate cost_est= head->file->table_scan_cost();
//table_scan_cost 核心代碼
//const double io_cost
// = scan_time() * table->cost_model()->page_read_cost(1.0)
// 這部分代價為IO部分
//page_read_cost 核心代碼
//
//const double in_mem= m_table->file->table_in_memory_estimate();
//
// table_in_memory_estimate 核心邏輯
//如果表的統計信息中提供了信息,使用統計信息,如果沒有則使用啟髮式估值計算
//pages=1.0
//
//const double pages_in_mem= pages * in_mem;
//const double pages_on_disk= pages - pages_in_mem;
//
//
//計算出兩部分IO的代價之和
//const double cost= buffer_block_read_cost(pages_in_mem) +
// io_block_read_cost(pages_on_disk);
//
//
//buffer_block_read_cost 核心代碼
// pages_in_mem比例 * 1.0 (memory_block_read_cost的預設值)
// blocks * m_se_cost_constants->memory_block_read_cost()
//
//
//io_block_read_cost 核心代碼
//pages_on_disk * 1.0 (io_block_read_cost的預設值)
//blocks * m_se_cost_constants->io_block_read_cost();
//返回IO與CPU代價
//這裡增加了個繫數調整,原因未知
cost_est.add_io(1.1);
cost_est.add_cpu(scan_time);

 

根據源代碼分析,當表中包含100行數據時,全表掃描的成本為23.1,計算邏輯如下

//CPU代價 = 總數據行數 * 0.2 (row_evaluate_cost預設值) + 1
cpu_cost = 100 * 0.2 + 1 等於 21
io_cost = 1.1 + 1.0 等於 2.1
//總成本 = cpu_cost + io_cost = 21 + 2.1 = 23.1

 

驗證結果如下圖

3.3.2 索引掃描(index_scan_cost)

以下代碼摘自MySql Server(5.7分支),當出現索引掃描時,是如何進行計算的,核心代碼如下

//核心代碼解析
*cost= index_scan_cost(keyno, static_cast<double>(n_ranges),
static_cast<double>(total_rows));
cost->add_cpu(cost_model->row_evaluate_cost(
static_cast<double>(total_rows)) + 0.01)

 

io代價計算核心代碼

//核心代碼
const double io_cost= index_only_read_time(index, rows) *
table->cost_model()->page_read_cost_index(index, 1.0);
// index_only_read_time(index, rows)
// 估算index占page個數
//page_read_cost_index(index, 1.0)
//根據buffer pool大小和索引大小來估算page in memory和in disk的比例,計算讀一個page的代價

 

cpu代價計算核心代碼

add_cpu(cost_model->row_evaluate_cost(
static_cast<double>(total_rows)) + 0.01);
//total_rows 等於索引過濾後的總行數
//row_evaluate_cost 與全表掃描的邏輯類似,
//區別在與一個是table_in_memory_estimate一個是index_in_memory_estimate

 

3.3.3 其他方式

計算代價的方式有很多,其他方式請參考 MySql原代碼。https://github.com/mysql/mysql-server.git

3.4 深度解析

通過查看optimizer_trace,可以瞭解查詢優化器是如何選擇的索引。

set optimizer_trace="enabled=on";
--如果不設置大小,可能導致json輸出不全
set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 0
ORDER BY
id
LIMIT 500;
select * FROM information_schema.optimizer_trace;
set optimizer_trace="enabled=off";

 

通過分析rows_estimation節點,可以看到通過全表掃描(table_scan)的話的代價是 8.29e6,同時也可以看到該查詢可以選擇到主鍵索引與聯合索引,如下圖。

上圖中全表掃描的代價是8.29e6,我們轉換成普通計數法為 8290000,如果使用主鍵索引成本是 3530000,聯合索引 185881,最小的應該是185881聯合索引,也可以看到第一步通過成本分析確實選擇了我們的聯合索引。

但是為什麼還是選擇了主鍵索引呢?

通過往下看,在reconsidering_access_paths_for_index_ordering節點下, 發現由於Order by 導致重新選擇了索引,在下圖中可以看到主鍵索引可用(usable=true),我們的聯合索引為not_applicable (不適用),意味著排序只能使用主鍵索引。

接下來通過index_order_summary可以看出,執行計劃最終被調整,由原來的聯合索引改成了主鍵索引,就是說這個選擇無視了之前的基於索引成本的選擇。

為什麼會有這樣的一個選項呢,主要原因如下:
The short explanation is that the optimizer thinks — or should I say hopes — that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. So by trying to avoid a sort, the optimizer ends-up losing time scanning the table.

從這段解釋可以看出主要原因是由於我們使用了order by id asc這種基於 id 的排序寫法,優化器認為排序是個昂貴的操作,所以為了避免排序,並且它認為 limit n 的 n 如果很小的話即使使用全表掃描也能很快執行完,所以它選擇了全表掃描,也就避免了 id 的排序。

5 總結

查詢優化器會基於代價來選擇最優的執行計劃,但由於order by id limit n的存在,MySql可能會重新選擇一個錯誤的索引,忽略原有的基於代價選擇出來的索引,轉而選擇全表掃描的主鍵索引。這個問題在國內外有大量的用戶反饋,BUG地址 https://bugs.mysql.com/bug.php?id=97001 。官方稱在5.7.33以後版本可以關閉prefer_ordering_index 來解決。如下圖所示。

另外在我們日常慢Sql調優時,可以通過以下兩種方式,瞭解更多查詢優化器選擇過程。

--第一種
explain format=json
sql語句
-------------------------------------------------------------------------
--第二種 optimizer_trace方式
set optimizer_trace="enabled=on";
--如果不設置大小,可能導致json輸出不全
set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
SQL語句
select * FROM information_schema.optimizer_trace;
set optimizer_trace="enabled=off";

 

當你也出現了本篇文章碰到的問題時,可以採用以下的方法來解決

  1. 使用force index,強制指定索引。
  2. order by中增加一個聯合索引的key。
  3. 擴大limit 返回的範圍(不推薦,隨著數據量的增大,可能還會走回主鍵索引)
  4. order by (id+0) asc 欺騙查詢優化器,讓其選擇聯合索引。
  5. MySQL 5.7.33版本以上,可以關閉prefer_ordering_index解決。

作者:陳強


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

-Advertisement-
Play Games
更多相關文章
  • alpine系統 alpine系統是什麼 Alpine Linux 是一個基於 musl libc 和 busybox 的面向安全的輕量級 Linux 發行版。 alpine系統的特點 小巧:基於Musl libc和busybox,和busybox一樣小巧,最小的Docker鏡像只有5MB; 安全: ...
  • linux 內核記憶體屏障 By: David Howells [email protected] Paul E. McKenney [email protected] Will Deacon [email protected] Peter Zijlstra peterz@infrad ...
  • Mac電腦系統如何維護?System Toolkit是一款非常不錯的Mac系統維護工具,使用System Toolkit Mac版,你可以隨時檢索最新的技術數據,例如進程負載,CPU溫度,主記憶體消耗,存儲空間,磁碟活動,網路介面上的通信等,你還可以用它刪除無需的應用程式和垃圾文件,保存電腦的良好運行 ...
  • 協程不是操作系統的底層特性,系統感知不到它的存在。它運行線上程裡面,通過分時復用線程的方式運行,不會增加線程的數量。協程也有上下文切換,但是不會切換到內核態去,比線程切換的開銷要小很多。每個協程的體積比線程要小得多,一個線程可以容納數量相當可觀的協程。在IO密集型的任務中有著大量的阻塞等待過程,協程... ...
  • Remote Desktop Manager for Mac是目前網路上最優秀的一款遠程桌面管理工具,remote desktop manager mac界面簡潔、操作簡單、功能強大,可以非常方便地管理你的web伺服器、ftp伺服器等,並支持保存多個遠程桌面連接、多個ftp連接,甚至是vpn連接,為 ...
  • DKOM 即直接內核對象操作,我們所有的操作都會被系統記錄在記憶體中,而驅動進程隱藏就是操作進程的EPROCESS結構與線程的ETHREAD結構、鏈表,要實現進程的隱藏我們只需要將某個進程中的信息,在系統EPROCESS鏈表中摘除即可實現進程隱藏。 ...
  • 今天我會進行StoneDB資料庫在Ubuntu 22.04系統下的安裝。 嚴格按照官方文檔的步驟執行,看看能否順利安裝。 準備Ubuntu系統 我已在虛擬機中安裝好了Ubuntu 22.04版本的系統,按照常規操作,先把系統更新到最新。然後執行後面的安裝步驟。 在Ubuntu系統中安裝StoneDB ...
  • 一、直播介紹 之前的內容,我們為大家分享了ChengYing入門介紹,以及ChengYing部署Hadoop集群實戰,本期我們為大家分享ChengYing安裝原理。 本次直播我們將詳細介紹ChengYing安裝原理及卸載原理,以及其中會遇到的常見問題剖析,通過本次分享,希望大家能對ChengYing ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 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 ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...