Threads, Sessions, Connection Limits, Thread Pool Concepts, and Resource Controls

Learning outcomes

ServiceHub passes its startup checks, but load testing opens hundreds of idle application connections. An engineer sees Threads_connected rising and concludes the server is executing hundreds of queries simultaneously. Another proposes raising max_connections to 10,000 as the universal fix. Both responses confuse client sessions, worker threads, active statements, useful concurrency, and resource capacity.

MariaDB accepts client connections, authenticates an account, creates session state, and schedules statements using its configured thread-handling model. The traditional model is one thread per client connection; MariaDB also provides an adaptive thread pool that can decouple the number of client connections from the number of actively executing worker threads. Neither model turns connection limits into a performance target. This lesson makes the connection lifecycle observable and teaches resource controls as safety boundaries.

1. A connection is a stateful server object

A client first reaches a TCP port, Unix socket, Windows named pipe, or other supported transport. The server negotiates protocol details and authentication, matches the account identity, establishes a session, initializes session-scoped variables from global/account defaults, and assigns a connection identifier. The session then cycles between executing commands and waiting for the client.

Session state can include the current database, transaction state, temporary tables, user variables, prepared statements, character-set settings, SQL mode, time zone, and other per-connection context. A connection pool therefore cannot blindly move every application operation between arbitrary sessions when the application depends on state. Later application-integration lessons revisit this boundary.

SELECT CONNECTION_ID() AS connection_id,       USER() AS login_identity,       CURRENT_USER() AS privilege_identity,       DATABASE() AS current_database,       @@autocommit AS autocommit,       @@sql_mode AS sql_mode,       @@time_zone AS time_zone;SHOW FULL PROCESSLIST;

CONNECTION_ID() identifies the current session while it exists; do not treat it as a durable business identifier. USER() describes the client/login identity, while CURRENT_USER() reflects the account used for privilege checking after host/account resolution.

2. Connected does not mean running

MariaDB status variables expose different views of concurrency. Threads_connected counts currently open connections. Threads_running counts threads that are not sleeping. Max_used_connections records the high-water mark of simultaneous connections since the relevant status reset/start. These values answer different questions.

SHOW GLOBAL STATUS WHERE Variable_name IN (  'Threads_connected',  'Threads_running',  'Max_used_connections',  'Connections',  'Aborted_connects',  'Aborted_clients');SHOW GLOBAL VARIABLES WHERE Variable_name IN (  'max_connections',  'max_user_connections',  'wait_timeout',  'interactive_timeout',  'thread_handling');

A web service can maintain 300 mostly idle pooled connections while only 12 statements are running. Conversely, 30 connections can still overload a server if each performs expensive sorts, joins, lock waits, or large per-session allocations. Capacity planning therefore combines connection count with statement concurrency, memory, CPU, I/O, locks, latency, and workload shape.

3. max_connections is a guardrail, not a tuning trophy

max_connections limits simultaneous client connections. Current MariaDB documentation lists it as a dynamic global variable, with one reserved administrative connection path under documented privilege conditions. The precise default and operating-system constraints should be verified on your build; the important mechanism is the limit.

Raising the limit can require more file descriptors, more session memory, more thread/process scheduler capacity, and more database work entering the system at once. Under systemd, service task limits can also cap the practical thread count even when MariaDB’s variable is configured higher. A large number in max_connections cannot manufacture CPU or I/O capacity.

SELECT @@GLOBAL.max_connections AS configured_limit;SHOW GLOBAL STATUS LIKE 'Max_used_connections';SHOW GLOBAL STATUS LIKE 'Threads_connected';SHOW GLOBAL STATUS LIKE 'Threads_running';SELECT ROUND(  100 * VARIABLE_VALUE / @@GLOBAL.max_connections, 1) AS current_connection_pctFROM information_schema.GLOBAL_STATUSWHERE VARIABLE_NAME='THREADS_CONNECTED';

4. Idle sessions and timeouts are part of application design

wait_timeout controls how long an inactive non-interactive session can remain before the server closes it; interactive_timeout applies when the client connects with the interactive flag. The session wait_timeout is initialized when the connection starts. This means changing the global value does not magically rewrite every existing session.

SELECT @@GLOBAL.wait_timeout AS global_wait_timeout,       @@SESSION.wait_timeout AS session_wait_timeout,       @@GLOBAL.interactive_timeout AS global_interactive_timeout,       @@SESSION.interactive_timeout AS session_interactive_timeout;

An aggressive idle timeout can free abandoned sessions but can also surprise connection pools that assume the socket remains valid. A robust pool detects stale connections, uses keepalive/validation where appropriate, and chooses its own idle/lifetime policy coherently with the server. Do not copy a 30-second timeout from a benchmark into production without understanding reconnect cost and application behavior.

5. Per-account resource limits contain noisy clients

MariaDB accounts can have resource limits such as MAX_USER_CONNECTIONS. This creates a more targeted boundary than one global connection ceiling. For example, a reporting account should not be able to consume every available connection and prevent the transactional application or operators from connecting.

