MYSQL中my.cnf參數(shù)的示例分析-創(chuàng)新互聯(lián)

這篇文章主要介紹了MYSQL中my.cnf參數(shù)的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

創(chuàng)新互聯(lián)建站是一家集網(wǎng)站建設,高青企業(yè)網(wǎng)站建設,高青品牌網(wǎng)站建設,網(wǎng)站定制,高青網(wǎng)站建設報價,網(wǎng)絡營銷,網(wǎng)絡優(yōu)化,高青網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學習、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。

mysql常用配置文件參數(shù)介紹和使用方法：

● max_conecctions：整個 MySQL 允許的大連接數(shù)；
這個參數(shù)主要影響的是整個 MySQL 應用的并發(fā)處理能力，當系統(tǒng)中實際需要的連接量大于max_conecctions 的情況下，由于 MySQL 的設置限制，那么應用中必然會產(chǎn)生連接請求的等待，從而限制了相應的并發(fā)量。所以一般來說，只要 MySQL 主機性能允許，都是將該參數(shù)設置的盡可能大一點。一般來說 500 到 800 左右是一個比較合適的參考值

● max_user_connections：每個用戶允許的大連接數(shù)；上面的參數(shù)是限制了整個 MySQL 的連接數(shù)，而 max_user_connections 則是針對于單個用戶的連接限制。在一般情況下我們可能都較少使用這個限制，只有在一些專門提供 MySQL 數(shù)據(jù)存儲服務，或者是提供虛擬主機服務的應用中可能需要用到。除了限制的對象區(qū)別之外，其他方面和max_connections 一樣。這個參數(shù)的設置完全依賴于應用程序的連接用戶數(shù)，對于普通的應用來說，完全沒有做太多的限制，可以盡量放開一些。

● net_buffer_length：網(wǎng)絡包傳輸中，傳輸消息之前的 net buffer 初始化大??；這個參數(shù)主要可能影響的是網(wǎng)絡傳輸?shù)男?，由于該參?shù)所設置的只是消息緩沖區(qū)的初始化大小，所以造成的影響主要是當我們的每次消息都很大的時候 MySQL 總是需要多次申請擴展該緩沖區(qū)大小。系統(tǒng)默認大小為 16KB，一般來說可以滿足大多數(shù)場景，當然如果我們的查詢都是非常小，每次網(wǎng)絡傳輸量都很少，而且系統(tǒng)內(nèi)存又比較緊缺的情況下，也可以適當將該值降低到8KB。

● max_allowed_packet：在網(wǎng)絡傳輸中，一次傳消息輸量的大值；這個參數(shù)與 net_buffer_length 相對應，只不過是 net buffer 的大值。當我們的消息傳輸量大于 net_buffer_length 的設置時，MySQL 會自動增大 net buffer 的大小，直到緩沖區(qū)大小達到 max_allowed_packet 所設置的值。系統(tǒng)默認值為 1MB，大值是 1GB，必須設定為 1024 的倍數(shù)，單位為字節(jié)。

● back_log：在 MySQL 的連接請求等待隊列中允許存放的大連接請求數(shù)。連接請求等待隊列，實際上是指當某一時刻客戶端的連接請求數(shù)量過大的時候，MySQL 主線程沒辦法及時給每一個新的連接請求分配（或者創(chuàng)建）連接線程的時候，還沒有分配到連接線程的所有請求將存放在一個等待隊列中，這個隊列就是 MySQL 的連接請求隊列。當我們的系統(tǒng)存在瞬時的大量連接請求的時候，則應該注意 back_log 參數(shù)的設置。系統(tǒng)默認值為 50，大可以設置為 65535。當我們增大 back_log 的設置的時候，同時還需要主義 OS 級別對網(wǎng)絡監(jiān)聽隊列的限制，因為如果 OS 的網(wǎng)絡監(jiān)聽設置小于 MySQL 的 back_log 設置的時候，我們加大“back_log”設置是沒有意義的。

