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

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

創(chuàng)新互聯(lián)公司專業(yè)為企業(yè)提供杜爾伯特網(wǎng)站建設(shè)、杜爾伯特做網(wǎng)站、杜爾伯特網(wǎng)站設(shè)計(jì)、杜爾伯特網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、杜爾伯特企業(yè)網(wǎng)站模板建站服務(wù)，十余年杜爾伯特做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(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 不會丟失，效率低

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

=2 即使mysql崩潰也不會丟數(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)格的要求，所以并不會把 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，會丟失對應(yīng)的最后一個(gè)事務(wù)組；如果這個(gè)事務(wù)組內(nèi)有10個(gè)事務(wù)，那么這10個(gè)事務(wù)都會丟失。

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

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

4、innodb_max_dirty_pages_pct

最大臟頁百分?jǐn)?shù)，當(dāng)系統(tǒng)中臟頁所占百分比超過這個(gè)值，INNODB就會進(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í)間，太小的話會頻繁刷新，增加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中就會返回完成），真正完成是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í)也會刷新一定量的臟頁。
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，也就是會有64個(gè)page cleaner線程并發(fā)工作清理臟頁

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

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

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會隨機(jī)的io取數(shù)據(jù)遍歷所有的表來取樣用于統(tǒng)計(jì)數(shù)據(jù)，這個(gè)實(shí)際使用中用的不多，建議關(guān)閉.

11、innodb_change_buffering=all

change  buffer可以認(rèn)為是insert buffer的升級，可以對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í)（對非聚集非唯一索引的更新操作通常會帶來隨機(jī)IO），會將其放到一個(gè)insert buffer中，當(dāng)隨后頁面被讀到內(nèi)存中時(shí)，會將這些變化的記錄merge到頁中。當(dāng)服務(wù)器比較空閑時(shí)，后臺線程也會做merge操作。

由于主要用到merge的優(yōu)勢來降低io，但對于一些場景并不會對固定的數(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，會進(jìn)行很長時(shí)間的恢復(fù)工作（進(jìn)行merger操作）

13、innodb_old_blocks_pct初始化默認(rèn)是37,
（innodb體系架構(gòu) 27頁）注意因?yàn)樾聛淼膒age是放到了尾部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ū)域，而是在多少微秒之后才會真正進(jìn)入到new區(qū)域，這也是防止new區(qū)域里面的數(shù)據(jù)不會立即被踢出。

所以就有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è)置太大的話，會比較消耗內(nèi)存資源（Cache本質(zhì)就是內(nèi)存），更加需要注意的是：binlog_cache是不是全局的，是按SESSION為單位獨(dú)享分配的，也就是說當(dāng)一個(gè)線程開始一個(gè)事務(wù)的時(shí)候，Mysql就會為這個(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ù)和索引都會存在自已的表空間中,

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

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

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

使用turncate table也會使空間收縮

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

缺點(diǎn)：

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

結(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ù)會繼續(xù)存放于ibdata1中，新添加的數(shù)據(jù)才會使用獨(dú)立表空間

23.sync_relay_log：

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

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í)候，你最多會丟失一個(gè)事務(wù)，但會造成磁盤的大量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è)請求鏈接，也不會有影響。但是由于mysql是屬于高內(nèi)存、高cpu、高io應(yīng)用，不建議把多個(gè)應(yīng)用于mysql混搭在同一臺機(jī)器上。即使業(yè)務(wù)量不大，也可以通過降低單臺機(jī)器的配置，多臺機(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ù)會從應(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)核信號量

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

默認(rèn)值如下：

# cat /proc/sys/kernel/sem

250     32000   32      128

說明：

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

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

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

第四列，表示系統(tǒng)范圍內(nèi)的最大信號集總數(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í)候，會在錯(cuò)誤日志中報(bào)錯(cuò)：InnoDB: Warning: a long semaphore wait?。。?！

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

網(wǎng)站題目：MySQL性能需要關(guān)注的參數(shù)有哪些
URL標(biāo)題：http://muchs.cn/article36/iepipg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站營銷、網(wǎng)頁設(shè)計(jì)公司、域名注冊、網(wǎng)站排名、建站公司、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)