MySQL性能需要關(guān)注的參數(shù)有哪些

本篇內(nèi)容介紹了“MySQL性能需要關(guān)注的參數(shù)有哪些”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

1、innodb_flush_log_at_trx_commit設(shè)置為2

這參數(shù)是指 事務(wù)log 怎樣從log buffer寫進(jìn)日志文件（ib_logfile0、ib_logfile1）

=0 mysql crash 就丟失了，性能最好

buffer pool -> log buffer 每秒 wirte os cache & flush磁盤

=1 不會(huì)丟失，效率低

每次commit，buffer pool ->  log buffer—> write os cache & flush磁盤

=2 即使mysql崩潰也不會(huì)丟數(shù)據(jù)

每次commit ，buffer pool -> os cache  然后每秒flush 磁盤

注意：由于進(jìn)程調(diào)度策略問(wèn)題,這個(gè)“每秒執(zhí)行一次 flush(刷到磁盤)操作”并不是保證100%的“每秒

可以根據(jù)業(yè)務(wù)的安全程度和對(duì)性能的要求來(lái)具體設(shè)置該參數(shù)，已經(jīng)下面的sync_binlog

2、sync_binlog

二進(jìn)制日志（binary log）同步到磁盤的頻率。binary log 每寫入sync_binlog 次后，刷寫到磁盤。

如果 autocommit 開(kāi)啟，每個(gè)語(yǔ)句都寫一次 binary log，否則每次事務(wù)寫一次。

默認(rèn)值是 0，不主動(dòng)同步，而依賴操作系統(tǒng)本身不定期把文件內(nèi)容 flush 到磁盤

設(shè)為 1 最安全，在每個(gè)語(yǔ)句或事務(wù)后同步一次 binary log，即使在崩潰時(shí)也最多丟失一個(gè)語(yǔ)句或事務(wù)的日志，但因此也最慢。

sync_binlog =  N： 控制的是從binlog buffer中刷新binlog到底層binlog文件（也就是刷新到底層磁盤）

N>0    每向二進(jìn)制日志文件寫入N條SQL或N個(gè)事務(wù)后，則把二進(jìn)制日志文件的數(shù)據(jù)刷新到磁盤上； 

N=0    不主動(dòng)刷新二進(jìn)制日志文件的數(shù)據(jù)到磁盤上，而是由操作系統(tǒng)決定； 

大多數(shù)情況下，對(duì)數(shù)據(jù)的一致性并沒(méi)有很嚴(yán)格的要求，所以并不會(huì)把 sync_binlog 配置成 1，為了追求高并發(fā)，提升性能，可以設(shè)置為 100 或直接用 0

注意 MySQL 5.6開(kāi)始引入Group Commit后

sync_binlog的含義就變了，假定設(shè)為1000，表示的不是1000個(gè)事務(wù)后做一次fsync，而是1000個(gè)事務(wù)組。也就是說(shuō)，當(dāng)設(shè)置sync_binlog=1，binlog還未落盤，此時(shí)系統(tǒng)crash，會(huì)丟失對(duì)應(yīng)的最后一個(gè)事務(wù)組；如果這個(gè)事務(wù)組內(nèi)有10個(gè)事務(wù)，那么這10個(gè)事務(wù)都會(huì)丟失。

如何查看是否屬于一個(gè)事務(wù)組

通過(guò)mysqlbinlog可以查看binlog日志中l(wèi)ast_committed值，如果值一樣，表明是在同一事務(wù)組內(nèi)。

### INSERT INTO `wukong_test`.`wukong`

### SET

### @1=3 /* INT meta=0 nullable=1 is_null=0 */

### @2='ccccc' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */

# at 496468

#170527 4:17:35 server id 12001 end_log_pos 496499 CRC32 0xd6e7f69f Xid = 5556

COMMIT/*!*/;

# at 496499

#170527 4:17:35 server id 12001 end_log_pos 496564 CRC32 0x28816d5c GTIDlast_committed=1845sequence_number=1846

