共計(jì) 8277 個(gè)字符，預(yù)計(jì)需要花費(fèi) 21 分鐘才能閱讀完成。

本篇內(nèi)容介紹了“MySQL 性能需要關(guān)注的參數(shù)有哪些”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓丸趣 TV 小編帶領(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)度策略問題, 這個(gè)“每秒執(zhí)行一次 flush(刷到磁盤)操作”并不是保證 100% 的“每秒

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

2、sync_binlog

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

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

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

設(shè)為 1 最安全，在每個(gè)語句或事務(wù)后同步一次 binary log，即使在崩潰時(shí)也最多丟失一個(gè)語句或事務(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ù)情況下，對數(shù)據(jù)的一致性并沒有很嚴(yán)格的要求，所以并不會(huì)把 sync_binlog 配置成 1，為了追求高并發(fā)，提升性能，可以設(shè)置為 100 或直接用 0

注意 MySQL 5.6 開始引入 Group Commit 后

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

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

通過 mysqlbinlog 可以查看 binlog 日志中 last_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í)例起效)臟頁寫的線程數(shù)，加大該參數(shù)可以提升寫入性能

4、innodb_max_dirty_pages_pct

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

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

5、innodb_io_capacity=5000

從緩沖區(qū)刷新臟頁時(shí)，一次刷新臟頁的數(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 的打開、刷寫模式。有三個(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 這步完成（不通過 os  buffer)，而數(shù)據(jù)文件的寫入是在 flush 這步通過 fsync 完成。

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

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

在大量隨機(jī)寫的環(huán)境中 O_DIRECT 要比 fdatasync 效率更高些，順序?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（使刷新臟頁更智能）

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

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

MySQL 5.7 開啟并發(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ā)工作清理臟頁

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

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

10、innodb_adaptive_flushing_method 設(shè)置為 keep_average

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

11、innodb_stats_on_metadata=OFF

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

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

11、innodb_change_buffering=all

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

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

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

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

12、innodb_change_buffer_max_size

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

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

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

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

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

innodb_old_blocks_time 參數(shù)是在訪問到 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í)間，不讓這些無用的查詢數(shù)據(jù)進(jìn)入 old 區(qū)域，盡量不讓緩存再 new 區(qū)域的有用的數(shù)據(jù)被立即刷掉。（這也是治標(biāo)的方法，大量全表掃描就要優(yōu)化 sql 和表索引結(jié)構(gòu)了）

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

21、binlog_cache_size

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

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

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

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)對應(yīng)的 Binlog_cache_disk_use 值比較大的時(shí)候 我們可以考慮適當(dāng)?shù)恼{(diào)高 binlog_cache_size 對應(yīng)的值

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

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

0：不開啟，也就使用共享表空間；

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

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

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

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

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

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

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

缺點(diǎn)：

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

結(jié)論：共享表空間在 Insert 操作上少有優(yōu)勢。其它都沒獨(dú)立表空間表現(xiàn)好。當(dāng)啟用獨(dú)立表空間時(shí)，請合理調(diào)整一 下：innodb_open_files，因?yàn)槊總€(gè)表對應(yīng)一個(gè)文件，需要打開的文件個(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;

使用第二種方式修改后，原來庫中的表中的數(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ā)送過來的 binlog 日志都要寫入系統(tǒng)緩沖區(qū)，然后刷入 relay log 中繼日志里，這樣是最安全的，因?yàn)樵诒罎⒌臅r(shí)候，你最多會(huì)丟失一個(gè)事務(wù)，但會(huì)造成磁盤的大量 I /O。

當(dāng)設(shè)置為 0 時(shí)，并不是馬上就刷入中繼日志里，而是由操作系統(tǒng)決定何時(shí)來寫入，雖然安全性降低了，但減少了大量的磁盤 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)對大量連接

1

echo‘1024 65000′ /proc/sys/net/ipv4/ip_local_port_range

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

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

1

echo‘1048576 /proc/sys/net/ipv4/tcp_max_syn_backlog

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

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

1

echo 10 /proc/sys/net/ipv4/tcp_fin_timeout

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

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

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

默認(rèn)值如下：

# cat /proc/sys/kernel/sem

250     32000   32      128

說明：

第一列，表示每個(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è)置過小，當(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)注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質(zhì)量的實(shí)用文章！