MySQL性能相關(guān)參數(shù)有哪些

這篇文章給大家介紹MySQL性能相關(guān)參數(shù)有哪些，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

整理MySQL常用性能相關(guān)參數(shù)如下

general_log

記錄所有執(zhí)行的語句，在需要分析問題打開即可，正常服務(wù)時(shí)不需要開啟，以免帶來io性能影響

query_cache_size

緩存sql文本和查詢結(jié)果的，如果對(duì)應(yīng)的表沒有變化，下次碰到一樣的SQL，跳過解析和查詢，直接返回結(jié)果。

但是表變化非常頻繁，SQL也是動(dòng)態(tài)生產(chǎn)的，由于需要不斷更新cache內(nèi)容，這時(shí)鎖力度很大，反而照成瓶頸。這時(shí)最好關(guān)掉這個(gè)功能，設(shè)置參數(shù)為0

sort_buffer_size

針對(duì)單個(gè)session的參數(shù)，

排序時(shí)，如果用不到index，session就會(huì)申請(qǐng)一塊這么大的內(nèi)存空間進(jìn)行排序。如果這個(gè)參數(shù)值過小會(huì)把排序結(jié)果寫入硬盤中，會(huì)影響效率。

如果太大，又可能導(dǎo)致物理內(nèi)存耗盡，導(dǎo)致OOM。

join_buffer_size

在join無法使用到index時(shí)候用到的buffer，和sort_buffer_size類似

tmp_table_size

在group by和distinct時(shí)如果SQL用不到索引，就會(huì)使用系統(tǒng)內(nèi)部臨時(shí)表記錄中間狀態(tài)。如果該值不夠大，就使用物理硬盤

Innodb_buffer_pool_size

InnoDB最重要的緩存，用來緩存innodb索引頁(yè)面、undo頁(yè)面及其他輔助數(shù)據(jù)。一般設(shè)定物理內(nèi)存50%~75%

Innodb_buffer_pool_instances

通過這個(gè)參數(shù)可以把整塊buffer pool分割為多塊instance內(nèi)存空間，每個(gè)空間獨(dú)立管理自己的內(nèi)存和鏈表，來提升MySQL請(qǐng)求處理的并發(fā)能力。

因?yàn)閎uffer pool是通過鏈表來管理的，同時(shí)為了保護(hù)頁(yè)面，需要在存取的時(shí)候?qū)︽湵砑渔i，在多線程情況下，并發(fā)讀寫buffer pool緩存會(huì)有鎖競(jìng)爭(zhēng)和等待。

官方說超過1G的Innodb_buffer_pool_size 考慮設(shè)定instances去切分內(nèi)存

Innodb_log_file_size,innodb_log_files_in_group

兩個(gè)參數(shù)決定redo空間的大小，設(shè)置存儲(chǔ)更新redo越大，有效降低buffer pool臟頁(yè)被淘汰的速度，減少了checkpoint此書，降低磁盤I/O

不過設(shè)置過大，在數(shù)據(jù)庫(kù)異常宕機(jī)時(shí)，恢復(fù)時(shí)間越長(zhǎng)

Innodb_old_blocks_pct,innodb_old_blocks_time

innodb_old_blocks_pct：

全局、動(dòng)態(tài)變量，默認(rèn)值 37，取值范圍為5~95. 用來確定LRU鏈表中old sublist所占比例

innodb_old_blocks_time：

全局、動(dòng)態(tài)變量，默認(rèn)值 1000，取值范圍為0~2**32-1，單位ms。

用來控制old sublist中page的轉(zhuǎn)移策略，新的page頁(yè)在進(jìn)入LRU鏈表中時(shí)，會(huì)先插入到old sublist的頭部，然后page需要在old sublist中停留innodb_old_blocks_time這么久后，下一次對(duì)該page的訪問才會(huì)使其移動(dòng)到new sublist的頭部，

該參數(shù)的設(shè)置可以保護(hù)new sublist，盡可能的防止其being filled by page that is referenced only for a brief period。

默認(rèn)的緩沖中的頁(yè)在第一次被讀取時(shí)（也就是命中緩存）會(huì)被移動(dòng)到新頁(yè)子表頭部，意味著其會(huì)長(zhǎng)期待在緩沖池中不會(huì)被淘汰。這樣就會(huì)存在一個(gè)問題，一次表掃描（比如使用select查詢）可能會(huì)將大量數(shù)據(jù)放入緩存中，并淘汰相應(yīng)數(shù)量的舊數(shù)據(jù)，但是可能這些數(shù)據(jù)只使用一次，后面不再使用；同樣地，因?yàn)閞ead-ahead也會(huì)在下一次訪問該頁(yè)時(shí)被放入新頁(yè)子表頭部。這些情形會(huì)將本應(yīng)會(huì)被頻繁使用的頁(yè)移動(dòng)到舊頁(yè)子表中。

