有哪些方法可以改善 MySQL 的 IO 瓶頸問題

通過sysbench的oltp_read_write測試來模擬業(yè)務(wù)壓力、以此來給指定的硬件環(huán)境配置一份比較合理的MySQL配置文件。

專注于為中小企業(yè)提供成都網(wǎng)站制作、成都網(wǎng)站設(shè)計服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)衡陽縣免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了上千企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。

環(huán)境介紹

硬件配置

請點擊輸入圖片描述

軟件環(huán)境

請點擊輸入圖片描述

優(yōu)化層級與指導(dǎo)思想

優(yōu)化層級

MySQL數(shù)據(jù)庫優(yōu)化可以在多個不同的層級進行，常見的有：

SQL優(yōu)化

參數(shù)優(yōu)化

架構(gòu)優(yōu)化

本文重點關(guān)注：參數(shù)優(yōu)化

指導(dǎo)思想

日志先行 -- 一個事務(wù)能否成功提交的關(guān)鍵是日志是否成功落盤，與數(shù)據(jù)沒有太大的關(guān)系；也就是說對寫的優(yōu)化可以表述為各方面的資源向?qū)懖僮鲀A斜。

瓶頸分析 -- 通過show global status 的各個計數(shù)器的值基本上就能分析出當(dāng)前瓶頸所在，再結(jié)合一些簡單的系統(tǒng)層面的監(jiān)控工具如top iostat 就能明確瓶頸。

整體性能是“讀”“寫”之間的再平衡。

如何查看 mysql 性能瓶頸

通過sysbench的oltp_read_write測試來模擬業(yè)務(wù)壓力、以此來給指定的硬件環(huán)境配置一份比較合理的MySQL配置文件。

環(huán)境介紹

硬件配置

請點擊輸入圖片描述

軟件環(huán)境

請點擊輸入圖片描述

優(yōu)化層級與指導(dǎo)思想

優(yōu)化層級

MySQL數(shù)據(jù)庫優(yōu)化可以在多個不同的層級進行，常見的有：

SQL優(yōu)化

參數(shù)優(yōu)化

架構(gòu)優(yōu)化

本文重點關(guān)注：參數(shù)優(yōu)化

指導(dǎo)思想

日志先行 -- 一個事務(wù)能否成功提交的關(guān)鍵是日志是否成功落盤，與數(shù)據(jù)沒有太大的關(guān)系；也就是說對寫的優(yōu)化可以表述為各方面的資源向?qū)懖僮鲀A斜。

瓶頸分析 -- 通過show global status 的各個計數(shù)器的值基本上就能分析出當(dāng)前瓶頸所在，再結(jié)合一些簡單的系統(tǒng)層面的監(jiān)控工具如top iostat 就能明確瓶頸。

整體性能是“讀”“寫”之間的再平衡。

MySQL查詢效率很慢的問題如何分析和解決

MySQL 在崩潰恢復(fù)時，會遍歷打開所有 ibd 文件的 header page 驗證數(shù)據(jù)字典的準(zhǔn)確性，如果 MySQL 中包含了大量表，這個校驗過程就會比較耗時。 MySQL 下崩潰恢復(fù)確實和表數(shù)量有關(guān)，表總數(shù)越大，崩潰恢復(fù)時間越長。另外磁盤 IOPS 也會影響崩潰恢復(fù)時間，像這里開發(fā)庫的 HDD IOPS 較低，因此面對大量的表空間，校驗速度就非常緩慢。另外一個發(fā)現(xiàn)，MySQL 8 下正常啟用時居然也會進行表空間校驗，而故障恢復(fù)時則會額外再進行一次表空間校驗，等于校驗了 2 遍。不過 MySQL 8.0 里多了一個特性，即表數(shù)量超過 5W 時，會啟用多線程掃描，加快表空間校驗過程。

如何跳過校驗MySQL 5.7 下有方法可以跳過崩潰恢復(fù)時的表空間校驗過程嘛？查閱了資料，方法主要有兩種：

