如何區分 Connection、Thread和Session

abce發表於2024-06-28

threadSession

什麼是 Connection

Connection 只是客戶端應用程式/使用者與 MySQL 資料庫伺服器之間建立的成功連線。它允許客戶端傳送查詢、接收結果並與資料庫交換資料。

每個 Connection 都代表一個單獨的會話，使客戶端可以相互獨立地執行查詢和事務。單個 MySQL 伺服器可以處理來自不同客戶端應用程式的多個併發連線，從而實現對資料庫的高效多使用者訪問。

相關的全域性變數:

·max_connections：同時支援的最大客戶端連線數量

相關的全域性狀態變數:

·Connections：嘗試連線到mysql server的連線數量，包含成功的、未成功的

·Max_used_connections：伺服器啟動後同時使用的最大連線數。

如果連線數超過了 max_connections 限制，就會出現"Too many connections"的錯誤。因此，資料庫管理員必須經常重新配置連線限制，並使用連線池技術來有效管理和重複使用連線。有效管理和監控連線對於確保 MySQL 伺服器的最佳效能和資源利用率非常重要。

 SELECT Threads_connected, max_connections,
    (Threads_connected / max_connections) AS Connection_Usage_rate
 FROM
    (SELECT gs1.VARIABLE_VALUE AS Threads_connected
    FROM performance_schema.global_status gs1
    WHERE gs1.VARIABLE_NAME LIKE 'Threads_connected') tc
JOIN
    (SELECT gs2.VARIABLE_VALUE AS max_connections
    FROM performance_schema.global_variables gs2
    WHERE gs2.VARIABLE_NAME LIKE 'max_connections') c;

+-------------------+-----------------+-----------------------+
| Threads_connected | max_connections | Connection_Usage_rate |
+-------------------+-----------------+-----------------------+
| 453               | 2048            |         0.22119140625 |
+-------------------+-----------------+-----------------------+

·如果 Connection_Usage_rate 接近 1，這意味著當前的 max_connections 可能會用完，應用程式將無法連線到資料庫

·如果 Connection_Usage_rate 接近 0，則表示當前的 max_connections 設定過大，超出了需要，這將增加為這麼多連線提供服務所需的預估的總記憶體

·因此，需要監控 Threads_connected 狀態變數或 processlist 連線數，並根據需要增加 max_connections

什麼是Threads

MySQL 使用執行緒管理多個客戶端連線，並同時處理查詢。每個客戶端連線都分配給一個單獨的執行緒，允許多個客戶端同時與資料庫伺服器互動。

執行緒分為後臺執行緒（FOREGROUND）和後臺執行緒（BACKGROUND）。使用者連線執行緒總是被稱為前臺執行緒，而與內部伺服器活動相關的執行緒則是後臺執行緒。例如：

·InnoDB 內部執行緒、

·向副本傳送資訊的 binlog 轉儲執行緒、

·複製的 I/O 和 SQL 執行緒。

相關的全域性變數:

·thread_cache_size：mysql server快取的用於重用的執行緒數，不應該超過max_connections的設定

·thread_handling：定義 mysql server 用於連線執行緒的執行緒處理模型。取值範圍有：no-threads、one-thread-per-connection(預設值)、loaded-dynamically

– no-threads (the server uses a single thread to handle one connection)
– one-thread-per-connection (the server uses one thread to handle each client connection)
– loaded-dynamically (set by the thread pool plugin when it initializes)

相關的全域性狀態變數:

·Threads_connected：顯示當前開啟的連線數。

·Threads_cached：執行緒快取中的執行緒數。

·Threads_created：由於執行緒快取中可用執行緒數量不足，為處理新連線而建立的執行緒數量。因此，如果 Threads_created 較大，則可能需要增加 thread_cache_size 值。快取未命中率的計算公式為 Threads_created/Connections

·Threads_running：執行查詢或執行某些工作但未休眠的活動執行緒數。

>show global status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 137   |
| Threads_connected | 457   |
| Threads_created   | 45667 |
| Threads_running   | 5     |
+-------------------+-------+
4 rows in set (0.00 sec)

>show global variables like '%thread_cache_size%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 512   |
+-------------------+-------+

故障排除提示