所以3/8位置處。在后面的第一次命中（被訪問時(shí)）的頁(yè)會(huì)被移動(dòng)到列表的頭部。因此，那些讀入緩存但是后面從來不會(huì)被訪問的頁(yè)也從不會(huì)被放入列表的頭部，也就會(huì)在后面被從緩沖池淘汰。

MySQL提供了配置參數(shù),milliseconds）讀取不會(huì)被標(biāo)識(shí)為年輕，也就是不會(huì)被移動(dòng)到列表頭部。參數(shù)1000，增大這個(gè)參數(shù)將會(huì)造成更多的頁(yè)會(huì)更快的從緩沖池中被淘汰。

Innodb_flush_method

Innodb刷數(shù)據(jù)和日志到磁盤的方式，這個(gè)值默認(rèn)為空，其實(shí)：

Linux默認(rèn)fsync

Windows 默認(rèn)async_unbuffered

SSD和PCIE存儲(chǔ)時(shí)可以使用o_direct 提升性能

Innodb_doublewrite

MySQL默認(rèn)每個(gè)page size是16k，而OS通常最小I/O單元是4k，所以如果寫page時(shí)可能需要調(diào)用4次OS I/O才能完成。假定在執(zhí)行兩次時(shí)DB crash了，這時(shí)page只寫了一部分，就產(chǎn)生了partial write(不完整寫)。

MySQL double write的設(shè)定就是為了在發(fā)生partial write時(shí)任然保證已經(jīng)commit的數(shù)據(jù)不丟失，以及數(shù)據(jù)文件不損壞。

但如果底層存儲(chǔ)支持原子性可以關(guān)閉兩次寫，主要看OS page size和DB page size的關(guān)系。

Innodb_io_capacity

控制后臺(tái)不斷將內(nèi)存(dirty data)數(shù)據(jù)flush硬盤的操作，遇到周期性IO QPS下降時(shí)可以考慮提高參數(shù)的設(shè)定，以加速flush的頻率

參考實(shí)驗(yàn)提高Innodb_io_capacity的設(shè)置，已提升QPS

Innodb_thread_concurrency

在并發(fā)量大的時(shí)，增加這個(gè)值，兒科降低innodb在并發(fā)線程之間切換開銷，以增加系統(tǒng)的并發(fā)吞吐量

innodb_flush_log_at_trx_commit

控制redo log刷盤機(jī)制

innodb_flush_log_at_trx_commit=0

事務(wù)提交時(shí)，不會(huì)處理log buffer的內(nèi)容，也不會(huì)處理log file在OS cache的刷盤操作，由MySQL后臺(tái)master線程每隔1秒將log buffer刷新到磁盤的log file中。

在MySQL服務(wù)宕掉，服務(wù)器正?；蝈礄C(jī)時(shí)：

由于事務(wù)提交不刷新logbuffer，即使事務(wù)提交了，logbuffer也會(huì)全部丟失，但只丟失最近1秒的事務(wù)

innodb_flush_log_at_trx_commit=1

事務(wù)提交時(shí)，會(huì)將log buffer的內(nèi)容寫入OS cache文件中，同時(shí)會(huì)將OS cache刷新到磁盤log file中。

在MySQL服務(wù)宕掉，服務(wù)器正常或宕機(jī)時(shí)：

由于事務(wù)提交會(huì)刷新到磁盤log file中，所以數(shù)據(jù)都不會(huì)丟失

innodb_flush_log_at_trx_commit=2

事務(wù)提交時(shí)，會(huì)將log buffer的內(nèi)容寫到OS cache文件中，由MySQL后臺(tái)master線程每隔1秒將OS cache的log file刷新到磁盤。

在MySQL服務(wù)宕掉，服務(wù)器正常：

由于事務(wù)已經(jīng)刷新到OS cache中，然而服務(wù)器沒宕機(jī)，這樣日志還是會(huì)被刷新到磁盤中，那么數(shù)據(jù)就不會(huì)丟失

在MySQL服務(wù)宕掉，服務(wù)器宕機(jī)：

由于事務(wù)只刷新到OS cache中，服務(wù)器宕機(jī)話，日志沒用被刷新到磁盤中，會(huì)丟失1秒的事務(wù)

sync_binlog

控制binlog同步到磁盤的方式

sync_binlog=0,事務(wù)提交時(shí)將MySQL Binlog信息寫入OS cache Binlog中，由OS自己空間其緩存的刷新。如果是服務(wù)器宕機(jī)binlog cache中所有binlog都會(huì)丟失

sync_binlog=1,每個(gè)事務(wù)提交時(shí)，MySQL都會(huì)把Binlog刷新到物理磁盤中。這樣安全性最高，性能損耗是最大。特別是在多事務(wù)同行提交，會(huì)對(duì)I/O性能帶來很大影響。

但group commit可以緩解壓力：

binlog_group_commit_sync_delay=N,默認(rèn)是0,定時(shí)執(zhí)行，在commit后等待N 微秒后，進(jìn)行binlog刷盤操作