SET @@SESSION.GTID_NEXT= '0a646c88-36e2-11e7-937d-fa163ed7a7b1:3624'/*!*/;

# at 496564

#170527 4:17:35 server id 12001 end_log_pos 496632 CRC32 0x03150d48 Query thread_id=1852 exec_time=0 error_code=0

SET TIMESTAMP=1495873055/*!*/;

BEGIN

3、write/read thread

異步IO線程數(shù)

innodb_write_io_threads=16

innodb_read_io_threads=16

(該參數(shù)需要在配置文件中添加，重啟mysql實(shí)例起效)臟頁(yè)寫的線程數(shù)，加大該參數(shù)可以提升寫入性能

4、innodb_max_dirty_pages_pct

最大臟頁(yè)百分?jǐn)?shù)，當(dāng)系統(tǒng)中臟頁(yè)所占百分比超過(guò)這個(gè)值，INNODB就會(huì)進(jìn)行寫操作以把頁(yè)中的已更新數(shù)據(jù)寫入到磁盤文件中。默認(rèn)75，一般現(xiàn)在流行的SSD硬盤很難達(dá)到這個(gè)比例?？梢罁?jù)實(shí)際情況在75-80之間調(diào)節(jié)，

這個(gè)參數(shù)太大，導(dǎo)致實(shí)例恢復(fù)需要很長(zhǎng)時(shí)間，太小的話會(huì)頻繁刷新，增加page_cleaner_thread以及innodb_write_io_threads和cpu的負(fù)擔(dān)，一般就用默認(rèn)值；

5、innodb_io_capacity=5000

從緩沖區(qū)刷新臟頁(yè)時(shí)，一次刷新臟頁(yè)的數(shù)量。根據(jù)磁盤IOPS的能力一般建議設(shè)置如下：

SAS 200

SSD 5000

PCI-E 10000-50000

6、innodb_flush_method=O_DIRECT(該參數(shù)需要重啟mysql實(shí)例起效)

控制innodb 數(shù)據(jù)文件和redo log的打開(kāi)、刷寫模式。有三個(gè)值：fdatasync(默認(rèn))，O_DSYNC，O_DIRECT。

fdatasync模式：寫數(shù)據(jù)時(shí)，write這一步并不需要真正寫到磁盤才算完成（可能寫入到操作系統(tǒng)buffer中就會(huì)返回完成），真正完成是flush操作，buffer交給操作系統(tǒng)去flush,并且文件的元數(shù)據(jù)信息也都需要更新到磁盤。

O_DSYNC模式：寫日志操作是在write這步完成（不通過(guò)os  buffer)，而數(shù)據(jù)文件的寫入是在flush這步通過(guò)fsync完成。

O_DIRECT模式：數(shù)據(jù)文件的寫入操作是直接從mysql innodb buffer到磁盤的，并不用通過(guò)操作系統(tǒng)的緩沖，而真正的完成也是在flush這步,日志還是要經(jīng)過(guò)OS緩沖。

通過(guò)圖可以看出O_DIRECT相比f(wàn)datasync的優(yōu)點(diǎn)是避免了雙緩沖，本身innodb buffer pool就是一個(gè)緩沖區(qū)，不需要再寫入到系統(tǒng)的buffer，但是有個(gè)缺點(diǎn)是由于是直接寫入到磁盤，所以相比f(wàn)datasync的順序讀寫的效率要低些。所以如果磁盤io壓力不大的話，并且如果系統(tǒng)使用了swap空間，可以考慮innodb_flush_method=O_DIRECT;

在大量隨機(jī)寫的環(huán)境中O_DIRECT要比f(wàn)datasync效率更高些，順序?qū)懚嗟脑?，還是默認(rèn)的fdatasync更高效，因?yàn)樵蹅儸F(xiàn)在使用了insert buffer cache的使用（轉(zhuǎn)化成了順序?qū)懀?，個(gè)人認(rèn)為還是默認(rèn)值比較好，

