《互聯(lián)網(wǎng)金融MySQL優(yōu)化參數(shù)標準》要點：
本文介紹了互聯(lián)網(wǎng)金融MySQL優(yōu)化參數(shù)標準，希望對您有用。如果有疑問，可以聯(lián)系我們。
張小虎,甜橙金融持久化組負責(zé)人,多年數(shù)據(jù)庫優(yōu)化、運維及開發(fā)經(jīng)驗.擅長不同業(yè)務(wù)場景下的數(shù)據(jù)庫架構(gòu)設(shè)計及持久化解決方案.
日常的MySQL運維中說起調(diào)優(yōu),MySQL的配置文件my.cnf是不可忽略的.MySQL的默認參數(shù)并不能滿足我們?nèi)粘＞€上業(yè)務(wù)的需求,因此對參數(shù)進行優(yōu)化也是不可缺少的環(huán)節(jié).這里不想列出my.cnf配置中有多少項和每一項的意思,這些都可以在官方文檔上查到.以下僅對日常工作用應(yīng)該注意的一些參數(shù)進行說明.
下面針對一些參數(shù)進行說明.當然還有其它的設(shè)置可以起作用,取決于你的負載或硬件：在慢內(nèi)存和快磁盤、高并發(fā)和寫密集型負載情況下,你將需要特殊的調(diào)整.然而這里的目標是讓你可以快速地獲得一個穩(wěn)健的MySQL配置,而不用花費太多時間在調(diào)整一些無關(guān)緊要的MySQL設(shè)置或讀文檔,找出哪些設(shè)置對你來說是重要的.
從MySQL 5.5版本開始,InnoDB就是默認的存儲引擎并且它比任何其它存儲引擎的使用要多得多.那也是為什么它需要小心配置的原因.
1.innodb_file_per_table
表的數(shù)據(jù)和索引存放在共享表空間里或者單獨表空間里.我們的工作場景安裝是默認設(shè)置了innodb_file_per_table = ON,這樣也有助于工作中進行單獨表空間的遷移工作.MySQL 5.6中,這個屬性默認值是ON.
2.innodb_flush_log_at_trx_commit
默認值為1,表示InnoDB完全支持ACID特性.當你的主要關(guān)注點是數(shù)據(jù)安全的時候這個值是最合適的,比如在一個主節(jié)點上.但是對于磁盤(讀寫)速度較慢的系統(tǒng),它會帶來很巨大的開銷,因為每次將改變flush到redo日志都需要額外的fsyncs.
如果將它的值設(shè)置為2會導(dǎo)致不太可靠(unreliable).因為提交的事務(wù)僅僅每秒才flush一次到redo日志,但對于一些場景是可以接受的,比如對于主節(jié)點的備份節(jié)點這個值是可以接受的.如果值為0速度就更快了,但在系統(tǒng)崩潰時可能丟失一些數(shù)據(jù)：只適用于備份節(jié)點.說到這個參數(shù)就一定會想到另一個sync_binlog.
3.innodb_flush_method
這項配置決定了數(shù)據(jù)和日志寫入硬盤的方式.一共有三種方式,我們默認使用O_DIRECT?.O_DIRECT模式：數(shù)據(jù)文件的寫入操作是直接從mysql innodb buffer到磁盤的,并不用通過操作系統(tǒng)的緩沖,而真正的完成也是在flush這步,日志還是要經(jīng)過OS緩沖.
4.innodb_log_buffer_size
這項配置決定了為尚未執(zhí)行的事務(wù)分配的緩存.其默認值(1MB)一般來說已經(jīng)夠用了,但是如果你的事務(wù)中包含有二進制大對象或者大文本字段的話,這點緩存很快就會被填滿并觸發(fā)額外的I/O操作.看看Innodb_log_waits狀態(tài)變量,如果它不是0,增加innodb_log_buffer_size.
5.innodb_buffer_pool_size
這個參數(shù)應(yīng)該是運維中必須關(guān)注的了.緩沖池是數(shù)據(jù)和索引緩存的地方,它屬于MySQL的核心參數(shù),默認為128MB,正常的情況下這個參數(shù)設(shè)置為物理內(nèi)存的60%~70%.(不過我們的實例基本上都是多實例混部的,所以這個值還要根據(jù)業(yè)務(wù)規(guī)模來具體分析.)
6.innodb_log_file_size
這是redo日志的大小.redo日志被用于確保寫操作快速而可靠并且在崩潰時恢復(fù).如果你知道你的應(yīng)用程序需要頻繁地寫入數(shù)據(jù)并且你使用的是MySQL 5.6,那么你可以一開始就把它這是成4G.(具體大小還要根據(jù)自身業(yè)務(wù)進行適當調(diào)整)
7.innodb_support_xa
innodb_support_xa可以開關(guān)InnoDB的XA兩段式事務(wù)提交.默認情況下,innodb_support_xa=true,支持XA兩段式事務(wù)提交.由于XA兩段式事務(wù)提交導(dǎo)致多余flush等操作,性能影響會達到10%,所有為了提高性能,有些DBA會設(shè)置innodb_support_xa=false.這樣的話,redolog和binlog將無法同步,可能存在事務(wù)在主庫提交,但是沒有記錄到binlog的情況.這樣也有可能造成事務(wù)數(shù)據(jù)的丟失.
8.innodb_additional_mem_pool_size
該參數(shù)用來存儲數(shù)據(jù)字段信息和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu).表越多,需要在這里分配的內(nèi)存越多.如果InnoDB用光了這個池內(nèi)的內(nèi)存,InnoDB開始從操作系統(tǒng)分配內(nèi)存,并且往MySQL錯誤日志寫警告信息,默認8MB.一般設(shè)置16MB.
9.max_connections
MySQL服務(wù)器默認連接數(shù)比較小,一般也就100來個最好把最大值設(shè)大一些.一般設(shè)置500~1000即可每一個鏈接都會占用一定的內(nèi)存,所以這個參數(shù)也不是越大越好.有的人遇到too many connections會去增加這個參數(shù)的大小,但其實如果是業(yè)務(wù)量或者程序邏輯有問題或者sql寫的不好,即使增大這個參數(shù)也無濟于事,再次報錯只是時間問題.在應(yīng)用程序里使用連接池或者在MySQL里使用進程池有助于解決這一問題.
max_threads(當前活躍連接數(shù))* (
read_buffer_size– 順序讀緩沖,提高順序讀效率
+read_rnd_buffer_size– 隨機讀緩沖,提高隨機讀效率
+sort_buffer_size– 排序緩沖,提高排序效率
+join_buffer_size– 表連接緩沖,提高表連接效率
+binlog_cache_size– 二進制日志緩沖,提高二進制日志寫入效率?
+tmp_table_size– 內(nèi)存臨時表,提高臨時表存儲效率
+thread_stack– 線程堆棧,暫時寄存SQL語句/存儲過程
+thread_cache_size– 線程緩存,降低多次反復(fù)打開線程開銷
+net_buffer_length– 線程持連接緩沖以及讀取結(jié)果緩沖
+bulk_insert_buffer_size– MyISAM表批量寫入數(shù)據(jù)緩沖
)
global buffer(全局內(nèi)存分配總和) =
innodb_buffer_pool_size
— InnoDB高速緩沖,行數(shù)據(jù)、索引緩沖,以及事務(wù)鎖、自適應(yīng)哈希等
+ innodb_additional_mem_pool_size
— InnoDB數(shù)據(jù)字典額外內(nèi)存,緩存所有表數(shù)據(jù)字典
+innodb_log_buffer_size
— InnoDB REDO日志緩沖,提高REDO日志寫入效率
+key_buffer_size
— MyISAM表索引高速緩沖,提高MyISAM表索引讀寫效率
+query_cache_size
–查詢高速緩存,緩存查詢結(jié)果,提高反復(fù)查詢返回效率+table_cahce — 表空間文件描述符緩存,提高數(shù)據(jù)表打開效率
+table_definition_cache
–表定義文件描述符緩存,提高數(shù)據(jù)表打開效率
參數(shù)的優(yōu)化最終目的是讓MySQL更好地利用資源通過合理地控制內(nèi)存的分配,合理的CPU使用建議降低Session的內(nèi)存分配.
10.server-id
復(fù)制架構(gòu)時確保 server-id 要不同,通常主ID要小于從ID.
11.log_bin
如果你想讓數(shù)據(jù)庫服務(wù)器充當主節(jié)點的備份節(jié)點,那么開啟二進制日志是必須的.如果這么做了之后,還別忘了設(shè)置server_id為一個唯一的值.就算只有一個服務(wù)器,如果你想做基于時間點的數(shù)據(jù)恢復(fù),這(開啟二進制日志)也是很有用的：從你最近的備份中恢復(fù)(全量備份),并應(yīng)用二進制日志中的修改(增量備份).
二進制日志一旦創(chuàng)建就將永久保存.所以如果你不想讓磁盤空間耗盡,你可以用 PURGE BINARY LOGS 來清除舊文件,或者設(shè)置expire_logs_days 來指定過多少天日志將被自動清除.記錄二進制日志不是沒有開銷的,所以如果你在一個非主節(jié)點的復(fù)制節(jié)點上不需要它的話,那么建議關(guān)閉這個選項.
12.skip_name_resolve
當客戶端連接數(shù)據(jù)庫服務(wù)器時,服務(wù)器會進行主機名解析,并且當DNS很慢時,建立連接也會很慢.因此建議在啟動服務(wù)器時關(guān)閉skip_name_resolve選項而不進行DNS查找.唯一的局限是之后GRANT語句中只能使用IP地址了,因此在添加這項設(shè)置到一個已有系統(tǒng)中必須格外小心.
13.sync_binlog
sync_binlog 的默認值是0,像操作系統(tǒng)刷其他文件的機制一樣,MySQL不會同步到磁盤中去而是依賴操作系統(tǒng)來刷新binary log.
當sync_binlog =N (N>0) ,MySQL 在每寫N次二進制日志binary log時,會使用fdatasync()函數(shù)將它的寫二進制日志binary log同步到磁盤中去.當innodb_flush_log_at_trx_commit和sync_binlog ?都為 1 時是最安全的,在mysqld服務(wù)崩潰或者服務(wù)器主機crash的情況下,binary log只有可能丟失最多一個語句或者一個事務(wù).但是魚與熊掌不可兼得,雙1會導(dǎo)致頻繁的IO操作,因此該模式也是最慢的一種方式.出于我們的業(yè)務(wù)考慮在業(yè)務(wù)壓力允許的情況下默認的都是雙1配置.
14.log_slave_update
當業(yè)務(wù)中需要使用級聯(lián)架構(gòu)的時候log_slave_update = 1這個參數(shù)必須打開,否者第三級可能無法接收到第一級產(chǎn)生的binlog,從而無法進行數(shù)據(jù)同步.
15.tmpdir
如果內(nèi)存臨時表超出了限制,MySQL就會自動地把它轉(zhuǎn)化為基于磁盤的MyISAM表,存儲在指定的tmpdir目錄下.因此盡可能將tmpdir配置到性能好速度快的存儲設(shè)備上.
16.慢日志相關(guān)
slow_query_log = 1 ??#打開慢日志
slow_query_log_file = /mysql/log/mysql.slow
long_query_time = 0.5 ?#設(shè)置超過多少秒的查詢會入慢日志
1.SSD對參數(shù)的影響
隨著科學(xué)技術(shù)的發(fā)展,越來越多的存儲設(shè)備開始由傳統(tǒng)的機械組件轉(zhuǎn)向由電子元件組成的永久存儲,且價錢越來越能讓企業(yè)接受.存儲組件速度提升后,再用傳統(tǒng)機械組件的DB配置就顯得浪費了,所以就需要針對不同的存儲技術(shù)對MySQL配置作出調(diào)整,比如 innodb_io_capacity需要調(diào)大, 日志文件和redo放到機械硬盤, undo放到SSD, atomic write不需要Double Write Buffer, InnoDB壓縮, 單機多實例+cgroup等等.分析 I/O 情況,動態(tài)調(diào)整 innodb_io_capacity 和 innodb_max_dirty_pages_pct；試圖調(diào)整 innodb_adaptive_flushing,查看效果.
2.線程池設(shè)置
針對innodb_write_io_threads 和 innodb_read_io_threads 的調(diào)優(yōu)我們目前沒有做,但我相信調(diào)整為8或者16,系統(tǒng) I/O 性能會更好.還有,需要注意以下幾點：任何一個調(diào)整,都要建立在數(shù)據(jù)的支撐和嚴謹?shù)姆治龌A(chǔ)上,否則都是空談；?這類調(diào)優(yōu)是非常有意義的,是真正能帶來價值的,所以需要多下功夫,并且盡可能地搞明白為什么要這么調(diào)整.
3.CPU相關(guān)
4.IO相關(guān)的
文章來自微信公眾號：DBAplus社群
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4079.html
