【MYSQL】my.cnf引數詳解

整理了mysql常用配置檔案引數介紹和使用方法：

● max_conecctions：整個 MySQL 允許的最大連線數；
這個引數主要影響的是整個 MySQL 應用的併發處理能力，當系統中實際需要的連線量大於max_conecctions 的情況下，由於 MySQL 的設定限制，那麼應用中必然會產生連線請求的等待，從而限制了相應的併發量。所以一般來說，只要 MySQL 主機效能允許，都是將該引數設定的儘可能大一點。一般來說 500 到 800 左右是一個比較合適的參考值

● max_user_connections：每個使用者允許的最大連線數；上面的引數是限制了整個 MySQL 的連線數，而 max_user_connections 則是針對於單個使用者的連線限制。在一般情況下我們可能都較少使用這個限制，只有在一些專門提供 MySQL 資料儲存服務，或者是提供虛擬主機服務的應用中可能需要用到。除了限制的物件區別之外，其他方面和max_connections 一樣。這個引數的設定完全依賴於應用程式的連線使用者數，對於普通的應用來說，完全沒有做太多的限制，可以儘量放開一些。

● net_buffer_length：網路包傳輸中，傳輸訊息之前的 net buffer 初始化大小；這個引數主要可能影響的是網路傳輸的效率，由於該引數所設定的只是訊息緩衝區的初始化大小，所以造成的影響主要是當我們的每次訊息都很大的時候 MySQL 總是需要多次申請擴充套件該緩衝區大小。系統預設大小為 16KB，一般來說可以滿足大多數場景，當然如果我們的查詢都是非常小，每次網路傳輸量都很少，而且系統記憶體又比較緊缺的情況下，也可以適當將該值降低到8KB。

● max_allowed_packet：在網路傳輸中，一次傳訊息輸量的最大值；這個引數與 net_buffer_length 相對應，只不過是 net buffer 的最大值。當我們的訊息傳輸量大於 net_buffer_length 的設定時，MySQL 會自動增大 net buffer 的大小，直到緩衝區大小達到 max_allowed_packet 所設定的值。系統預設值為 1MB，最大值是 1GB，必須設定為 1024 的倍數，單位為位元組。

● back_log：在 MySQL 的連線請求等待佇列中允許存放的最大連線請求數。連線請求等待佇列，實際上是指當某一時刻客戶端的連線請求數量過大的時候，MySQL 主執行緒沒辦法及時給每一個新的連線請求分配（或者建立）連線執行緒的時候，還沒有分配到連線執行緒的所有請求將存放在一個等待佇列中，這個佇列就是 MySQL 的連線請求佇列。當我們的系統存在瞬時的大量連線請求的時候，則應該注意 back_log 引數的設定。系統預設值為 50，最大可以設定為 65535。當我們增大 back_log 的設定的時候，同時還需要主義 OS 級別對網路監聽佇列的限制，因為如果 OS 的網路監聽設定小於 MySQL 的 back_log 設定的時候，我們加大“back_log”設定是沒有意義的。

在 MySQL 中，為了儘可提高客戶端請求建立連線這個過程的效能，實現了一個 Thread Cache 池，將空閒的連線執行緒存放在其中，而不是完成請求後就銷燬。這樣，當有新的連線請求的時候，MySQL 首先會檢查 Thread Cache 池中是否存在空閒連線執行緒，如果存在則取出來直接使用，如果沒有空閒連線執行緒，才建立新的連線執行緒。在 MySQL 中與連線執行緒相關的系統引數及狀態變數說明如下：
● thread_cache_size：Thread Cache 池中應該存放的連線執行緒數。
當系統最初啟動的時候，並不會馬上就建立 thread_cache_size 所設定數目的連線執行緒存放在Thread Cache 池中，而是隨著連線執行緒的建立及使用，慢慢的將用完的連線執行緒存入其中。當存放的連線執行緒達到 thread_cache_size 值之後，MySQL 就不會再續儲存用完的連線執行緒了。如果我們的應用程式使用的短連線，Thread Cache 池的功效是最明顯的。因為在短連線的資料庫應用中，資料庫連線的建立和銷燬是非常頻繁的，如果每次都需要讓 MySQL 新建和銷燬相應的連線執行緒，那麼這個資源消耗實際上是非常大的，而當我們使用了 Thread Cache 之後，由於連線執行緒大部分都是在建立好了等待取用的狀態，既不需要每次都重新建立，又不需要在使用完 之 後 銷 毀 ， 所 以 可 以 節 省 下 大 量 的 系 統 資 源 。 所 以 在 短 連 接 的 應 用 系 統 中 ，thread_cache_size 的值應該設定的相對大一些，不應該小於應用系統對資料庫的實際併發請求數。而如果我們使用的是長連線的時候，Thread Cache 的功效可能並沒有使用短連線那樣的大，但
也並不是完全沒有價值。因為應用程式即使是使用了長連線，也很難保證他們所管理的所有連線都能處於很穩定的狀態，仍然會有不少連線關閉和新建的操作出現。在有些併發量較高，應
用伺服器數量較大的系統中，每分鐘十來次的連線建立與關閉的操作是很常見的。而且如果應用伺服器的連線池管理不是太好，容易產生連線池抖動的話，所產生的連線建立和銷燬操作將
會更多。所以即使是在使用長連線的應用環境中，Thread Cache 機制的利用仍然是對效能大有幫助的。只不過在長連線的環境中我們不需要將 thread_cache_size 引數設定太大，一般來說
可能 50 到 100 之間應該就可以了。

