Connection Lifecycle, Threads, Sessions, Timeouts, and Resource Consumption

Learning outcomes

ServiceHub now has a dispatcher dashboard, a technician API, and a reporting script. All three can connect to the same MySQL server, but each connection becomes a server-side session with its own identity, transaction state, variables, temporary objects, and resource footprint. A server that accepts “only a few queries” can still fail if an application opens hundreds of idle sessions or leaks connections.

This lesson follows one client from connect to disconnect. The goal is not to memorize every thread counter; it is to understand which state belongs to a session, which state belongs to the server, and which measurements reveal connection pressure.

The lifecycle of one connection

A classic-protocol client targets an endpoint, establishes transport, performs the MySQL handshake, authenticates as an account, and receives a server-side session. From that point until disconnect, session-scoped state can accumulate: current database, character-set settings, SQL mode, transaction state, user variables, temporary tables, prepared statements, and more.

client opens transport      |      vserver handshake + authentication      |      vforeground session / connection id created      |      +--> statements, transactions, session variables      |      +--> idle wait between requests      |      vdisconnect / timeout / kill / network loss      |      vsession resources released; worker thread may be cached/reused

The connection identifier is not a permanent user ID. It identifies a particular server session. When that session ends, a future connection receives its own identifier and fresh session state.

Observe your own session first

Use a normal lab account, not a superuser, for the first observations. This demonstrates what an application can learn about itself without broad process privileges.

SELECT CONNECTION_ID() AS connection_id,       USER() AS client_identity,       CURRENT_USER() AS authenticated_account,       DATABASE() AS current_schema,       @@SESSION.autocommit AS autocommit,       @@SESSION.transaction_isolation AS isolation_level,       @@SESSION.wait_timeout AS wait_timeout_seconds;SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER,       PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND,       PROCESSLIST_TIME, PROCESSLIST_STATEFROM performance_schema.threadsWHERE PROCESSLIST_ID = CONNECTION_ID();

USER() reports the client user/host information used for the connection, while CURRENT_USER() reports the account MySQL actually used for privilege checking. Later security chapters explore why those can differ. For now, the important lesson is that session identity is server-side state you can inspect.

The Performance Schema threads table includes both foreground and background threads. A client session normally appears as a foreground thread with a PROCESSLIST_ID matching CONNECTION_ID(). Background server work has no client connection ID in the same sense.

Session state versus global server state

Many variables have both global and session values. The global value usually acts as the default for new sessions; changing it does not retroactively rewrite every existing session. This explains a common operational puzzle: two clients connected at different times can report different settings even though they use the same server.

SELECT @@GLOBAL.sql_mode AS global_sql_mode,       @@SESSION.sql_mode AS session_sql_mode,       @@GLOBAL.wait_timeout AS global_wait_timeout,       @@SESSION.wait_timeout AS session_wait_timeout;SET SESSION sql_mode = CONCAT(@@SESSION.sql_mode, ',NO_ENGINE_SUBSTITUTION');SELECT @@SESSION.sql_mode;-- Open a second connection and compare its session value.-- Then disconnect this session; its session-only change disappears.

See active connections without confusing them with every server thread

MySQL offers several process-list interfaces. Current documentation recommends Performance Schema-backed sources for scalable observation. The threads table exposes foreground and background activity, while the sys.session view presents user-session information in a friendlier form and filters background threads.

SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST,       PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME,       PROCESSLIST_STATE, TYPEFROM performance_schema.threadsWHERE TYPE = 'FOREGROUND'ORDER BY PROCESSLIST_TIME DESC;SELECT conn_id, user, db, command, time, state, current_statementFROM sys.sessionORDER BY time DESC;

Privileges affect visibility. A normal user generally sees less information about other accounts than an administrator. That is desirable: monitoring access is itself a security boundary. Do not grant broad process privileges merely to make a tutorial query convenient.

