mysql數據庫越訪問越卡,sql數據庫越來越卡

本文目錄一覽：

• 1、MySQL數據庫服務器逐漸變慢 該怎麼分析與解決
• 2、mysql數據庫突然變慢 數據庫變慢是什麼原因
• 3、MySQL數據庫服務器逐漸變慢分析與解決方法分享

MySQL數據庫服務器逐漸變慢 該怎麼分析與解決

我們先來看第一個階段，MySQL慢的診斷思路，一般我們會從三個方向來做：

第一個方向是MySQL內部的觀測

第二個方向是外部資源的觀測

第三個方向是外部需求的改造

1.1 MySQL 內部觀測

我們來看MySQL內部的觀測，常用的觀測手段是這樣的，從上往下看，第一部分是Processlist，看一下哪個SQL壓力不太正常，第二步是explain，解釋一下它的執行計劃，第三步我們要做Profilling，如果這個SQL能再執行一次的話, 就做一個Profilling，然後高級的DBA會直接動用performance_schema ，MySQL 5.7 以後直接動用sys_schema，sys_schema是一個視圖，裏面有便捷的各類信息，幫助大家來診斷性能。再高級一點，我們會動用innodb_metrics進行一個對引擎的診斷。

除了這些手段以外，大家還提出了一些亂七八糟的手段，我就不列在這了，這些是常規的一個MySQL的內部的狀態觀測的思路。除了這些以外，MySQL還陸陸續續提供了一些暴露自己狀態的方案，但是這些方案並沒有在實踐中形成套路，原因是學習成本比較高。

1.2 外部資源觀測

外部資源觀測這部分，我引用了一篇文章，這篇文章的二維碼我貼在上面了。這篇文章是國外的一個神寫的，標題是：60秒的快速巡檢，我們來看一下它在60秒之內對服務器到底做了一個什麼樣的巡檢。一共十條命令，這是前五條，我們一條一條來看。

1.uptime，uptime告訴我們這個機器活了多久，以及它的平均的負載是多少。

2.dmesg -T | tail，告訴我們系統日誌裡邊有沒有什麼報錯。

3.vmstat 1，告訴我們虛擬內存的狀態，頁的換進換出有沒有問題，swap有沒有使用。

4. mpstat -P ALL，告訴我們CPU壓力在各個核上是不是均勻的。

5.pidstat 1，告訴我們各個進程的對資源的佔用大概是什麼樣子。

我們來看一下後五條：

首先是iostat-xz 1，查看IO的問題，然後是free-m內存使用率，之後兩個sar，按設備網卡設備的維度，看一下網絡的消耗狀態，以及總體看TCP的使用率和錯誤率是多少。最後一條命令top，看一下大概的進程和線程的問題。

這個就是對於外部資源的診斷，這十條命令揭示了應該去診斷哪些外部資源。

1.3 外部需求改造

第三個診斷思路是外部的需求改造，我在這裡引用了一篇文檔，這篇文檔是MySQL的官方文檔中的一章，這一章叫Examples of Common Queries，文檔中介紹了常規的SQL怎麼寫, 給出了一些例子。文章的鏈接二維碼在slide上。

我們來看一下它其中提到的一個例子。

它做的事情是從一個表裡邊去選取，這張表有三列，article、dealer、price，選取每個作者的最貴的商品列在結果集中，這是它的最原始的SQL，非常符合業務的寫法，但是它是個關聯子查詢。

關聯子查詢成本是很貴的，所以上面的文檔會教你快速地把它轉成一個非關聯子查詢，大家可以看到中間的子查詢和外邊的查詢之間是沒有關聯性的。

第三步，會教大家直接把子查詢拿掉，然後轉成這樣一個SQL，這個就叫業務改造，前後三個SQL的成本都不一樣，把關聯子查詢拆掉的成本，拆掉以後SQL會跑得非常好，但這個SQL已經不能良好表義了，只有在診斷到SQL成本比較高的情況下才建議大家使用這種方式。

為什麼它能夠把一個關聯子查詢拆掉呢？

這背後的原理是關係代數，所有的SQL都可以被表達成等價的關係代數式，關係代數式之間有等價關係，這個等價關係通過變換可以把關聯子查詢拆掉。