有時，你可能會看到為處理新連線而建立了大量執行緒，這是因為執行緒快取大小（thread_cache_size）中沒有足夠的執行緒可用，那麼這就表明需要增加執行緒快取大小（thread_cache_size）。此外，快取未命中率可以透過 Threads_created/Connections 來計算。

 SELECT threads_created, connections,
    (threads_created / connections) AS thread_cache_miss_rate
 FROM
    (SELECT gs1.VARIABLE_VALUE AS threads_created
    FROM performance_schema.global_status gs1
    WHERE gs1.VARIABLE_NAME LIKE 'Threads_created') tc
JOIN
    (SELECT gs2.VARIABLE_VALUE AS connections
    FROM performance_schema.global_status gs2
    WHERE gs2.VARIABLE_NAME LIKE 'Connections') c;

+-----------------+-------------+------------------------+
| threads_created | connections | thread_cache_miss_rate |
+-----------------+-------------+------------------------+
| 45667           | 36565313    |  0.0012489158782805988 |
+-----------------+-------------+------------------------+

·如果 thread_cache_miss_rate 越來越接近 1，表示當前執行緒快取的大小不足以跟上新的連線請求。

·如果 thread_cache_miss_rate 接近 0，則表示當前執行緒快取大小等於或大於滿足新連線請求所需的大小。

·因此，我們需要監控 Threads_cached 狀態變數，以確定執行緒快取大小的正確值。

什麼是Session

會話一般能讓我們瞭解客戶端和資料庫伺服器是如何互動的。會話表示客戶端應用程式連線到伺服器、執行一個或多個查詢或事務，然後斷開連線的時間段。在會話期間，客戶端保持與伺服器的連線，允許其執行一個或多個查詢。

會話可以包含哪些資訊？

·連線建立：客戶端何時與 MySQL 伺服器建立連線，使用哪個使用者

·查詢執行：跟蹤並儲存會話中執行的查詢

·狀態持久化：維護客戶端的當前狀態，如變數、臨時表和鎖等。這些資訊在會話期間始終可用。

·事務管理：可以從 performance_schema.events_statements_history 表中獲取使用者執行的所有命令或事務。

·資源利用率：當會話處於活動狀態時，伺服器會分配記憶體和資源來處理客戶端的查詢並管理所需的鎖。可以檢視sys.session 輸出及其後設資料分析

一旦客戶端完成任務或決定斷開連線，會話就會結束。與會話相關的資源被釋放，伺服器就可以處理其他客戶端請求了。

有效管理會話非常重要，可確保高效使用資源，避免出現資源爭用、死鎖或記憶體消耗過大等問題。

如何配置語句事件收集

查詢performance_schema 中啟用了哪些 instruments 來收集事件語句。

SELECT NAME, ENABLED, TIMED
       FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'statement/%';

SELECT *
       FROM performance_schema.setup_consumers
       WHERE NAME LIKE '%statements%';

可以用以下語句開啟 instruments、或者直接配置到配置檔案中

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';
 
或
[mysqld]
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-statements-history-long=ON
performance-schema-consumer-statements-digest=ON

如何獲取會話的詳細資訊

舉例說明，我們可以獲取 sys.processlist 檢視或 performance_schema.threads

執行緒表中的每一個伺服器執行緒都有一行。每一行都包含執行緒的相關資訊，並指明是否已啟用監控和歷史事件日誌記錄功能：

>select thread_id,name,processlist_id,processlist_user,processlist_command,processlist_info from performance_schema.threads where PROCESSLIST_USER like 'myabc%'  and type='FOREGROUND' limit 10;
+-----------+---------------------------+----------------+------------------+---------------------+------------------+
| thread_id | name                      | processlist_id | processlist_user | processlist_command | processlist_info |
+-----------+---------------------------+----------------+------------------+---------------------+------------------+
|  36547794 | thread/sql/one_connection |       36547602 | myabc            | Sleep               | NULL             |
|  36427839 | thread/sql/one_connection |       36427647 | myabc            | Sleep               | NULL             |
|  36549953 | thread/sql/one_connection |       36549761 | myabc            | Sleep               | NULL             |
|  36427951 | thread/sql/one_connection |       36427759 | myabc            | Sleep               | NULL             |
|  36562336 | thread/sql/one_connection |       36562144 | myabc            | Sleep               | NULL             |
|  36564866 | thread/sql/one_connection |       36564674 | myabc            | Sleep               | NULL             |
|  36573402 | thread/sql/one_connection |       36573210 | myabc            | Sleep               | NULL             |
|  36554541 | thread/sql/one_connection |       36554349 | myabc            | Sleep               | NULL             |
|  36560047 | thread/sql/one_connection |       36559855 | myabc            | Sleep               | NULL             |
|  36547800 | thread/sql/one_connection |       36547608 | myabc            | Sleep               | NULL             |
+-----------+---------------------------+----------------+------------------+---------------------+------------------+

