對MySQL性能影響關系緊密的五大配置參數

 

對MySQL性能影響關系緊密的五大配置參數

以下的文章主要是對MySQL 性能影響關系緊密的五大配置參數的介紹,我前幾天在相關網站看見對MySQL性能影響關系緊密的五大配置參數的資料,覺得挺好,就拿出來供大傢分享,望你能有所收獲。

(一)連接

連接通常來自Web 服務器,下面列出瞭一些與連接有關的參數,以及該如何設置它們。

1、max_connections

這是Web服務器允許的最大連接數,記住每個連接都要使用會話內存(關於會話內存,文章後面有涉及)。

2、max_packet_allowed

最大數據包大小,通常等於你需要在一個大塊中返回的最大數據集的大小,如果你在使用遠程mysqldump,那它的值需要更大。

3、aborted_connects

檢查系統狀態的計數器,確定其沒有增長,如果數量增長說明客戶端連接時遇到瞭錯誤。

4、thread_cache_size

入站連接會在MySQL中創建一個新的線程,因為MySQL中打開和關閉連接都很廉價,速度也快,它就沒有象其它數據庫 ,如Oracle 那麼多持續連接瞭,但線程預先創建並不會節約時間,這就是為什麼 要MySQL線程緩存的原因瞭。

如果在增長請密切註意創建的線程,讓你的線程緩存更大,對於2550或100的thread_cache_size,內存占用也不多。

(二)查詢緩存

(三)臨時表

內存速度是相當快的,因此我們希望所有的排序操作都在內存中進行,我們可以通過調整查詢讓結果集更小以實現內存排序,或將變量設置得更大。

tmp_table_size

max_heap_table_size

無論何時在MySQL中創建臨時表,它都會使用這兩個變量的最小值作為臨界值,除瞭在磁盤上構建臨時表外,還會創建許多會話,這些會話會搶占有 限制的資源,因此最好是調整查詢而不是將這些參數設置得更高,同時,需要註意的是有BLOB或TEXT字段類型的表將直接寫入磁盤。 深入淺出MySQL雙向復制技術

(四)會話內存

MySQL中每個會話都有其自己的內存,這個內存就是分配給SQL 查詢的內存,因此你想讓它變得盡可能大以滿足需要。但你不得不平衡同一時間數 據庫內一致性會話的數量。這裡顯得有點黑色藝術的是MySQL是按需分配緩存的,因此,你不能隻添加它們並乘以會話的數量,這樣估算下來比MySQL典型 的使用要大得多。

最佳做法是啟動MySQL,連接所有會話,然後繼續關註頂級會話的VIRT列,mysqld行的數目通常保持相對穩定,這就是實際的內存 總用量,減去所有的靜態MySQL內存區域,就得到瞭實際的所有會話內存,然後除以會話的數量就得到平均值。

1、read_buffer_size

緩存連續掃描的塊,這個緩存是跨存儲引擎的,不隻是MyISAM表。

2、sort_buffer_size

執行排序緩存區的大小,最好將其設置為1M-2M,然後在會話中設置,為一個特定的查詢設置更高的值。

3、join_buffer_size

執行聯合查詢分配的緩存區大小,將其設置為1M-2M大小,然後在每個會話中再單獨按需設置。

4、read_rnd_buffer_size

用於排序和order by操作,最好將其設置為1M,然後在會話中可以將其作為一個會話變量設置為更大的值。

(五)慢速查詢日志

慢速查詢日志是MySQL很有用的一個特性。

1、log_slow_queries

MySQL參數中log_slow_queries參數在my.cnf文件中設置它,將其設置為on,默認情況下,MySQL會將文件放到數據目錄,文件以“主機名-slow.log”的形式命名,但你在設置這個選項的時候也可以為其指定一個名字。

2、long_query_time

默認值是10秒,你可以動態設置它,值從1到將其設置為on,如果數據庫啟動瞭,默認情況下,日志將關閉。截至5.1.21和安裝瞭Google 補丁的版本,這個選項可以以微秒設置,這是一個瞭不起的功能,因為一旦你消除瞭所有查詢時間超過1秒的查詢,說明調整非常成功,這樣可以幫助 你在問題變大之前消除問題SQL。

3、log_queries_not_using_indexes

開啟這個選項是個不錯的主意,它真實地記錄瞭返回所有行的查詢。

小結

我們介紹瞭MySQL參數的五大類設置,平時我們一般都很少碰它們,在進行MySQL性能調優和故障診斷時這些參數還是非常有用的。

MySQL中的緩存查詢包括兩個解析查詢計劃,以及返回的數據集,如果基礎表數據或結構有變化,將會使查詢緩存中的項目 無效。

1、query_cache_min_res_unit

MySQL參數中query_cache_min_res_unit查詢緩存中的塊是以這個大小進行分配的,使用下面的公式計算查詢緩存的平均大小,根據計算結果設置這個變量,MySQL就會更有效地使用查詢緩存,緩存更多的查詢,減少內存的浪費。

2、query_cache_size

這個參數設置查詢緩存的總大小。

3、query_cache_limit

這個參數告訴MySQL丟掉大於這個大小的查詢,一般大型查詢還是比較少見的,如運行一個批處理執行一個大型報表的統計,因此那些大型結果集不應該填滿查詢緩存。

   1. qcache hit  ratio  =  qcache_hits  / (qcache_hits + com_select)

使用

   1. SQL >  show status like ‘qcache%’; 

   2. SQL >  show status like ‘com_%’; 

找到這些變量。

   1. average query  size  = (query_cache_size – qcache_free_memory)/qcache_queries_in_cache

使用

   1. SQL >  show variables like ‘query%’; 

   2. qcache_* status variables you can get with: 

   3. SQL >  show status like ‘qcache%’; 

獲取query_cache_size的值。

發佈留言