RDS MySQL引數調優最佳實踐

姬子玉發表於2017-12-05
原文網址 : https://juejin.im/post/5a24fb766fb9a045030feb5d
MySql

前言 很多時候，RDS使用者經常會問如何調優RDS MySQL的引數，為了回答這個問題，寫一篇blog來進行解釋： 哪一些引數不能修改，那一些引數可以修改； 這些提供修改的引數是不是已經是最佳設定，如何才能利用好這些引數； 哪些引數可以改 細心的使用者在購買RDS的時候都會看到，不同規格能夠提供的最大連線數以及記憶體是不同的，所以這一些產品規格的限制引數：連線數、記憶體使用者是不能夠修改的，如果記憶體或者連線數出現了瓶頸： 記憶體瓶頸：例項會出現OOM，然後導致主備發生切換 連線數瓶頸：應用不能新建立連線到資料庫 則需要進行應用優化、慢SQL優化或者進行彈性升級例項規格來解決。

前言

很多時候，RDS使用者經常會問如何調優RDS MySQL的引數，為了回答這個問題，寫一篇blog來進行解釋：

  1. 哪一些引數不能修改，那一些引數可以修改；
  2. 這些提供修改的引數是不是已經是最佳設定，如何才能利用好這些引數；

哪些引數可以改

細心的使用者在購買RDS的時候都會看到，不同規格能夠提供的最大連線數以及記憶體是不同的，所以這一些產品規格的限制引數：連線數、記憶體使用者是不能夠修改的，如果記憶體或者連線數出現了瓶頸：

  1. 記憶體瓶頸：例項會出現OOM，然後導致主備發生切換
  2. 連線數瓶頸：應用不能新建立連線到資料庫

則需要進行應用優化、慢SQL優化或者進行彈性升級例項規格來解決。

還有一些涉及主備資料安全的引數比如innodb_flush_log_at_trx_commit、sync_binlog、gtid_mode、semi_sync、binlog_format等為了保證主備的資料安全，目前還暫不提供給使用者進行修改。

除上述的這些引數外，絕大部分的引數都已經由DBA團隊和原始碼團隊優化過，使用者不需要過多調整線上的引數就可以把資料庫比較好的執行起來。但這些引數只是適合大多數的應用場景，個別特殊的場景還是需要個別對待，比如使用了tokudb引擎，這個時候就需要調整tokudb引擎能使用的記憶體比例(tokudb_buffer_pool_ratio)；又比如我的應用特點本身需要很大的一個鎖超時時間，那麼則需要調整innodb_lock_wait_timeout引數的大小以適應應用等等。

如何調引數

下面我將把控制檯中能夠修改的一些比較重要的引數給大家介紹一下，這些引數如果設定不當，則可能會出現效能問題或應用報錯。

open_files_limit

作用：該引數用於控制MySQL例項能夠同時開啟使用的檔案控制程式碼數目。
原因：當資料庫中的表（MyISAM 引擎表在被訪問的時候需要消耗檔案描述符，InnoDB引擎會自己管理已經開啟的表—table_open_cache）開啟越來越多後，會消耗分配給每個例項的檔案控制程式碼數目，RDS在起初初始化例項的時候設定的open_files_limit為8192，當開啟的表數目超過該引數則會導致所有的資料庫請求報錯誤。
現象：如果引數設定過小可導致應用報錯
[ERROR] /mysqld: Can't open file: './mysql/user.frm' (errno: 24 -Too many open files);
建議：提高open_files_limit的值，RDS目前可以支撐最大為65535，，同時建議替換MyISAM儲存引擎為InnoDB引擎。

back_log

作用：MySQL每處理一個連線請求的時候都會對應的建立一個新執行緒與之對應，那麼在主執行緒建立新執行緒期間，如果前端應用有大量的短連線請求到達資料庫，MySQL 會限制此刻新的連線進入請求佇列，由引數back_log控制，如果等待的連線數量超過back_log，則將不會接受新的連線請求，所以如果需要MySQL能夠處理大量的短連線，需要提高此引數的大小。
現象：如果引數過小可能會導致應用報錯
SQLSTATE[HY000] [2002] Connection timed out;
建議：提高此引數值的大小，注意需要重啟例項，RDS在起初初始化的值的預設值是50，現在初始化值已經調大了3000。

innodb_autoinc_lock_mode

作用：在MySQL5.1.22後，InnoDB為了解決自增主鍵鎖表的問題，引入了引數innodb_autoinc_lock_mode，用於控制自增主鍵的鎖機制，該引數可以設定的值為0/1/2，RDS 預設的引數值為1，表示InnoDB使用輕量級別的mutex鎖來獲取自增鎖，替代最原始的表級鎖，但是在load data（包括：INSERT … SELECT, REPLACE … SELECT）場景下會使用自增表鎖，這樣會則可能導致應用在併發匯入資料出現死鎖。
現象：如果應用併發使用load data(包括：INSERT … SELECT, REPLACE … SELECT)匯入資料的時候出現死鎖：
RECORD LOCKS space id xx page no xx n bits xx index PRIMARY of table xx.xx trx id xxx lock_mode X insert intention waiting. TABLE LOCK table xxx.xxx trx id xxxx lock mode AUTO-INC waiting；
建議：建議將引數設定改為2，則表示所有情況插入都使用輕量級別的mutex鎖(只針對row模式)，這樣就可以避免auto_inc的死鎖，同時在INSERT … SELECT 的場景下會提升很大的效能（注意該引數設定為2，binlog的格式需要設定為row）。

