如何区分 Connection、Thread和Session
2024-06-28 11:07 abce 阅读(20) 评论(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