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

創(chuàng)新互聯(lián)公司專業(yè)為企業(yè)提供杜爾伯特網(wǎng)站建設、杜爾伯特做網(wǎng)站、杜爾伯特網(wǎng)站設計、杜爾伯特網(wǎng)站制作等企業(yè)網(wǎng)站建設、網(wǎng)頁設計與制作、杜爾伯特企業(yè)網(wǎng)站模板建站服務，十余年杜爾伯特做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡服務。

1、innodb_flush_log_at_trx_commit設置為2

這參數(shù)是指 事務log 怎樣從log buffer寫進日志文件（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 磁盤

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

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

2、sync_binlog

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

如果 autocommit 開啟，每個語句都寫一次 binary log，否則每次事務寫一次。

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

設為 1 最安全，在每個語句或事務后同步一次 binary log，即使在崩潰時也最多丟失一個語句或事務的日志，但因此也最慢。

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

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

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

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

注意 MySQL 5.6開始引入Group Commit后

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

如何查看是否屬于一個事務組

通過mysqlbinlog可以查看binlog日志中l(wèi)ast_committed值，如果值一樣，表明是在同一事務組內(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ù)可以提升寫入性能

4、innodb_max_dirty_pages_pct

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

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

5、innodb_io_capacity=5000

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

SAS 200

SSD 5000

PCI-E 10000-50000

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

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

fdatasync模式：寫數(shù)據(jù)時，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)點是避免了雙緩沖，本身innodb buffer pool就是一個緩沖區(qū)，不需要再寫入到系統(tǒng)的buffer，但是有個缺點是由于是直接寫入到磁盤，所以相比fdatasync的順序讀寫的效率要低些。所以如果磁盤io壓力不大的話，并且如果系統(tǒng)使用了swap空間，可以考慮innodb_flush_method=O_DIRECT;

在大量隨機寫的環(huán)境中O_DIRECT要比fdatasync效率更高些，順序?qū)懚嗟脑?，還是默認的fdatasync更高效，因為咱們現(xiàn)在使用了insert buffer cache的使用（轉(zhuǎn)化成了順序?qū)懀?，個人認為還是默認值比較好，

7、innodb_adaptive_flushing 設置為 ON （使刷新臟頁更智能）

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

規(guī)則由原來的“大于innodb_max_dirty_pages_pct時刷新100個臟頁到磁盤”變?yōu)?“通過buf_flush_get_desired_flush_reate函數(shù)判斷重做日志產(chǎn)生速度確定需要刷新臟頁的最合適數(shù)目”，即使臟頁比例小于 innodb_max_dirty_pages_pct時也會刷新一定量的臟頁。
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值

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

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

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

10、innodb_adaptive_flushing_method 設置為 keep_average

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

11、innodb_stats_on_metadata=OFF

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

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

11、innodb_change_buffering=all

change  buffer可以認為是insert buffer的升級，可以對dml操作---insert、delete、update都進行緩沖，他們分別是：insert  buffer、delete buffer、purge buffer；

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

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

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

12、innodb_change_buffer_max_size

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

如果使用太多，那么當MySQL server crash，會進行很長時間的恢復工作（進行merger操作）

13、innodb_old_blocks_pct初始化默認是37,
（innodb體系架構 27頁）注意因為新來的page是放到了尾部3/8的位置(也就是屬于sublist of old blocks)所以sublist of new blocks只能來自于sublist of old blocks的移動

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

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

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

所以就有2種情況：

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

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

21、binlog_cache_size

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

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

怎么判斷我們當前的binlog_cache_size設置的沒問題呢？

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)

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

22.innodb_file_per_table兩個取值:

1：開啟獨立表空間；

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

優(yōu)點：

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

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

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

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

使用turncate table也會使空間收縮

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

缺點：

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

結論：共享表空間在Insert操作上少有優(yōu)勢。其它都沒獨立表空間表現(xiàn)好。當啟用獨立表空間時，請合理調(diào)整一 下：innodb_open_files，因為每個表對應一個文件，需要打開的文件個數(shù)比共享表空間要多，所以需要適當調(diào)大innodb_open_files的參數(shù)，并且調(diào)高linux內(nèi)核參數(shù) open files的限制1

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

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

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

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

23.sync_relay_log：

sync_relay_log：默認為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

當設置為1時，slave的I/O線程每次接收到master發(fā)送過來的binlog日志都要寫入系統(tǒng)緩沖區(qū)，然后刷入relay log中繼日志里，這樣是最安全的，因為在崩潰的時候，你最多會丟失一個事務，但會造成磁盤的大量I/O。

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

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

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

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

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

25.增加本地端口，以應對大量連接

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

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

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

1	echo ‘1048576' > /proc/sys/net/ipv4/tcp_max_syn_backlog

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

27.設置鏈接超時時間

1	echo '10'> /proc/sys/net/ipv4/tcp_fin_timeout

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

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

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

默認值如下：

# cat /proc/sys/kernel/sem

250     32000   32      128

說明：

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

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

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

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

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

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

然后sysctl -p

重啟mysql

如果設置過小，當大量并發(fā)的時候，會在錯誤日志中報錯：InnoDB: Warning: a long semaphore wait?。。。?/p>

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

網(wǎng)站題目：MySQL性能需要關注的參數(shù)有哪些
URL標題：http://jinyejixie.com/article36/iepipg.html

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

廣告

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