小黒糖2017-12-01 13:07:49瀏覽132評(píng)論0
mysqlinnodbRDS性能線程數(shù)據(jù)庫(kù)Cache數(shù)據(jù)安全tokudb
摘要：前言 很多時(shí)候，RDS用戶經(jīng)常會(huì)問(wèn)如何調(diào)優(yōu)RDS MySQL的參數(shù)蜗帜，為了回答這個(gè)問(wèn)題恋拷，寫一篇blog來(lái)進(jìn)行解釋： 哪一些參數(shù)不能修改，那一些參數(shù)可以修改厅缺； 這些提供修改的參數(shù)是不是已經(jīng)是最佳設(shè)置蔬顾，如何才能利用好這些參數(shù)； 哪些參數(shù)可以改 細(xì)心的用戶在購(gòu)買RDS的時(shí)候都會(huì)看到湘捎，不同規(guī)格能夠提供的最大連接數(shù)以及內(nèi)存是不同的诀豁，所以這一些產(chǎn)品規(guī)格的限制參數(shù)：連接數(shù)、內(nèi)存用戶是不能夠修改的窥妇，如果內(nèi)存或者連接數(shù)出現(xiàn)了瓶頸： 內(nèi)存瓶頸：實(shí)例會(huì)出現(xiàn)OOM舷胜，然后導(dǎo)致主備發(fā)生切換 連接數(shù)瓶頸：應(yīng)用不能新建立連接到數(shù)據(jù)庫(kù) 則需要進(jìn)行應(yīng)用優(yōu)化、慢SQL優(yōu)化或者進(jìn)行彈性升級(jí)實(shí)例規(guī)格來(lái)解決活翩。
前言
很多時(shí)候烹骨，RDS用戶經(jīng)常會(huì)問(wèn)如何調(diào)優(yōu)RDS MySQL的參數(shù)，為了回答這個(gè)問(wèn)題材泄，寫一篇blog來(lái)進(jìn)行解釋：
哪一些參數(shù)不能修改沮焕，那一些參數(shù)可以修改；
這些提供修改的參數(shù)是不是已經(jīng)是最佳設(shè)置拉宗，如何才能利用好這些參數(shù)峦树；
哪些參數(shù)可以改
細(xì)心的用戶在購(gòu)買RDS的時(shí)候都會(huì)看到，不同規(guī)格能夠提供的最大連接數(shù)以及內(nèi)存是不同的旦事，所以這一些產(chǎn)品規(guī)格的限制參數(shù)：連接數(shù)空入、內(nèi)存用戶是不能夠修改的，如果內(nèi)存或者連接數(shù)出現(xiàn)了瓶頸：
內(nèi)存瓶頸：實(shí)例會(huì)出現(xiàn)OOM族檬，然后導(dǎo)致主備發(fā)生切換
連接數(shù)瓶頸：應(yīng)用不能新建立連接到數(shù)據(jù)庫(kù)
則需要進(jìn)行應(yīng)用優(yōu)化、慢SQL優(yōu)化或者進(jìn)行彈性升級(jí)實(shí)例規(guī)格來(lái)解決化戳。
還有一些涉及主備數(shù)據(jù)安全的參數(shù)比如innodb_flush_log_at_trx_commit单料、sync_binlog埋凯、gtid_mode、semi_sync扫尖、binlog_format等為了保證主備的數(shù)據(jù)安全白对，目前還暫不提供給用戶進(jìn)行修改。
除上述的這些參數(shù)外换怖，絕大部分的參數(shù)都已經(jīng)由DBA團(tuán)隊(duì)和源碼團(tuán)隊(duì)優(yōu)化過(guò)甩恼，用戶不需要過(guò)多調(diào)整線上的參數(shù)就可以把數(shù)據(jù)庫(kù)比較好的運(yùn)行起來(lái)。但這些參數(shù)只是適合大多數(shù)的應(yīng)用場(chǎng)景沉颂，個(gè)別特殊的場(chǎng)景還是需要個(gè)別對(duì)待条摸，比如使用了tokudb引擎，這個(gè)時(shí)候就需要調(diào)整tokudb引擎能使用的內(nèi)存比例(tokudb_buffer_pool_ratio)铸屉；又比如我的應(yīng)用特點(diǎn)本身需要很大的一個(gè)鎖超時(shí)時(shí)間钉蒲，那么則需要調(diào)整innodb_lock_wait_timeout參數(shù)的大小以適應(yīng)應(yīng)用等等。
如何調(diào)參數(shù)
下面我將把控制臺(tái)中能夠修改的一些比較重要的參數(shù)給大家介紹一下彻坛，這些參數(shù)如果設(shè)置不當(dāng)顷啼，則可能會(huì)出現(xiàn)性能問(wèn)題或應(yīng)用報(bào)錯(cuò)。
open_files_limit
作用：該參數(shù)用于控制MySQL實(shí)例能夠同時(shí)打開使用的文件句柄數(shù)目昌屉。
原因：當(dāng)數(shù)據(jù)庫(kù)中的表（MyISAM 引擎表在被訪問(wèn)的時(shí)候需要消耗文件描述符钙蒙，InnoDB引擎會(huì)自己管理已經(jīng)打開的表—table_open_cache）打開越來(lái)越多后，會(huì)消耗分配給每個(gè)實(shí)例的文件句柄數(shù)目间驮，RDS在起初初始化實(shí)例的時(shí)候設(shè)置的open_files_limit為8192躬厌，當(dāng)打開的表數(shù)目超過(guò)該參數(shù)則會(huì)導(dǎo)致所有的數(shù)據(jù)庫(kù)請(qǐng)求報(bào)錯(cuò)誤。
現(xiàn)象：如果參數(shù)設(shè)置過(guò)小可導(dǎo)致應(yīng)用報(bào)錯(cuò)
[ERROR] /mysqld: Can't open file: './mysql/user.frm' (errno: 24 -Too many open files);
建議：提高open_files_limit的值蜻牢，RDS目前可以支撐最大為65535烤咧，，同時(shí)建議替換MyISAM存儲(chǔ)引擎為InnoDB引擎抢呆。
back_log
作用：MySQL每處理一個(gè)連接請(qǐng)求的時(shí)候都會(huì)對(duì)應(yīng)的創(chuàng)建一個(gè)新線程與之對(duì)應(yīng)煮嫌，那么在主線程創(chuàng)建新線程期間，如果前端應(yīng)用有大量的短連接請(qǐng)求到達(dá)數(shù)據(jù)庫(kù)抱虐，MySQL 會(huì)限制此刻新的連接進(jìn)入請(qǐng)求隊(duì)列昌阿，由參數(shù)back_log控制，如果等待的連接數(shù)量超過(guò)back_log恳邀，則將不會(huì)接受新的連接請(qǐng)求懦冰，所以如果需要MySQL能夠處理大量的短連接，需要提高此參數(shù)的大小谣沸。
現(xiàn)象：如果參數(shù)過(guò)小可能會(huì)導(dǎo)致應(yīng)用報(bào)錯(cuò)
SQLSTATE[HY000] [2002] Connection timed out;
建議：提高此參數(shù)值的大小刷钢，注意需要重啟實(shí)例，RDS在起初初始化的值的默認(rèn)值是50乳附，現(xiàn)在初始化值已經(jīng)調(diào)大了3000内地。
innodb_autoinc_lock_mode
作用：在MySQL5.1.22后伴澄，InnoDB為了解決自增主鍵鎖表的問(wèn)題，引入了參數(shù)innodb_autoinc_lock_mode阱缓，用于控制自增主鍵的鎖機(jī)制非凌，該參數(shù)可以設(shè)置的值為0/1/2，RDS 默認(rèn)的參數(shù)值為1荆针，表示InnoDB使用輕量級(jí)別的mutex鎖來(lái)獲取自增鎖敞嗡，替代最原始的表級(jí)鎖，但是在load data（包括：INSERT … SELECT, REPLACE … SELECT）場(chǎng)景下會(huì)使用自增表鎖航背，這樣會(huì)則可能導(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ù)的時(shí)候出現(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沃粗，則表示所有情況插入都使用輕量級(jí)別的mutex鎖(只針對(duì)row模式)粥惧，這樣就可以避免auto_inc的死鎖，同時(shí)在INSERT … SELECT 的場(chǎng)景下會(huì)提升很大的性能（注意該參數(shù)設(shè)置為2最盅，binlog的格式需要設(shè)置為row）突雪。
query_cache_size
作用：該參數(shù)用于控制MySQL query cache的內(nèi)存大小涡贱；如果MySQL開啟query cache咏删，再執(zhí)行每一個(gè)query的時(shí)候會(huì)先鎖住query cache，然后判斷是否存在query cache中问词，如果存在直接返回結(jié)果督函，如果不存在，則再進(jìn)行引擎查詢等操作激挪；同時(shí)insert辰狡、update和delete這樣的操作都會(huì)將query cahce失效掉，這種失效還包括結(jié)構(gòu)或者索引的任何變化垄分，cache失效的維護(hù)代價(jià)較高宛篇，會(huì)給MySQL帶來(lái)較大的壓力，所以當(dāng)我們的數(shù)據(jù)庫(kù)不是那么頻繁的更新的時(shí)候薄湿，query cache是個(gè)好東西叫倍，但是如果反過(guò)來(lái)，寫入非常頻繁豺瘤，并集中在某幾張表上的時(shí)候吆倦，那么query cache lock的鎖機(jī)制會(huì)造成很頻繁的鎖沖突，對(duì)于這一張表的寫和讀會(huì)互相等待query cache lock解鎖坐求，導(dǎo)致select的查詢效率下降蚕泽。
現(xiàn)象：數(shù)據(jù)庫(kù)中有大量的連接狀態(tài)為checking query cache for query、Waiting for query cache lock桥嗤、storing result in query cache赛糟；
建議：RDS默認(rèn)是關(guān)閉query cache功能的派任，如果您的實(shí)例打開了query cache，當(dāng)出現(xiàn)上述情況后可以關(guān)閉query cache璧南；當(dāng)然有些情況也可以打開query cache，比如：巧用query cache解決數(shù)據(jù)庫(kù)性能問(wèn)題师逸。
net_write_timeout
作用：等待將一個(gè)block發(fā)送給客戶端的超時(shí)時(shí)間司倚。
現(xiàn)象：參數(shù)設(shè)置過(guò)小可能導(dǎo)致客戶端報(bào)錯(cuò)the last packet successfully received from the server was milliseconds ago，the last packet sent successfully to the server was milliseconds ago篓像。
建議：該參數(shù)在RDS中默認(rèn)設(shè)置為60S动知，一般在網(wǎng)絡(luò)條件比較差的時(shí)，或者客戶端處理每個(gè)block耗時(shí)比較長(zhǎng)時(shí)员辩，由于net_write_timeout設(shè)置過(guò)小導(dǎo)致的連接中斷很容易發(fā)生盒粮，建議增加該參數(shù)的大小奠滑；
tmp_table_size
作用：該參數(shù)用于決定內(nèi)部?jī)?nèi)存臨時(shí)表的最大值丹皱，每個(gè)線程都要分配（實(shí)際起限制作用的是tmp_table_size和max_heap_table_size的最小值），如果內(nèi)存臨時(shí)表超出了限制宋税，MySQL就會(huì)自動(dòng)地把它轉(zhuǎn)化為基于磁盤的MyISAM表摊崭，優(yōu)化查詢語(yǔ)句的時(shí)候，要避免使用臨時(shí)表杰赛，如果實(shí)在避免不了的話呢簸，要保證這些臨時(shí)表是存在內(nèi)存中的。
現(xiàn)象：如果復(fù)雜的SQL語(yǔ)句中包含了group by/distinct等不能通過(guò)索引進(jìn)行優(yōu)化而使用了臨時(shí)表乏屯，則會(huì)導(dǎo)致SQL執(zhí)行時(shí)間加長(zhǎng)根时。
建議：如果應(yīng)用中有很多group by/distinct等語(yǔ)句，同時(shí)數(shù)據(jù)庫(kù)有足夠的內(nèi)存辰晕，可以增大tmp_table_size(max_heap_table_size)的值蛤迎，以此來(lái)提升查詢性能。
RDS MySQL 新增參數(shù)
下面介紹幾個(gè)比較有用的 RDS MySQL 新增參數(shù)伞芹。
rds_max_tmp_disk_space
作用：用于控制MySQL能夠使用的臨時(shí)文件的大小忘苛，RDS初始默認(rèn)值是10G，如果臨時(shí)文件超出此大小唱较，則會(huì)導(dǎo)致應(yīng)用報(bào)錯(cuò)扎唾。
現(xiàn)象：The table ‘/home/mysql/dataxxx/tmp/#sql_2db3_1’ is full。
建議：需要先分析一下導(dǎo)致臨時(shí)文件增加的SQL語(yǔ)句是否能夠通過(guò)索引或者其他方式進(jìn)行優(yōu)化南缓，其次如果確定實(shí)例的空間足夠胸遇，則可以提升此參數(shù)的值，以保證SQL能夠正常執(zhí)行汉形。注意此參數(shù)需要重啟實(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中默認(rèn)設(shè)置為0，如果RDS中使用tokudb引擎凯旭，則建議調(diào)大該參數(shù)概耻，以此來(lái)提升TokuDB引擎表的訪問(wèn)性能。該參數(shù)調(diào)整需要重啟數(shù)據(jù)庫(kù)實(shí)例罐呼。
max_statement_time
作用：用于控制查詢?cè)贛ySQL的最長(zhǎng)執(zhí)行時(shí)間鞠柄，如果超過(guò)該參數(shù)設(shè)置時(shí)間，查詢將會(huì)自動(dòng)失敗嫉柴，默認(rèn)是不限制厌杜。
建議：如果用戶希望控制數(shù)據(jù)庫(kù)中SQL的執(zhí)行時(shí)間，則可以開啟該參數(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同時(shí)進(jìn)行的并發(fā)查詢?yōu)?00個(gè)呐萌，超過(guò)水位的查詢將會(huì)被拒絕掉，該參數(shù)與rds_threads_running_ctl_mode配合使用（默認(rèn)值為select）谊娇。
建議：該參數(shù)常常在秒殺或者大并發(fā)的場(chǎng)景下使用肺孤，對(duì)數(shù)據(jù)庫(kù)具有較好的保護(hù)作用。
版權(quán)聲明：本文內(nèi)容由互聯(lián)網(wǎng)用戶自發(fā)貢獻(xiàn)济欢，本社區(qū)不擁有所有權(quán)赠堵，也不承擔(dān)相關(guān)法律責(zé)任。如果您發(fā)現(xiàn)本社區(qū)中有涉嫌抄襲的內(nèi)容法褥，歡迎發(fā)送郵件至：yqgroup@service.aliyun.com進(jìn)行舉報(bào)茫叭，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)半等，本社區(qū)將立刻刪除涉嫌侵權(quán)內(nèi)容揍愁。
