資料庫層面問題解決思路

maxingyu發表於2021-05-15
資料庫

資料庫層面問題解決思路

一般應急調優的思路：

針對突然的業務辦理卡頓，無法進行正常的業務處理！需要立馬解決的場景！

  • 1、show processlist

  • 2、**explain select id ,name from stu where name=<u>'clsn'</u>**; # ALL id name age sex

select id,name from stu where id=2-1 函式 結果集>30;show index from table;

  • 3、通過執行計劃判斷，索引問題(有沒有、合不合理)或者語句本身問題

  • 4、show status like ‘%lock%’; # 查詢鎖狀態 kill SESSION_ID; # 殺掉有問題的 session

常規調優思路：

針對業務週期性的卡頓，例如在每天 10-11 點業務特別慢，但是還能夠使用，過了這段時間就好了。

  • 1、檢視 slowlog，分析 slowlog，分析出查詢慢的語句。

  • 2、按照一定優先順序，進行一個一個的排查所有慢語句。

  • 3、分析 top sql，進行 explain 除錯，檢視語句執行時間。

  • 4、調整索引或語句本身。

系統層面

  • cpu 方面：

vmstat、sar top、htop、nmon、mpstat

  • 記憶體：

free 、ps -aux 、

  • IO 裝置(磁碟、網路)：

iostat 、 ss 、 netstat 、 iptraf、iftop、lsof、

vmstat 命令說明：

Procs：r 顯示有多少程式正在等待 CPU 時間。b 顯示處於不可中斷的休眠的程式數量。在等待 I/O

Memory：swpd 顯示被交換到磁碟的資料塊的數量。未被使用的資料塊，使用者緩衝資料塊，用於作業系統的資料塊的數量

Swap：作業系統每秒從磁碟上交換到記憶體和從記憶體交換到磁碟的資料塊的數量。s1 和s0 最好是 0

Io：每秒從裝置中讀入 b1 的寫入到裝置 b0 的資料塊的數量。反映了磁碟 I/O

System：顯示了每秒發生中斷的數量(in)和上下文交換(cs)的數量

Cpu：顯示用於執行使用者程式碼，系統程式碼，空閒，等待 I/O 的 CPU 時間

iostat 命令說明

例項命令： iostat -dk 1 5 iostat -d -k -x 5 (檢視裝置使用率(%util)和響應時間(await))

tps：該裝置每秒的傳輸次數。“一次傳輸”意思是“一次 I/O 請求”。多個邏輯請求可能會被合併為“一次 I/O 請求”。

iops ：硬體出廠的時候，廠家定義的一個每秒最大的 IO 次數,”一次傳輸”請求的大小是未知的。

kB_read/s：每秒從裝置(drive expressed)讀取的資料量；

KB_wrtn/s：每秒向裝置(drive expressed)寫入的資料量；

kB_read：讀取的總資料量；

kB_wrtn：寫入的總數量資料量；這些單位都為 Kilobytes。

系統層面問題解決辦法

你認為到底負載高好，還是低好呢？

在實際的生產中，一般認為 cpu 只要不超過 90% 都沒什麼問題 。

當然不排除下面這些特殊情況：

問題一：cpu 負載高，IO 負載低

記憶體不夠

磁碟效能差

SQL 問題 ——>去資料庫層，進一步排查 sql 問題

IO 出問題了(磁碟到臨界了、raid 設計不好、raid 降級、鎖、在單位時間內 tps 過高)

tps 過高: 大量的小資料 IO、大量的全表掃描

問題二：IO 負載高，cpu 負載低

大量小的 IO 寫操作：

autocommit ，產生大量小 IO

IO/PS 磁碟的一個定值，硬體出廠的時候，廠家定義的一個每秒最大的 IO 次數。

大量大的 IO 寫操作

SQL 問題的機率比較大

問題三：IO 和 cpu 負載都很高

硬體不夠了或 SQL 存在問題

基礎優化

優化思路

定位問題點:硬體 –> 系統 –> 應用 –> 資料庫 –> 架構(高可用、讀寫分離、分庫分表)

  • 處理方向：明確優化目標、效能和安全的折中、防患未然