processlist和 x$processlist 檢視

processlist和 x$processlist 檢視彙總了當前正在執行的程序資訊。與 SHOW PROCESSLIST 語句和 INFORMATION_SCHEMA PROCESSLIST 表相比，它們能提供更完整的資訊，而且也是非阻塞的。這些檢視提供了更多詳細資訊，如延遲、進度、行、掃描型別等，對分析和故障排除更有用。

>select thd_id,conn_id,command,last_statement,program_name from sys.processlist where user like 'myabc%' limit 10;
+----------+----------+---------+-------------------------------------------------------------------+--------------+
| thd_id   | conn_id  | command | last_statement                                                    | program_name |
+----------+----------+---------+-------------------------------------------------------------------+--------------+
| 36575327 | 36575135 | Sleep   | COMMIT                                                            | NULL         |
| 36605509 | 36605317 | Sleep   | SHOW COLUMNS FROM `myabc`.`sto ... ker_adexunt_enxtiax_posxxiony` | NULL         |
| 36605262 | 36605070 | Sleep   | SELECT STATE AS `Status`, ROUN ... OUP BY SEQ, STATE ORDER BY SEQ | NULL         |
| 36430590 | 36430398 | Sleep   | COMMIT                                                            | NULL         |
| 36431664 | 36431472 | Sleep   | COMMIT                                                            | NULL         |
| 36430614 | 36430422 | Sleep   | COMMIT                                                            | NULL         |
| 36430569 | 36430377 | Sleep   | COMMIT                                                            | NULL         |
| 36615079 | 36614887 | Sleep   | NULL                                                              | NULL         |
| 36615086 | 36614894 | Sleep   | NULL                                                              | NULL         |
| 36431450 | 36431258 | Sleep   | COMMIT                                                            | NULL         |
+----------+----------+---------+-------------------------------------------------------------------+--------------+

>select * from sys.processlist where user like 'myabc%' limit 1\G
*************************** 1. row ***************************
                thd_id: 36575327
               conn_id: 36575135
                  user: myabc@192.168.1.1
                    db: myabc
               command: Sleep
                 state: NULL
                  time: 2456
     current_statement: NULL
      execution_engine: PRIMARY
     statement_latency: NULL
              progress: NULL
          lock_latency:   0 ps
           cpu_latency:   0 ps
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: COMMIT
last_statement_latency: 3.43 ms
        current_memory: 66.13 KiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 4.96 ms
             trx_state: COMMITTED
        trx_autocommit: NO
                   pid: NULL
          program_name: NULL
1 row in set (0.16 sec)

session 和 x$session 檢視

這些檢視與 processlist 和 x$processlist 檢視類似，但它們會過濾掉後臺程序，只顯示使用者會話。

>select * from sys.session where thd_id=36605509\G
*************************** 1. row ***************************
                thd_id: 36605509
               conn_id: 36605317
                  user: myabc@192.168.1.1
                    db: myabc
               command: Sleep
                 state: NULL
                  time: 93
     current_statement: NULL
      execution_engine: PRIMARY
     statement_latency: NULL
              progress: NULL
          lock_latency:   0 ps
           cpu_latency:   0 ps
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: SHOW CREATE TABLE `myabc`.`sb_adexunt`
last_statement_latency: 279.53 us
        current_memory: 1.08 MiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 393.12 us
             trx_state: COMMITTED
        trx_autocommit: YES
                   pid: 32240
          program_name: NULL
1 row in set (0.15 sec)

Session的活動可從 performance_schema.events_statements_history 中檢索。