在 MySQL 中，為了盡可提高客戶端請求創(chuàng)建連接這個過程的性能，實現(xiàn)了一個 Thread Cache 池，將空閑的連接線程存放在其中，而不是完成請求后就銷毀。這樣，當有新的連接請求的時候，MySQL 首先會檢查 Thread Cache 池中是否存在空閑連接線程，如果存在則取出來直接使用，如果沒有空閑連接線程，才創(chuàng)建新的連接線程。在 MySQL 中與連接線程相關的系統(tǒng)參數(shù)及狀態(tài)變量說明如下：
● thread_cache_size：Thread Cache 池中應該存放的連接線程數(shù)。
當系統(tǒng)最初啟動的時候，并不會馬上就創(chuàng)建 thread_cache_size 所設置數(shù)目的連接線程存放在Thread Cache 池中，而是隨著連接線程的創(chuàng)建及使用，慢慢的將用完的連接線程存入其中。當存放的連接線程達到 thread_cache_size 值之后，MySQL 就不會再續(xù)保存用完的連接線程了。如果我們的應用程序使用的短連接，Thread Cache 池的功效是最明顯的。因為在短連接的數(shù)據(jù)庫應用中，數(shù)據(jù)庫連接的創(chuàng)建和銷毀是非常頻繁的，如果每次都需要讓 MySQL 新建和銷毀相應的連接線程，那么這個資源消耗實際上是非常大的，而當我們使用了 Thread Cache 之后，由于連接線程大部分都是在創(chuàng)建好了等待取用的狀態(tài)，既不需要每次都重新創(chuàng)建，又不需要在使用完 之 后 銷 毀 ， 所 以 可 以 節(jié) 省 下 大 量 的 系 統(tǒng) 資 源 。 所 以 在 短 連 接 的 應 用 系 統(tǒng) 中 ，thread_cache_size 的值應該設置的相對大一些，不應該小于應用系統(tǒng)對數(shù)據(jù)庫的實際并發(fā)請求數(shù)。而如果我們使用的是長連接的時候，Thread Cache 的功效可能并沒有使用短連接那樣的大，但
也并不是完全沒有價值。因為應用程序即使是使用了長連接，也很難保證他們所管理的所有連接都能處于很穩(wěn)定的狀態(tài)，仍然會有不少連接關閉和新建的操作出現(xiàn)。在有些并發(fā)量較高，應
用服務器數(shù)量較大的系統(tǒng)中，每分鐘十來次的連接創(chuàng)建與關閉的操作是很常見的。而且如果應用服務器的連接池管理不是太好，容易產(chǎn)生連接池抖動的話，所產(chǎn)生的連接創(chuàng)建和銷毀操作將
會更多。所以即使是在使用長連接的應用環(huán)境中，Thread Cache 機制的利用仍然是對性能大有幫助的。只不過在長連接的環(huán)境中我們不需要將 thread_cache_size 參數(shù)設置太大，一般來說
可能 50 到 100 之間應該就可以了。

● thread_stack：每個連接線程被創(chuàng)建的時候，MySQL 給他分配的內(nèi)存大小。
當 MySQL 創(chuàng)建一個新的連接線程的時候，是需要給他分配一定大小的內(nèi)存堆?？臻g，以便存放客戶端的請求 Query 以及自身的各種狀態(tài)和處理信息。不過一般來說如果不是對 MySQL 的連接線
程處理機制十分熟悉的話，不應該輕易調(diào)整該參數(shù)的大小，使用系統(tǒng)的默認值（192KB）基本上可以所有的普通應用環(huán)境。如果該值設置太小，會影響 MySQL 連接線程能夠處理客戶端請求的Query 內(nèi)容的大小，以及用戶創(chuàng)建的 Procedures 和 Functions 等

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

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

