Configuring the MariaDB Thread Cache for Efficiency: Better Performance with Less Overhead

I specifically configure the MariaDB thread cache to optimize connection establishment and thread creation. This way, I reduce Latency and save CPU‑Overhead, especially with many short sessions and a high connection rate.

Key points

The following aspects serve as guidelines for effectively configuring and measuring the cache. I will focus on clear Values and actionable Steps.

• Mechanism of Action: Reusing completed threads instead of creating new ones, which is expensive
• Relevance: Useful for many short connections per second
• Measurement: Threads_created, Connections, Threads_cached
• Boundaries: Ignored if the thread pool is active
• Procedure: Start small, measure, and increase gradually

How the MariaDB Thread Cache Works

After a connection is closed, MariaDB places the thread in a cache as long as the limit has not been reached. New connections can reuse this thread, which saves on the costly creation process and Response time reduces. This is particularly effective when there are many logins per second and workloads with short sessions, in which creating and destroying threads results in a noticeable Cost factor The cache clears itself after about five minutes of inactivity, preventing the server from carrying unnecessary overhead. Without a thread pool, the default value is often 256, which provides a small buffer for typical spikes. I also note that reuse does not solve all problems: poor connections or faulty client strategies remain visible and require separate corrections.

When Tuning Is Worth It

I increase the cache size if the application creates many short connections and the counter Threads_created is growing rapidly. A clear indicator is a high ratio of `Threads_created` divided by `Connections`, because in that case, reuse too often misses the mark. In this case, new threads are crowding out the CPU and slow down response times, while Reuse shortens the path. However, I always check to see if the cause might lie with the client—for example, due to unnecessary reconnects. If proper connection handling stabilizes the load, the cache often only needs a moderate adjustment. Those who blindly maximize performance quickly pay the price in memory consumption and overlook real areas for optimization in the application logic.

Measurements I check in advance

To arrive at an accurate diagnosis, I use a few, but meaningful, metrics based on clear formulas. To begin with, I read Threads_created, Connections, Threads_cached and Threads_connected and check for trends. The simple ratio of Threads_created to Connections shows me how often the database rebuilds instead of reusing connections. It’s also very helpful to see how close Threads_cached is to the usual peak number of concurrent connections. If the cache and the distance from the peak remain large, I give away Resources or meet the Load No. The following table summarizes key metrics and their direct implications:

Key figure	Meaning	interpretation	Action
Threads_created	New threads created since startup	Rapid growth indicates frequent regeneration	Check the cache, reduce client reconnects
Connections	Total number of connections	Basis for Quota and Trend Analysis	Monitor the trend toward peak load
Threads_cached	Threads in the Cache	Low, even at high frequencies, may be too small	Increase the cache in small increments
Threads_connected	Currently Active Connections	Guideline for a reasonable cache size	Size the cache based on typical peaks

Step-by-Step Adaptation in Practice

I start by taking measurements under realistic load conditions and record the key metrics before each change. Then I check the current value using SHOW VARIABLES LIKE 'thread_cache_size' and write down the Base for later Comparisons. I then increase the value in small increments and observe whether `Threads_created` rises more slowly and the connection times become more stable. A single large change can obscure the underlying causes, so I deliberately take small, verifiable steps. After each adjustment, I wait for a meaningful load phase to ensure the effect remains reliable. Only when multiple load windows confirm the results do I consider the next step.

Recommended Configuration Logic and Default Values

There is no universal ideal value, so I base my decisions on typical peaks and historical data. For low or moderate connection rates, a small to medium-sized cache is often sufficient, especially one close to the standard of 256. With highly fluctuating load and many connections per second, a larger buffer size helps as long as the reuse rate actually increases. I keep the cache slightly below the usual peaks in `Threads_connected` so that I don't create any unnecessary Resources bind. If you make the cache huge, you're wasting memory without gaining any benefits. I also look at related background threads such as the Page Cleaner Threads, because they, too, influence overall performance during periods of high I/O activity.

Memory Requirements per Thread and the Impact of Cache Size

I deliberately factor in the cache's storage effect. A cached thread primarily maintains its thread_stack and minimal thread metadata. Per-connection buffers such as sort_buffer_size, join_buffer_size ...or the network buffer is released upon disconnect and does not permanently burden the cache. The stack, on the other hand, remains bound to the thread. As a rule of thumb, I assume that: Cache ≈ thread_cache_size × thread_stack (plus a little overhead). In the case of a thread_stack With a stack size of 256–320 KB and a cache of 512, this already amounts to approximately 130–170 MB of allocated memory. Anyone who increases the stack size or uses very large caches should keep this effect in mind and weigh it against more important buffers (e.g., InnoDB buffers).