SHOW PROCESSLIST remains useful interactively, but do not build long-term monitoring around the deprecated INFORMATION_SCHEMA implementation. Prefer Performance Schema and sys views where practical.

Idle sessions, timeouts, and why “sleeping” is not free

After a statement finishes, a client can remain connected and wait for the next request. In a process list this often appears as Sleep. An idle session may hold comparatively little CPU, but it still represents server state and can retain transaction context, temporary objects, prepared statements, memory allocations, or locks if the application left a transaction open.

wait_timeout controls how long the server waits before closing many noninteractive idle connections. Do not lower it aggressively in production without understanding pool behavior: a pool that assumes connections remain valid longer than the server timeout can hand dead connections to requests.

SELECT @@SESSION.wait_timeout AS original_wait_timeout;SET SESSION wait_timeout = 60;SELECT @@SESSION.wait_timeout AS lab_wait_timeout;-- Leave this connection idle for more than the configured interval only-- in a disposable lab. The next client operation should discover that-- the server closed the idle session. Reconnect afterward.

Capacity evidence: connections, threads, and thread reuse

MySQL Community Server normally uses the one-thread-per-connection handling model. That does not mean every connection causes a brand-new operating-system thread forever: the server can cache connection threads for reuse. Enterprise Thread Pool is a separate edition-dependent capability and should not be assumed in Community labs.

SHOW GLOBAL STATUS WHERE Variable_name IN (  'Connections','Threads_connected','Threads_running','Threads_created',  'Aborted_connects','Max_used_connections');SHOW GLOBAL VARIABLES WHERE Variable_name IN (  'max_connections','thread_cache_size','thread_handling');

Compare deltas over a time window, not isolated snapshots. For example, Connections - Threads_created can help reason about thread-cache reuse, but performance tuning should follow observed churn and latency rather than a universal cache-size recipe.

Hands-on lab: two sessions, two realities

Open two terminal windows and connect both to the same ServiceHub lab server. Call them Session A and Session B.

1. In both sessions, record CONNECTION_ID(), @@SESSION.sql_mode, and @@SESSION.time_zone.
2. In Session A, run SET SESSION time_zone = '+00:00';. Confirm Session B did not change.
3. In Session A, create a TEMPORARY TABLE session_probe(id INT); and insert a row. Attempt to query that temporary table from Session B; it should not exist there.
4. As an account with sufficient monitoring visibility, inspect both sessions through performance_schema.threads or sys.session.
5. Begin a transaction in Session A, make a harmless update to a dedicated lab row, then intentionally leave it open. Observe transaction/session state; roll it back before continuing.
6. Close Session A and verify its temporary table and session variables disappear with the session.

CREATE TEMPORARY TABLE session_probe (  id INT PRIMARY KEY,  note VARCHAR(100) NOT NULL);INSERT INTO session_probe VALUES (1, 'belongs only to this session');SELECT * FROM session_probe;-- Before disconnecting:DROP TEMPORARY TABLE session_probe;

Production judgment and references

Healthy connection design is a queueing and resource problem, not a race to maximize max_connections. Applications should bound pools, set sensible connect/read timeouts, return connections with clean transaction state, detect broken connections, and expose pool metrics alongside MySQL metrics. Operators should watch connection churn, active versus sleeping sessions, aborted connects, high-water marks, transaction age, and memory pressure.

When investigating load, correlate MySQL session evidence with application request rates and operating-system resources. A server with 500 sleeping sessions and low memory pressure is a different problem from a server with 80 sessions all running expensive sorts and scans.

Authoritative references

• MySQL 8.4 Reference Manual — Accessing the Process List
• MySQL 8.4 Reference Manual — Performance Schema threads Table
• MySQL 8.4 Reference Manual — Server System Variables
• MySQL 8.4 Reference Manual — Server Status Variables
• MySQL 8.4 Reference Manual — sys session View

Next: distinguish configuration variables from status measurements, then make a controlled dynamic change and decide whether it should survive restart.