query_cache_size

作用：該引數用於控制MySQL query cache的記憶體大小；如果MySQL開啟query cache，再執行每一個query的時候會先鎖住query cache，然後判斷是否存在query cache中，如果存在直接返回結果，如果不存在，則再進行引擎查詢等操作；同時insert、update和delete這樣的操作都會將query cahce失效掉，這種失效還包括結構或者索引的任何變化，cache失效的維護代價較高，會給MySQL帶來較大的壓力，所以當我們的資料庫不是那麼頻繁的更新的時候，query cache是個好東西，但是如果反過來，寫入非常頻繁，並集中在某幾張表上的時候，那麼query cache lock的鎖機制會造成很頻繁的鎖衝突，對於這一張表的寫和讀會互相等待query cache lock解鎖，導致select的查詢效率下降。
現象：資料庫中有大量的連線狀態為checking query cache for query、Waiting for query cache lock、storing result in query cache；
建議：RDS預設是關閉query cache功能的，如果您的例項開啟了query cache，當出現上述情況後可以關閉query cache；當然有些情況也可以開啟query cache，比如：巧用query cache解決資料庫效能問題。

net_write_timeout

作用：等待將一個block傳送給客戶端的超時時間。
現象：引數設定過小可能導致客戶端報錯the last packet successfully received from the server was milliseconds ago，the last packet sent successfully to the server was milliseconds ago。
建議：該引數在RDS中預設設定為60S，一般在網路條件比較差的時，或者客戶端處理每個block耗時比較長時，由於net_write_timeout設定過小導致的連線中斷很容易發生，建議增加該引數的大小；

tmp_table_size

作用：該引數用於決定內部記憶體臨時表的最大值，每個執行緒都要分配（實際起限制作用的是tmp_table_size和max_heap_table_size的最小值），如果記憶體臨時表超出了限制，MySQL就會自動地把它轉化為基於磁碟的MyISAM表，優化查詢語句的時候，要避免使用臨時表，如果實在避免不了的話，要保證這些臨時表是存在記憶體中的。
現象：如果複雜的SQL語句中包含了group by/distinct等不能通過索引進行優化而使用了臨時表，則會導致SQL執行時間加長。
建議：如果應用中有很多group by/distinct等語句，同時資料庫有足夠的記憶體，可以增大tmp_table_size(max_heap_table_size)的值，以此來提升查詢效能。

RDS MySQL 新增引數

下面介紹幾個比較有用的 RDS MySQL 新增引數。

rds_max_tmp_disk_space

作用：用於控制MySQL能夠使用的臨時檔案的大小，RDS初始預設值是10G，如果臨時檔案超出此大小，則會導致應用報錯。
現象：The table ‘/home/mysql/dataxxx/tmp/#sql_2db3_1’ is full。
建議：需要先分析一下導致臨時檔案增加的SQL語句是否能夠通過索引或者其他方式進行優化，其次如果確定例項的空間足夠，則可以提升此引數的值，以保證SQL能夠正常執行。注意此引數需要重啟例項；

tokudb_buffer_pool_ratio

作用：用於控制TokuDB引擎能夠使用的buffer記憶體大小，比如innodb_buffer_pool_size設定為1000M，tokudb_buffer_pool_ratio設定為50（代表50%），那麼tokudb引擎的表能夠使用的buffer 記憶體大小則為500M；
建議：該引數在RDS中預設設定為0，如果RDS中使用tokudb引擎，則建議調大該引數，以此來提升TokuDB引擎表的訪問效能。該引數調整需要重啟資料庫例項。

max_statement_time

作用：用於控制查詢在MySQL的最長執行時間，如果超過該引數設定時間，查詢將會自動失敗，預設是不限制。
建議：如果使用者希望控制資料庫中SQL的執行時間，則可以開啟該引數，單位是毫秒。
現象：ERROR 3006 (HY000): Query execution was interrupted, max_statement_time exceeded

rds_threads_running_high_watermark

作用：用於控制MySQL併發的查詢數目，比如將rds_threads_running_high_watermark該值設定為100，則允許MySQL同時進行的併發查詢為100個，超過水位的查詢將會被拒絕掉，該引數與rds_threads_running_ctl_mode配合使用（預設值為select）。
建議：該引數常常在秒殺或者大併發的場景下使用，對資料庫具有較好的保護作用。

原文連結


相關文章