如何区分 Connection、Thread和Session
2024-06-28 11:07 abce 阅读(96) 评论(0) 编辑 收藏 举报
什么是 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