7、innodb_adaptive_flushing 設(shè)置為 ON （使刷新臟頁(yè)更智能）

影響每秒刷新臟頁(yè)的數(shù)目

規(guī)則由原來(lái)的“大于innodb_max_dirty_pages_pct時(shí)刷新100個(gè)臟頁(yè)到磁盤”變?yōu)?“通過(guò)buf_flush_get_desired_flush_reate函數(shù)判斷重做日志產(chǎn)生速度確定需要刷新臟頁(yè)的最合適數(shù)目”，即使臟頁(yè)比例小于 innodb_max_dirty_pages_pct時(shí)也會(huì)刷新一定量的臟頁(yè)。
8.innodb_page_cleaners

MySQL 5.7開(kāi)啟并發(fā)刷新線程，innodb_page_cleaners控制刷新線程數(shù)

mysql> show variables like 'i%cleaners';

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| innodb_page_cleaners | 1     |

+----------------------+-------+

1 row in set (0.05 sec)

配置文件my.cnf中添加innodb_page_cleaners=num值

默認(rèn)是1；最大可以是64，也就是會(huì)有64個(gè)page cleaner線程并發(fā)工作清理臟頁(yè)

9.innodb_flush_ neighbors刷新臨近頁(yè)的參數(shù)；

當(dāng)刷新一個(gè)臟頁(yè)時(shí)，Innodb存儲(chǔ)引擎會(huì)檢測(cè)該頁(yè)所在區(qū)（extent）的所有的頁(yè)，如果是臟頁(yè)，那么一起進(jìn)行刷新。這樣做的好處顯而易見(jiàn)，通過(guò)AIO可以將多個(gè)IO寫入操作合并為一個(gè)IO操作，故該工作機(jī)制在傳統(tǒng)機(jī)械磁盤下有著顯著的優(yōu)勢(shì)。至于固態(tài)硬盤來(lái)說(shuō)，因?yàn)樗兄叩腎OPS性能，則建議將該參數(shù)設(shè)置為0，也就是關(guān)閉這個(gè)特性；個(gè)人覺(jué)得如果io并不是性能瓶頸，不建議開(kāi)啟這個(gè)功能，因?yàn)樗赡軐⒉辉趺磁K的頁(yè)進(jìn)行刷新，而該頁(yè)之后又會(huì)很快變成臟頁(yè)；

10、innodb_adaptive_flushing_method 設(shè)置為 keep_average

影響checkpoint，更平均的計(jì)算調(diào)整刷臟頁(yè)的速度，進(jìn)行必要的flush.（該變量為mysql衍生版本Percona Server下的一個(gè)變量，原生mysql不存在）

11、innodb_stats_on_metadata=OFF

關(guān)掉一些訪問(wèn)information_schema庫(kù)下表而產(chǎn)生的索引統(tǒng)計(jì)。

當(dāng)重啟mysql實(shí)例后，mysql會(huì)隨機(jī)的io取數(shù)據(jù)遍歷所有的表來(lái)取樣用于統(tǒng)計(jì)數(shù)據(jù)，這個(gè)實(shí)際使用中用的不多，建議關(guān)閉.

11、innodb_change_buffering=all

change  buffer可以認(rèn)為是insert buffer的升級(jí)，可以對(duì)dml操作---insert、delete、update都進(jìn)行緩沖，他們分別是：insert  buffer、delete buffer、purge buffer；

該參數(shù)用來(lái)開(kāi)啟各種buffer的選項(xiàng)，可選擇的值為：inserts、delete、purges、changes、all、none;其中changes代表啟用了inserts和delete，all 表示啟用所有，none表示都不啟用，默認(rèn)為all;

