小黒糖2017-12-01 13:07:49瀏覽132評論0
mysqlinnodbRDS性能線程數(shù)據(jù)庫Cache數(shù)據(jù)安全tokudb
摘要：前言 很多時候，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，然后導致主備發(fā)生切換 連接數(shù)瓶頸：應(yīng)用不能新建立連接到數(shù)據(jù)庫 則需要進行應(yīng)用優(yōu)化、慢SQL優(yōu)化或者進行彈性升級實例規(guī)格來解決。
前言
很多時候，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，然后導致主備發(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è)置不當，則可能會出現(xiàn)性能問題或應(yīng)用報錯。
open_files_limit
作用：該參數(shù)用于控制MySQL實例能夠同時打開使用的文件句柄數(shù)目。
原因：當數(shù)據(jù)庫中的表（MyISAM 引擎表在被訪問的時候需要消耗文件描述符，InnoDB引擎會自己管理已經(jīng)打開的表—table_open_cache）打開越來越多后，會消耗分配給每個實例的文件句柄數(shù)目，RDS在起初初始化實例的時候設(shè)置的open_files_limit為8192，當打開的表數(shù)目超過該參數(shù)則會導致所有的數(shù)據(jù)庫請求報錯誤。
現(xiàn)象：如果參數(shù)設(shè)置過小可導致應(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ù)過小可能會導致應(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）場景下會使用自增表鎖，這樣會則可能導致應(yīng)用在并發(fā)導入數(shù)據(jù)出現(xiàn)死鎖。
現(xiàn)象：如果應(yīng)用并發(fā)使用load data(包括：INSERT … SELECT, REPLACE … SELECT)導入數(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帶來較大的壓力，所以當我們的數(shù)據(jù)庫不是那么頻繁的更新的時候，query cache是個好東西，但是如果反過來，寫入非常頻繁，并集中在某幾張表上的時候，那么query cache lock的鎖機制會造成很頻繁的鎖沖突，對于這一張表的寫和讀會互相等待query cache lock解鎖，導致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，當出現(xiàn)上述情況后可以關(guān)閉query cache；當然有些情況也可以打開query cache，比如：巧用query cache解決數(shù)據(jù)庫性能問題。
net_write_timeout
作用：等待將一個block發(fā)送給客戶端的超時時間。
現(xiàn)象：參數(shù)設(shè)置過小可能導致客戶端報錯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è)置過小導致的連接中斷很容易發(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)化而使用了臨時表，則會導致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，如果臨時文件超出此大小，則會導致應(yīng)用報錯。
現(xiàn)象：The table ‘/home/mysql/dataxxx/tmp/#sql_2db3_1’ is full。
建議：需要先分析一下導致臨時文件增加的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)?00個，超過水位的查詢將會被拒絕掉，該參數(shù)與rds_threads_running_ctl_mode配合使用（默認值為select）。
建議：該參數(shù)常常在秒殺或者大并發(fā)的場景下使用，對數(shù)據(jù)庫具有較好的保護作用。
版權(quán)聲明：本文內(nèi)容由互聯(lián)網(wǎng)用戶自發(fā)貢獻，本社區(qū)不擁有所有權(quán)，也不承擔相關(guān)法律責任。如果您發(fā)現(xiàn)本社區(qū)中有涉嫌抄襲的內(nèi)容，歡迎發(fā)送郵件至：yqgroup@service.aliyun.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，本社區(qū)將立刻刪除涉嫌侵權(quán)內(nèi)容。
