關於MySQL使用者會話及連接線程

標籤：緩衝   slist   網頁   RoCE   block   max   variables   日誌   toolbar   

0、概念理解：使用者會話和連接線程是什麼關係？

使用者會話和使用者連接線程是一一對應的關係，一個會話就一個使用者連接線程。

問題描述：

　　如果系統因為執行了一個非常大的dml或者ddl操作導致系統hang住，我們想斷掉這個操作，怎麼辦？

解決辦法：

1、kill thread：殺死使用者的會話

　　但是時間長，效果不佳：前滾+復原，前提是已經進行了很長時間，復原就需要更多的時間

2、kill mysqld進程：推薦，用這種殺進程的方式，速度快

　　kill -9 進程號（ps aux 查看進程號）

　　資料庫先前滾，不主動復原，直接可以對外進行服務了，當讀到哪個未提交事務時再去慢慢復原。

 

一、kill使用者會話及使用者連接線程

1、如何查看使用者會話，如何殺掉使用者會話

[[email protected] ~]# netstat -anp |grep 3306tcp        0      0 :::3306                     :::*                        LISTEN      17324/mysqld        [[email protected] ~]# ps -ef |grep ‘mysql -px x‘root     20510 20483  0 15:59 pts/1    00:00:00 mysql -px xroot     20528 46646  0 15:59 pts/4    00:00:00 grep mysql -px xroot     45626 45565  0 05:44 pts/3    00:00:00 mysql -px x[[email protected] ~]# kill -9 20510 

　　# kill -9 <mysql會話進程PID>　　//快速釋放資源(推薦)

注意：千萬不要將mysqld給kill掉了，不要殺掉LISTEN進程

2、如何查看使用者連接線程，如何殺掉使用者連接線程

mysql> show processlist;+--------+| Id     |+--------+| 194850 || 194851 |+--------+mysql> kill 194850;mysql> show processlist;+--------+| Id     |+--------+| 194851 || 194852 |+--------+

注意：如果將使用者連接線程殺死斷掉，而會話沒有殺掉的話，該使用者會話又會重新開啟一個使用者連接線程。

3、殺掉使用者連接線程的工作過程、弊端風險

　　1、過程：

　　　　rollback--->釋放資源--->kill線程

　　2、弊端與風險：

　　　　1、可能出現系統更加繁忙的情況(因為大量的rollback)

　　　　2、會話釋放需要很長的時間

Q：假設現在有1000個使用者串連，如何快速殺掉？

A：

　　1、使用concat寫指令碼

　　　　mysql> select concat(‘kill ‘,ID,‘;‘) into outpfile ‘/tmp/kill.txt‘ from information_schema.PROCESSLIST;

　　　　shell> cat kill.txt

　　　　kill 194850;

　　　　kill 194851;

　　然後，複製到mysql中進行執行，將使用者連接線程都kill掉。

　　2、使用awk取出使用者會話進程ID都kill掉

　　shell> netstat -anp|grep mysql|grep -v mysqld|awk ‘{print $8}‘|awk -F ‘/‘ ‘{print $1}‘|xargs kill -9

關於mysqld_safe的注意點：

　　在os層面將使用者連接線程kill掉，通過pstree -p可以看到MySQL相關進程及線程ID，#kill -9 mysql任一線程，會導致該mysqld進程被kill；但是通過mysqld_safe的安全機制，又會重新啟一個mysqld進程；如此原來的使用者連接線程也就隨原來的進程一塊被幹掉了。

 

二、MySQL用戶端的串連

1、最大串連數

mysql> show variables like ‘max_connections‘;+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+

由上可見，MySQL預設用戶端的最大串連數是151，但是，在大並發下一百多的串連數就會不夠用，需要調整最大串連數，修改並寫入設定檔中：

　　max_connections = 1000

　　wait_timeout = 1000000　　#逾時時間

重啟MySQL服務

2、查看當前有多少串連

mysql> show status like ‘%Threads_connected%‘;+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Threads_connected | 1     |+-------------------+-------+1 row in set (0.01 sec)mysql> show processlist;+-------+------+-----------+------+---------+------+----------+------------------+| Id    | User | Host      | db   | Command | Time | State    | Info             |+-------+------+-----------+------+---------+------+----------+------------------+| 17219 | root | localhost | NULL | Query   |    0 | starting | show processlist |+-------+------+-----------+------+---------+------+----------+------------------+1 row in set (0.01 sec)

3、最大失敗串連數：max_connect_errors

mysql> show variables like ‘max%errors‘;+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | 100   |+--------------------+-------+1 row in set (0.00 sec)

　　是一個MySQL中與安全有關的計數器值，負責阻止過多嘗試失敗的用戶端以暴力破解密碼的情況，值的大小與效能無太大的關係。

　　預設是100，也就是說如果某一用戶端嘗試串連此MySQL伺服器，但是失敗（如密碼錯誤等等）10次，則MySQL會無條件強制阻止此用戶端串連。如果想重設計數器對某一用戶端的值，則必須重啟mysqld或者mysql> flush hosts;，當該用戶端成功串連一次mysqld後，針對此用戶端的max_connect_errors會清零。

　　如果max_connect_errors的設定過小，則網頁可能提示無法串連資料庫伺服器。

　　一般來說建議資料庫伺服器不監聽來自網路的串連，僅僅通過sock串連，這樣可以防止絕大多數針對mysql的攻擊；如果必須要開啟mysql的網路連接，則最好設定此值，以防止窮舉密碼的攻擊手段。

 

三、關於使用者工作空間

Q：如何判斷使用者會話線程空間(使用者工作空間)是否分配過小？

A：

　　1、sort_buffer_size：需要排序會話的緩衝大小(預設256K)，是針對每一個connection的；過大的配置+高並發可能會耗盡系統記憶體資源。

　　2、binlog_cache_size：二進位日誌緩衝區(預設32K)，基於會話；

　　一個事務做出的修改，

　　　　當小於binlog_cache_size時，所有修改內容存入binary log cache；

　　　　當大於binlog_cache_size時，內容存入磁碟暫存資料表。

　　3、join_buffer_size：多表串連結果集緩衝區，基於會話；

　　(每次join操作都會調用my_malloc、my_free函數申請/釋放join_buffer_size大小的記憶體)

也就是說，sort_buffer、binlog_cache、join_buffer的組成，形成使用者會話線程空間。一般的，當Sort_merge_passes(磁碟排序)每秒值很大時，就說明使用者工作空間分配過小，就應該考慮增加sort_buffer_size值。

關於MySQL使用者會話及連接線程