mysql最佳化之串連最佳化

標籤：

 

Posted by Money Talks on 2012/02/23 |

第一篇 序章
第二篇 串連最佳化
第三篇 索引最佳化
第四篇 查詢最佳化
第五篇 到實戰中去

串連最佳化

串連最佳化主要指用戶端串連資料庫以及資料庫為響應用戶端的請求而開啟資料表和索引的過程中涉及到的參數調整。原文可以參考這裡或者這裡。(原文連結 http://ddbiz.com/?p=950)
儘管不同的mysql發行版本的編譯和連結方式也會影響到用戶端的連結請求，但是由於我的系統多是直接安裝mysql的發行包，且很少會做改動，因此關於手動編譯mysql的以達到最佳化的目的的方面，此處無法聊及，或許日後會有機會涉足其中。

根據MySQL如何使用記憶體中的說法，下面的參數會影響到用戶端的每個請求：

1. open-files-limit

   命令列參數: –open-files-limit=#
   ini/cnf參數: open-files-limit
   mysql 變數: open_files_limit
   全域變數，不可動態調整，取值範圍 0到65535。
   open_files_limit指mysql能夠開啟的檔案控制代碼數。該值不足時，會引發 Too many open files錯誤。具體需要多少個檔案控制代碼，還需要根據 max_connections 和 table_open_cache來計算。
   一個有趣的現象是，在我的64bit linux中， –open-files-limit或者–open_files_limit可以設定超過 64k，如：
   

   open-files-limit可能受到作業系統的限制，比如linux中，/proc/sys/fs/file-max，就限制了系統最大能夠開啟的檔案控制代碼數目。像oracle在linux的安裝運行要求，對最低要求就是要超過 64k. 可以通過修改/etc/sysctl.conf，增加或者修改 fs.file-max=#來增加系統最大開啟值，別忘了修改完了，用 sysctl -p 來啟用新值(以上操作為centos/rhel)。
   在linux中，還有一個參數可能會限制系統最大開啟檔案數值，就是/etc/security/limits.conf
   
   具體如何修改其值，請參考系統文檔

   受如下參數影響: 受系統限制
   將影響如下參數: max_connections table_open_cache
   調整觸發條件: 當系統出現 Too many open files 時需要調整此參數。

2. thread_stack

   命令列參數: –thread_stack=#
   ini/cnf參數: thread_stack
   mysql 變數: thread_stack
   全域變數，不可動態調整。
   32bit系統中預設為192k, 64bit系統中預設為256k. 先談及thread_stack是因為他對下面要講的max_connections有關鍵影響因素。

   thread_stack 對應於作業系統層面中的stack size，windows中的預設線程的stack size為1M, linux根據版本不同會有變化，一般在8m或者10m。在我的幾個Centos 5.x/6.x中，預設的stack size 都是10M(這要比windows高出10倍)

   ulimit -s
   10240

   stack size在32bit的OS中是一個很重要的參數，減少一個線程的stack size可以增加線程數，比如從10m減少到64k。但是在64bit的Linux（核心版本>= 2.6.x）中，如果允許 /proc/sys/vm/overcommit_memory，stack size或許沒那麼重要了。

   受如下參數影響: 無
   將影響如下參數: max_connections
   調整觸發條件: max_connections 已經達到當前系統允許的最大值。

3. max_connections

   命令列參數: –max_connections 或者 –max-connections
   ini/cnf定義: max_connections
   mysql 變數: max_connections
   全域變數，可動態調整

   MySQL資料庫允許的並發串連數。
   對於一個訪問者眾多(pv值很高)的網站來說，有時可能會發生 : Too many connections 的錯誤。可以考慮增加此值。對於MySQL來說，能夠支援的最大的並發串連數，取決於很多因素，包括：

   1. 作業系統執行緒模式、作業系統版本(參見 thread_size)
   2. 可用的記憶體數量
   3. 每個串連的記憶體使用量量/工作負載(參見 thread_size)
   4. 預期的伺服器回應時間

   在記憶體允許的情況下,32bit windows可以支援最大2000左右的並發請求（因為單進程最大支援的記憶體為2G，而預設的一個線程需要資源為1MB），64bit windows 也可以根據記憶體計算得出可支援的線程數。（關於windows中可用線程估算，可以參考Mark Russinovich的文章Pushing the Limits of Windows: Processes and Threads，或者參考微軟的一篇簡述(進程地址空間)）。
   而Linux中的因素可能更複雜，不過 stack_size 依然如同windows中一樣，是制約線程數的一個重要因素，最大線程數在Liunx下也有預設值，cat /proc/sys/kernel/threads-max, 當不調整這個值時，MySQL的max_connections應該遠小於它。

   在實際應用中，可支援的並發數將會遠小於理論值，因為每個線程不可能只是空串連一下就斷開。線程工作時的CPU/記憶體損耗，會降低整個系統的可用資源撫平。對於MySQL來說，其提供了一個可以調整stack size的參數: thread_stack.
   mysql 的 max_connections * thread_stack 應小於可用記憶體；根據mysql的官方文檔(doc5.5)，linux(或者solaris)下，可以支援500到1000個並發串連，如果每個串連的工作很小，且伺服器記憶體很大的話，那麼可以支援到10k的連結。在windows下，則有一個 (open tables*2+open connection) < 2048的限制。因此：

   受如下參數影響: thread_stack table_open_cache open_file_limit
   將影響如下參數: 無
   調整觸發條件: 當threads_connected（show status like ‘threads_connected’） 接近 max_connections 時，應該採取行動提高並發數.

4. thread_cache_size

   命令列參數: –thread_cache_size
   ini/cnf定義: thread_cache_size
   mysql 變數: thread_cache_size
   全域變數，可動態調整, 預設值0，最大值16k

   mysql使用此參數限定資料庫服務中，緩衝多少個線程共用戶端使用。 如果伺服器每秒都有幾百個新的串連出現，那麼這個值應該挑高一些。通過評測connections和threads_created判定是否需要增加thread_cache_size.
   mysql> show status like ‘%connections%’; 輸出
   Connections 嘗試串連請求（包括不能成功建立串連的請求）
   Max_used_connections 最大並發串連數量

   mysql> show status like ‘threads_c%’; 輸出
   threads_cached 當前緩衝線程數
   threads_connected 當前串連數
   thread_created 線程建立數

   當串連緩衝的利用率( thread cache hit =(connections – threads_created)/connections*100%） 的值較低時，表明mysql需要建立更多的線程（線程緩衝不夠了）來接受用戶端請求。

   受如下參數影響: 無
   將影響如下參數: 無
   調整觸發條件: 當thread cache hit較低時，應該採取行動提高thread_cache_size此值.

5. table_open_cache/table_cache

   命令列參數: –table-open-cache
   ini/cnf定義: table_open_cache
   mysql 變數: table_open_cache
   全域變數，可動態調整, 預設值400，最大值512k

   mysql開啟表的描述符，被緩衝在table_open_cache中，table_open_cache >= max_connections * 2，這是因為有些表開啟時，需要兩個檔案符，如myisam表，另外還有index、暫存資料表等的檔案符。自連結的查詢語句，會額外再多開啟一個該表的檔案符。

   一個針對性的設定是，找到和資料庫有關的所有最複雜的查詢語句（包括自連結,left/right/outer join，以及group 等統計語句）查看這些連結將開啟多少資料表，設定此值為 N，則

   table_open_cache > max_connections * N

   受如下參數影響: max_connections open_file_limit
   將影響如下參數: max_connections
   調整觸發條件: 當opened_tables（show status like ‘opened_tables’）值很大時，應該採取行動提高table_open_cache此值.

6. net_buffer_length

   命令列參數: –net_buffer_length
   ini/cnf定義: net_buffer_length
   mysql 變數: thread_cache_size
   全域變數，可動態調整, 預設值16k，範圍1k到1m.

   用戶端串連時的緩衝和結果緩衝, 可以動態調整（自動調整，意味著 set net_buffer_length=xxx是無效的）到最大max_allowed_packet的大小。每個SQL語句結束後，這個值會恢複到初始值。記憶體不足時–這個情況也很少了，畢竟現在記憶體這麼便宜–或者並發串連很大時，可以適當的縮小這個初始值，比如1k.

   受如下參數影響: max_allowed_packet
   將影響如下參數: 無
   調整觸發條件: 如果要裝載/匯入/匯出大量資料時；查詢結果中包含大的資料欄位時，如TEXT,BLOB等

7. max_allowed_packet

   命令列參數: –max_allowed_packet
   ini/cnf定義: max_allowed_packet
   mysql 變數: max_allowed_packet
   全域變數，可動態調整, 預設值1m，範圍1k到1g.

   用戶端和服務端的max_allowed_packet需要一致，或者用戶端的max_allowed_packet要大於服務端的max_allowed_packet。

   受如下參數影響: 無
   將影響如下參數: 無
   調整觸發條件: 如果要裝載/匯入/匯出大量資料時；查詢結果中包含大的資料欄位時，如TEXT,BLOB等

   Q:max_allowed_packet和net_buffer_length會影響load data infile嗎？
   A:No 

8. wait_timeout

   命令列參數: –wait_timeout
   ini/cnf定義: wait_timeout
   mysql 變數: wait_timeout
   全域變數，可動態調整, 預設值8小時，範圍1秒到31536000.
   wait_timeout定義了一個已串連的用戶端在不進行任何查詢動作時最常的空閑時間。
   注意：對已經建立的串連將不產生影響。
   可以通過 show processlist 來查看當前資料庫連接的狀況，如：
   [singlepic id=96 w=320 h=240 float=none]

   受如下參數影響:
   將影響如下參數: max_connections
   調整觸發條件: 短連結、高並發的系統應用中.

上一篇 序章 下一篇 索引最佳化

mysql最佳化之串連最佳化