當(dāng)更新/插入的非聚集非唯一索引的數(shù)據(jù)所對(duì)應(yīng)的頁(yè)不在內(nèi)存中時(shí)（對(duì)非聚集非唯一索引的更新操作通常會(huì)帶來(lái)隨機(jī)IO），會(huì)將其放到一個(gè)insert buffer中，當(dāng)隨后頁(yè)面被讀到內(nèi)存中時(shí)，會(huì)將這些變化的記錄merge到頁(yè)中。當(dāng)服務(wù)器比較空閑時(shí)，后臺(tái)線程也會(huì)做merge操作。

由于主要用到merge的優(yōu)勢(shì)來(lái)降低io，但對(duì)于一些場(chǎng)景并不會(huì)對(duì)固定的數(shù)據(jù)進(jìn)行多次修改，此處則并不需要把更新/插入操作開(kāi)啟change_buffering，如果開(kāi)啟只是多余占用了buffer_pool的空間和處理能力。這個(gè)參數(shù)要依據(jù)實(shí)際業(yè)務(wù)環(huán)境來(lái)配置。

12、innodb_change_buffer_max_size

從Innodb 1.2.X版本開(kāi)始，可以通過(guò)參數(shù)innodb_change_buffer_max_size來(lái)控制change buffer最大使用內(nèi)存的數(shù)量；默認(rèn)值為25，表示最多使用25%的緩沖池內(nèi)存空間，該參數(shù)最大有效值為50%。

如果使用太多，那么當(dāng)MySQL server crash，會(huì)進(jìn)行很長(zhǎng)時(shí)間的恢復(fù)工作（進(jìn)行merger操作）

13、innodb_old_blocks_pct初始化默認(rèn)是37,
（innodb體系架構(gòu) 27頁(yè)）注意因?yàn)樾聛?lái)的page是放到了尾部3/8的位置(也就是屬于sublist of old blocks)所以sublist of new blocks只能來(lái)自于sublist of old blocks的移動(dòng)

innodb緩存池有2個(gè)區(qū)域一個(gè)是sublist of old blocks存放不經(jīng)常被訪問(wèn)到的數(shù)據(jù)，另外一個(gè)是sublist of new blocks存放經(jīng)常被訪問(wèn)到的數(shù)據(jù)

innodb_old_blocks_pct參數(shù)是控制進(jìn)入到sublist of old blocks區(qū)域的數(shù)量，初始化默認(rèn)是37.

innodb_old_blocks_time參數(shù)是在訪問(wèn)到sublist of old blocks里面數(shù)據(jù)的時(shí)候控制數(shù)據(jù)不立即轉(zhuǎn)移到sublist of new blocks區(qū)域，而是在多少微秒之后才會(huì)真正進(jìn)入到new區(qū)域，這也是防止new區(qū)域里面的數(shù)據(jù)不會(huì)立即被踢出。

所以就有2種情況：

1、如果在業(yè)務(wù)中做了大量的全表掃描，那么你就可以將innodb_old_blocks_pct設(shè)置減小，增到innodb_old_blocks_time的時(shí)間，不讓這些無(wú)用的查詢數(shù)據(jù)進(jìn)入old區(qū)域，盡量不讓緩存再new區(qū)域的有用的數(shù)據(jù)被立即刷掉。（這也是治標(biāo)的方法，大量全表掃描就要優(yōu)化sql和表索引結(jié)構(gòu)了）

2、如果在業(yè)務(wù)中沒(méi)有做大量的全表掃描，那么你就可以將innodb_old_blocks_pct增大，減小innodb_old_blocks_time的時(shí)間，讓有用的查詢緩存數(shù)據(jù)盡量緩存在innodb buffer pool中，減小磁盤io，提高性能。

21、binlog_cache_size

二進(jìn)制日志緩沖大?。阂粋€(gè)事務(wù)，在沒(méi)有提交（uncommitted）的時(shí)候，產(chǎn)生的日志，記錄到Cache中；等到事務(wù)提交（committed）需要提交的時(shí)候，則把日志持久化到磁盤。

