mysql中CPU或記憶體利用率過高問題

參考：https://www.phpmianshi.com/?id=173

CPU 利用率過高

原因

在 MySQL 使用過程中，出現 CPU 利用率過高甚至超過100%時，與資料庫存在低效 SQL 或大量行鎖衝突有非常大的關係，一般都是由於大量低效的 SQL 導致，出現行鎖衝突的概率非常低。

風險

若 MySQL CPU 的利用率長時間處於100%，會嚴重影響資料庫的整體效能，極端情況下可能會出現例項 HANG 住的情況。
為避免業務因 CPU 資源不足而受影響，建議提前對 CPU 利用率過高的例項進行業務優化或者升級 CPU 資源。

解決方案

MySQL CPU 利用率過高，大部分原因與低效 SQL 有關係，通過優化低效 SQL 基本可以解決大部分問題。
MySQL 慢查詢時間（long_query_time）的預設值是10s，在遇到效能問題時，若發現沒有慢查詢，建議將其引數調成1s ，再觀察業務週期內的慢查詢，進而對其慢查詢進行優化。若引數調整後，在其業務週期內依然未發現慢查詢，而 CPU 利用率依然偏高，建議升級 CPU 的配置，進而提高資料庫的整體效能。

記憶體利用率過高

原因

MySQL 的記憶體是重要的效能引數，常出現由於低效 SQL 請求以及待優化的資料庫導致記憶體利用率過高甚至超過100%的情況。

風險

由於低效 SQL 請求以及待優化的資料庫導致記憶體利用率升高的問題時，嚴重時還會觸發記憶體 OOM 進而發生主備切換。
為避免業務因記憶體利用率過高而受影響，建議您提前對記憶體利用率過高的例項進行業務優化或者升級記憶體空間。

解決方案

MySQL 的記憶體大體可以分為 global 級的共享記憶體和 session 級的私有記憶體兩部分：

• 共享記憶體是例項建立時即分配的記憶體空間，並且是所有連線共享的。

• 私有記憶體用於每個連線到 MySQL 伺服器時才分配各自的快取。
  一些特殊的 SQL 或欄位型別會導致單個執行緒可能分配多次快取，因此當出現 OOM 異常，都是由各個連線的私有記憶體造成的，通過限制資料庫的連線數和優化低效 SQL，可降低記憶體利用率過高的風險。

 

 

共享記憶體

執行以下命令，查詢示例的共享記憶體分配情況：

show variables where variable_name in ('innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query_cache_size');

innodb_additional_mem_pool_size | 8388608   
innodb_buffer_pool_size         | 524288000 
innodb_log_buffer_size          | 67108864  
key_buffer_size                 | 16777216  
query_cache_size                | 0        

注意：5.7版本不支援 innodb_additional_mem_pool_size。

 

引數說明：

• innodb_buffer_pool_size
  該部分快取是 Innodb 引擎最重要的快取區域，是通過記憶體來彌補物理資料檔案的重要手段，在雲資料庫 MySQL 上會採用例項規格配置的50% - 80%作為該部分大小（上圖為1000MB * 0.5 = 500MB）。其中主要包含資料頁、索引頁、undo 頁、insert buffer、自適應雜湊索引、鎖資訊以及資料字典等資訊。在進行 SQL 讀和寫的操作時，首先並不是對物理資料檔案操作，而是先對 buffer_pool 進行操作，再通過 checkpoint 等機制寫回資料檔案。該空間的優點是可以提升資料庫的效能、加快 SQL 執行速度，缺點是故障恢復速度較慢。

• innodb_log_buffer_size
  該部分主要存放 redo log 的資訊，在雲資料庫 MySQL 上會設定64MB的大小。InnoDB 會首先將 redo log 寫在這裡，然後按照一定頻率將其重新整理回重做日誌檔案中。該空間不需要太大，因為一般情況下該部分快取會以較快頻率重新整理至 redo log（Master Thread 會每秒重新整理、事務提交時會重新整理、其空間少於1/2時同樣會重新整理）。

• innodb_additional_mem_pool_size
  該部分主要存放 InnoDB 內的一些資料結構，在雲資料庫 MySQL 中統一設定為8MB。通常是在 buffer_pool 中申請記憶體的時候還需要在額外記憶體中申請空間儲存該物件的結構資訊。該大小主要與表數量有關，表數量越大需要更大的空間。

• key_buffer_size
  該部分是 MyISAM 表的重要快取區域，所有例項統一為16M。該部分主要存放 MyISAM 表的鍵。MyISAM 表不同於 InnoDB 表，其快取的索引快取是放在 key_buffer 中的，而資料快取則儲存於作業系統的記憶體中。雲資料庫 MySQL 的系統是 MyISAM 引擎的，因此需給予該部分一定量的空間的。

• query_cache_size
  該部分是對查詢結果做快取，以減少解析 SQL 和執行 SQL 的開銷，在雲資料庫 MySQL 上關閉了該部分的快取。主要適合於讀多寫少的應用場景，因為它是按照 SQL 語句的 hash 值進行快取的，當表資料發生變化後即失效。

 

私有記憶體

執行以下命令，查詢示例的 session 私有記憶體分配情況：

show variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size');

binlog_cache_size    | 32768    
join_buffer_size     | 262144    
read_buffer_size     | 262144    
read_rnd_buffer_size | 524288    
sort_buffer_size     | 524288  
tmp_table_size       | 209715200

引數說明：

• read_buffer_size
  分別存放了對順序掃描的快取，當 thread 進行順序掃描資料時會首先掃描該 buffer 空間以避免更多的物理讀。

• read_rnd_buffer_size
  分別存放了對隨機掃描的快取，當 thread 進行隨機掃描資料時會首先掃描該 buffer 空間以避免更多的物理讀。

• sort_buffer_size
  需要執行 order by 和 group by 的 SQL 都會分配 sort_buffer，用於儲存排序的中間結果。在排序過程中，若儲存量大於 sort_buffer_size，則會在磁碟生成臨時表以完成操作。

• join_buffer_size
  MySQL 僅支援 nest loop 的 join 演算法，處理邏輯是驅動表的一行和非驅動表聯合查詢，這時就可以將非驅動表放入 join_buffer，不需要訪問擁有併發保護機制的 buffer_pool。

• binlog_cache_size
  該區域用來快取該 thread 的 binlog 日誌，在一個事務還沒有 commit 之前會先將其日誌儲存於 binlog_cache 中，等到事務 commit 後會將其 binlog 刷回磁碟上的 binlog 檔案以持久化。

• tmp_table_size
  不同於上面各個 session 級的 buffer，這個引數可以在控制檯上修改。該引數是指使用者記憶體臨時表的大小，如果該 thread 建立的臨時表超過它設定的大小會把臨時錶轉換為磁碟上的一張 MyISAM 臨時表。