#更改磁盤調(diào)度算法

創(chuàng)新互聯(lián)主要從事網(wǎng)站設計制作、成都網(wǎng)站設計、網(wǎng)頁設計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務。立足成都服務郴州,十多年網(wǎng)站建設經(jīng)驗,價格優(yōu)惠、服務專業(yè),歡迎來電咨詢建站服務:028-86922220

echo 'deadline' >/sys/block/xvdb/queue/scheduler

#關(guān)掉NUMA特性

#numactl --interleave=all

下面是my.cnf

[client]
port??????????? = 3306
socket????????? = /tmp/MySQL.sock
[mysqld_safe]
#google優(yōu)化過的內(nèi)存分配模塊,這個模塊可以讓MySQL在高并發(fā)下內(nèi)存占用更加穩(wěn)定.系統(tǒng)不自帶,需要自行安裝,建議使用.沒有安裝模塊設置這個參數(shù)會報錯.
#malloc-lib=tcmalloc
[mysqld]
port??????????? = 3306
socket????????? = /tmp/mysql.sock
log-error??????? = /data/mysql/data/mysql.err
#表名的大小寫敏感選項,默認為0,即開啟大小寫敏感,1為大小寫不敏感
#lower_case_table_names = 1
#改變mysql的時區(qū),默認是SYSTEM,即與系統(tǒng)同步,show variables like '%time_zone%'查看,按實際情況更改
default-time-zone = '+8:00'
#改變?nèi)罩撅@示時間,默認是UTC,和中國時區(qū)相差了8小時,看著很奇怪,改成系統(tǒng)SYSTEM就可以了
log_timestamps = SYSTEM
#默認存儲引擎,5.5之后默認就都是innodb
default-storage-engine=INNODB
#默認表的存儲引擎,5.5之后默認就都是innodb,不設置就跟隨default-storage-engine配置
#default_table_type = InnoDB
#MySQL程序的路徑,代碼調(diào)用路徑
basedir=/usr/local/mysql
#全局數(shù)據(jù)文件及結(jié)構(gòu)的存放位置；表數(shù)據(jù)(包括innodb引擎),索引,日志(除非單獨設置)等文件都會存放在這里
datadir=/data/mysql/data
#innodb引擎的共享表空間數(shù)據(jù)文件根目錄,如果不設置,默認使用datadir參數(shù)的目錄
#innodb_data_home_dir = /data/mysql/data
#全局默認字符集類型,按需求設定,utf8,utf8mb4,gb2312等
character-set-server = utf8mb4
#全局默認字符校對規(guī)則,utf8默認校對規(guī)則是utf8_general_ci(不區(qū)分大小寫),utf8_bin是區(qū)分大小寫的,按需求設置.
#collation_server = utf8_bin
#當連接處于輸入密碼階段,超過connect_timeout的連接請求將會被拒絕.默認是10秒,一般不用設置,也算是不長不短了,最好不要設置太長,連接掛起也是要耗費一定資源
#connect_timeout=5
#關(guān)閉一個非交互的連接(純sleep狀態(tài))之前所要等待的秒數(shù),其取值范圍為1-31536000(linux),默認值28800.太長不利于高效利用資源,但某些耗費時間的sql和一些php/python/ruby等的腳本調(diào)用則不能設置太短時間,不然會提前中斷.如果有連接池則最好默認或設置更大一些,而且告訴開發(fā),連接池的超時時間不應該超過這個時間,不然會有奇怪的事情
wait_timeout=86400
#關(guān)閉一個交互的連接(sleep和其他狀態(tài))之前所要等待的秒數(shù),默認值為28800,調(diào)小一些有利于資源回收,如果有需求用到長連接可以調(diào)大,因為超時就會強制關(guān)閉sql,如果有連接池則最好默認或設置更大一些,而且告訴開發(fā),連接池的超時時間不應該超過這個時間,不然會有奇怪的事情
interactive_timeout = 86400
#在連接繁忙階段(非sleep),對TCP/IP鏈接有效,只針對在Activity狀態(tài)下的線程,客戶端發(fā)送、接收、或者處理數(shù)據(jù)包的讀超時時間,默認是30,一般不用設置,除非你網(wǎng)絡狀態(tài)很差
#net_read_timeout=300
#在連接繁忙階段(非sleep),對TCP/IP鏈接有效,只針對在Activity狀態(tài)下的線程,客戶端發(fā)送、接收、或者處理數(shù)據(jù)包的寫超時時間,默認是60,一般不用設置,除非你網(wǎng)絡狀態(tài)很差
#net_write_timeout=300
#禁用域名DNS查找,不能在mysql的授權(quán)表中使用主機名或域名,只能使用IP或localhost
#skip_name_resolve = 1
#跳過外部鎖定,防止文件目錄不可用
skip-external-locking
#使用InnoDB引擎獨立表空間,每個表獨立表空間,不使用共享表空間ibdata,但是表的元數(shù)據(jù)、undo log、插入緩沖等等還是保存在共享表空間里,新版本默認為開
innodb_file_per_table = 1
#在MySQL暫時停止響應新請求之前的短時間內(nèi)多少個請求可以被存在堆棧中,默認值為50.并發(fā)高需要調(diào)高這個值,但是太高會占內(nèi)存,不能超過系統(tǒng)設定值.
back_log = 103
#安全參數(shù),與性能無關(guān),阻止設定值次數(shù)嘗試登陸失敗后的客戶端登陸,以防止暴力破解密碼,內(nèi)網(wǎng)安全要求低的可以適當增加.可以用flush hosts命令刷新這個登錄失敗的主機記錄.
max_connect_errors = 100000
#控制innodb自增長鎖模式參數(shù),全局變量,只讀變量,整型值,有0,1,2三個值(分別代表traditional(“傳統(tǒng)”), consecutive(“連續(xù)”)和interleaved(“交叉”)),默認為1,表示自增ID是連續(xù)的,一般也不用修改.
#innodb_autoinc_lock_mode = 2
#線程處理模式,no-threads(單線程處理),one-thread-per-connection(每個請求對應一個線程),dynamically-loaded/pool-of-threads(線程池模式,官方mysql和Percona參數(shù)名字不一樣)官方版本需要購買服務開啟
#thread_handling = pool-of-threads
#線程池中連接超時的時間,默認500ms,調(diào)低可以提高連接池的利用
#thread_pool_stall_limit = 200
#索引緩沖區(qū)的大小,只對MyISAM表起作用,但是系統(tǒng)表和臨時磁盤表都還是MyISAM表,要想加快這些的運行速度,這個值還是不能太低
key_buffer_size = 32M
#批量插入緩存大小,只對MyISAM表起作用,適用于在一次性插入100-1000+條記錄時,提高效率.默認值是8M
#bulk_insert_buffer_size = 64M
#網(wǎng)絡數(shù)據(jù)包容量限制,較大的插入/更新/導出/導入會受此參數(shù)限制,超過限制則導致sql執(zhí)行失敗.默認4M(太小),最大限制為1GB(太大),現(xiàn)在網(wǎng)絡設備帶寬都比較大,建議最少設置到32M以上,千兆以上網(wǎng)絡建議64M/128M比較好.該值應為1024的倍數(shù);否則四舍五入取最接近的倍數(shù),mha等集群架構(gòu)最好設定成64M以上.
max_allowed_packet = 128M
#全局最大打開文件數(shù),不可以超過系統(tǒng)設定的最大文件數(shù),不然無效
open_files_limit = 65535
#innodb引擎限制一次打開表空間文件.ibd的文件描述符數(shù)量,只在使用獨立表空間時才有用,范圍10~4294967295,不可以超過全局最大文件數(shù).新版默認為-1,表示根據(jù)實際打開數(shù)量自動設置(自動計算規(guī)則：如果innodb_file_per_table沒有打開,則默認值為300,如果打開了,則以innodb_open_files與table_open_cache中的較大值為準),僅用于限制innodb表空間.ibd文件的描述符數(shù)量,不影響table cache的數(shù)量,增大這個變量需要注意同時修改server層的變量open_files_limit=num(只讀變量),這個參數(shù)為系統(tǒng)級別的文件句柄限制參數(shù),由于每個session操作數(shù)據(jù)庫表時都要占用文件描述符,數(shù)據(jù)庫連接本身也要占用文件描述符,因此如果手動指定innodb_open_files參數(shù)時,還需要注意并發(fā)連接數(shù)量open_files_limit的大小設置
innodb_open_files = 65530
#允許存到緩存里的表的句柄數(shù)量,增大可改善頻繁打開和關(guān)閉表的操作,新版本默認是2000.參考值為max_connections*表的個數(shù),如果在show processlist里經(jīng)常發(fā)現(xiàn)opening table那么可能就是這兩個變量設置小了,也受open_files_limit的設置和innodb_open_files變量影響.
table_open_cache = 65530
#與table_open_cache作用類似,但是是緩存表定義文件.frm的數(shù)量.默認值是-1,表示自動計算,計算公式(400 + (table_open_cache / 2)),如果打開的表實例的數(shù)量超過這個參數(shù)設置,則LRU機制標記表實例并最終從數(shù)據(jù)字典緩存中刪除,有助于解決大量內(nèi)存被用于緩存極少使用的表實例的情況.
#table_definition_cache = 4096
#5.6.6版本新增,每一個線程可以使用緩存表的句柄的并發(fā)數(shù)(分區(qū)數(shù)),不能超過table_open_cache,默認是1,最大值為64,一般夠用,除非庫的并發(fā)實在太多且頻繁打開表.
table_open_cache_instances = 16
#排序緩存,會話級別內(nèi)存緩存,一般OLTP超過512K性能增加不明顯,建議值為56K-1M,如果是數(shù)據(jù)倉庫(OLAP)設置8M以上能進一步提升效果.如果通過show global status看到Sort_merge_passes的值很大,可以考慮通過適當調(diào)整這個參數(shù)的值來增大排序緩存區(qū),但最好不要全局設置,只對當前session設置較大的值就可以了
sort_buffer_size = 512K
#TCP/IP和套接字通信緩沖區(qū)大小,創(chuàng)建長度達net_buffer_length的行,主要影響導入導出
net_buffer_length = 8K
#以順序讀掃描的方式掃描表數(shù)據(jù)的時候使用緩沖區(qū)的大小,會話級別內(nèi)存緩存,默認為128K,最大為2G,只對MyISAM表起作用,要注意系統(tǒng)臨時表也是myisam
read_buffer_size = 1M
#會話級別內(nèi)存緩存,以隨機讀掃描的方式掃描表數(shù)據(jù)的時候使用緩沖區(qū)的大小,默認為256K,最大為2G,原本只對MyISAM表起作用,但是新版本也會用做新特性mrr的buffer,
read_rnd_buffer_size = 16M
#MyISAM表發(fā)生變化時重新排序所需的緩沖,臨時磁盤表都是MyISAM表,要加快臨時表排序,這個值不能低,特別是數(shù)據(jù)倉庫(OLAP)
myisam_sort_buffer_size = 128M
#MySQL重建索引時所允許的最大臨時文件的最大大小
myisam_max_sort_file_size = 10G
#自動檢查和修復沒有適當關(guān)閉的 MyISAM 表
myisam_repair_threads = 1
#MyISAM表在打開的時候被自動檢查,如果表被不恰當?shù)仃P(guān)閉,就修復表.
#myisam_recover
#啟用binlog,并指定存儲位置,默認存到數(shù)據(jù)目錄,主庫一定要開,從庫可適當關(guān)閉
log-bin=/data/mysql/data/mysql-bin
#默認是1,開啟記錄sql語句到binlog中,改成0則是禁止sql語句記入二進制日志文件binlog中,當然也就不會更新到備庫中
#sql_log_bin = 0
#binlog類型格式,建議用mixed或row,但現(xiàn)在大多數(shù)情況會出現(xiàn)mixed格式轉(zhuǎn)換成row格式的事情(特別是RC隔離級別),而STATEMENT只在個別情況使用了.
binlog_format=mixed
#binlog校驗規(guī)則,一般不用設置.當binlog event發(fā)送回來最后獲取event內(nèi)容的時候,會增加4個額外字節(jié)做校驗用.在5.6.5以后的版本默認是CRC32,低版本都是NONE.在使用MGR集群時需要設置為NONE
#binlog_checksum=NONE
#數(shù)據(jù)庫ID號,主要用于主從復制相關(guān),盡量設置成全網(wǎng)唯一值
server-id = 18650
#共享表空間容量大小,建議至少1G以上,5.7之后才實現(xiàn)真正大小縮放(以前只會變大)
innodb_data_file_path = ibdata1:1G:autoextend
#硬盤上單個redolog文件的容量大小,建議1-2G或以上,默認值為48M,最小值為1M,5.6.3版本開始支持最大512G.設置越大則數(shù)據(jù)庫I/O越少,但是故障恢復的代價就越大,時間越長.
innodb_log_file_size = 1024M
#redolog內(nèi)存參數(shù),8-32M即可,設置越大則數(shù)據(jù)庫I/O越少
innodb_log_buffer_size = 16M
#設置有幾組redolog,設置多一些也可以減少I/O壓力,特別是大事務的情況,最大值為100,最小值且也是默認值是2.
innodb_log_files_in_group = 3
#redolog存放目錄,建議不設置,ib_logfile默認存在數(shù)據(jù)文件目錄下,設置它主要是分散磁盤壓力
#innodb_log_group_home_dir = /tmp
#控制innodb數(shù)據(jù)文件及redo log的打開、刷寫模式,有三個值：fdatasync(默認且速度最快),O_DSYNC(san存儲和讀較多推薦使用),O_DIRECT(禁用了系統(tǒng)緩存,速度慢但最安全,raid卡+wb或原子寫FIO上效果好),現(xiàn)在大多數(shù) 新環(huán)境中,都用上了固態(tài)硬盤和raid卡,所以建議設置成O_DIRECT,避免使用固態(tài)硬盤而造成的雙緩存現(xiàn)象,減輕存儲數(shù)據(jù)的代價
innodb_flush_method = O_DIRECT
#開啟獨立的purge線程并設定有多少個,有助于提高DML多表操作的效率.以及減少innodb內(nèi)部的資源爭用.新版默認值為4,最大值為32,
#innodb_purge_threads = 8
#表示一次完成多少個undolog page,但這個值的副作用是會影響到undolog的釋放,因為總是在128輪purge后釋放undolog page.默認300,可以設置1-5000,設置少一些會加快undo回收.
#innodb_purge_batch_size = 1
#控制innodb內(nèi)部寫線程數(shù)量參數(shù),全局變量,只讀變量,新版默認值為4,最小值為1,最大值為64,受操作系統(tǒng)的aio-max-nr設置限制
#innodb_write_io_threads = 8
#控制innodb內(nèi)部讀線程數(shù)量參數(shù),全局變量,只讀變量,新版默認值為4,最小值為1,最大值為64
#innodb_read_io_threads = 8
#事務隔離級別,總共四種:READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE,默認是RR,一般而言,用RC也不會有大問題,畢竟有問題的幻讀也都是已經(jīng)提交的數(shù)據(jù),反而減輕鎖粒度.
transaction_isolation = READ-COMMITTED
#默認為0,即關(guān)閉,意為禁止使用非安全鎖,也即啟用間隙鎖功能.類似于將MySQL的事務隔離級別設定為READ-COMMITTED,但是這個參數(shù)必須重啟mysql程序來加載配置才生效,一般也不用設置,除非有特殊需求.不過也有如下特性:(1)對UPDATE或DELETE語句來說,InnoDB僅鎖定需要更新或刪除的行,對不能夠被WHERE條件匹配的行施加的鎖會在條件檢查后予以釋放.這可以有效地降低死鎖出現(xiàn)的概率；(2)執(zhí)行UPDATE語句時,如果某行已經(jīng)被其它語句鎖定,InnoDB會啟動一個“半一致性(semi-consistent)”讀操作從MySQL最近一次提交版本中獲得此行,并以之判定其是否能夠并當前UPDATE的WHERE條件所匹配.如果能夠匹配,MySQL會再次對其進行鎖定,而如果仍有其它鎖存在,則需要先等待它們退出.
#innodb_locks_unsafe_for_binlog = 1
#事務提交后通過fsync()寫到硬盤redo文件ib_logfile的頻率,0是存在內(nèi)存中到一定量再寫入(一般是1秒),1是立刻寫入(速度最慢但最安全,最多只丟一個事務),2是存到操作系統(tǒng)緩存由操作系統(tǒng)決定寫入時間(5.6.6之前是每隔1s刷盤,之后的版本是通過參數(shù)innodb_flush_log_at_timeout設置,默認也是1s.,但避免了mysql進程掛了就完全丟數(shù)據(jù)的問題),由于直接影響了磁盤IO,所以對數(shù)據(jù)庫性能影響較大.
innodb_flush_log_at_trx_commit = 1
#5.6.6引入,參數(shù)innodb_flush_log_at_trx_commit=1時,此超時參數(shù)不起作用,當innodb_flush_log_at_trx_commit=0/2時才起作用.表示每隔幾秒一次的頻率刷新redo log(在5.6.6之前是固定每秒一次刷新一次,5.6.6之后刷新頻率可以通過這個參數(shù)設置.)默認值是1S.如果你覺得磁盤壓力太大,那可以適當調(diào)大,但請注意數(shù)據(jù)安全性就降低了.
#innodb_flush_log_at_timeout = 5
#刷新binlog的頻率,0是由操作系統(tǒng)決定刷新時間,1每次事務都刷新一次(最慢但最安全,最多只丟一個事件),N(除0和1的其他數(shù)量,非負數(shù))每N個事務刷新一次,如果不支持group commit那么就表示n個event(所有事件,包含一般事務和alter/create等的隱式事務)刷新一次,如果是支持group commit,那么就表示攢夠n個event后binlog group才刷新一次,即支持group commit的性能更好,減少IO次數(shù),安全性也比0提高了一些,但是和1的選項相比,安全性還是略差,會丟n-1個事務,mysql5.6后基本完美支持了group commit.
sync_binlog = 1
#行格式定義,有Antelope類型(支持冗余,緊湊行格式)可以使用redundant和compact兩種行格式,Barracuda類型(支持數(shù)據(jù)壓縮,動態(tài)行格式,改善blob,text字段存儲布局)可以使用舊的redundant和compact,還可以用compressed和dynamic四種行格式,要想支持壓縮類型,必須先定義Barracuda.盡量不要用compressed行格式,因為innodb_buffer中的pages無法壓縮,只能壓縮保存到磁盤中的page,表定義中需要添加ROW_FORMAT=xxx行格式配合使用.默認值為antelope,建議使用默認的antelope文件格式和compact的行格式,適用于絕大多數(shù)場景,需要用到壓縮和不需要經(jīng)常修改和備份的表則選擇Barracuda更好一些.
#innodb_file_format = Barracuda
#允許索引使用動態(tài)壓縮,但是表的row_format必須是compressed或者dynamic.可以使索引列長度大于767bytes,但是總長度不能大于3072 bytes.
#innodb_large_prefix = 1
#開啟與關(guān)閉刷新鄰接頁功能,機械磁盤建議設置為1即啟用,對于SSD則建議設置為0即關(guān)閉,還可以設置為2,表示同樣刷新鄰接頁,但不要求在同一個區(qū)中的臟頁是連續(xù)的.
#innodb_flush_neighbors = 0
#實例級別內(nèi)存參數(shù),innodb緩存總量(包含一些其他緩存),默認值為128M,設置越大,innodb性能越高,一般單實例建議50%-70%,多實例建議20%左右,5.6版本前是只讀變量,只能重啟實例生效,5.7.x后可動態(tài)修改.
innodb_buffer_pool_size = 830M
#緩存池實例并發(fā)數(shù)量,減少內(nèi)部對緩存池數(shù)據(jù)結(jié)構(gòu)的爭用(緩存池的訪問在某些階段是互斥的)而提高并發(fā)性能,只在innodb_buffer_pool_size大于1G時生效,默認值在小于1G時為1,大于1G時為8,要保證innodb_buffer_pool_size/innodb_buffer_pool_instances大于128M,不然可能會有反效果,建議大于512M以上.在5.7最大值是64.
#innodb_buffer_pool_instances = 16
#設置 InnoDB 存儲的數(shù)據(jù)目錄信息和其它內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小.表越多需要分配越多的內(nèi)存.如果超出設定值,會在錯誤日志寫警告信息.默認值是 1MB.
#innodb_additional_mem_pool_size = 2M
#默認為關(guān)閉OFF.如果開啟該參數(shù),啟動MySQL服務時,MySQL將本地熱數(shù)據(jù)加載到InnoDB緩沖池中,按需求開啟
#innodb_buffer_pool_load_at_startup = 1
#默認為關(guān)閉OFF.在關(guān)閉時把熱數(shù)據(jù)dump到本地磁盤,和上面參數(shù)配合,按需求開啟.
#innodb_buffer_pool_dump_at_shutdown = 1
#當臟頁占了多少百分比后就開始刷新臟頁,寫得越多,應該設置越低(減少占用緩存),根據(jù)LSN來參考log sequence number,Log flushed up to的差距來設定,相差越遠就應該設的越低,默認值為75表示75%,最小值為0,最大值為99
#innodb_max_dirty_pages_pct = 50
#innodb_buffer_pool中old_block_sublist(舊數(shù)據(jù)鏈)最大占比,默認是8分3,也就是37%,取值范圍是5~95,如果讀多寫少可適當增加,反之減少.
#innodb_old_blocks_pct = 50
#將緩存池刷新到磁盤的臟頁數(shù)量,默認值為200,根據(jù)硬盤性能判定,機械硬盤1千左右,SSD硬盤4-5千左右,PCIE-SSD可以1-2萬,但不建議超過2萬
#innodb_io_capacity = 1000
#最大刷新到磁盤的臟頁數(shù)量,根據(jù)上面參數(shù)設定,但一定要比他多
#innodb_io_capacity_max = 2000
#控制Innodb雙寫緩沖的開關(guān)(雙寫緩沖是對連續(xù)磁盤空間的順序?qū)?減少隨機I/O),默認開啟,0是開啟,1是關(guān)閉,如果存儲設備支持原子寫,可以關(guān)閉,寫入速度增加明顯,其他情況建議開啟
#innodb_doublewrite = 1
#限制并發(fā)增刪改查的操作數(shù)量,當并發(fā)達到這個限制,額外的線程將被放置到隊列中,等待并發(fā)連接釋放,會睡眠數(shù)微秒,可以通過設定參數(shù)innodb_thread_sleep_delay來配置睡眠時間,期間不會占用cpu和io資源.默認為0(無限制),最大值為1000.大多數(shù)情況下不需要設置,但當并發(fā)太高,服務器支撐不起負載的時候,可以設置64~128,建議為CPU核心數(shù)X磁盤數(shù)量X2,實際上，在線程進入鎖等待以后，并發(fā)線程的計數(shù)也會減一，也就是說等行鎖（也包括間隙鎖）的線程是不算在 128 里面的。
#innodb_thread_concurrency = 128
#以前叫insert buffer,現(xiàn)在叫change buffer,因為現(xiàn)在設置的值有：inserts、deletes、purges、changes(inserts和deletes)、all(默認)、none.只能用在非唯一的索引上,嵌套在innodb_buffer_pool里面,一般不需要動
#innodb_change_buffering = all
#設置innodb_change_buffering占用innodb_buffer_pool的大小,默認是25%,最高只能是50%,5.6后支持,數(shù)據(jù)修改比較多的話,看需求設置
#innodb_change_buffer_max_size = 25
#在新版本mysql中,query_cache已失去意義,并且偶爾會造成query_cache鎖,增加額外開銷,建議直接關(guān)閉
query_cache_size=0
query_cache_type=0
#MDL元數(shù)據(jù)鎖的超時時間,類似(不只是)數(shù)據(jù)結(jié)構(gòu)ddl或dml等操作的鎖的等待時間,默認值為31536000秒(一年),設置這個參數(shù)主要是避免一些大表的DDL或DML操作長期鎖住表,如果表的數(shù)據(jù)量小默認就可以.
lock_wait_timeout = 600
#innodb事務請求行鎖的超時時間限制參數(shù),太多鎖等待是很糟糕的,而innodb有自旋鎖,等待時間太長也會耗費資源,單位是秒,最小可設置為1s,最大可設置1073741824秒(34年),默認是50s.
innodb_lock_wait_timeout = 300
#默認值為OFF,如果事務因為加鎖超時,會回滾上一條語句執(zhí)行的操作.如果設置ON,則整個事務都會回滾.注重事務一致性的需要開啟
innodb_rollback_on_timeout = 1
#當innodb線程獲取mutex資源失敗后,自旋鎖嘗試的次數(shù),默認是30.因為自旋鎖是占用cpu和內(nèi)存資源的,所以適當降低可以降低負載,但是也會提高查詢失敗的次數(shù).提高該值則相反,最好測試后再考慮修改.
#innodb_sync_spin_loops = 30
#定義InnoDB自旋操作的空閑循環(huán)轉(zhuǎn)數(shù),默認為6轉(zhuǎn).只要不為0,對性能變化影響不大,一般是配合innodb_sync_spin_loops來使用.
#innodb_spin_wait_delay = 6
#用來配置從服務器的更新是否寫入二進制日志,如果有層級從庫就必須開(注意server-id),沒有的話建議關(guān)閉,能有效提高性能.在GTID復制模式下,所有數(shù)據(jù)庫都要開啟log_slave_updates參數(shù),是因為為了方便快速同步數(shù)據(jù),如果涉及到切換,那么可以快速地把二進制日志進行同步(因為所有服務器都有記錄完整的binlog)
log_slave_updates = 1
#當slave從庫宕機后,假如relay-log損壞了,導致一部分中繼日志沒有處理,則自動放棄所有未執(zhí)行的relay-log,并且重新從master上獲取日志,這樣就保證了relay-log的完整性.默認情況下該功能是關(guān)閉的,建議開啟,可以有效保證數(shù)據(jù)一致性.
relay_log_recovery = 1
#設置數(shù)據(jù)庫為只讀狀態(tài),普通用戶不可以寫入/更改數(shù)據(jù)(管理員還是可以寫),默認關(guān)閉(即0),一般從庫最好設置,避免不必要的誤操作,主庫就當然不要動他
#read_only=1
#5.7引入的新參數(shù),以前的read_only不能限制super用戶(例如root),而這個參數(shù)可以.
#super_read_only=1
#5.7引入的新參數(shù),開啟后之后,執(zhí)行的SQL只能是只讀
#tx_read_only=1
#從庫relay log執(zhí)行完后的自動清除開關(guān),默認開啟(即1),某些情況適用關(guān)閉(即0),只作用于從庫
#relay_log_purge=0
#臨時表容量緩存,會話級別內(nèi)存緩存,超過之后臨時表會轉(zhuǎn)到磁盤上,磁盤上的臨時表引擎默認是myisam,內(nèi)存的臨時表引擎是memory,均不可讀寫.由于復雜查詢往往都有用到臨時表并且這個參數(shù)是會話級別的,如果設置太大可能容易造成OOM,如果有大的查詢,可以對當前session設置較大的值就可以了,如果通過show global status看到Created_tmp_disk_tables的值很大,可以套用這個公式:Created_tmp_disk_tables/(Created_tmp_disk_tables+Created_tmp_tables)*100% > 10%,確認是否需要增加.
tmp_table_size=64M
#臨時表行數(shù)緩存,會話級別內(nèi)存緩存,和tmp_table_size一起限制臨時表大小,可以創(chuàng)建的內(nèi)存表(memory table)的最大行數(shù),一般默認16M就足夠.如果有大的查詢,可以對當前session設置較大的值就可以了,如果數(shù)據(jù)量很大,最好調(diào)大.
#max_heap_table_size=96M
#如果臨時表實在太大,磁盤空間壓力增大,則需要考慮設置臨時表存儲位置,默認是/tmp
#tmpdir=/tmp
#數(shù)據(jù)庫允許的最大連接數(shù)限制,建議設置成最高可負載連接數(shù)的80%,不然可能卡死,默認是151.如果status變量Connection_errors_max_connections不為零并一直在增長,就說明被這個參數(shù)限制而連接失敗的用戶很多,應該考慮增大這個參數(shù)的值,另外這個參數(shù)也受操作系統(tǒng)的open-files-limit設置限制,而mysql的最大連接數(shù)其實是：max_connections+1
max_connections=1024
#每個獨立數(shù)據(jù)庫用戶的最大連接數(shù),默認是0,表示沒限制,設置這個可以防止最大連接數(shù)max_connections滿了,導致管理用戶(root等)都連不上的情況
max_user_connections=256
#可以重新利用保存在緩存中線程的數(shù)量,當線程斷開后不會立刻關(guān)閉,會保存到緩存中等待被再利用,默認值為-1,表示自動計算:8+(max_connections/100).并發(fā)低此參數(shù)效果不明顯,并發(fā)高調(diào)大效果明顯,當公式Threads_created/Connections狀態(tài)變量比值越接近1,說明線程的命中率越低,就應該考慮增加這個參數(shù)的值
#thread_cache_size = 51
#會話級別內(nèi)存緩存,聯(lián)結(jié)查詢緩存,默認值為256K,最小值為128字節(jié),join比較多就調(diào)大,也是要注意oom,如果有大的查詢,可以對當前session設置較大的值就可以了.
join_buffer_size = 128M
#開啟慢查詢參數(shù),0關(guān)閉,1開啟,默認關(guān)閉,可動態(tài)修改.
#slow_query_log = 1
#設置慢查詢存儲路徑
slow_query_log_file = /tmp/slow_querys.log
#慢查詢記錄時間,5.5之后可以設置到少于1秒,建議設置到0.1秒
long_query_time = 0.1
#用來設置是否記錄沒有使用索引的查詢到慢查詢記錄,默認關(guān)閉,看需求開啟,會產(chǎn)生很多日志,可動態(tài)修改.
#log_queries_not_using_indexes = 1
#管理指令也會被記錄到慢查詢.比如OPTIMEZE TABLE, ALTER TABLE,默認關(guān)閉,看需求開啟,會產(chǎn)生很多日志,可動態(tài)修改.
#log_slow_admin_statements = 1
#是否把從庫執(zhí)行慢的語句也計入從庫的慢查詢?nèi)罩局?看需求開啟,會產(chǎn)生很多日志,可動態(tài)修改.
#log_slow_slave_statements? = 1
#binlog會話級別內(nèi)存緩存,事務沒有提交的時候,產(chǎn)生的日志記錄到Cache中；等到事務需要提交的時候,則把日志持久化到磁盤.
binlog_cache_size = 4M
#最大binlog日志的緩存區(qū)大小,所有會話的binlog_cache_size總數(shù)不能超過這個值,默認就是最大值,在32位的系統(tǒng)中是4G,64位的是16P,設置它是防止mysql內(nèi)存溢出
#max_binlog_cache_size = 4G
#binlog二進制日志寫入給定值,默認值是1GB,最大不能設置大于1G,根據(jù)實際產(chǎn)生情況設置,文件太大不利于打開,但是如果你正使用大的事務,二進制日志還是會超過限制
max_binlog_size = 500M
#限制binlog空間大小,默認為0(即無限制),如果你正使用大的事務,二進制日志還是會超過max_binlog_size限制,而最大值則受這個參數(shù)限制,可以防止磁盤被撐爆.
#relay_log_space_limit=16G
#binlog保存天數(shù),過期自動刪除,默認值為0(代表不會自動清理binlog)
expire_logs_days=7
#當binlog_format=row時控制是否記錄用戶原生SQL的參數(shù)到binlog file中,默認關(guān)閉
#binlog_rows_query_log_events = 1
#打開、關(guān)閉普通查詢?nèi)罩竟δ?默認關(guān)閉,如果設置為1或者ON,則數(shù)據(jù)庫所有的SQL都會被記錄下來,一般會增長很快,也會浪費數(shù)據(jù)庫5%-10%的性能.
#general_log = 1
#設置查詢?nèi)罩韭窂胶臀募?如果不設置,則默認在datadir目錄下命名為hostname.log,和上面參數(shù)結(jié)合使用.
#general_log_file = /tmp/general_querys.log
#指定查詢?nèi)罩緂eneral_log和慢查詢?nèi)罩緎low_log存到數(shù)據(jù)庫的表中,分別是mysql.general_log和mysql.slow_log,默認是FILE,即文件中,按實際情況設置.
#log_output=TABLE
#5.6新功能,從ibdata1分離undo文件,開啟并指定有多少個undo log文件,默認為0,即不開啟該功能.mysql8.0默認開啟并設置成2
innodb_undo_tablespaces = 4
#指定存放undo的目錄,默認是當前數(shù)據(jù)目錄(datadir參數(shù)指定的目錄),出于減輕磁盤壓力的思維下,可以考慮獨立出去
#innodb_undo_directory = /tmp/
#指定undo回滾段大小,至少大于等于35,默認128,一般情況下也夠了,mysql8.0取消了這個參數(shù)
#innodb_undo_logs = 256
#開啟在線回收(收縮)undo log日志文件,支持動態(tài)設置.
innodb_undo_log_truncate =1
#當undo超過這個閥值(默認是1G),會觸發(fā)truncate回收(收縮)動作,truncate后空間縮小到10M,視乎你硬盤容量而定.
#innodb_max_undo_log_size = 2G
#控制回收(收縮)undo log的頻率.undo log空間在它的回滾段沒有得到釋放之前不會收縮,想要增加釋放回滾區(qū)間的頻率,就得降低這個參數(shù)值,默認128.
#innodb_purge_rseg_truncate_frequency = 64
#mysql5.6后參數(shù),開啟后,會將所有deadlock記錄在error_log中,作為dba,你應該需要知道有多少死鎖信息
innodb_print_all_deadlocks = 1
#在5.6.2之前,slave記錄的master信息以及slave應用binlog的信息存放在文件中,即master.info與relay-log.info.在5.6.2版本之后,允許記錄到table中,多源復制,這個必須是table
master_info_repository = TABLE
#同上信息,多源復制,這個必須是table
relay_log_info_repository = TABLE
#GTID模式,5.6新功能,新的復制方式,需要就打開,建議binlog改成row
gtid_mode = on
#強制GTID的一致性,一般和上面參數(shù)一起使用,但是開啟后只允許能夠保障事務安全,并且能夠被日志記錄的SQL語句被執(zhí)行,像create table ... select 和 create temporarytable語句,以及同時更新事務表和非事務表的SQL語句或事務都不允許執(zhí)行.
enforce_gtid_consistency = 1
#關(guān)閉numa功能,5.6.27新參數(shù),默認為0,即開啟,我們要設成1,即關(guān)閉這個功能.numa是linux系統(tǒng)為了合理分配多核環(huán)境下內(nèi)存空間而存在的功能,但是對于大內(nèi)存應用反而造成了性能瓶頸,所以對于數(shù)據(jù)庫類別應用來說,應當要關(guān)閉.
#innodb_numa_interleave = 1
#用于從flush隊列中取事務的超時時間,這主要是防止并發(fā)事務過高,導致某些事務的RT上升,單位微秒,默認是0.
#binlog_max_flush_queue_time = 0
#默認為開啟on(即1),事務將以二進制日志的順序相同的順序進行寫入,當設置為關(guān)閉off(即0)時,事務可能以和binlog不相同的順序被提交,高并發(fā)下會有一些性能的提升
#binlog_order_commits = 1
#innodb存儲引擎恢復級別,可選0-6,默認是0,當數(shù)據(jù)庫或表不能正常使用時才可能需要用到
#innodb_force_recovery = 0
#5.7.15新功能,關(guān)閉死鎖監(jiān)測,正常情況下死鎖監(jiān)測只占少部分資源,所以默認開啟也不需要理會.在高并發(fā)情況下則相反,會造成額外的開銷,關(guān)閉他就能提高并發(fā),但是出現(xiàn)死鎖就不會回滾,會一直鎖等待,所以關(guān)閉之后就需要調(diào)小innodb_lock_wait_timeout的值來減少鎖等待時間,避免鎖等待太長時間,代碼層面也需要做好這個報錯的返回規(guī)則
#innodb_deadlock_detect=off
#percona特有功能,其他版本暫時沒有,限制所有事務的空閑時間,例如大量JDBC的長連接,特別有用,設置秒數(shù)即啟用,默認不設置即沒限制.
#innodb_kill_idle_transaction = 30
#數(shù)據(jù)導入/導出到文件的限制(load data和SELECT...into outfile),屬于安全限制的一種,新版本默認是null,即禁止.可以輸入文件夾名字,表示限制導入/導出文件夾,舊版本默認值為空,即完全不限制出導入/導出
#secure_file_priv = /tmp
#影響mysql選擇排序的算法,適當調(diào)大有助于優(yōu)化sql執(zhí)行效率,mysql有兩種文件排序算法(雙路排序和單路排序),如果需要排序的列的總大小加上orderby列的大小超過了這個參數(shù)定義的字節(jié),mysql就會使用雙路排序,當包含text、blob列時,也會使用雙路排序,雙路排序的開銷可能會非常巨大,因為他會讀取表兩次,第二次讀取會引發(fā)大量的隨機IO,對于myisam來說這個代價尤其昂貴,myisam表利用系統(tǒng)調(diào)用去提取每行的數(shù)據(jù).單路排序效率更快一些,避免了第二次讀取數(shù)據(jù).并且把隨機IO變成了順序IO,但是它會使用更多的內(nèi)存空間,因為它把排序需要的所有列都一次性導出來保存在內(nèi)存中
#max_length_for_sort_data = 4096
#設置從庫SQL線程并行重放events(事務)的worker線程數(shù)量,默認值為0,最大值為1024.在5.6.x版本中設置這個參數(shù)大于0時,則SQL線程充當worker線程的協(xié)調(diào)者,在多個worker線程之間分發(fā)基于庫級別的events,也就是庫級別并行復制,5.7.x版本的并行復制可以設置基于組提交事務的并行復制,更加好用.
#slave_parallel_workers = 8
#5.7新參數(shù),指定并行復制方式,但為了兼容5.6只支持庫模式,默認的并行復制方式是基于庫級別,即默認值是：DATABASE,我們要設置成5.7支持的基于組提交事務的并行復制方式,則需要設置成：LOGICAL_CLOCK,速度更快.
#slave_parallel_type = LOGICAL_CLOCK
#默認是0即關(guān)閉,當slave_parallel_type設置為LOGICAL_CLOCK的時候使用,1為開啟,控制Slave上的binlog提交順序和Master上的binlog的提交順序一樣,保證GTID的順序.因為在slave上應用事務的順序是無序的,和relay log中記錄的事務順序不一樣,這樣數(shù)據(jù)一致性是無法保證的.5.7.19前即使設成1也有可能有問題,所以需要使用這個參數(shù)的話請更新到5.7.19.
#slave_preserve_commit_order = 1
#binlog提交后等待延遲多少時間再同步到磁盤,單位是微秒,默認0,不延遲.設置延遲可以讓多個事務在用一時刻提交,提高binlog組提交的并發(fā)數(shù)和效率,從而提高slave的吞吐量.建議默認
#binlog_group_commit_sync_delay = 100
#在等待上面參數(shù)超時之前,如果有足夠多的事務,則停止等待直接提交.單位是事務數(shù),默認0.建議默認
#binlog_group_commit_sync_no_delay_count = 100
#5.6.13之后引入的參數(shù),控制stop slave 的執(zhí)行時間,假設重放大事務的時候,突然執(zhí)行stop slave,會執(zhí)行很久,甚至可能產(chǎn)生死鎖或阻塞,嚴重影響性能.默認值是31536000秒=1年.
#rpl_stop_slave_timeout=300
#判斷主庫是否掛掉的超時設置,在設定時間內(nèi)依然沒有獲取到Master的回應就認為Master已經(jīng)掛掉了,后續(xù)根據(jù)超時重連參數(shù)設置進行重連主庫的操作.默認值：3600S
#slave_net_timeout=300
#自動處理同步復制錯誤,默認是STRICT嚴格模式,IDEMPOTENT是冪等模式,它跳過了duplicate-key(1062)和no-key-found(1032)等錯誤,該模式只有在ROW的binlog模式下生效,在STATEMENT的binlog模式下無效,和slave_skip_errors的作用是一樣的,但是slave_skip_errors不支持動態(tài)修改,必須重啟mysql才能生效,因此建議使用slave_exec_mode.正常情況下,應該是從庫遇到錯誤就停止復制,然后人工去處理數(shù)據(jù)一致性問題,第一時間去想怎么滿足這個復制,而不是去跳過這個事務.
#slave_exec_mode=IDEMPOTENT
#半同步主庫開關(guān),使用前需要先確認有沒有加載半同步插件
#rpl_semi_sync_master_enabled = 1
#當確認半同步開啟之后,控制主庫使用半同步復制機制把binlog發(fā)送到slave之后,等待從庫ACK接收確認包的時間,超過這個時間會自動轉(zhuǎn)為異步,直到從庫重新上線
#rpl_semi_sync_master_timeout = 3000
#5.7.3新加的半同步參數(shù),至少有N個slave接收到日志,然后返回ack,這個半同步事務才能提交,默認是1.當這個值設置到和從庫數(shù)量相等的話,則效果會等同于全同步復制.
#rpl_semi_sync_master_wait_for_slave_count = 2
#5.7新參數(shù),控制半同步模式下 主庫在返回給會話事務成功之前提交事務的方式.舊模式是AFTER_COMMIT,新模式是AFTER_SYNC,默認值：AFTER_SYNC .master 將每個事務寫入binlog ,傳遞到slave,并且刷新到磁盤.master等待slave 反饋接收到事務并刷新到磁盤.一旦接到slave反饋,master在主庫提交事務并且返回結(jié)果給會話. 在AFTER_SYNC模式下,所有的客戶端在同一時刻查看已經(jīng)提交的數(shù)據(jù).假如發(fā)生主庫crash,所有在主庫上已經(jīng)提交的事務已經(jīng)同步到slave并記錄到relay log.此時切換到從庫,可以保障最小的數(shù)據(jù)損失.
#rpl_semi_sync_master_wait_point = AFTER_SYNC
#默認為on,即當從庫降到0個的時候,主庫依然等待從庫發(fā)送ack確認包.改為off之后,即從庫降到0個后,主庫不再等待從庫,直接轉(zhuǎn)為異步.
#rpl_semi_sync_master_wait_no_slave=OFF
#半同步從庫開關(guān),使用前需要先確認有沒有加載半同步插件
#rpl_semi_sync_slave_enabled = 1
#5.7.5新參數(shù),定義磁盤臨時表的引擎類型,默認是InnoDB.之前的版本中只能使用MyISAM(就算是新版的5.6也是)
#internal_tmp_disk_storage_engine = InnoDB
#5.6.3新參數(shù),控制CREATE TEMPORARY TABLE 創(chuàng)建的臨時表的引擎類型,在以前默認是MEMORY,
#default_tmp_storage_engine = InnoDB
#控制主從復制的同步進程是否隨mysql程序啟動而啟動,默認是0(即隨mysql進程一起啟動),1為關(guān)閉.關(guān)閉之后從庫重啟后會出現(xiàn)雙no狀態(tài),對于集群有一定意義.
#skip_slave_start=1
#只記錄參數(shù)指定的數(shù)據(jù)庫的binlog,通常用于主從復制環(huán)境的主庫設置,有一定隱患,通常不會這樣用
#binlog_do_db=work,test
#忽略參數(shù)指定的數(shù)據(jù)庫的binlog,通常用于主從復制環(huán)境的主庫設置,有一定隱患,通常不會這樣用
#binlog_ignore_db=mysql,test
#從庫同步選擇參數(shù),只執(zhí)行某個庫或某個表的同步語句,其他庫和表都不處理,不能單行多選,有多一個庫的需求就要多寫一行配置
#replicate_wild_do_table=test.%
#從庫同步選擇參數(shù),忽略掉某個庫或某個表的同步語句,其他庫和表的都會執(zhí)行,不能單行多選,有多一個庫的需求就要多寫一行配置
#replicate_wild_ignore_table=mysql.%
#5.7.8被添加,單位為ms,動態(tài)參數(shù),默認為0.控制查詢的最長時間,默認是0,即無限制,但暫時也只能控制select,看你對數(shù)據(jù)庫性能要求多高而定,設置較短時間等同于禁用復雜查詢.
#max_execution_time = 300000
#在多線程復制時,在隊列中Pending的事件所占用的最大內(nèi)存,默認為16M,如果內(nèi)存富余,或者延遲較大時,可以適當調(diào)大;這個值要比主庫的max_allowed_packet大
#slave_pending_jobs_size_max = 128M
#5.6.6新參數(shù),默認為0即關(guān)閉,1為開啟,舊的時間格式timestamp有非標準行為,未聲明為null的話,會被分配為notnull屬性,而向此列插入null時將自動轉(zhuǎn)換為時間戳,或者是"0000-00-00 00:00:00"這樣的數(shù)據(jù),這個參數(shù)的作用就是關(guān)閉這種非標準行為,讓他可以插入null,當徹底放棄timestamp后,這個參數(shù)也會被放棄,建議多用datatime
#explicit_defaults_for_timestamp=1
#限制了同一時間在mysqld上所有session中prepared 語句的上限.它的取值范圍為“0 - 1048576”,默認為16382.通常這個值夠用,因為沒有那么多這種需求,但是超出這個值的prepare語句就會報Can't create more than max_prepared_stmt_count statements (current value: 16382)錯誤.改大一些就好
#max_prepared_stmt_count=100000
#MySQL5.6新參數(shù),可以設置Innodb數(shù)據(jù)頁為8K,4K,32K,64K來適應更多的場景,一般默認16K即可.這個參數(shù)在一開始初始化時就要加入my.cnf里,如果已經(jīng)創(chuàng)建了表,再修改,啟動MySQL會報錯.一般情況下我們會建議一頁最好存儲兩行數(shù)據(jù),但是有些情況數(shù)據(jù)很大或很小,就會浪費或者是溢出數(shù)據(jù)頁,造成行遷移,更改這個參數(shù)可以適當避免.
#innodb_page_size = 8k
#認證加密方式,一般不用設置,默認是:mysql_native_password,在5.7.2之后可以設置sha256_password來使得更加安全,在mysql8.0之后默認是:caching_sha2_password,會讓舊版本的mysql連不上.修改之后只對新授權(quán)用戶生效.
#default_authentication_plugin=mysql_native_password
#開啟performance_schema性能統(tǒng)計庫功能,5.7之后默認開啟,5.6之前默認關(guān)閉,注意：該參數(shù)為只讀參數(shù),需要在實例啟動之前設置才生效.如果開啟失敗就要去錯誤日志進行排查.
#performance_schema = ON

[mysqldump]
quick
#同上,sql太長用mysqldump受此參數(shù)影響
max_allowed_packet = 128M
[mysql]
no-auto-rehash
prompt="\u@\h \R:\m:\s [\d]> "
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout = 28800