設(shè)置太大的話，會(huì)比較消耗內(nèi)存資源（Cache本質(zhì)就是內(nèi)存），更加需要注意的是：binlog_cache是不是全局的，是按SESSION為單位獨(dú)享分配的，也就是說(shuō)當(dāng)一個(gè)線程開(kāi)始一個(gè)事務(wù)的時(shí)候，Mysql就會(huì)為這個(gè)SESSION分配一個(gè)binlog_cache

怎么判斷我們當(dāng)前的binlog_cache_size設(shè)置的沒(méi)問(wèn)題呢？

mysql> show status like 'binlog_%';

+-----------------------+-----------+|

Variable_name | Value |

Binlog_cache_disk_use | 1425 |

| Binlog_cache_use | 126945718 |

2 rows in set (0.00 sec)

mysql> select @@binlog_cache_size;

+-----------------------+-----------+|

@@binlog_cache_size

1048576

1 row in set (0.00 sec)

運(yùn)行情況Binlog_cache_use 表示binlog_cache內(nèi)存方式被用上了多少次，Binlog_cache_disk_use表示binlog_cache臨時(shí)文件方式被用上了多少次,當(dāng)對(duì)應(yīng)的Binlog_cache_disk_use 值比較大的時(shí)候 我們可以考慮適當(dāng)?shù)恼{(diào)高 binlog_cache_size 對(duì)應(yīng)的值

22.innodb_file_per_table兩個(gè)取值:

1：開(kāi)啟獨(dú)立表空間；

0：不開(kāi)啟，也就使用共享表空間；

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

1)每個(gè)表的數(shù)據(jù)和索引都會(huì)存在自已的表空間中,

2)可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫(kù)中移動(dòng)

3)空間可以回收（除drop table操作）

4)刪除大量數(shù)據(jù)后可以通過(guò):alter table TableName engine=innodb; 回縮不用的空間

使用turncate table也會(huì)使空間收縮

5）對(duì)于使用獨(dú)立表空間的表，不管怎么刪除，表空間的碎片不會(huì)太嚴(yán)重的影響性能

缺點(diǎn)：

1）單表增加過(guò)大，如超過(guò)100個(gè)G，使用共享表空間可能會(huì)更好！

結(jié)論：共享表空間在Insert操作上少有優(yōu)勢(shì)。其它都沒(méi)獨(dú)立表空間表現(xiàn)好。當(dāng)啟用獨(dú)立表空間時(shí)，請(qǐng)合理調(diào)整一 下：innodb_open_files，因?yàn)槊總€(gè)表對(duì)應(yīng)一個(gè)文件，需要打開(kāi)的文件個(gè)數(shù)比共享表空間要多，所以需要適當(dāng)調(diào)大innodb_open_files的參數(shù)，并且調(diào)高linux內(nèi)核參數(shù) open files的限制1

想要將共享表空間轉(zhuǎn)化為獨(dú)立表空間有兩種方法：

1.先邏輯備份，然后修改配置文件my.cnf中的參數(shù)innodb_file_per_table參數(shù)為1，重啟服務(wù)后將邏輯備份導(dǎo)入即可。

2.修改配置文件my.cnf中的參數(shù)innodb_file_per_table參數(shù)為1，重啟服務(wù)后將需要修改的所有innodb表都執(zhí)行一遍：alter table table_name engine=innodb;

使用第二種方式修改后，原來(lái)庫(kù)中的表中的數(shù)據(jù)會(huì)繼續(xù)存放于ibdata1中，新添加的數(shù)據(jù)才會(huì)使用獨(dú)立表空間

23.sync_relay_log：

sync_relay_log：默認(rèn)為10000，即每10000次sync_relay_log事件會(huì)刷新到磁盤。為0則表示不刷新，交由OS的cache控制，為N就是n次次sync_relay_log事件會(huì)刷新到磁盤；

If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk (using fdatasync()) after every sync_relay_log events are written to the relay log. Setting this variable takes effect for all replication channels immediately, including running channels

