mysql优化之连接优化
?
第一篇 序章
第二篇 連接優化
第三篇 索引優化
第四篇 查詢優化
第五篇 到實戰中去
連接優化
連接優化主要指客戶端連接數據庫以及數據庫為響應客戶端的請求而打開數據表和索引的過程中涉及到的參數調整。原文可以參考這里或者這里。(原文鏈接 http://ddbiz.com/?p=950)
盡管不同的mysql發行版本的編譯和鏈接方式也會影響到客戶端的鏈接請求,但是由于我的系統多是直接安裝mysql的發行包,且很少會做改動,因此關于手動編譯mysql的以達到優化的目的的方面,此處無法聊及,或許日后會有機會涉足其中。
根據MySQL如何使用內存中的說法,下面的參數會影響到客戶端的每個請求:
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 時需要調整此參數。
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 已經達到當前系統允許的最大值。
max_connections
命令行參數: –max_connections 或者 –max-connections
ini/cnf定義: max_connections
mysql 變量: max_connections
全局變量,可動態調整
MySQL數據庫允許的并發連接數。
對于一個訪問者眾多(pv值很高)的網站來說,有時可能會發生 : Too many connections 的錯誤。可以考慮增加此值。對于MySQL來說,能夠支持的最大的并發連接數,取決于很多因素,包括:
在內存允許的情況下,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 時,應該采取行動提高并發數.
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此值.
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此值.
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等
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?
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
調整觸發條件: 短鏈接、高并發的系統應用中.
上一篇 序章?下一篇 索引優化
轉載于:https://www.cnblogs.com/zengkefu/p/5645705.html
總結
以上是生活随笔為你收集整理的mysql优化之连接优化的全部內容,希望文章能夠幫你解決所遇到的問題。
                            
                        - 上一篇: 宏定义和内联函数的学习
 - 下一篇: 分享个B端产品分析报告