共計 4252 個字符，預(yù)計需要花費 11 分鐘才能閱讀完成。

這期內(nèi)容當(dāng)中丸趣 TV 小編將會給大家?guī)碛嘘P(guān) MySQL 參數(shù)調(diào)優(yōu)的最佳實踐是怎么樣的，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

前言
很多時候，RDS 用戶經(jīng)常會問如何調(diào)優(yōu) RDS MySQL 的參數(shù)，為了回答這個問題，寫一篇 blog 來進行解釋：

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

內(nèi)存瓶頸：實例會出現(xiàn) OOM，然后導(dǎo)致主備發(fā)生切換
連接數(shù)瓶頸：應(yīng)用不能新建立連接到數(shù)據(jù)庫
則需要進行應(yīng)用優(yōu)化、慢 SQL 優(yōu)化或者進行彈性升級實例規(guī)格來解決。

還有一些涉及主備數(shù)據(jù)安全的參數(shù)比如 innodb_flush_log_at_trx_commit、sync_binlog、gtid_mode、semi_sync、binlog_format 等為了保證主備的數(shù)據(jù)安全，目前還暫不提供給用戶進行修改。

除上述的這些參數(shù)外，絕大部分的參數(shù)都已經(jīng)由 DBA 團隊和源碼團隊優(yōu)化過，用戶不需要過多調(diào)整線上的參數(shù)就可以把數(shù)據(jù)庫比較好的運行起來。但這些參數(shù)只是適合大多數(shù)的應(yīng)用場景，個別特殊的場景還是需要個別對待，比如使用了 tokudb 引擎，這個時候就需要調(diào)整 tokudb 引擎能使用的內(nèi)存比例(tokudb_buffer_pool_ratio)；又比如我的應(yīng)用特點本身需要很大的一個鎖超時時間，那么則需要調(diào)整 innodb_lock_wait_timeout 參數(shù)的大小以適應(yīng)應(yīng)用等等。

如何調(diào)參數(shù)
下面我將把控制臺中能夠修改的一些比較重要的參數(shù)給大家介紹一下，這些參數(shù)如果設(shè)置不當(dāng)，則可能會出現(xiàn)性能問題或應(yīng)用報錯。

