調(diào)優(yōu)參數(shù)詳情
| 參數(shù)選項(xiàng) | 釋義 | 默認(rèn)參數(shù) | 是否支持熱更新(dynamic) | 策略 | 注意事項(xiàng) | 推薦組合 |
|---|---|---|---|---|---|---|
| innodb_buffer_pool_size | 數(shù)據(jù)緩存，索引緩存，更改數(shù)據(jù)的緩沖薯鼠，存儲(chǔ)內(nèi)部結(jié)構(gòu) | 128M | Yes | MEM*70% | ||
| innodb_flush_log_at_trx_commit | InnoDB依靠重做日志來保證數(shù)據(jù)能在丟失后進(jìn)行恢復(fù)丁眼，該參數(shù)控制重做日志寫入磁盤的過程 | 1 | Yes | 0：log buffer將每秒一次地寫入log file中蛋褥，并且log file的flush(刷到磁盤)操作同時(shí)進(jìn)行钳降。 1：每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫入log file，并且flush(刷到磁盤)中去。 2：每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫入log file碑韵，但是flush(刷到磁盤)操作并不會(huì)同時(shí)進(jìn)行。該模式下缎脾，MySQL會(huì)每秒執(zhí)行一次 flush(刷到磁盤)操作 |
設(shè)置為0祝闻，該模式速度最快，但不太安全遗菠，mysqld進(jìn)程的崩潰會(huì)導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失联喘。 設(shè)置為1，該模式是最安全的舷蒲，但也是最慢的一種方式耸袜。在mysqld 服務(wù)崩潰或者服務(wù)器主機(jī)crash的情況下，binary log 只有可能丟失最多一個(gè)語句或者一個(gè)事務(wù)牲平。 設(shè)置為2堤框，該模式速度較快，也比0安全纵柿，只有在操作系統(tǒng)崩潰或者系統(tǒng)斷電的情況下蜈抓，上一秒鐘所有事務(wù)數(shù)據(jù)才可能丟失。 |
innodb_flush_log_at_trx_commit=2 + sync_binlog=100或者0 |
| sync_binlog | 控制數(shù)據(jù)庫的binlog刷到磁盤上 | 1 | YES | 為0昂儒，由文件系統(tǒng)自己控制它的緩存的刷新沟使。這時(shí)候的性能是最好的。 為1渊跋，每次事務(wù)提交腊嗡，MySQL都會(huì)把binlog刷下去着倾。 大于1，每($sync_binlog)次事務(wù)提交燕少，MySQL調(diào)用文件系統(tǒng)的刷新操作將緩存刷下去 (犧牲一定的一致性卡者，可以獲得更高的并發(fā)和性能) |
為0，風(fēng)險(xiǎn)也是最大的客们。因?yàn)橐坏┫到y(tǒng)Crash崇决，在binlog_cache中的所有binlog信息都會(huì)被丟失。 為1底挫，性能損耗最大恒傻，一旦系統(tǒng)crash，系統(tǒng)才有可能丟失1個(gè)事務(wù)的數(shù)據(jù)建邓。 大于1盈厘，刷新的頻率過高對(duì)IO的影響也非常大 |
|
| wait_timeout | 設(shè)置睡眠連接超時(shí)秒數(shù)，如果某個(gè)連接超時(shí)官边，會(huì)被mysql自然終止 | 28800(秒) | Yes | 查詢show processlist; 以大多數(shù)sleep進(jìn)程TIME為標(biāo)準(zhǔn)設(shè)值 |
過大有弊端扑庞，導(dǎo)致MySQL里大量的SLEEP進(jìn)程無法及時(shí)釋放，拖累系統(tǒng)性能 不能設(shè)置過小拒逮，導(dǎo)致“MySQL has gone away”之類的問題 |
|
|
innodb_log_file_size innodb_log_files_in_group |
InnoDB 存儲(chǔ)引擎使用一個(gè)指定大小的Redo log空間，Redo log的空間通過innodb_log_file_size和innodb_log_files_in_group來調(diào)節(jié)臀规。將這倆參數(shù)相乘即可得到總的可用Redo log 空間 | 48M 2 |
No No |
配置的Redo空間越大滩援，InnoDB就能更好的優(yōu)化寫操作。 | 當(dāng)出現(xiàn)崩潰或掉電等意外時(shí)塔嬉，增大Redo空間也意味著更長(zhǎng)的恢復(fù)時(shí)間玩徊。 | |
| innodb_log_buffer_size | 數(shù)據(jù)寫入到內(nèi)存中的日志緩存中，由于InnoDB在事務(wù)提交前谨究，并不將改變的日志寫入到磁盤中恩袱，因此在大事務(wù)中，可以減輕磁盤I/O的壓力胶哲。 | 16M | No | 通常情況下畔塔，4MB-8MB已經(jīng)足夠 | 當(dāng)mysql服務(wù)掛掉時(shí)，數(shù)值越大鸯屿，則恢復(fù)數(shù)據(jù)需要越久 | 對(duì)于比較小innodb_buffer_pool_size澈吨，建議是設(shè)置為一樣大 innodb_buffer_pool_size + innodb_log_buffer_size |
| innodb_file_per_table | 默認(rèn)開啟InnoDB為獨(dú)立表空間模式，每個(gè)數(shù)據(jù)庫的每個(gè)表都會(huì)生成一個(gè)數(shù)據(jù)空間 | 1 | Yes | 默認(rèn)開啟寄摆，單表在不同的數(shù)據(jù)庫中移動(dòng)谅辣，空間可回收，而且不管怎么刪除婶恼，表空間的碎片不會(huì)太嚴(yán)重的影響性能桑阶，而且還有機(jī)會(huì)處理柏副。 不開啟，會(huì)降低文件系統(tǒng)操作的性能開銷蚣录。適合于將整個(gè)磁盤都用于存儲(chǔ)mysql數(shù)據(jù)的情況割择。 |
默認(rèn)開啟時(shí)，應(yīng)避免單表增加過大包归。 不開啟時(shí)锨推，應(yīng)避免在空間受限的系統(tǒng)表空間里導(dǎo)入大量臨時(shí)數(shù)據(jù)。 |
默認(rèn)開啟 |
| innodb_lock_wait_timeout | 事務(wù)等待獲取資源等待的最長(zhǎng)時(shí)間公壤，超過這個(gè)時(shí)間還未分配到資源則會(huì)返回應(yīng)用失敗 | 50 | Yes | 最小可設(shè)置為1s换可，最大可設(shè)置1073741824秒以上再大就會(huì)被截?cái)嗔?/td> | 如果事務(wù)開始前部分有其他操作而中途遇到鎖等待超時(shí)則mysql端還需要回滾，如果頻繁出現(xiàn)厦幅，會(huì)增加DB消耗 | 根據(jù)業(yè)務(wù)量級(jí)沾鳄，對(duì)于數(shù)值進(jìn)行適當(dāng)?shù)慕档突蛘呱摺?/td> |
| innodb_purge_threads | 將purge線程從master線程分離出來，提高cpu使用率提升存儲(chǔ)引擎性能确憨，最大值為32 | 4 | No | 可以指定多個(gè)innodb_purge_threads來進(jìn)一步加快和提高undo（還原段）回收速度 | ||
|
innodb_thread_concurrency innodb_commit_concurrency |
默認(rèn)是0译荞，則表示沒有并發(fā)線程數(shù)限制，所有請(qǐng)求都會(huì)直接請(qǐng)求線程執(zhí)行休弃。 同樣控制了多線程并發(fā)提交的數(shù)量吞歼。 |
0 0 |
Yes Yes |
如果數(shù)據(jù)庫沒有出現(xiàn)性能問題，且當(dāng)并發(fā)用戶線程數(shù)量小于64塔猾，建議設(shè)置使用默認(rèn)值就好篙骡。 如果負(fù)載不穩(wěn)定，時(shí)而低丈甸，時(shí)而高到峰值糯俗，建議先設(shè)置為128，并通過不斷的降低這個(gè)參數(shù)睦擂，直到發(fā)現(xiàn)能夠提供最佳性能的線程數(shù) |
兩者的修改相輔相成得湘，單純調(diào)高innodb_thread_concurrency的數(shù)值，會(huì)造成大量線程阻塞顿仇。 設(shè)置過高值淘正，可能會(huì)因?yàn)橄到y(tǒng)資源內(nèi)部爭(zhēng)奪導(dǎo)致性能下降； 定期監(jiān)控和分析DB臼闻，因?yàn)殡S著數(shù)據(jù)庫負(fù)載的變化跪帝，業(yè)務(wù)的增加，參數(shù)需要?jiǎng)討B(tài)的調(diào)整些阅。 |
在大多數(shù)情況下伞剑，最佳的值是小于并接近虛擬CPU的個(gè)數(shù) + innodb_concurrency_tickets（5000） + innodb_commit_concurrency |
|
innodb_write_io_threads innodb_read_io_threads |
IO Thread 的主要工作是要負(fù)責(zé)這些IO請(qǐng)求的回調(diào)（call back）處理 | 4 4 |
No No |
根據(jù)CPU核數(shù)來更改相應(yīng)的參數(shù)值，更有效的利用cpu性能市埋。 允許值的范圍是1～64 根據(jù)業(yè)務(wù)量級(jí)的不同黎泣，讀與寫的參數(shù)設(shè)置可以有微小差距恕刘。 |
||
| innodb_io_capacity | 緩沖池沖洗頁和合并從所述插入緩沖數(shù)據(jù)執(zhí)行的I / O活動(dòng)的上限 | 200 | Yes | 服務(wù)器的磁盤是5400 RPM ~7200 RPM，屬于比較低級(jí)的磁盤抒倚，根據(jù)MySQL 官方的建議褐着，應(yīng)該將innodb_io_capacity降低到100. 如果具有大量IOPS的快速驅(qū)動(dòng)器，可是適當(dāng)提高該值 |
設(shè)置過大托呕，則會(huì)造成MySQL高估了磁盤的能力含蓉，導(dǎo)致臟頁堆積。 設(shè)置過低项郊，則會(huì)出現(xiàn)MySQL低估了磁盤的能力馅扣，使得數(shù)據(jù)庫能夠單位時(shí)間內(nèi)提交的事務(wù)數(shù)(tps)降低 |
采用SSD云盤,確認(rèn)支持的IOPS數(shù)量 |
| innodb_buffer_pool_instances | 允許多個(gè)緩沖池實(shí)例，每頁根據(jù)哈希平均分配到不同緩沖池實(shí)例中着降，減少數(shù)據(jù)庫內(nèi)部資源競(jìng)爭(zhēng)差油，增加數(shù)據(jù)庫并發(fā)處理能力 | 1 | No | 在buffer_pool不大時(shí)，該值越大（低于10）性能越優(yōu)任洞。使用大的 buffer_pool 時(shí)蓄喇，為1時(shí) 的表現(xiàn)最棒 | 適用于多核處理器，依業(yè)務(wù)量進(jìn)行調(diào)整交掏。 | |
| innodb_change_buffer_max_size | change buffer在buffer pool中的最大占比妆偏，默認(rèn)25%，最大可設(shè)置為50% | 25 | Yes | 如果系統(tǒng)中有嚴(yán)重的insert盅弛、update并且還有活躍的delete時(shí)楼眷，就增大。 針對(duì)不更改數(shù)據(jù)的純報(bào)表系統(tǒng)熊尉，可以減小該參數(shù)值 |
性能測(cè)試腳本實(shí)例
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
import pymysql
import time
create_table_sql = """
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE,
nickname VARCHAR(255) NOT NULL
)
"""
insert_table_sql = """
INSERT INTO users(username, nickname)
VALUES(%s,%s)
"""
conn = pymysql.connect(host='127.0.0.1', user='root',
passwd='123456', db='sbtest', port=4408, charset='utf8')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS users')
cur.execute(create_table_sql)
conn.commit()
print('開始時(shí)間: '+ time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
start = time.time()
failed = 0 # 插入失敗條數(shù)
count = 10000 # 插入1W條數(shù)據(jù)
for i in range(1, count + 1):
username = 'username_' + str(i)
nickname = 'nickname_' + str(i)
try:
cur.execute(insert_table_sql, (username, nickname))
conn.commit()
except:
print('failed')
end = time.time()
print('結(jié)束時(shí)間: '+ time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
print('消耗秒數(shù): ' + str(int(end - start)))
print('失敗條數(shù): ' + str(failed)