● thread_stack：每個連線執行緒被建立的時候，MySQL 給他分配的記憶體大小。
當 MySQL 建立一個新的連線執行緒的時候，是需要給他分配一定大小的記憶體堆疊空間，以便存放客戶端的請求 Query 以及自身的各種狀態和處理資訊。不過一般來說如果不是對 MySQL 的連線線
程處理機制十分熟悉的話，不應該輕易調整該引數的大小，使用系統的預設值（192KB）基本上可以所有的普通應用環境。如果該值設定太小，會影響 MySQL 連線執行緒能夠處理客戶端請求的Query 內容的大小，以及使用者建立的 Procedures 和 Functions 等

計算出系統新建連線連線的 Thread
Cache 命中率，也就是透過 Thread Cache 池中取得連線執行緒的次數與系統接收的總連線次數的比率，如下：
Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%我們可以透過上面的這個運算公式計算一下上面環境中的 Thread Cache 命中率：Thread_Cache_Hit= (127 - 12) / 127 * 100% = 90.55%一般來說，當系統穩定執行一段時間之後，我們的 Thread Cache 命中率應該保持在 90%左右甚至更高的比率才算正常。可以看出上面環境中的 Thread Cache 命中比率基本還算是正常的。Table Cache 相關的最佳化，我們先來看一下 MySQL 開啟表的相關機制。由於多執行緒的實現機制，為了儘可能的提高效能，在MySQL 中每個執行緒都是獨立的開啟自己需要的表的檔案描述符，而不是透過共享已經開啟的表的檔案描述符的機制來實現。當然，針對於不同的儲存引擎可能有不同的處理方式。如 MyISAM 表，每一個客戶端執行緒開啟任何一個 MyISAM 表的資料檔案都需要開啟一個檔案描述符，但如果是索引檔案，則可以多個執行緒共享同一個索引檔案的描述符。對於 Innodb 的儲存引擎，如果我們使用的是共享表空間來儲存資料，那
麼我們需要開啟的檔案描述符就比較少，而如果我們使用的是獨享表空間方式來儲存資料，則同樣，由於儲存表資料的資料檔案較多，則同樣會開啟很多的表檔案描述符。除了資料庫的實際表或者索引開啟以外，臨時檔案同樣也需要使用檔案描述符，同樣會佔用系統中 open_files_limit 的設定限額。為了解決開啟表檔案描述符太過頻繁的問題，MySQL 在系統中實現了一個 Table Cache 的機制，和前面介紹的 Thread Cache 機制有點類似，主要就是 Cache 開啟的所有表檔案的描述符，當有新的請求的時候不需要再重新開啟，使用結束的時候也不用立即關閉。透過這樣的方式來減少因為頻繁開啟關閉檔案描述符所帶來的資源消耗。我們先看一看 Table Cache 相關的系統引數及狀態變數。在 MySQL 中我們透過 table_cache（從 MySQL5.1.3 開始改為table_open_cache），來設定系統中為我們 Cache 的開啟表檔案描述符的數量。透過 MySQL 官方手冊中的介紹，我們設定 table_cache 大小的時候應該透過 max_connections 引數計算得來，公式如下：
table_cache = max_connections * N；其中 N 代表單個 Query 語句中所包含的最多 Table 的數量。但是我個人理解這樣的計算其實並不是太準確，分析如下：
首先，max_connections 是系統同時可以接受的最大連線數，但是這些連線並不一定都是 active 狀態的，也就是說可能裡面有不少連線都是處於 Sleep 狀態。而處於 Sleep 狀態的連線是不可能開啟任何Table 的。其次，這個 N 為執行 Query 中包含最多的 Table 的 Query 所包含的 Table 的個數也並不是太合適，因為我們不能忽略索引檔案的開啟。雖然索引檔案在各個連線執行緒之間是可以共享開啟的連線描述符的，但總還是需要的。而且，如果我 Query 中的每個表的訪問都是透過現透過索引定位檢索的，甚至可能還是透過多個索引，那麼該 Query 的執行所需要開啟的檔案描述符就更多了，可能是 N 的兩倍甚至三倍。最後，這個計算的公式只能計算出我們同一時刻需要開啟的描述符的最大數量，而 table_cache 的設定也不一定非得根據這個極限值來設定，因為 table_cache 所設定的只是 Cache 開啟的描述符的數量的大小，而不是最多能夠開啟的量的大小。

