Thread Pool, Connection Concurrency, Queueing, and CPU Saturation

Learning outcomes

ServiceHub can maintain 2,000 TCP connections while only 20 requests are actively using CPU. During an incident, however, 300 requests become runnable at once and latency rises much faster than throughput. The important variables are therefore not “connections = load” but active concurrency, scheduling, queueing, context switching, CPU saturation, and lock/I/O waits.

1. Connection count is inventory; active concurrency is pressure

DROP DATABASE IF EXISTS servicehub18;CREATE DATABASE servicehub18 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub18;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status ENUM('open','waiting','closed') NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  updated_at DATETIME(6) NOT NULL,  summary VARCHAR(240) NOT NULL,  INDEX ix_status_opened(status, opened_at),  INDEX ix_customer_updated(customer_id, updated_at)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,status,priority,opened_at,updated_at,summary)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 1000)SELECT MOD(n,125)+1,       ELT(MOD(n,3)+1,'open','waiting','closed'),       MOD(n,5)+1,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       CONCAT('ServiceHub ticket ',n)FROM seq;SELECT VERSION() AS server_version, @@version_comment AS build_comment,       @@innodb_buffer_pool_size AS buffer_pool_bytes;SELECT COUNT(*) AS seeded_rows FROM tickets;

CREATE USER IF NOT EXISTS 'lab_bench'@'127.0.0.1' IDENTIFIED BY 'replace-for-local-lab';GRANT SELECT ON servicehub18.* TO 'lab_bench'@'127.0.0.1';SHOW GRANTS FOR 'lab_bench'@'127.0.0.1';

SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Threads_connected','Threads_running','Threads_created','Threads_cached', 'Max_used_connections','Threadpool_threads','Threadpool_idle_threads');SHOW GLOBAL VARIABLES WHERE Variable_name IN ( 'max_connections','thread_handling','thread_pool_size', 'thread_pool_max_threads','thread_pool_stall_limit','extra_port');SHOW FULL PROCESSLIST;

Threads_connected is the number of current client connections. Threads_running is closer to active server work, though it still needs context. Under one-thread-per-connection, the server associates a thread with each active connection model. Under pool-of-threads, MariaDB maps many client connections onto a bounded, adaptive set of workers and thread groups.

2. MariaDB thread pool: platform matters

On Unix-like systems, thread_handling=pool-of-threads uses MariaDB thread groups. thread_pool_size controls the number of groups and defaults according to available processors; groups coordinate runnable work and can create extra workers when a running thread stalls. Windows uses the native Windows thread-pool API and exposes different tuning details. This is why a Unix tuning recipe must not be copied verbatim to Windows.

MariaDB’s thread pool is part of Community Server; do not import the MySQL Enterprise packaging assumption. Conversely, do not assume identical internals or variable units across vendors.

3. A concurrency benchmark must sweep, not jump

The benchmark question is: at what concurrency does throughput stop scaling while latency and CPU queueing accelerate? Use a tiny local load generator, record every condition, and sweep a sequence such as 1, 2, 4, 8, 16, 32 rather than testing one arbitrary number.

# Linux/macOS shell example; adapt executable/path on Windows.# Run against the disposable local schema only.for c in 1 2 4 8 16 32; do  echo "=== concurrency=$c ==="  mariadb-slap \    --host=127.0.0.1 --user=lab_bench --password \    --create-schema=servicehub18 \    --query="SELECT COUNT(*) FROM tickets WHERE customer_id BETWEEN 20 AND 80" \    --concurrency="$c" --iterations=5 --number-of-queries=500  # In another terminal capture CPU/load and MariaDB status deltas.done

Do not publish the example command’s results as a universal benchmark. Your local outcome depends on CPU quota, storage/cache state, schema, data size, client network path, thread mode, MariaDB build, and background work. The lesson’s required output is your own table of concurrency, throughput-equivalent work rate, average/maximum timing reported by the tool, CPU utilization, and Threads_running.

4. Identify the knee and diagnose why it occurs

Suppose throughput improves from concurrency 1 through 8, barely improves at 16, and drops at 32 while p95/p99 or maximum latency rises. That bend is the queueing knee for this local workload—not a permanent server limit. Correlate it with CPU utilization/run queue, lock waits, storage latency, and the query plan.

SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Threads_connected','Threads_running','Questions','Queries', 'Innodb_row_lock_waits','Innodb_row_lock_time', 'Threadpool_threads','Threadpool_idle_threads');SELECT * FROM information_schema.THREAD_POOL_GROUPS;SELECT * FROM information_schema.THREAD_POOL_QUEUES;

The thread-pool Information Schema tables are relevant only when the thread pool is active and supported by your build/version. An empty or unavailable table is evidence to verify the server mode—not permission to invent queue statistics.

5. The wrong approach: increase max_connections and thread_pool_size together

When clients queue, an operator may open the floodgates: raise max_connections and thread_pool_size. If the real bottleneck is CPU or a hot lock, this converts an orderly queue into more simultaneous contention and worse tail latency.

SELECT @@GLOBAL.max_connections,       @@GLOBAL.thread_handling,       @@GLOBAL.thread_pool_size;SHOW GLOBAL STATUS LIKE 'Threads_running';SHOW ENGINE INNODB STATUS\G-- Change nothing until you can answer:-- 1) Is CPU saturated or is work waiting on locks/I/O?-- 2) Does throughput still increase at the next concurrency step?-- 3) Is the application pool already larger than useful DB concurrency?-- 4) Is there an administrative extra_port if a pool can stall?

The repair may be application-side backpressure/pool limits, query/index correction, lock-hotspot redesign, storage work, or a thread-pool configuration experiment. Tune one layer at a time so the effect remains attributable.

6. Reproducible lab: compare scheduling models safely

Prerequisites: a disposable local MariaDB Community instance and mariadb-slap or equivalent free client utility. Switching thread_handling may require server startup configuration/restart depending on target behavior, so the mandatory lab can remain on the existing mode; comparison with another mode is an optional second disposable instance. Do not restart a shared server for this exercise.

1. Create the lab schema and the lab_bench@127.0.0.1 least-privilege benchmark account.
2. Record CPU count/quota, thread_handling, thread-pool variables, and baseline status.
3. Sweep concurrency over at least five levels with identical query count and cache/warmup policy.
4. Record tool timings, CPU/load, Threads_running, lock waits, and thread-pool queue evidence where applicable.
5. Plot or tabulate concurrency against throughput and latency; identify the first level where latency rises materially faster than useful throughput.
6. Do not change a production setting; write the hypothesized next experiment and its rollback/acceptance criteria. Cleanup with DROP USER IF EXISTS 'lab_bench'@'127.0.0.1'; DROP DATABASE IF EXISTS servicehub18;.

Production judgment and bridge

Use the thread pool when measured concurrency and scheduling overhead justify it, and verify platform/build behavior. Preserve an administrative access path and monitor queueing, Threads_running, CPU saturation, context switches, lock/I/O waits, application-pool queues, and tail latency. The next lesson follows write-heavy work into storage: redo capacity, checkpoint age, I/O capacity, durability and the modern MariaDB flush controls.

Authoritative references

Use the target-version tab or release notes when a variable or default differs from the course baseline. These lessons intentionally avoid treating old tuning folklore as current MariaDB behavior.

• Thread Pool in MariaDB
• Thread Pool system and status variables
• Thread groups in the Unix implementation