MySQL優(yōu)化之連接優(yōu)化
上文MySQL優(yōu)化之緩存優(yōu)化 這篇文章中提到了一個很重要的概念，就是show variables是用來表示系統(tǒng)編譯或者配置在my.cnf中的變量值。而show status則稱之為狀態(tài)值，顯示的是當(dāng)前服務(wù)實(shí)例運(yùn)行所具有的狀態(tài)信息，是一個動態(tài)改變的值。因此常用來觀測當(dāng)前MySQl的運(yùn)行是否正常，如果不正常那么依靠調(diào)整靜態(tài)參數(shù)來提高M(jìn)ySQL的性能。所以明白這兩個概念的不同，是后面調(diào)優(yōu)的基礎(chǔ)。
MySQL 連接優(yōu)化
記得有一次在公司內(nèi)部連接MySQL的時候，總是連接不上。找到DBA后，經(jīng)過排查原因，是當(dāng)前MySQL連接數(shù)滿了，經(jīng)過調(diào)整后，解決了問題。引發(fā)連接數(shù)過多的錯誤原因一般有兩個，第一的確是有很多人在連接MySQL，造成連接數(shù)用完。第二種是max_connections值過小。
1、連接參數(shù)（show variables）
mysql> show variables like '%connect%'; +-----------------------------------------------+-----------------+ | Variable_name | Value | +-----------------------------------------------+-----------------+ | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | connect_timeout | 10 | | disconnect_on_expired_password | ON | | init_connect | | | max_connect_errors | 100 | | max_connections | 151 | | max_user_connections | 0 | | performance_schema_session_connect_attrs_size | 512 | +-----------------------------------------------+-----------------+
max_connections是指MySQL服務(wù)實(shí)例能夠同時接受的的最大并發(fā)連接數(shù)。MySQL實(shí)際上支持最大連接數(shù)加一的算法，保障當(dāng)連接數(shù)用完的時候，超級管理員依然可以和服務(wù)端建立連接，進(jìn)行管理。
max_user_connections設(shè)置指定賬號的最大并發(fā)連接數(shù)。
max_connect_errors 當(dāng)某臺非法主機(jī)惡意連接MySQL服務(wù)端，遭到的錯誤達(dá)到設(shè)置值后，MySQL會解決來自該主機(jī)的所有連接。但執(zhí)行flush hosts后會清零。
2、連接狀態(tài)(show status)
有一點(diǎn)需要注意的，變量值（show variables）是以小寫字母開頭的，而狀態(tài)值（show status）是以大寫字母開頭。這樣區(qū)分有助于記憶和分類
mysql> show status like '%connections%'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | Connection_errors_max_connections | 0 | | Connections | 197 | | Max_used_connections | 2 | +-----------------------------------+-------+
Connection_errors_max_connections 當(dāng)MySQL的最大并發(fā)數(shù)大于系統(tǒng)變量（show variables）中max_connections的最大并發(fā)數(shù)，因此而被拒絕的次數(shù)，將會記錄在這個變量里。如果Connection_error_max_connections值比較大，則說明當(dāng)前系統(tǒng)并發(fā)比較高，要考慮調(diào)大max_connections的值。
Connections表示MySQL從啟動至今，成功建立連接的連接數(shù)，這個值是不斷累加的。
Max_used_connections表示MySQL從啟動至今，同一時刻并發(fā)的連接數(shù)，取得是最大值。如果這個值大于 max_connections則表明系統(tǒng)經(jīng)常處于高并發(fā)的狀態(tài)，應(yīng)該考慮調(diào)大最大并發(fā)連接數(shù)。
3、連接線程參數(shù)（thread variabls and status）
mysql> show variables like 'thread%'; +--------------------+---------------------------+ | Variable_name | Value | +--------------------+---------------------------+ | thread_cache_size | 9 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | +--------------------+---------------------------+
thread_cache_size 設(shè)置連接線程緩存的數(shù)目。這個緩存相當(dāng)于MySQL線程的緩存池（thread cache pool）,將空閑的連接線程放入連接池中緩存起來，而非立即銷毀。當(dāng)有新的連接請求時，如果連接池中有空閑的連接，則直接使用。否則要重新創(chuàng)建線程。創(chuàng)建線程是一個不小的系統(tǒng)開銷。MySQL的這部分線程處理和Nginx 的線程處理有異曲同工之妙，以后介紹Nginx的線程處理時，會拿來做對比。
thread_handling 默認(rèn)值是： one-thread-per-connection 表示為每個連接提供或者創(chuàng)建一個線程來處理請求，直至請求完畢，連接銷毀或者存入緩存池。當(dāng)值是no-threads 時，表示在始終只提供一個線程來處理連接，一般是單機(jī)做測試使用的。
thread_stack stack 是堆的意思，由PHP 進(jìn)程詳解這篇博客，知道進(jìn)程和線程都是有唯一的ID的，進(jìn)程的ID系統(tǒng)會維護(hù)，二線程的ID,則由具體的線程庫區(qū)維護(hù)，當(dāng)進(jìn)程或者線程休眠的時候，進(jìn)程的上下文信息要在內(nèi)存中開辟出一塊區(qū)域，保存進(jìn)程的上下文信息，以便于迅速喚醒程序。默認(rèn)為MySQL的每個線程設(shè)置的堆棧大小為：262144/1024=256k
查看線程狀態(tài)信息
mysql> show status like 'Thread%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 1 | | Threads_connected | 1 | | Threads_created | 2 | | Threads_running | 1 | +-------------------+-------+
Thread_cached 當(dāng)前線程池的線程數(shù)
Thread_connected 當(dāng)前的連接數(shù)
Thread_cached: 當(dāng)前連接線程創(chuàng)建數(shù), 如果這個值過高，可以調(diào)整threadcachesize 也就是調(diào)整線程緩存池的大小。
Thred_runnint: 當(dāng)前活躍的線程數(shù)。
連接請求堆棧
MySQL在很短的時間內(nèi)，突然收到很多的連接請求時，MySQL會將不能來得及處理的連接請求保存在堆棧中，以便MySQL后續(xù)處理。back_log參數(shù)設(shè)置了堆棧的大小，可以通過如下命令查看：
mysql> show variables like 'back_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | back_log | 80 | +---------------+-------+
連接異常
mysql> show status like 'Aborted%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Aborted_clients | 0 | | Aborted_connects | 219 | +------------------+-------+
Aborted_clients MySQL 客戶機(jī)被異常關(guān)閉的次數(shù)。
Aborted_connects 試圖連接到MySQL服務(wù)器而失敗的連接次數(shù)。
other
mysql> show status like 'Slow%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Slow_launch_threads | 0 | | Slow_queries | 0 | +---------------------+-------+
mysql> show variables like 'slow_launch_time'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | slow_launch_time | 2 | +------------------+-------+
Slow_lunch_threads 創(chuàng)建線程的時間過長，超過slow_launch_time的設(shè)定值，則會記錄。
可以通過使用 Connection_error%來查看連接的錯誤狀態(tài)信息：
mysql> show status like 'Connection_error%'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | Connection_errors_accept | 0 | | Connection_errors_internal | 0 | | Connection_errors_max_connections | 0 | | Connection_errors_peer_address | 0 | | Connection_errors_select | 0 | | Connection_errors_tcpwrap | 0 | +-----------------------------------+-------+
Connection_errors_peer_address 查找MySQL客戶機(jī)IP地址是發(fā)生的錯誤數(shù)。
相關(guān)文章
在SQL標(biāo)準(zhǔn)中，一共規(guī)定了6種不同的約束，包括非空約束，唯一約束和檢查約束等，而在MySQL中是不支持檢查約束的，所以這篇文章先對其余5種約束做一個詳解和練習(xí),需要的朋友可以參考下2023-06-06
這篇文章主要介紹了Windows 下 MySQL 8.X 的安裝教程，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下2019-11-11
虛擬機(jī)linux端mysql數(shù)據(jù)庫無法遠(yuǎn)程訪問的解決辦法
最近在項(xiàng)目搭建過程中遇到一問題，有關(guān)虛擬機(jī)linux端mysql數(shù)據(jù)庫無法遠(yuǎn)程訪問，通過查閱相關(guān)數(shù)據(jù)庫資料問題解決，下面把具體的解決辦法分享給大家，有需要的朋友可以參考下2015-08-08
MySQL數(shù)據(jù)庫通常包含關(guān)鍵的數(shù)據(jù),為確保這些數(shù)據(jù)的安全和完整,需要利用訪問控制和用戶管理的功能,下面這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫必知必會之安全管理的相關(guān)資料,需要的朋友可以參考下2022-05-05
MySQL 5.7 mysql command line client 使用命令詳解
這篇文章主要介紹了MySQL 5.7 mysql command line client 使用命令,需要的朋友可以參考下2017-06-06
Can''t connect to MySQL server的解決辦法
ERROR 2003 (HY000): Can't connect to MySQL server on '*.*.*.*' (113)的解決辦法2010-06-06
mysql下完整導(dǎo)出導(dǎo)入實(shí)現(xiàn)方法
對于大量數(shù)據(jù)的導(dǎo)入導(dǎo)出，是件挺麻煩的事，需要考慮很多的細(xì)節(jié)，這類對于需要大量數(shù)據(jù)導(dǎo)入導(dǎo)出的朋友可以參考下。2010-12-12
最近在工作上遇到了MySQL重裝的問題，今天記錄一下我的解決過程。不論我用控制面板的卸載刪除程序方式還是安全衛(wèi)士的卸載，都會遇到一個問題，就是安裝到如下圖位置，server start時就程序無響應(yīng)了，一直死在那里2013-04-04
這篇文章主要介紹了MySQL中的流式查詢及游標(biāo)查詢方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
這篇文章主要介紹了mysql數(shù)據(jù)自增ID為2的解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12