MySQL參數配置優化

                                             MySQL參數配置優化

max_connections

Variable Scope：      Global

Dynamic Variable：  Yes

Default：                   151 （mysql5.5+）

Meaning：                 允許用戶端同時連接配接的最大數

預設值以前是100,MySQL5.5+後151，但是預設值對大部分應用來說這都不夠。通過觀察Max_used_connections

狀态變量随着時間的變化。可以告訴你伺服器連接配接是不是在某個時間點有個尖峰。如果這個值達到了max_connections，說明用戶端至少被拒絕了一次。

建議值： 500+ （設定為你認為正常情況下有300或者更多連接配接，則可以設定為500或更多）

thread_cache_size

Variable Scope：       Global

Dynamic Variable：   Yes

Default：                    0（mysql5.6.7-）

Meaning：                 有多少線程應該緩存重用

其預設值在mysql5.6.8+（autosized），根據如下公式得到：8 + (max_connections / 100)，其上限值為100.設定這個變量，可以通過觀察伺服器一段時間的活動，來計算一個有理有據的值。觀察Threads_connected狀态變量并且找到它在一般情況下的最大值和最小值。例如：若Threads_connected狀态從150變化到175，可以設定線程緩存為75。但是不用設定的非常大，因為保持大量等待連接配接的空閑線程并沒有什麼真正的用處。也可以觀察Threads_created狀态随時間的變化。如果這個值很大或一直增長，這是另一個線索，告訴你可能需要調大thread_cache_size變量。Threads_cached來檢視有多少線程已經在緩存中了。

建議值：50-100

table_open_cache

Variable Scope:         Global

Dynamic Variable:     Yes

Default:                      400( mysql5.6.7- )

Meaning:                    所有線程打開表的數量

從官方文檔看出在MySQL5.6.8+開始預設值為2000，就能簡單的判斷出原來預設值是不夠的。可以通過觀察Opened_tables其值及其一段時間的變化來檢查該變量。如果看到Opened_tables的值很大并且又不經常執行FLUSH TABLES（執行其指令強制所有的表重新關閉且打開），那麼可能你應該增加該變量的值。

建議值： 4096（有另一種說法：這個值從max_connections的10倍開始設定）

open_files_limit

Dynamic Variable：   No

Default：                    0 （mysql5.6.7-）

Meaning：                 作業系統允許mysqld服務打開的檔案數。

其預設值在mysql5.6.8+（autosized），根據如下公式得到：

1) 10 + max_connections + (table_open_cache * 2)

2) max_connections * 5

3) open_files_limit value specified at startup, 5000 if none

要知道每個MyISAM表打開需要2個檔案句柄；每個用戶端的連接配接也是一個檔案句柄。有效的open_files_limit的值是基于系統啟動時所指定的值和max_connections，table_open_cache有關聯。

建議值：65535 （其值在大多作業系統是最安全的）

table_definition_cache

Variable Scope ：       Global

Dynamic Variable ：   Yes

Default ：                    400（mysql5.6.7-）

Meaning：                   緩存表定義的的數量（以.frm結尾的檔案）

其預設值在mysql5.6.8+（autosized），根據如下公式得到：400 + (table_open_cache / 2)   其上限值為：2000。常可以把table_definition_cache 設定得足夠高，以緩存所有的表定義。除非有上萬張表，否則這可能是最簡單的方法。

建議值：  根據真正的資料庫中表的數量（例如：資料庫執行個體有1000張表，可以将其設定為1000+）

back_log

Variable Scope:           Global

Dynamic Variable:       No

Default:                        50( mysql5.6.5- )

Meaning:                     在很短時間内，可以有多少個請求連結在堆棧中等待被處理。

其預設值在mysql5.6.6+（autosized），根據如下公式得到：  50 + (max_connections / 5) 其上限值為：900。

如果每秒的連接配接數很多，可以将其值調大。其值和OS的TCP/IP連結有關聯，和核心參數net.ipv4.tcp_max_syn_backlog的值相關，back_log的值不能大于其值。

建議值：2048 

max_allowed_packet

Variable Scope：         Global

Dynamic Variable：     Yes

Default:                         1MB（mysql5.6.5-）