1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ，那么 validate = false，即可以跳過表空間校驗。實際測試的時候設(shè)置 innodb_force_recovery =1，也就是強制恢復(fù)跳過壞頁，就可以跳過校驗，然后重啟就是正常啟動了。通過這種臨時方式可以避免崩潰恢復(fù)后非常耗時的表空間校驗過程，快速啟動 MySQL，個人目前暫時未發(fā)現(xiàn)有什么隱患。2. 使用共享表空間替代獨立表空間這樣就不需要打開 N 個 ibd 文件了，只需要打開一個 ibdata 文件即可，大大節(jié)省了校驗時間。自從聽了姜老師講過使用共享表空間替代獨立表空間解決 drop 大表時性能抖動的原理后，感覺共享表空間在很多業(yè)務(wù)環(huán)境下，反而更有優(yōu)勢。

臨時冒出另外一種解決想法，即用 GDB 調(diào)試崩潰恢復(fù)，通過臨時修改 validate 變量值讓 MySQL 跳過表空間驗證過程，然后讓 MySQL 正常關(guān)閉，重新啟動就可以正常啟動了。但是實際測試發(fā)現(xiàn)，如果以 debug 模式運行，確實可以臨時修改 validate 變量，跳過表空間驗證過程，但是 debug 模式下代碼運行效率大打折扣，反而耗時更長。而以非 debug 模式運行，則無法修改 validate 變量，想法破滅。

如果mysql里面的數(shù)據(jù)過多，查詢太慢怎么辦？

問題

我們有一個 SQL，用于找到?jīng)]有主鍵 / 唯一鍵的表，但是在 MySQL 5.7 上運行特別慢，怎么辦？

實驗

我們搭建一個 MySQL 5.7 的環(huán)境，此處省略搭建步驟。

寫個簡單的腳本，制造一批帶主鍵和不帶主鍵的表：

執(zhí)行一下腳本：

現(xiàn)在執(zhí)行以下 SQL 看看效果：

...

執(zhí)行了 16.80s，感覺是非常慢了。

現(xiàn)在用一下 DBA 三板斧，看看執(zhí)行計劃：

感覺有點慘，由于 information_schema.columns 是元數(shù)據(jù)表，沒有必要的統(tǒng)計信息。

那我們來 show warnings 看看 MySQL 改寫后的 SQL：

我們格式化一下 SQL：

可以看到 MySQL 將

select from A where A.x not in (select x from B) //非關(guān)聯(lián)子查詢

轉(zhuǎn)換成了

select from A where not exists (select 1 from B where B.x = a.x) //關(guān)聯(lián)子查詢

如果我們自己是 MySQL，在執(zhí)行非關(guān)聯(lián)子查詢時，可以使用很簡單的策略：

select from A where A.x not in (select x from B where ...) //非關(guān)聯(lián)子查詢:1. 掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時表 C 中，建好索引2. 掃描 A 表中的記錄，與臨時表 C 中的記錄進行比對，直接在索引里比對，

而關(guān)聯(lián)子查詢就需要循環(huán)迭代：

select from A where not exists (select 1 from B where B.x = a.x and ...) //關(guān)聯(lián)子查詢掃描 A 表的每一條記錄 rA： ? ? 掃描 B 表，找到其中的第一條滿足 rA 條件的記錄。

顯然，關(guān)聯(lián)子查詢的掃描成本會高于非關(guān)聯(lián)子查詢。

我們希望 MySQL 能先"緩存"子查詢的結(jié)果（緩存這一步叫物化，MATERIALIZATION），但MySQL 認(rèn)為不緩存更快，我們就需要給予 MySQL 一定指導(dǎo)。

...

可以看到執(zhí)行時間變成了 0.67s。

整理

我們診斷的關(guān)鍵點如下：

\1. 對于 information_schema 中的元數(shù)據(jù)表，執(zhí)行計劃不能提供有效信息。

\2. 通過查看 MySQL 改寫后的 SQL，我們猜測了優(yōu)化器發(fā)生了誤判。

\3. 我們增加了 hint，指導(dǎo) MySQL 正確進行優(yōu)化判斷。

但目前我們的實驗僅限于猜測，猜中了萬事大吉，猜不中就無法做出好的診斷。

分享名稱：mysql查詢瓶頸怎么辦 mysql性能測試瓶頸及調(diào)優(yōu)
文章網(wǎng)址：http://jinyejixie.com/article22/dodojcc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站改版、自適應(yīng)網(wǎng)站、網(wǎng)站排名、網(wǎng)站策劃、網(wǎng)站制作、微信公眾號

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)