1. 硬體優化

  • 主機方面：

根據資料庫型別，主機 CPU 選擇、記憶體容量選擇、磁碟選擇

平衡記憶體和磁碟資源

隨機的 I/O 和順序的 I/O

主機 RAID 卡的 BBU(Battery Backup Unit)關閉

  • cpu 的選擇：

cpu 的兩個關鍵因素：核數、主頻

  • 根據不同的業務型別進行選擇：

  • cpu 密集型：計算比較多，OLTP 主頻很高的 cpu、核數還要多

  • IO 密集型：查詢比較，OLAP 核數要多，主頻不一定高的

  • 記憶體的選擇：

OLAP 型別資料庫，需要更多記憶體，和資料獲取量級有關。

OLTP 型別資料一般記憶體是 cpu 核心數量的 2 倍到 4 倍，沒有最佳實踐。

  • 儲存方面：

根據儲存資料種類的不同，選擇不同的儲存裝置

配置合理的 RAID 級別(raid 5、raid 10、熱備盤)

對與作業系統來講，不需要太特殊的選擇，最好做好冗餘(raid1)(ssd、sas 、sata)

  • raid 卡：主機 raid 卡選擇：

實現作業系統磁碟的冗餘(raid1)

平衡記憶體和磁碟資源

隨機的 I/O 和順序的 I/O

主機 RAID 卡的 BBU(Battery Backup Unit)要關閉

  • 網路裝置方面：

使用流量支援更高的網路裝置(交換機、路由器、網線、網路卡、HBA 卡)

注意：以上這些規劃應該在初始設計系統時就應該考慮好。

1. 伺服器硬體優化

1、物理狀態燈：

2、自帶管理裝置：遠端控制卡(FENCE 裝置：ipmi ilo idarc)，開關機、硬體監控。

3、第三方的監控軟體、裝置(snmp、agent)對物理設施進行監控

4、儲存裝置：自帶的監控平臺。EMC2(hp 收購了)， 日立(hds)，IBM 低端 OEM hds，高階儲存是自己技術，華為儲存

1. 系統優化

  • Cpu：基本不需要調整，在硬體選擇方面下功夫即可。

  • 記憶體：基本不需要調整，在硬體選擇方面下功夫即可。

  • SWAP：MySQL 儘量避免使用 swap。阿里雲的伺服器中預設 swap 為 0

  • IO ：raid、no lvm、 ext4 或 xfs、ssd、IO 排程策略

  • Swap 調整(不使用 swap 分割槽)

  • 這個引數決定了 Linux 是傾向於使用 swap，還是傾向於釋放檔案系統 cache。在記憶體緊張的情況下，數值越低越傾向於釋放檔案系統 cache。當然，這個引數只能減少使用 swap 的概率，並不能避免 Linux 使用 swap。

  • 修改 MySQL 的配置引數 innodb_flush_method，開啟 O_DIRECT 模式。這種情況下，InnoDB 的 buffer pool 會直接繞過檔案系統 cache 來訪問磁碟，但是 redo log依舊會使用檔案系統 cache。值得注意的是，Redo log 是覆寫模式的，即使使用了檔案系統的 cache，也不會佔用太多

  • IO 排程策略：

    1. 系統引數調整

Linux 系統核心引數優化：

使用者限制引數(MySQL 可以不設定以下配置)：

  • 應用優化

業務應用和資料庫應用獨立,防火牆：iptables、selinux 等其他無用服務(關閉)：

安裝圖形介面的伺服器不要啟動圖形介面 runlevel 3,另外，思考將來我們的業務是否真的需要 MySQL，還是使用其他種類的資料庫。用資料庫的最高境界就是不用資料庫。

資料庫優化

SQL 優化方向：

執行計劃、索引、SQL 改寫

架構優化方向：

高可用架構、高效能架構、分庫分表

1. 資料庫引數優化

調整：

例項整體(高階優化，擴充套件)

連線層(基礎優化)

設定合理的連線客戶和連線方式

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章