Meaning:                       這個設定防止伺服器發送太大的包，也會控制多大的包可以被接受。

其預設值在mysql5.6.6+為4MB，其預設值可能太小了，但設定太大也可能有危險。如果設定太小，有時複制上會出現問題，通常表現為備庫不能接收主庫發過來的複制資料。使用mysql和mysqldump用戶端程式都可以指定其值的大小。

建議值： 16MB

max_connect_errors

Variable Scope：          Global

Dynamic Variable：      Yes

Default：                        100（mysql5.6.6+）

Meaning：                     最大的連接配接錯誤數

如果有時網絡短暫抽風了，或者應用配置出現錯誤，或另有問題，如權限，在短暫的時間内不斷地嘗試連結，用戶端可能被列入黑名單，然後将無法連接配接，知道再次重新整理主機緩存（FLUSH HOSTS）。這個選項的預設設定太小了，很容易導緻問題。你也許希望增加這個值，實際上，如果知道伺服器可以充分抵禦蠻力攻擊，可以把這個值設定的非常大，以有效地禁用主機黑名單。這個選項也就是所謂的可以防止暴力破解。

建議值： 1000000 （其值為Percona 給出的建議值，但是應該确定其主機的已有抵禦蠻力攻擊的能力）

skip_name_resolve

Dynamic Variable：      No

Default：                       OFF

Meaning：                     DNS查找

這個選項禁用了另一個網絡相關和鑒權認證的陷進：DNS查找。DNS是MySQL連接配接過程中的一個薄弱環節。當連接配接伺服器時，它試圖确定連接配接和使用的主機的主機名，作為身份驗證憑據的一部分。（就是說，你的憑據是使用者名，主機名，以及密碼，并不隻是使用者名和密碼）但是驗證主機來源，伺服器需要執行DNS的正向和反向查找。要是DNS有問題就悲劇了，在某些時間點這是必然的事。為了避免這種情況，我們強烈建議設定這個選項，在驗證時關閉DNS查找，這樣即快又安全。

建議值： ON

log_bin

Variable Scope：           Global

Dynamic Variable：       No

Meaning：                     是否開啟binlog

開啟此選項用來支援複制和時間點恢複。

建議值： 設定其值為mysql-bin來避免其預設生成的檔案名（也就是與主機名無關）

sync_binlog

Dynamic Variable：       Yes

Default：                         0

Meaning：                      控制MySQL怎麼重新整理二進制日志到磁盤

預設值為0，意味着MySQL并不重新整理，有作業系統自己決定什麼時候重新整理緩存到持久化裝置。如果這個值比0大，它指定了兩次重新整理到磁盤的動作之間間隔多少次二進制日志寫操作（如果autocommit被設定了，每個獨立的語句都是一次寫，否則就是一個事務一次寫）。如果沒有設定sync_binlog為1，那麼崩潰以後可能導緻二進制日志沒有同步事務資料。這可以輕易地導緻複制中斷，并且使得及時恢複變得不可能。無論如何，可以把這個值設定為1來獲得安全的保障。這樣就會要求MySQL把二進制日志和事務日志兩個檔案重新整理到不同的位置。這可能需要磁盤尋道，相對來說是個很慢的操作。

建議值：   1

expire_log_days

Variable Scope ：           Global

Dynamic Variable：        Yes

Meaning：                      伺服器在指定的天數之後清理舊的二進制日志

如果啟用了二進制日志，應該打開這個選項，可以讓伺服器在指定的天數之後清理舊的二進制日志。如果不啟用，最終伺服器的空間會被耗盡，導緻伺服器卡住或崩潰。

建議值：  7~14

tmp_table_size和max_heap_table_size

這兩個設定控制使用Memory引擎的記憶體臨時表能使用多大的記憶體。如果隐式記憶體臨時表的大小超過這兩個設定的值，将會被轉換為MyISAM表，是以它的大小可以繼續增長。（隐式臨時表是一種并非由自己建立，而是伺服器建立，用于儲存執行行中的查詢的中間結果的表）應該簡單地把這兩個變量設為同樣的值。但是要謹防這個變量太大了，臨時表最好呆在記憶體裡，但是如果它們被撐得很大，實際上還是讓它們使用磁盤比較好，否則可能會讓伺服器記憶體溢出。假設查詢語句沒有建立龐大的臨時表（通常可以通過合理的索引和查詢設計來避免），那把這些變量設大一點，免得把記憶體臨時表轉換為磁盤臨時表。這個過程可以在SHOW PROCESSLIST中看到。使用臨時表的情況可以通過狀态變量Created_tmp_tables 和 Created_tmp_disk_tables 來監控。