That's why I always check:

• SHOW VARIABLES LIKE 'thread_stack'; to determine the amount of memory allocated per thread
• The proximity of Threads_cached to the 95th percentile peak of Threads_connected
• Whether increasing the cache size affects the rate Threads_created / Connections actually improved

If there's no benefit, I'll reduce it again. You can tell the cache is too large when Threads_cached remains consistently above the typical connection peak without further reducing latency.

Running on Linux and in Containers: Limits and Challenges

I verify system-wide limits before increasing the cache size. Creating threads can fail due to OS limits long before the database itself reaches its maximum number of connections. In doing so, I check:

• Process/Thread Boundaries: ulimit -u (max. processes/threads), /proc/sys/kernel/threads-max and /proc/sys/kernel/pid_max
• Stack Limit: ulimit -s affects the stack reserved per thread—which is significant overall for large caches
• cgroups in the container: pids.max and memory limits; PID limits that are too tight slow down bursts
• Scheduler Print: With a very large number of threads without a pool, context-switching overhead can increase; in this case, a thread pool or application pooling may be a better option

On multi-socket or NUMA hosts, I also monitor whether threads jump across nodes, thereby causing remote memory accesses. In such environments, stable pools are often more efficient than constantly creating new threads that are widely distributed by the scheduler.

Common Misconceptions About the Thread Cache

I clear up common misconceptions in order to optimize in a targeted manner:

• „More cache = faster and faster.“ The cache only comes into its own when a large number of new threads are actually created. Otherwise, I'm just tying up memory for no reason.
• „The cache speeds up authentication.“ The cache primarily saves the time required to create OS threads. Authentication, the TLS handshake, and, if necessary, DNS lookups occur for each connection and still require optimization independently.
• „Per-thread buffers remain occupied.“ After the disconnect, these buffers are released; mainly the thread's stack remains in the cache.
• „A large cache replaces app pooling.“ Server-side caching reduces costs, but application-side pooling eliminates them. I always consider application pooling as the first option.

Impact of TLS, DNS, and Authentication

I evaluate connection times on a case-by-case basis, since the cache does not address every part. High Handshake Times I often interpret this as a TLS issue (certificate validation, lack of resumption) or a DNS reverse lookup issue. With skip_name_resolve=ON I avoid expensive reverse lookups and rely on IP-based grants. The choice and configuration of the auth plugin also influence the login path. The thread cache, on the other hand, primarily reduces the cost of Thread Creation and Destruction. If I continue to see high Connect latencies despite a large cache, I focus on TLS parameters, DNS, and client connection management.

Decision-Making Logic: Cache, Thread Pool, or Application Pooling?

I make my decisions by following a simple path:

• Is app pooling available? If so, size it properly. Sinks Threads_created Clearly, a small-to-medium-sized cache is sufficient as a buffer.
• Is the thread pool active? Then... thread_cache_size No. I fine-tune the pool and measure wait times before I adjust any other parameters.
• Many short connections without a pool? Increase the cache size moderately. Goal: a noticeable decrease in the rate. Threads_created / Connections and quieter Connect times.
• Very high parallelism and scheduler pressure? I'm looking into switching to the thread pool, which can provide work-stealing and tighter worker quotas.

The fallback strategy is important: If an approach doesn't perform measurably better, I revert to the previous change. That way, I stay close to the data and avoid unnecessary complexity.

Measurement Methodology with Sample Queries

I use reproducible queries to track progress. For a snapshot:

• SHOW GLOBAL STATUS LIKE 'Threads\_%'; supplies Threads_created, Threads_cached, Threads_connected
• SHOW GLOBAL STATUS LIKE 'Connections'; for the basis of the quota
• SHOW VARIABLES LIKE 'thread\_%'; at thread_cache_size and thread_stack to check

Here's how I calculate the ratio, for example:

SELECT 
  ROUND(tc.variable_value+0 / NULLIF(c.variable_value+0, 0), 4) AS threads_created_per_connection
FROM information_schema.GLOBAL_STATUS tc
JOIN information_schema.GLOBAL_STATUS c
  ON tc.variable_name='Threads_created' AND c.variable_name='Connections';

For load tests, I rely on time windows. I take two snapshots (the start and end of a 5- to 10-minute interval) and calculate the differences. Optionally, I use an isolated test environment FLUSH STATUS, to reset counters—in production, I avoid doing this so as not to interfere with other analyses. In addition to the rate, I store the 95th and 99th percentiles of connection duration from client monitoring, because that’s exactly where the effects on latency spikes become apparent.

Identify: The cache is too small

