Skip to content 
I show how I work with Buffer Instances scale the InnoDB cache on multi-core systems and significantly reduce lock conflicts. The focus is on the MariaDB Buffer and the `innodb_buffer_pool_instances` parameter, so that threads can access the buffer pool efficiently, latencies are more consistent, and throughput increases.
Key points
- Mutex Contention Minimize and decouple concurrent accesses
- Cache Locality increase performance and make better use of CPU caches
- Version check, since the parameter is partially ineffective
- Scale Note: ≥ 1 GB per instance
- Monitoring Use it and make gradual adjustments
The InnoDB Buffer Pool Explained Briefly
I view the InnoDB buffer pool as Hub for data and index pages in RAM, because it determines how often MariaDB can avoid slow I/O operations. The more active data that fits into RAM, the less often the engine has to read from the disk, which reduces response times and increases throughput. On servers running almost exclusively MariaDB, I usually reserve 60–80 % of RAM; on mixed hosts, I tend to reserve 40–60 % to ensure enough memory remains for the system. It’s important that the „hot data“ fits in memory so that queries can repeatedly read from the cache. To ensure this, I monitor the hit rate, adjust the size, and maintain the Load peaks at a glance.
Why use multiple buffer pool instances on multi-core systems?
Reduce the number of instances Lock waiting times, because threads do not all access the same internal structures. With a single, large pool, competition for mutexes increases, which slows things down under high parallelism. I split the pool so that workloads are distributed across different instances, which reduces the likelihood of hotspots. In addition, this improves cache locality because repeated accesses are more likely to occur within the same instance, allowing CPU caches to be used more effectively. The result is more consistent latencies and a reliably higher Throughput with a high degree of parallelization.
Version Reality: When `innodb_buffer_pool_instances` Takes Effect
Before I set the number of instances, I check the Version in my MariaDB, because starting with certain releases (e.g., 10.5.1), the parameter sometimes no longer takes effect. Newer versions have improved buffer pool locking internally, meaning that fewer instances are sufficient or the change has no effect at all. In older versions, however, splitting the buffer pool often provides clear benefits, especially with large pools and high concurrency. I therefore wait until after a version check to decide whether to optimize the instances or instead prioritize other tuning parameters. These include the size of the buffer pool, redo log parameters, and the system-wide Thread Control.
Determine the buffer pool size
I start by determining the pool size so that the instances end up being a reasonable size later on and don't end up being too small. On dedicated database servers, I allocate 60–80 % of RAM, and on shared hosts, I tend to allocate 40–60 %, so that the OS and services retain enough buffer. The goal: to keep 80–90 % of the active data in the pool, if possible, so that the hit rate remains close to 99 %. If you’d like to dive deeper into this, you’ll find a concise overview in the Buffer Pool Sizing Practical guidelines. I understand greatness as something fluid Budget and scale it as workloads grow or new applications are added.
Selecting the Number of Instances: Rules of Thumb with a Sense of Proportion
For larger pools, I like to start with „one instance per GB,“ but I usually limit the number to 8–16 instances so that management doesn’t become too burdensome. For pools smaller than about 1 GB, I skip creating instances altogether, since the benefit is minimal. I also make sure that each instance has at least 1 GB; otherwise, the fragmentation becomes too high relative to the benefit. I also base my decisions on the number of CPU cores and the expected level of parallelism to ensure the instances are allocated sensibly. For example, on an 8-core server with a 16 GB pool, I run 8 instances of about 2 GB each, which Resources well distributed and contention reduced.
How InnoDB Distributes Pages Across Instances
When I think of instances, I don't think of „separate caches per table,“ but rather an internal, deterministic distribution individual pages (data and index pages) across multiple subpools. The assignment is based on internal IDs and hashes; as a result, identical ranges are consistently placed in the same instance. This is good for locality, but it has an important consequence: A only A hotspot (e.g., the „last“ leaf page for monotonically increasing primary keys) remains a hotspot within a single instance. Using multiple instances does not eliminate such design hotspots, but it does decouple different hot sets from one another and reduces global mutex contention. That is why I also examine the key design and the query profile to Hot Pages to prevent it from arising in the first place.
Making the Most of NUMA and Cache Locality
On systems with NUMA architecture, I check memory placement to ensure that threads compute as close as possible to their data. A good strategy minimizes remote accesses, which reduces latency and mitigates variance. I coordinate the number of instances, CPU pinning, and memory policy to enhance cache locality. If you’d like more details on this, take a look at the brief NUMA Policies for database servers. This way, I keep data paths short and ensure consistent Performance even under pressure.
Flush Strategy, Page Cleaner, and I/O Capacity
A well-structured buffer pool only shows its true strength when the Background Flushing It's running smoothly. I monitor the length of the flush and LRU lists and adjust the I/O capacities so that the page cleaner handles spikes without causing bursts. Typical tuning parameters are `innodb_io_capacity` and `innodb_io_capacity_max`, which I base on the underlying storage subsystem (significantly higher for SSDs than for HDDs). On flash media, I like to disable neighbor flushing („neighbors“) so that I don’t unnecessarily flush pages that will be replaced soon anyway. Consistent checkpoints and short flush queues keep latencies stable—which directly benefits the performance of multiple instances, since fewer threads have to wait for background write operations.
LRU Policy, Read-Ahead, and „Cold“ Traffic
I observe how workloads move pages through the LRU. For highly sequential scans, I use an appropriate „Old Blocks“ time to prevent cold accesses from displacing the recent block region. Read-ahead helps with true sequences, but puts a strain on the pool with random access patterns. The rule here is: Make it measurable, then fine-tune it. The point of the exercise is to the LRU division for young people to reserve for hot data so that queries are repeatedly executed from the same Instances and whether CPU caches are worthwhile. Incorrect read-ahead is particularly noticeable when there are multiple instances, because it spreads „noise“ surprisingly evenly across the subpools.
Adaptive Hash Index and Change Buffer
I check whether the Adaptive Hash Index (AHI) helps or hinders my pattern. Under very high concurrency, the AHI itself can become a bottleneck. In that case, it’s worth throttling or disabling it on a trial basis to observe the effect on latencies. For write-heavy workloads with many secondary index inserts, the Change Buffer Impact on I/O and page rotation. A larger buffer pool reduces this pressure because more index pages remain "hot," and inserts do not end up in "cold" structures as often. I correlate these observations with the number of instances: If I decouple the global locks by using more instances, it becomes clearer whether AHI or the change buffer is the actual bottleneck.
Warm Starts: Loading Buffer Pool Dumps
After a reboot, I don't want to see „cold“ latencies that last for minutes. So I enable the Dumping and Loading hot pages during shutdown/startup. This way, the service starts with a pool that’s already filled; the hit rate quickly returns to near 99 %, and I can see the performance effects of my instance selection without a cold cache distorting the picture. This particularly speeds up rollouts and kernel updates and is my default setting in production environments where I prioritize stability over pure peak values.
Configuration in my.cnf and restart
I enter the settings into my.cnf in an organized manner and carefully document every change. Important: First define the target size of the pool, then set the number of instances, and finally restart the server. After the reboot, I use `SHOW VARIABLES` to check whether the values have taken effect and verify the distribution in `SHOW ENGINE INNODB STATUS`. This ensures that the server is actually operating with the selected configuration. When making adjustments, I proceed in small steps so that I can clearly identify the effects and the Stability does not jeopardize operations.
# Example
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1
Monitoring: Metrics That Really Matter
First, I measure the pool's hit rate, then latencies, I/O load, and lock wait times. For day-to-day operations, a few meaningful metrics are sufficient; I check them regularly and store them as time series. If the hit rate falls below 99 %, I consider increasing the pool size before adding more instances. If mutex wait times increase despite a generally good hit rate, I test more instances, but only incrementally. This way, I remain able to act, identify trends early, and focus on the real Bottlenecks.
| Key figure | Target value | Query | Note |
|---|---|---|---|
| Buffer Pool Hit Rate | ≥ 99 % | SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%'; | If the values are low, increase the pool size or Workload optimise |
| Reads/Writes per Second | constant | SHOW GLOBAL STATUS LIKE 'Innodb_data_reads'; | Spikes indicate I/O bottlenecks and incorrect Sizes to |
| Mutex/Lock Wait Times | low | SHOW ENGINE INNODB STATUS; | If there are delays, increase the number of instances if necessary |
| Checkpoint Behavior | evenly | SHOW GLOBAL STATUS LIKE 'Innodb_checkpoint_%'; | Adjust the redo log size and flush strategy |
I link the data points to deployments, schema changes, and peaks so I can identify cause and effect. Clear notes save me time and reduce the risk of repeating the same mistakes. This way, a robust system gradually takes shape. Practical foundation for my business.
Fine-tuning: Make gradual adjustments instead of taking big leaps
I never change multiple parameters at once; instead, I evaluate them one after another and in small increments. First the pool size, then the instances, followed by redo log and flush strategies, and finally thread parameters. After each change, I wait long enough for the effects to become apparent and record the metrics. Especially with workloads that have fluctuating traffic, it’s worth monitoring the system over several days. This way, I avoid flying blind and keep the Performance Curve can be interpreted clearly.
Benchmarking Process: Rigorous Testing
I clearly separate the lab and production environments. In the lab, I warm up the pool, run different load levels (e.g., 4/8/16/32 threads), and vary the read/write ratios. I measure P95/P99 latencies, throughput, and wait times on mutexes. The key factor is the Reproducibility: same data set, same data distribution, same test period. Only when a configuration consistently performs better in two to three independent runs do I deploy it to production. Once there, I roll it out canary-like and compare time series before and after the change. This approach prevents random fluctuations from being mistaken for „optimization.“.
Typical pitfalls and anti-patterns
- Too many instances: Administrative costs are rising, LRU/flush lists are becoming fragmented, and background threads are running inefficiently. I'm staying conservative (2–8) and will only increase the value if measurements are needed.
- Instances that are too small: If you go below 1 GB per instance, the balance shifts quickly. It's better to use fewer, larger instances.
- Cold Cache in Analytics: Statements about instance effects are meaningless when the pool is cold. Use warm starts or long test windows.
- Hot Page Design Errors: Monotonous keys without distribution, wide secondary indexes, or missing covering indexes create hotspots that no number of instances can remedy.
- Incorrect I/O settings: SSDs with HDD-like flush parameters waste their potential and generate bursts that are incorrectly attributed to the instances.
Hosting and VPS in Practice: RAM, Cores, Workload
On shared environments, I configure the pool more conservatively so that web servers, caches, and the OS have enough headroom. On VPS or dedicated machines, I allocate more RAM to the pool to keep the hit rate high. I configure the instances so that they are optimally matched to the vCPUs and maintain at least 1 GB per instance. If you need powerful hosting or server solutions, choose offerings from webhoster.de, because their CPU cores, RAM, and I/O performance are designed for high parallelism. With this foundation, I keep latencies lower and maximize the Multi-core better.
Thread Pool and Concurrent Accesses
Even a well-allocated buffer pool is of little use to me if too many connections are competing at the same time. I therefore adjust connection and thread limits and check whether the Thread Pool brings benefits to my system. The goal is to keep active workers constantly busy without causing bottlenecks. I make sure that short, frequent queries don't get stuck behind heavy transactions. With clean control logic, I increase efficiency per core and ensure reliable Response times.
Quick Summary: Settings That Work for Me
I first check the Version and decide whether to use `innodb_buffer_pool_instances` or to focus on pool size, redo logs, and threads. Then I size the pool so that the active data fits into it, and set the number of instances high enough so that each one gets at least 1 GB. On multi-core systems, I aim for 2–8 instances and only increase this number if there is demonstrable mutex contention. I keep my monitoring lean but consistent, and I adjust parameters in small increments with clear benchmarks. This way, I achieve consistent latencies, better utilization, and noticeably greater efficiency. Throughput for my MariaDB workloads.