建議值： 設定兩個變量為同樣的值（這個大小要根據自己的SQL查詢級别及SQL語句的優化情況）

query_cache_size

Dynamic Variable ：     Yes

Default：                       0（mysql5.6.7-）

Meaning:                       緩存查詢結果的記憶體大小

查詢緩存使用的總記憶體空間，機關是位元組。這個值必須是1024的整數倍，否則MySQL實際配置設定的資料會和你指定的略有不同。

建議值：<512MB

sort_buffer_size

Variable Scope:              Global, Session

Dynamic Variable:          Yes

Meaning:                        查詢需要做排序操作時為該緩存配置設定記憶體大小

MySQL隻會在有查詢需要做排序操作時才會為該緩存配置設定記憶體，然後，一旦需要排序，MySQL就會立刻配置設定該參數指定大小的全部記憶體，而不管該排序是否需要這麼大的記憶體。 通過SHOW GLOBAL STATUS檢視如Sort_merge_passes/s 有很多，可以考慮增加sort_buffer_size的值，以來加快order by 或 group by語句的查詢速度（其排序或分組操作已不能查詢優化或索引優化）。如果查詢必須使用一個更大的排序緩存才能更好的執行，可以考慮session級别的設定其值。

建議值：1MB

join_buffer_size

Variable Scope：            Global, Session

Dynamic Variable ：       Yes

Meaning：                       設定使用連接配接查詢緩存的大小

此選項可以提高沒有使用索引的連接配接查詢的性能。全局的建議不要設定太大，可以動态設定session級别的值。

建議值：  8MB

read_rnd_buffer_size

Variable Scope ：              Global, Session

Dynamic Variable ：          Yes

Default：                            256kb

Meaning：                          讀取排序行的緩存的大小

MySQL隻會在有查詢需要時才會為該緩存配置設定記憶體，并且隻會配置設定需要的記憶體大小而不是全部指定的大小。如果增加其值，可以提高order by 的操作性能。

key_buffer_size

Variable Scope:                    Global

Dynamic Variable :               Yes

Default:                                 8MB

Meaning:                               MyISAM 表索引緩存的大小

如果MySQL server的表全部是或者說大多為MyISAM存儲引擎的，可以考慮将其設定為OS記憶體的30%。其緩存僅僅緩存的是索引塊，而不緩存資料。

建議值：  根據其資料庫中表的存儲引擎的類型來作為參考

myisam_sort_buffer_size

Variable Scope：                   Global, Session

Dynamic Variable ：              Yes

Default：                                8MB

Meaning：                             排序MyISAM的索引緩存的大小

當REPAIR TABLE或者建立索引，修改索引時操作（CREATE INDEX，ALTER TABLE）時，配置設定給用于其MyISAM索引排序的大小。

建議值:  8MB~256MB

innodb_buffer_pool_size

Variable Scope :                    Global

Dynamic Variable:                 No

Default：                               128MB

Meaning：                             InnoDB緩沖池的大小

如果大部分都是InnoDB表，InnoDB緩沖池或許比其他任何東西更需要記憶體。InnoDB緩沖池并不僅僅緩存索引：它還會緩存行的資料，自适應哈希索引，插入緩沖，鎖，以及其他内部資料結構。

建議值：80%+ 實體記憶體

innodb_buffer_pool_instances

Variable Scope ：                  Global

Dynamic Variable ：              No

Default：                                1（mysql5.6.5-）

Meaning：                              InnoDB緩存池的執行個體個數

從InnoDB1.0.x版本開始，允許有多個緩沖池執行個體。每個頁根據哈希值平均配置設定到不同的緩沖池執行個體中。這樣做的好處是減少資料庫内部資源競争，增加資料庫的并發處理能力。可以通過參數innodb_buffer_pool_instances來配置。在MySQL5.6.6+其預設值為：（autosized），除了Window 32bit其值是根據innodb_buffer_pool_size的大小動态得到，其它預設值為8.

