MySQL參數調優的最佳實踐是怎么樣的

這期內容當中小編將會給大家帶來有關MySQL參數調優的最佳實踐是怎么樣的，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

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


哪一些參數不能修改，那一些參數可以修改；
這些提供修改的參數是不是已經是最佳設置，如何才能利用好這些參數；
哪些參數可以改
細心的用戶在購買RDS的時候都會看到，不同規格能夠提供的最大連接數以及內存是不同的，所以這一些產品規格的限制參數：連接數、內存用戶是不能夠修改的，如果內存或者連接數出現了瓶頸：


內存瓶頸：實例會出現OOM，然后導致主備發生切換
連接數瓶頸：應用不能新建立連接到數據庫
則需要進行應用優化、慢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）。
建議：該參數常常在秒殺或者大并發的場景下使用，對數據庫具有較好的保護作用。

上述就是小編為大家分享的MySQL參數調優的最佳實踐是怎么樣的了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。