上面的這篇文檔是一個大學的教材，它從頭教了關於代數和SQL之間的關係。然後一步步推導怎麼去簡化這句SQL。

第一，MySQL本身提供了很多命令來觀察MySQL自身的各類狀態，大家從上往下檢一般能檢到SQL的問題或者服務器的問題。

第二，從服務器的角度，我們從巡檢的腳本角度入手，服務器的資源就這幾種，觀測手法也就那麼幾種，我們把服務器的資源全部都觀察一圈就可以了。

第三，如果實在搞不定，需求方一定要按照數據庫容易接受的方式去寫SQL，這個成本會下降的非常快，這個是常規的MySQL慢的診斷思路。

mysql數據庫突然變慢 數據庫變慢是什麼原因

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

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

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

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

MySQL數據庫服務器逐漸變慢分析與解決方法分享

一、檢查系統的狀態

通過操作系統的一些工具檢查系統的狀態，比如CPU、內存、交換、磁盤的利用率，根據經驗或與系統正常時的狀態相比對，有時系統表面上看起來看空閑，這也可能不是一個正常的狀態，因為cpu可能正等待IO的完成。除此之外，還應觀注那些佔用系統資源(cpu、內存)的進程。

1.使用sar來檢查操作系統是否存在IO問題

#sar-u210—

即每隔2秒檢察一次，共執行20次。

結果示例：

註：在redhat下，%system就是所謂的%wio。

Linux2.4.21-20.ELsmp

(YY075)05/19/2005

10:36:07AMCPU%user%nice%system%idle

10:36:09AMall0.000.000.1399.87

10:36:11AMall0.000.000.00100.00

10:36:13AMall0.250.000.2599.49

10:36:15AMall0.130.000.1399.75

10:36:17AMall0.000.000.00100.00

其中：

%usr指的是用戶進程使用的cpu資源的百分比;

%sys指的是系統資源使用cpu資源的百分比;

%wio指的是等待io完成的百分比，這是值得觀注的一項;

%idle即空閑的百分比。

如果wio列的值很大，如在35%以上，說明系統的IO存在瓶頸，CPU花費了很大的時間去等待I/O的完成。Idle很小說明系統CPU很忙。像以上的示例，可以看到wio平均值為11，說明I/O沒什麼特別的問題，而idle值為零，說明cpu已經滿負荷運行了。

2.使用vmstat監控內存

cpu資源

[root@mysql1

~]#

vmstat

procs

———–memory———-—swap–

—–io—-–system–

—–cpu——

r

b

swpd

free

buff

cache

si

so

bi

bo

in

cs

us

sy

id

wa

st

72

25428

54712672264

14

43

53

59

1

198

vmstat

的輸出那些信息值得關注?

io

bo:

磁盤寫的數據量稍大，如果是大文件的寫，10M以內基本不用擔心，如果是小文件寫2M以內基本正常

①

CPU問題

下面幾列需要被察看，以確定cpu是否有問題

Processesinthe

run

queue

(procs

r)

Usertime

(cpu

us)

System

time

(cpu

sy)

Idle

time

(cpu

id)

問題情況:

如果processes

in

run

queue

(procs

r)的數量遠大於系統中cpu的數量，將會使系統便慢。

如果這個數量是cpu的4倍的話，說明系統正面臨cpu能力短缺,這將使系統運行速度大幅度降低

如果cpu的idle時間經常為0的話，或者系統佔用時間(cpu

sy)是用戶佔用時間(cpu

us)兩輩的話，系統面臨缺少cpu資源

解決方案

:

解決這些情況，涉及到調整應用程序，使其能更有效的使用cpu，同時增加cpu的能力或數量

②內存問題

主要查看頁導入的數值(swap中的si)，如果該值比較大就要考慮內存，大概方法如下：

最簡單的，加大RAM

減少RAM的需求

3.磁盤IO問題

處理方式：做raid10提高性能

4.網絡問題

telnet一下MySQL對外開放的端口，如果不通的話，看看防火牆是否正確設置了。另外，看看MySQL是不是開啟了skip-networking的選項，如果開啟請關閉。