建議值：4+ （mysql5.5+）

innodb_log_buffer_size

Variable Scope：                   Global

Meaning：                              InnoDB寫日志檔案到磁盤上的緩沖大小

一個較大的日志緩沖，可以使一個大的事務在commit之前不用将log寫到磁盤上。同樣的在update，delete，Insert

很多行時，也可以減少磁盤I/O的調用。

建議值：8~128MB

innodb_flush_log_at_trx_commit

Dynamic Variable：               Yes

Default：                                1

Meaning：                              控制日志緩沖重新整理的頻繁程度

日志緩沖必須重新整理到持久化存儲，以確定送出的事務完全被持久化了。如果和持久相比更在乎性能，可以修改

innodb_flush_log_at_trx_commit變量來控制日志緩沖重新整理的頻繁程度。可能的設定如下：

把日志緩沖寫到日志檔案，并且每秒重新整理一次，但是事務送出時不做任何事情。

1

将日志緩沖寫到日志檔案，并且每次事務送出都重新整理到持久化存儲。這是預設的（并且是最安全的）設定，該設定

能保證不會丢失任何已經送出的事務，除非磁盤或者OS是’僞‘重新整理。

2

每次送出時把日志緩沖寫到日志檔案，但是并不重新整理。Innodb每秒做一次重新整理。0與2最重要的不同是，如果MySQL

程序“挂了”，2不會丢失事務。

建議值： 1

innodb_log_file_size

Meaning：                              指定每個重做日志檔案的大小

重做日志檔案的大小設定對于InnoDB存儲引擎的性能有着非常大的影響。一方面重做日志檔案不能設定的太大，如果設定得很大，在恢複時可能需要很長的時間；另一方面又不能設定太小了，否則可能導緻一個事務的日志需要多次切換重做日志檔案。

建議值： 根據自己能接受的方面（更好的性能or更短的恢複時間）決定

innodb_io_capacity

Variable Scope：                    Global

Dynamic Variable：                Yes

Default：                                  200

InnoDB曾經在代碼裡寫死了假設伺服器運作在每秒100個I/O操作的單硬碟上。預設值很糟糕。現在可以告訴InnoDB伺服器有多大的I/O能力。有時需要把這個值設定得相當高（像SSD這樣極快的儲存設備上需要設定為上萬）才能穩定地重新整理髒頁。

建議值： 根據server的I/O能力有關系

innodb_read_io_threads和innodb_write_io_threads

這些選項控制有多少背景線程可以被I/O操作使用。最近版本的MySQL裡，預設值4個讀線程和4個寫線程，對大部分

伺服器這都足夠了，尤其是MySQL5.5裡面可以用作業系統原生的異步I/O以後。

建議值：各為4（即預設值）

innodb_log_files_in_group

Dynamic Variable：                No

Default ：                                2

Meaning：                               每組InnoDB重做日志檔案的個數

建議值：                                    2

innodb_file_per_table

Variable Scope ：                   Global

Dynamic Variable ：               Yes

Default：                                 OFF（mysql5.6.5-）

Meaning：                              控制InnoDB表空間存儲形式

其預設值在mysql5.6.6+後為ON。開啟此選項後，關于InnoDB表的資料和索引單獨存儲在自己的表空間中（.ibd結尾的檔案）。否則，存儲在系統的表空間中（ibdata）。

特别說明：

1）：此文檔并非涵蓋所有的參數。

2）：此總結不是亂寫的，參考書籍：《High Performance MySQL》；《MySQL官方文檔5.5,5.6》；《MySQL技術内幕InnoDB存儲引擎》；《MySQL Troubleshooting》。

友情提示：

關于mysql參數配置的優化可以參考一下工具

1）：mysqltuner

2）：Percona 的線上工具https://tools.percona.com/

關于檢視mysql的global  status狀态值輔助工具

1）：Percona的Percona_Toolkit工具包中的pt-mext

别人的不一定符合自己的環境。了解才是最重要。