binlog_group_commit_sync_no_delay_count=N，在commit后等待達(dá)到最大事務(wù)等待數(shù)量N， 就忽視binlog_group_commit_sync_delay的設(shè)置，直接開始刷盤,注意如果binlog_group_commit_sync_delay設(shè)置為0，則此選項(xiàng)無效

不過group commit的設(shè)置，可能會(huì)影響commit執(zhí)行執(zhí)行速度，可參考： https://www.cnblogs.com/ziroro/p/9600359.html

sync_binlog=N, 表示每N次事務(wù)提交，MySQL會(huì)做刷盤。如果DB服務(wù)或者服務(wù)器宕機(jī)會(huì)丟失一些事務(wù)

注：開啟Binlog后，MySQL內(nèi)部會(huì)自動(dòng)將事務(wù)當(dāng)作一個(gè)XA事務(wù)處理，在提交事務(wù)過程中，會(huì)自動(dòng)分配一個(gè)唯一的XID，XID會(huì)記錄到Binlog和redo log中。事務(wù)在提交過程會(huì)自動(dòng)份為Prepare和Commit兩個(gè)階段。

Prepare階段：告訴InnoDB做prepare，InnoDB更改事務(wù)狀態(tài)，并將redo log刷入磁盤

Commit階段：先記錄Binlog，然后告訴InnoDB commit

binlog_format

binlog_format=STATEMENT

寫入執(zhí)行的SQL語句到binlog，從庫(kù)讀取這些SQL并執(zhí)行

優(yōu)勢(shì)：

技術(shù)成熟

減少binlog的寫入量

binlog包含所有修改語句沒便于審計(jì)

缺點(diǎn)：

有些函數(shù)不能再slave上復(fù)雜，如sleep(),last_insert_id(),udf等會(huì)除問題

與基于row的復(fù)制比，insert...select需要更多的鎖

隔離級(jí)別必須是repeatable-read,而這是發(fā)生死鎖的元兇之一

binlog_format=MIXED

默認(rèn)使用STATEMENT記錄日志，特定情況下轉(zhuǎn)換成ROW記錄

binlog_format=ROW

MySQL5.7.7之后的默認(rèn)值

優(yōu)點(diǎn)：

復(fù)制是最安全的

slave需要的鎖也最少

缺點(diǎn)：

binlog會(huì)記錄更多的數(shù)據(jù)

無法在slave上看到master上獲取的語句，因?yàn)槎际莈vent。但可以開啟binlog_rows_query_log_events參數(shù)，讓binlog記錄events同時(shí)也記錄原始SQL語句。

(復(fù)制建議使用row模式，其它模式有可能出現(xiàn)主從數(shù)據(jù)不一致)

tx_isolation

MySQL隔離級(jí)別，默認(rèn)是repeatable-read

Read Uncommitted

Read Committed

Repeatable Read

Serializable

這四種級(jí)別越來越嚴(yán)格，但性能越來越差。

推薦使用Read Committed，同時(shí)binlog_format=ROW ，確認(rèn)binlog同步數(shù)據(jù)主從庫(kù)一致性，兼顧安全，滿足絕大多數(shù)業(yè)務(wù)。

slave_parallel_workers

MySQL 5.6中，設(shè)置參數(shù)slave_parallel_workers = 4，即可有4個(gè)SQL Thread（coordinator線程）來進(jìn)行并行復(fù)制，其狀態(tài)為：Waiting for an evant from Coordinator。但是其并行只是基于database的。如果數(shù)據(jù)庫(kù)實(shí)例中存在多個(gè)database，這樣設(shè)置對(duì)于Slave復(fù)制的速度可以有比較大的提升。

其核心思想是：不同database下的表并發(fā)提交時(shí)的數(shù)據(jù)不會(huì)相互影響，即slave節(jié)點(diǎn)可以用對(duì)relay log中不同的schema各分配一個(gè)類似SQL功能的線程，來重放relay log中主庫(kù)已經(jīng)提交的事務(wù)，保持?jǐn)?shù)據(jù)與主庫(kù)一致。

在MySQL 5.7中，引入了基于組提交的并行復(fù)制（Enhanced Multi-threaded Slaves），

設(shè)置slave_parallel_workers>0并且global.slave_parallel_type＝‘LOGICAL_CLOCK’，即可支持一個(gè)database下，slave_parallel_workers個(gè)的worker線程并發(fā)執(zhí)行relay log中主庫(kù)提交的事務(wù)。

其核心思想：一個(gè)組提交的事務(wù)都是可以并行回放(配合binary log group commit);

slave機(jī)器的relay log中 last_committed相同的事務(wù)（sequence_num不同）可以并發(fā)執(zhí)行。

參數(shù)slave_parallel_type可以有兩個(gè)值：

DATABASE 默認(rèn)值，基于庫(kù)的并行復(fù)制方式

LOGICAL_CLOCK：基于組提交的并行復(fù)制方式

關(guān)于MySQL性能相關(guān)參數(shù)有哪些就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。