I can often tell that the cache is too small when `Threads_created` rises sharply under a constant load. At the same time, `Threads_cached` remains low, even though the system is processing many connections and the utilization rate is poor. The result is fluctuating Latencies and unnecessary CPU‑Load caused by frequent thread creation. If the cache grows moderately and the metrics stabilize, this confirms the diagnosis. If the response times become more consistent and the performance ratio improves significantly, I’m on the right track. If there’s no improvement, I’ll look specifically for client-side causes, network issues, or storage bottlenecks.

Identify: The cache is too large

A cache that's too large is less likely to be noticed, but it can tie up memory that other buffer targets need. I'm already seeing a good hit rate, but increasing the cache size hardly makes a difference and just puts a strain on the Resources. If `Threads_cached` remains consistently well above the usual peak, the benefit is lost. I reduce the value gradually and check whether metrics or response times change. If everything remains stable, I stick with the smaller, more efficient setup. This way, I keep the instance lean and leave room for more important memory areas such as the InnoDB buffer and query cache replacement structures.

Special Feature: Thread Pool Active

Once the thread pool is running, MariaDB completely ignores the `thread_cache_size` variable. In this mode, a single pool controls a small number of workers that handle many connections, thereby preventing wait times for new threads. I decide whether to use thread pooling based on the load profile. right The approach is... or the cache is more Flexibility provides. Highly parallelized workloads often benefit from the pool, while traditional login spikes perform well with cache reuse. Those who use the pool should focus on its parameters and leave `thread_cache_size` out of the equation. A good starting point is to read about the MariaDB Thread Pool, before I plan any further tuning steps.

Interaction with the Application's Connection Pooling

I prefer an app-side pool because it keeps connections open and reduces the load on the database server. If `Threads_created` remains low despite high load, this indicates effective pooling and a low need for additional cache. In this setup, a small cache is often sufficient to absorb occasional spikes and does not Resources wasted. If, on the other hand, I see constant reconnects, I first try proper app pooling and only then increase the database settings. Looking at idle times and pool sizes helps find the sweet spot for an even load. A practical guide to [topic] provides useful introductory information on Connection Pooling, which I use in conjunction with cache optimization.

Example: Configuration and Monitoring

First, I'll check the current setting using SHOW VARIABLES LIKE 'thread_cache_size' and record the load. Then, as a test, I set a moderate value such as SET GLOBAL thread_cache_size = 256; or 512, depending on the tips. It's important to make a permanent change in the configuration file, such as in my.cnf at [mysqld], so that a restart will preserve the settings. In the following load windows, I observe Threads_created and the associated Quote, until I see a clear trend. If the number of new entries drops significantly, the cache is working as intended. If the numbers remain unchanged, I look for causes in connection management before increasing the value further.

Practical Playbook: Sizing with Guiding Principles

I work with reliable guidelines rather than blindly maximizing:

• Start: Current highs of Threads_connected observe (across several typical load windows).
• Initial Sizing: Cache ≈ 70–90 % of the usual peak, further capped by an upper limit such as max_connections / 2 as a safety limit.
• Step size: Increase in small increments of 64–128 and the ratio Threads_created / Connections check.
• Target Range: A significant drop in the rate and lower 95th percentiles for connection times; if the effect does not occur, reduce the cache.
• Persistence: Starting with MariaDB versions that include SET PERSIST I save tested values directly on the server; otherwise, in my.cnf.
• Rollback: Before making any changes, I record the previous value so I can quickly revert it if necessary.

In environments with highly variable day/night patterns, I recommend a conservative sizing approach that smooths out peaks without tying up an unnecessary amount of storage at night. For special loads (deployments, cron waves), I intentionally build in buffers.

Troubleshooting Checklist

First, I check whether the thread pool is active and thus overrides the cache. Then I measure the ratio of Threads_created to Connections over several time windows, rather than just taking a single snapshot. Next, I compare `Threads_cached` with the peak value of `Threads_connected` to identify over- or under-provisioning. If performance remains poor, I investigate application reconnects, network latencies, and storage signals such as increased I/O wait times. Finally, I examine competing settings that affect threads and provide repeatable test scenarios. This is the only way I can draw clear conclusions and avoid taking action without a solid data foundation.

Abridged version for those in a hurry

I use the thread cache to reuse threads and reduce creation costs. This is effective when the connection frequency is high, whereas an active thread pool ignores this variable. Success can be measured by a decreasing ratio of Threads_created to Connections and lower latency. I start small, monitor performance consistently, and only increase settings when the numbers and profile justify it. Client-side pooling is often the most effective lever, so that's where I look first. This way, I achieve better performance with less overhead and keep the configuration lean.