open_files_limit
作用：該參數(shù)用于控制 MySQL 實例能夠同時打開使用的文件句柄數(shù)目。
原因：當(dāng)數(shù)據(jù)庫中的表（MyISAM 引擎表在被訪問的時候需要消耗文件描述符，InnoDB 引擎會自己管理已經(jīng)打開的表—table_open_cache）打開越來越多后，會消耗分配給每個實例的文件句柄數(shù)目，RDS 在起初初始化實例的時候設(shè)置的 open_files_limit 為 8192，當(dāng)打開的表數(shù)目超過該參數(shù)則會導(dǎo)致所有的數(shù)據(jù)庫請求報錯誤。
現(xiàn)象：如果參數(shù)設(shè)置過小可導(dǎo)致應(yīng)用報錯
[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 每處理一個連接請求的時候都會對應(yīng)的創(chuàng)建一個新線程與之對應(yīng)，那么在主線程創(chuàng)建新線程期間，如果前端應(yīng)用有大量的短連接請求到達數(shù)據(jù)庫，MySQL 會限制此刻新的連接進入請求隊列，由參數(shù) back_log 控制，如果等待的連接數(shù)量超過 back_log，則將不會接受新的連接請求，所以如果需要 MySQL 能夠處理大量的短連接，需要提高此參數(shù)的大小。
現(xiàn)象：如果參數(shù)過小可能會導(dǎo)致應(yīng)用報錯
SQLSTATE[HY000] [2002] Connection timed out;
建議：提高此參數(shù)值的大小，注意需要重啟實例，RDS 在起初初始化的值的默認值是 50，現(xiàn)在初始化值已經(jīng)調(diào)大了 3000。

innodb_autoinc_lock_mode
作用：在 MySQL5.1.22 后，InnoDB 為了解決自增主鍵鎖表的問題，引入了參數(shù) innodb_autoinc_lock_mode，用于控制自增主鍵的鎖機制，該參數(shù)可以設(shè)置的值為 0 /1/2，RDS 默認的參數(shù)值為 1，表示 InnoDB 使用輕量級別的 mutex 鎖來獲取自增鎖，替代最原始的表級鎖，但是在 load data（包括：INSERT … SELECT, REPLACE … SELECT）場景下會使用自增表鎖，這樣會則可能導(dǎo)致應(yīng)用在并發(fā)導(dǎo)入數(shù)據(jù)出現(xiàn)死鎖。
現(xiàn)象：如果應(yīng)用并發(fā)使用 load data(包括：INSERT … SELECT, REPLACE … SELECT)導(dǎo)入數(shù)據(jù)的時候出現(xiàn)死鎖：
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；
建議：建議將參數(shù)設(shè)置改為 2，則表示所有情況插入都使用輕量級別的 mutex 鎖(只針對 row 模式)，這樣就可以避免 auto_inc 的死鎖，同時在 INSERT … SELECT 的場景下會提升很大的性能（注意該參數(shù)設(shè)置為 2，binlog 的格式需要設(shè)置為 row）。

query_cache_size
作用：該參數(shù)用于控制 MySQL query cache 的內(nèi)存大?。蝗绻?MySQL 開啟 query cache，再執(zhí)行每一個 query 的時候會先鎖住 query cache，然后判斷是否存在 query cache 中，如果存在直接返回結(jié)果，如果不存在，則再進行引擎查詢等操作；同時 insert、update 和 delete 這樣的操作都會將 query cahce 失效掉，這種失效還包括結(jié)構(gòu)或者索引的任何變化，cache 失效的維護代價較高，會給 MySQL 帶來較大的壓力，所以當(dāng)我們的數(shù)據(jù)庫不是那么頻繁的更新的時候，query cache 是個好東西，但是如果反過來，寫入非常頻繁，并集中在某幾張表上的時候，那么 query cache lock 的鎖機制會造成很頻繁的鎖沖突，對于這一張表的寫和讀會互相等待 query cache lock 解鎖，導(dǎo)致 select 的查詢效率下降。
現(xiàn)象：數(shù)據(jù)庫中有大量的連接狀態(tài)為 checking query cache for query、Waiting for query cache lock、storing result in query cache；
建議：RDS 默認是關(guān)閉 query cache 功能的，如果您的實例打開了 query cache，當(dāng)出現(xiàn)上述情況后可以關(guān)閉 query cache；當(dāng)然有些情況也可以打開 query cache，比如：巧用 query cache 解決數(shù)據(jù)庫性能問題。

net_write_timeout
作用：等待將一個 block 發(fā)送給客戶端的超時時間。
現(xiàn)象：參數(shù)設(shè)置過小可能導(dǎo)致客戶端報錯 the last packet successfully received from the server was milliseconds ago，the last packet sent successfully to the server was milliseconds ago。
建議：該參數(shù)在 RDS 中默認設(shè)置為 60S，一般在網(wǎng)絡(luò)條件比較差的時，或者客戶端處理每個 block 耗時比較長時，由于 net_write_timeout 設(shè)置過小導(dǎo)致的連接中斷很容易發(fā)生，建議增加該參數(shù)的大??；

tmp_table_size
作用：該參數(shù)用于決定內(nèi)部內(nèi)存臨時表的最大值，每個線程都要分配（實際起限制作用的是 tmp_table_size 和 max_heap_table_size 的最小值），如果內(nèi)存臨時表超出了限制，MySQL 就會自動地把它轉(zhuǎn)化為基于磁盤的 MyISAM 表，優(yōu)化查詢語句的時候，要避免使用臨時表，如果實在避免不了的話，要保證這些臨時表是存在內(nèi)存中的。
現(xiàn)象：如果復(fù)雜的 SQL 語句中包含了 group by/distinct 等不能通過索引進行優(yōu)化而使用了臨時表，則會導(dǎo)致 SQL 執(zhí)行時間加長。
建議：如果應(yīng)用中有很多 group by/distinct 等語句，同時數(shù)據(jù)庫有足夠的內(nèi)存，可以增大 tmp_table_size(max_heap_table_size)的值，以此來提升查詢性能。

RDS MySQL 新增參數(shù)
下面介紹幾個比較有用的 RDS MySQL 新增參數(shù)。

rds_max_tmp_disk_space
作用：用于控制 MySQL 能夠使用的臨時文件的大小，RDS 初始默認值是 10G，如果臨時文件超出此大小，則會導(dǎo)致應(yīng)用報錯。
現(xiàn)象：The table‘/home/mysql/dataxxx/tmp/#sql_2db3_1’is full。
建議：需要先分析一下導(dǎo)致臨時文件增加的 SQL 語句是否能夠通過索引或者其他方式進行優(yōu)化，其次如果確定實例的空間足夠，則可以提升此參數(shù)的值，以保證 SQL 能夠正常執(zhí)行。注意此參數(shù)需要重啟實例；

tokudb_buffer_pool_ratio
作用：用于控制 TokuDB 引擎能夠使用的 buffer 內(nèi)存大小，比如 innodb_buffer_pool_size 設(shè)置為 1000M，tokudb_buffer_pool_ratio 設(shè)置為 50（代表 50%），那么 tokudb 引擎的表能夠使用的 buffer 內(nèi)存大小則為 500M；
建議：該參數(shù)在 RDS 中默認設(shè)置為 0，如果 RDS 中使用 tokudb 引擎，則建議調(diào)大該參數(shù)，以此來提升 TokuDB 引擎表的訪問性能。該參數(shù)調(diào)整需要重啟數(shù)據(jù)庫實例。

max_statement_time
作用：用于控制查詢在 MySQL 的最長執(zhí)行時間，如果超過該參數(shù)設(shè)置時間，查詢將會自動失敗，默認是不限制。
建議：如果用戶希望控制數(shù)據(jù)庫中 SQL 的執(zhí)行時間，則可以開啟該參數(shù)，單位是毫秒。
現(xiàn)象：ERROR 3006 (HY000): Query execution was interrupted, max_statement_time exceeded

rds_threads_running_high_watermark
作用：用于控制 MySQL 并發(fā)的查詢數(shù)目，比如將 rds_threads_running_high_watermark 該值設(shè)置為 100，則允許 MySQL 同時進行的并發(fā)查詢?yōu)?100 個，超過水位的查詢將會被拒絕掉，該參數(shù)與 rds_threads_running_ctl_mode 配合使用（默認值為 select）。
建議：該參數(shù)常常在秒殺或者大并發(fā)的場景下使用，對數(shù)據(jù)庫具有較好的保護作用。

上述就是丸趣 TV 小編為大家分享的 MySQL 參數(shù)調(diào)優(yōu)的最佳實踐是怎么樣的了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道。