-- Run only as an administrator in the local lab.CREATE USER IF NOT EXISTS 'servicehub_limited'@'localhost'  IDENTIFIED BY 'Replace-This-Disposable-Password!'  WITH MAX_USER_CONNECTIONS 2;GRANT SELECT ON servicehub.*  TO 'servicehub_limited'@'localhost';SHOW CREATE USER 'servicehub_limited'@'localhost';SHOW GRANTS FOR 'servicehub_limited'@'localhost';

Open two sessions with the limited account, then attempt a third. The third should be rejected by the account connection limit while other accounts remain unaffected. Exact error wording can vary; verify the mechanism by querying the existing sessions and the user definition. Finally close the sessions and drop the disposable account.

DROP USER IF EXISTS 'servicehub_limited'@'localhost';

6. Traditional thread handling: simple mapping, real resource cost

With traditional one-thread-per-connection handling, a connection is associated with a server thread that manages its work. This mental model is straightforward and can provide predictable latency for moderate connection counts, but many simultaneously active threads can increase context switching, CPU-cache disruption, memory consumption, and lock contention.

Do not infer operating-system thread names from SQL alone. Use MariaDB status/process interfaces for portable evidence, and OS tools only as an additional layer. On Linux you can inspect process/thread counts; on Windows, Task Manager or PowerShell can expose the server process and threads. Container runtimes add another resource-control boundary such as CPU/memory limits.

7. MariaDB’s thread pool decouples clients from active workers

MariaDB provides an adaptive thread pool intended to keep the number of actively executing worker threads closer to useful CPU concurrency rather than allocating an independently active worker for every connected client. The primary switch is thread_handling=pool-of-threads. On Unix-like systems, the implementation uses thread groups; on Windows it uses native Windows thread-pool APIs, so some configuration details differ by operating system.

The thread pool is most attractive for workloads with many client connections and relatively short, CPU-bound statements. It is not universally faster. Work can queue, so a tiny SELECT 1 can wait behind other work under saturation. Long-running or blocked statements can also change scheduling dynamics; MariaDB includes stall-detection and extra-port concepts to preserve operability.

SHOW VARIABLES LIKE 'thread_handling';SHOW VARIABLES LIKE 'thread_pool%';SHOW STATUS LIKE 'Threadpool%';-- Information Schema exposes thread-pool tables when relevant.SELECT TABLE_NAMEFROM information_schema.TABLESWHERE TABLE_SCHEMA='information_schema'  AND TABLE_NAME LIKE 'THREAD_POOL%'ORDER BY TABLE_NAME;

8. Deliberately wrong approach: solve saturation by multiplying limits

Suppose load testing shows max_connections=151, Threads_connected=145, and high request latency. The wrong response is to set max_connections=5000 without checking why connections accumulate. If the application leaks sessions, queries block on locks, or a pool has no backpressure, the larger ceiling delays the visible failure while increasing the blast radius.

Diagnose in layers: inspect process-list states and ages; compare connected versus running; identify long transactions and lock waits; measure application pool occupancy; inspect connection creation rates; estimate per-session memory; check file/thread OS limits; then decide whether to repair pooling, add backpressure, tune queries, increase capacity, or carefully change the limit.

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE,       LEFT(INFO, 160) AS statement_textFROM information_schema.PROCESSLISTORDER BY TIME DESCLIMIT 30;

Do not automate KILL based only on age. A long-running transaction can be legitimate, and killing it can trigger a large rollback or application error storm. Chapter 06 teaches transaction/lock diagnosis before operational termination policies.

9. Hands-on concurrency lab

Use three to six local terminal sessions, not a synthetic connection storm. The objective is to see lifecycle states, not benchmark maximum connections.

1. In Session A, connect as servicehub_app and record CONNECTION_ID().
2. Open Sessions B and C and leave them idle.
3. From an administrative session, query information_schema.PROCESSLIST and global thread counters.
4. Run SELECT SLEEP(8); in Session A and observe how its process-list command/state and Threads_running change.
5. Create the account limited to two simultaneous connections and verify the third connection fails without affecting the admin session.
6. Inspect thread_handling and thread-pool variables; do not enable the pool on the main lab merely to complete this lesson.
7. Close sessions and drop the limited account.

Verification checklist

• You can distinguish an open idle session from an executing statement.
• You recorded global connection counters before and during the experiment.
• The per-account limit failed safely on the third session.
• You did not raise max_connections as part of the lab.
• You can state whether your server uses one-thread-per-connection or pool-of-threads.
• You can explain why a thread pool changes scheduling but does not create database capacity.

10. Summary and bridge

A MariaDB connection is a stateful session, not just a TCP socket. Connection count, active work, server threads, and useful concurrency are related but not equivalent. You used process-list and status evidence, treated max_connections as a guardrail, applied an account-level limit, and learned when the adaptive thread pool can help—and why it still requires measurement.

The next lesson focuses on the configuration knobs and counters you have already queried. You will separate system variables from status variables, GLOBAL from SESSION scope, dynamic from startup-only settings, and runtime changes from persisted option-file configuration. The central habit remains the same: change, verify in the right scope, and prove persistence rather than assuming it.