join_buffer_size ：當我們的 Join 是 ALL ， index ， rang 或者 index_merge 的時候使用的Buffer；實際上這種 Join 被稱為 Full Join。實際上參與 Join 的每一個表都需要一個 Join Buffer，所以在Join 出現的時候，至少是兩個。Join Buffer 的設定在 MySQL 5.1.23 版本之前最大為 4GB，但是從5.1.23 版本開始，在除了 Windows 之外的 64 位的平臺上可以超出 4BG 的限制。系統預設是 128KB。

● sort_buffer_size：系統中對資料進行排序的時候使用的 Buffer；Sort Buffer 同樣是針對單個 Thread 的，所以當多個 Thread 同時進行排序的時候，系統中就會出現多個 Sort Buffer。一般我們可以透過增大 Sort Buffer 的大小來提高 ORDER BY 或者是 GROUP BY的處理效能。系統預設大小為 2MB，最大限制和 Join Buffer 一樣，在 MySQL 5.1.23 版本之前最大為 4GB，從 5.1.23 版本開始，在除了 Windows 之外的 64 位的平臺上可以超出 4GB 的限制。如果應用系統中很少有 Join 語句出現，則可以不用太在乎 join_buffer_size 引數的大小設定，但是如果 Join 語句不是很少的話，個人建議可以適當增大 join_buffer_size 的設定到 1MB 左右，如果記憶體充足甚至可以設定為 2MB。對於 sort_buffer_size 引數來說，一般設定為 2MB 到 4MB 之間可以滿足大多數
應用的需求。當然，如果應用系統中的排序都比較大，記憶體充足且併發量不是特別的大的時候，也可以繼續增大 sort_buffer_size 的設定。在這兩個 Buffer 設定的時候，最需要注意的就是不要忘記是每個Thread 都會建立自己獨立的 Buffer，而不是整個系統共享的 Buffer，不要因為設定過大而造成系統記憶體不足。


innodb_thread_concurrendy：此引數為innodb為保障服務正常執行的限流操作，設定為0表示由innodb自己控制；一般建議設定為伺服器CPU的核數（不含超執行緒），過大會導致服務hang死等不可用情況


innodb_io_capacity：每秒後臺程式處理IO操作的資料頁上線，一般可設定為儲存總IO能力的75%，一般IO效能比較好的情況下此引數建議配置成1000


innodb_buffer_pool_instance：在innodb_buffer_pool中劃分例項，每個例項下都包含flush、LRU、free列表，一般大記憶體建議配置多個innodb_buffer_pool_instance


innodb_max_dirty_pages_pct：innodb從innodb_buffer_pool中重新整理髒頁到磁碟的比例，設定太高對IO影響較大，此引數與innodb_io_capacity結合使用，IO效能較好情況下可以設定為75%


innodb_flush_method：設定為O_DIRECT時，直接重新整理記憶體資料到磁碟避免raid裝置上的快取


innodb_file_per_table：設定為1，每個表單獨一個資料檔案，這樣可以放在其他磁碟做軟連線，提升IO效能；另一方面可以降低共享表空間的IO競爭，避免ibdata1過大


innodb_flush_log_at_trx_commit：設定為0：每秒將log buffur中的內容重新整理到磁碟；設定為1：每次事務提交前將log buffur中內容重新整理到磁碟；設定為2：將log buffur中內容寫入事務日誌，但由於作業系統等快取可能存在，不一定會重新整理到磁碟


sync_binlog：重新整理binlog的數目，非核心繫統設定為1000，表示當累積1000條binlog記錄時才會重新整理到磁碟；核心系統可以設定為1，保證主備伺服器資料同步；雙1模式：即innodb_flush_log_at_trx_commit和sync_binlog都設定為1，這樣主備的資料時一致的，不會丟失資料