當(dāng)設(shè)置為1時(shí)，slave的I/O線程每次接收到master發(fā)送過(guò)來(lái)的binlog日志都要寫入系統(tǒng)緩沖區(qū)，然后刷入relay log中繼日志里，這樣是最安全的，因?yàn)樵诒罎⒌臅r(shí)候，你最多會(huì)丟失一個(gè)事務(wù)，但會(huì)造成磁盤的大量I/O。

當(dāng)設(shè)置為0時(shí)，并不是馬上就刷入中繼日志里，而是由操作系統(tǒng)決定何時(shí)來(lái)寫入，雖然安全性降低了，但減少了大量的磁盤I/O操作。這個(gè)值默認(rèn)是10000，可動(dòng)態(tài)修改；

24.然后介紹參數(shù)sync_binlog：

sync_binlog =  N： 控制的是從binlog buffer中刷新binlog到底層binlog文件（也就是刷新到底層磁盤）

N>0    每向二進(jìn)制日志文件寫入N條SQL或N個(gè)事務(wù)后（組提交的時(shí)候，實(shí)際上是n個(gè)組事務(wù)），則把二進(jìn)制日志文件的數(shù)據(jù)刷新到磁盤上；

N=0    不主動(dòng)刷新二進(jìn)制日志文件的數(shù)據(jù)到磁盤上，而是由操作系統(tǒng)決定；

25.增加本地端口，以應(yīng)對(duì)大量連接

該參數(shù)指定端口的分配范圍，該端口是向外訪問(wèn)的限制。mysql默認(rèn)監(jiān)聽(tīng)的3306端口即使有多個(gè)請(qǐng)求鏈接，也不會(huì)有影響。但是由于mysql是屬于高內(nèi)存、高cpu、高io應(yīng)用，不建議把多個(gè)應(yīng)用于mysql混搭在同一臺(tái)機(jī)器上。即使業(yè)務(wù)量不大，也可以通過(guò)降低單臺(tái)機(jī)器的配置，多臺(tái)機(jī)器共存來(lái)實(shí)現(xiàn)更好。

26.增加隊(duì)列的鏈接數(shù)

建立鏈接的隊(duì)列的數(shù)越大越好，但是從另一個(gè)角度想，實(shí)際環(huán)境中應(yīng)該使用連接池更合適，避免重復(fù)建立鏈接造成的性能消耗。使用連接池，鏈接數(shù)會(huì)從應(yīng)用層面更可控些。

27.設(shè)置鏈接超時(shí)時(shí)間

該參數(shù)主要為了降低TIME_WAIT占用的資源時(shí)長(zhǎng)。尤其針對(duì)http短鏈接的服務(wù)端或者mysql不采用連接池效果比較明顯。

28.linux內(nèi)核信號(hào)量

來(lái)linux內(nèi)核信號(hào)量默認(rèn)設(shè)置太小，造成大量等待，

默認(rèn)值如下：

# cat /proc/sys/kernel/sem

250     32000   32      128

說(shuō)明：

第一列，表示每個(gè)信號(hào)集中的最大信號(hào)量數(shù)目。

第二列，表示系統(tǒng)范圍內(nèi)的最大信號(hào)量總數(shù)目。

第三列，表示每個(gè)信號(hào)發(fā)生時(shí)的最大系統(tǒng)操作數(shù)目。

第四列，表示系統(tǒng)范圍內(nèi)的最大信號(hào)集總數(shù)目。

將第三列調(diào)大一點(diǎn)，參考網(wǎng)上的數(shù)據(jù)

echo "kernel.sem=250 32000 100 128″>>/etc/sysctl.conf

然后sysctl -p

重啟mysql

如果設(shè)置過(guò)小，當(dāng)大量并發(fā)的時(shí)候，會(huì)在錯(cuò)誤日志中報(bào)錯(cuò)：InnoDB: Warning: a long semaphore wait！?。?！

“MySQL性能需要關(guān)注的參數(shù)有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！