● sort_buffer_size：系統(tǒng)中對數(shù)據(jù)進行排序的時候使用的 Buffer；Sort Buffer 同樣是針對單個 Thread 的，所以當多個 Thread 同時進行排序的時候，系統(tǒng)中就會出現(xiàn)多個 Sort Buffer。一般我們可以通過增大 Sort Buffer 的大小來提高 ORDER BY 或者是 GROUP BY的處理性能。系統(tǒng)默認大小為 2MB，大限制和 Join Buffer 一樣，在 MySQL 5.1.23 版本之前大為 4GB，從 5.1.23 版本開始，在除了 Windows 之外的 64 位的平臺上可以超出 4GB 的限制。如果應用系統(tǒng)中很少有 Join 語句出現(xiàn)，則可以不用太在乎 join_buffer_size 參數(shù)的大小設置，但是如果 Join 語句不是很少的話，個人建議可以適當增大 join_buffer_size 的設置到 1MB 左右，如果內(nèi)存充足甚至可以設置為 2MB。對于 sort_buffer_size 參數(shù)來說，一般設置為 2MB 到 4MB 之間可以滿足大多數(shù)
應用的需求。當然，如果應用系統(tǒng)中的排序都比較大，內(nèi)存充足且并發(fā)量不是特別的大的時候，也可以繼續(xù)增大 sort_buffer_size 的設置。在這兩個 Buffer 設置的時候，最需要注意的就是不要忘記是每個Thread 都會創(chuàng)建自己獨立的 Buffer，而不是整個系統(tǒng)共享的 Buffer，不要因為設置過大而造成系統(tǒng)內(nèi)存不足。


innodb_thread_concurrendy：此參數(shù)為innodb為保障服務正常運行的限流操作，設置為0表示由innodb自己控制；一般建議設置為服務器CPU的核數(shù)（不含超線程），過大會導致服務hang死等不可用情況


innodb_io_capacity：每秒后臺進程處理IO操作的數(shù)據(jù)頁上線，一般可設置為存儲總IO能力的75%，一般IO性能比較好的情況下此參數(shù)建議配置成1000


innodb_buffer_pool_instance：在innodb_buffer_pool中劃分實例，每個實例下都包含flush、LRU、free列表，一般大內(nèi)存建議配置多個innodb_buffer_pool_instance


innodb_max_dirty_pages_pct：innodb從innodb_buffer_pool中刷新臟頁到磁盤的比例，設置太高對IO影響較大，此參數(shù)與innodb_io_capacity結合使用，IO性能較好情況下可以設置為75%


innodb_flush_method：設置為O_DIRECT時，直接刷新內(nèi)存數(shù)據(jù)到磁盤避免raid設備上的緩存


innodb_file_per_table：設置為1，每個表單獨一個數(shù)據(jù)文件，這樣可以放在其他磁盤做軟連接，提升IO性能；另一方面可以降低共享表空間的IO競爭，避免ibdata1過大


innodb_flush_log_at_trx_commit：設置為0：每秒將log buffur中的內(nèi)容刷新到磁盤；設置為1：每次事務提交前將log buffur中內(nèi)容刷新到磁盤；設置為2：將log buffur中內(nèi)容寫入事務日志，但由于操作系統(tǒng)等緩存可能存在，不一定會刷新到磁盤


sync_binlog：刷新binlog的數(shù)目，非核心系統(tǒng)設置為1000，表示當累積1000條binlog記錄時才會刷新到磁盤；核心系統(tǒng)可以設置為1，保證主備服務器數(shù)據(jù)同步；雙1模式：即innodb_flush_log_at_trx_commit和sync_binlog都設置為1，這樣主備的數(shù)據(jù)時一致的，不會丟失數(shù)據(jù)

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MYSQL中my.cnf參數(shù)的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持創(chuàng)新互聯(lián)，關注創(chuàng)新互聯(lián)-成都網(wǎng)站建設公司行業(yè)資訊頻道，更多相關知識等著你來學習!

文章題目：MYSQL中my.cnf參數(shù)的示例分析-創(chuàng)新互聯(lián)
當前URL：http://muchs.cn/article42/dcpdec.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、App設計、靜態(tài)網(wǎng)站、定制網(wǎng)站、定制開發(fā)、全網(wǎng)營銷推廣

廣告

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