>select thread_id,event_name,sql_text,statement_id as query from performance_schema.events_statements_history where thread_id=36605509;
+-----------+---------------------------------+--------------------------------------------------------------------+------------+
| thread_id | event_name                      | sql_text                                                           | query      |
+-----------+---------------------------------+--------------------------------------------------------------------+------------+
|  36605509 | statement/com/Init DB           | NULL                                                               | 2916320767 |
|  36605509 | statement/sql/select            | SELECT * FROM `myabc`.`abcde_myabcd_adexunts` LIMIT 0,1000         | 2916320768 |
|  36605509 | statement/sql/show_table_status | SHOW TABLE STATUS LIKE 'abcde_myabcd_adexunts'                     | 2916320772 |
|  36605509 | statement/sql/show_create_table | SHOW CREATE TABLE `myabc`.`abcde_myabcd_adexunts`                  | 2916320773 |
|  36605509 | statement/com/Init DB           | NULL                                                               | 2915970400 |
|  36605509 | statement/sql/show_fields       | SHOW COLUMNS FROM `myabc`.`abcde_myabcd_addount_ixitixl_posyuions` | 2915970401 |
|  36605509 | statement/com/Init DB           | NULL                                                               | 2916320754 |
|  36605509 | statement/sql/show_tables       | SHOW FULL TABLES WHERE Table_type != 'VIEW'                        | 2916320755 |
|  36605509 | statement/com/Init DB           | NULL                                                               | 2916320756 |
|  36605509 | statement/sql/show_table_status | SHOW TABLE STATUS                                                  | 2916320757 |
+-----------+---------------------------------+--------------------------------------------------------------------+------------+
10 rows in set (0.00 sec)

結論

系統表和 performance_schema 表可以為我們提供工作負載的詳細資訊，因為它們在後臺收集了大量資料。performance_schema 應根據需要開啟或關閉。

下面是用來獲取 processlist 後設資料的一些命令：

·show processlist
·show full processlist
·select * from performance_schema.processlist
·select * from sys.x$processlist
·select * from performance_schema.threads
·select * from sys.x$session

Scheduler&Task&Worker&Thread&Request&Session&Connection OF MSSQL
2018-01-10
threadSessionSQL
connection和session的關係
2008-06-19
Session
connection session process的聯絡與區別
2011-05-16
Session
Connection/Session/Server Processes
2005-03-02
SessionServer
tomcat one connection one thread one request one thread
2019-02-27
Tomcatthread
Thread和Runnable的區別
2019-04-28
thread
Thread 和runnable的區別
2016-10-26
thread
No Hibernate Session bound to thread
2011-11-02
Sessionthread
Session 和 Cookie 區別
2017-04-19
SessionCookie
如何區分資料和指令
2017-10-10
disconnect session和kill session的區別
2016-03-14
Session
hibernate的session.connection被session.dowork()替代
2012-06-14
Session
如何區分網盤和同步盤？
2019-12-06
如何區分jsonarray 和 jsonobject
2016-10-27
JSONObject
cookie和session的區別
2018-08-17
CookieSession
disconnect session和kill session的區別轉
2016-11-23
Session
【轉】Session ID/session token 及和cookie區別
2016-04-28
SessionCookie
Cookie 和 Session 關係和區別
2018-03-14
CookieSession
Java中Runnable和Thread的區別
2016-08-08
Javathread
Java 中Thread 和Runnable的區別
2014-12-24
Javathread
機器學習是如何區分貓和狗的？
2019-03-14
機器學習
(譯)React是如何區分Class和Function？
2018-12-05
ReactFunction
如何區分CRM系統和呼叫中心?
2023-11-07
[譯] React 是如何區分 Class 和 Function 的 ?
2019-02-27
ReactFunction
cookie和session 有什麼區別?
2020-11-26
CookieSession
session 和 cookie 有什麼區別？
2020-12-02
SessionCookie
cookie 和session 的區別詳解
2016-09-26
CookieSession
COOKIE和SESSION有什麼區別？
2017-07-20
CookieSession
Cookie和Session的區別詳解
2012-10-10
CookieSession
process/session/connection的一些問題
2008-08-11
Session
轉android-Service和Thread的區別
2012-08-18
Androidthread
如何區分Linux和Unix?作業系統!
2020-10-28
Linux作業系統
Redis分散式Session和普通的cookie session有什麼區別？
2023-03-29
Redis分散式SessionCookie
http中session和cookie的區別和關係
2020-10-04
HTTPSessionCookie
alter database和alter system和alter session的區別
2014-03-25
DatabaseSession
cookie和session的區別（全面總結）
2019-09-07
CookieSession
cookie和session的詳解與區別
2020-11-10
CookieSession
Session和Cookie的聯絡與區別
2017-10-20
SessionCookie