調(diào)優(yōu)參數(shù)詳情
| 參數(shù)選項 | 釋義 | 默認參數(shù) | 是否支持熱更新(dynamic) | 策略 | 注意事項 | 推薦組合 |
|---|---|---|---|---|---|---|
| innodb_buffer_pool_size | 數(shù)據(jù)緩存，索引緩存，更改數(shù)據(jù)的緩沖，存儲內(nèi)部結(jié)構(gòu) | 128M | Yes | MEM*70% | ||
| innodb_flush_log_at_trx_commit | InnoDB依靠重做日志來保證數(shù)據(jù)能在丟失后進行恢復(fù)，該參數(shù)控制重做日志寫入磁盤的過程 | 1 | Yes | 0：log buffer將每秒一次地寫入log file中，并且log file的flush(刷到磁盤)操作同時進行。 1：每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file，并且flush(刷到磁盤)中去。 2：每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file，但是flush(刷到磁盤)操作并不會同時進行。該模式下，MySQL會每秒執(zhí)行一次 flush(刷到磁盤)操作 |
設(shè)置為0，該模式速度最快，但不太安全，mysqld進程的崩潰會導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失。 設(shè)置為1，該模式是最安全的，但也是最慢的一種方式。在mysqld 服務(wù)崩潰或者服務(wù)器主機crash的情況下，binary log 只有可能丟失最多一個語句或者一個事務(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)自己控制它的緩存的刷新。這時候的性能是最好的。 為1，每次事務(wù)提交，MySQL都會把binlog刷下去。 大于1，每($sync_binlog)次事務(wù)提交，MySQL調(diào)用文件系統(tǒng)的刷新操作將緩存刷下去 (犧牲一定的一致性，可以獲得更高的并發(fā)和性能) |
為0，風險也是最大的。因為一旦系統(tǒng)Crash，在binlog_cache中的所有binlog信息都會被丟失。 為1，性能損耗最大，一旦系統(tǒng)crash，系統(tǒng)才有可能丟失1個事務(wù)的數(shù)據(jù)。 大于1，刷新的頻率過高對IO的影響也非常大 |
|
| wait_timeout | 設(shè)置睡眠連接超時秒數(shù)，如果某個連接超時，會被mysql自然終止 | 28800(秒) | Yes | 查詢show processlist; 以大多數(shù)sleep進程TIME為標準設(shè)值 |
過大有弊端，導(dǎo)致MySQL里大量的SLEEP進程無法及時釋放，拖累系統(tǒng)性能 不能設(shè)置過小，導(dǎo)致“MySQL has gone away”之類的問題 |
|
|
innodb_log_file_size innodb_log_files_in_group |
InnoDB 存儲引擎使用一個指定大小的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)化寫操作。 | 當出現(xiàn)崩潰或掉電等意外時，增大Redo空間也意味著更長的恢復(fù)時間。 | |
| innodb_log_buffer_size | 數(shù)據(jù)寫入到內(nèi)存中的日志緩存中，由于InnoDB在事務(wù)提交前，并不將改變的日志寫入到磁盤中，因此在大事務(wù)中，可以減輕磁盤I/O的壓力。 | 16M | No | 通常情況下，4MB-8MB已經(jīng)足夠 | 當mysql服務(wù)掛掉時，數(shù)值越大，則恢復(fù)數(shù)據(jù)需要越久 | 對于比較小innodb_buffer_pool_size，建議是設(shè)置為一樣大 innodb_buffer_pool_size + innodb_log_buffer_size |
| innodb_file_per_table | 默認開啟InnoDB為獨立表空間模式，每個數(shù)據(jù)庫的每個表都會生成一個數(shù)據(jù)空間 | 1 | Yes | 默認開啟，單表在不同的數(shù)據(jù)庫中移動，空間可回收，而且不管怎么刪除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。 不開啟，會降低文件系統(tǒng)操作的性能開銷。適合于將整個磁盤都用于存儲mysql數(shù)據(jù)的情況。 |
默認開啟時，應(yīng)避免單表增加過大。 不開啟時，應(yīng)避免在空間受限的系統(tǒng)表空間里導(dǎo)入大量臨時數(shù)據(jù)。 |
默認開啟 |
| innodb_lock_wait_timeout | 事務(wù)等待獲取資源等待的最長時間，超過這個時間還未分配到資源則會返回應(yīng)用失敗 | 50 | Yes | 最小可設(shè)置為1s，最大可設(shè)置1073741824秒以上再大就會被截斷了 | 如果事務(wù)開始前部分有其他操作而中途遇到鎖等待超時則mysql端還需要回滾，如果頻繁出現(xiàn)，會增加DB消耗 | 根據(jù)業(yè)務(wù)量級，對于數(shù)值進行適當?shù)慕档突蛘呱摺?/td> |
| innodb_purge_threads | 將purge線程從master線程分離出來，提高cpu使用率提升存儲引擎性能，最大值為32 | 4 | No | 可以指定多個innodb_purge_threads來進一步加快和提高undo（還原段）回收速度 | ||
|
innodb_thread_concurrency innodb_commit_concurrency |
默認是0，則表示沒有并發(fā)線程數(shù)限制，所有請求都會直接請求線程執(zhí)行。 同樣控制了多線程并發(fā)提交的數(shù)量。 |
0 0 |
Yes Yes |
如果數(shù)據(jù)庫沒有出現(xiàn)性能問題，且當并發(fā)用戶線程數(shù)量小于64，建議設(shè)置使用默認值就好。 如果負載不穩(wěn)定，時而低，時而高到峰值，建議先設(shè)置為128，并通過不斷的降低這個參數(shù)，直到發(fā)現(xiàn)能夠提供最佳性能的線程數(shù) |
兩者的修改相輔相成，單純調(diào)高innodb_thread_concurrency的數(shù)值，會造成大量線程阻塞。 設(shè)置過高值，可能會因為系統(tǒng)資源內(nèi)部爭奪導(dǎo)致性能下降； 定期監(jiān)控和分析DB，因為隨著數(shù)據(jù)庫負載的變化，業(yè)務(wù)的增加，參數(shù)需要動態(tài)的調(diào)整。 |
在大多數(shù)情況下，最佳的值是小于并接近虛擬CPU的個數(shù) + innodb_concurrency_tickets（5000） + innodb_commit_concurrency |
|
innodb_write_io_threads innodb_read_io_threads |
IO Thread 的主要工作是要負責這些IO請求的回調(diào)（call back）處理 | 4 4 |
No No |
根據(jù)CPU核數(shù)來更改相應(yīng)的參數(shù)值，更有效的利用cpu性能。 允許值的范圍是1～64 根據(jù)業(yè)務(wù)量級的不同，讀與寫的參數(shù)設(shè)置可以有微小差距。 |
||
| innodb_io_capacity | 緩沖池沖洗頁和合并從所述插入緩沖數(shù)據(jù)執(zhí)行的I / O活動的上限 | 200 | Yes | 服務(wù)器的磁盤是5400 RPM ~7200 RPM，屬于比較低級的磁盤，根據(jù)MySQL 官方的建議，應(yīng)該將innodb_io_capacity降低到100. 如果具有大量IOPS的快速驅(qū)動器，可是適當提高該值 |
設(shè)置過大，則會造成MySQL高估了磁盤的能力，導(dǎo)致臟頁堆積。 設(shè)置過低，則會出現(xiàn)MySQL低估了磁盤的能力，使得數(shù)據(jù)庫能夠單位時間內(nèi)提交的事務(wù)數(shù)(tps)降低 |
采用SSD云盤,確認支持的IOPS數(shù)量 |
| innodb_buffer_pool_instances | 允許多個緩沖池實例，每頁根據(jù)哈希平均分配到不同緩沖池實例中，減少數(shù)據(jù)庫內(nèi)部資源競爭，增加數(shù)據(jù)庫并發(fā)處理能力 | 1 | No | 在buffer_pool不大時，該值越大（低于10）性能越優(yōu)。使用大的 buffer_pool 時，為1時 的表現(xiàn)最棒 | 適用于多核處理器，依業(yè)務(wù)量進行調(diào)整。 | |
| innodb_change_buffer_max_size | change buffer在buffer pool中的最大占比，默認25%，最大可設(shè)置為50% | 25 | Yes | 如果系統(tǒng)中有嚴重的insert、update并且還有活躍的delete時，就增大。 針對不更改數(shù)據(jù)的純報表系統(tǒng)，可以減小該參數(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('開始時間: '+ 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é)束時間: '+ time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
print('消耗秒數(shù): ' + str(int(end - start)))
print('失敗條數(shù): ' + str(failed)
