MYSQL中my.cnf參數的示例分析

這篇文章主要介紹了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，這樣主備的數據時一致的，不會丟失數據

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MYSQL中my.cnf參數的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!