Memory Allocation Strategies

Allocate memory pools for connections, sorts, and joins to prevent swapping.

In this lesson, you will learn how to configure MySQL and MariaDB memory buffers to handle concurrent operations efficiently while avoiding the performance death-trap of OS swapping.

The Problem with Over-Allocation

In database management, "more memory" isn't always better. If you allocate more memory to MySQL than your OS has available, the operating system will start swapping memory pages to disk. Because disk access is exponentially slower than RAM, a database that begins "swapping" will experience a catastrophic drop in performance. To prevent this, we must balance fixed-size global buffers with dynamic per-connection memory.

Balancing Global and Per-Connection Pools

Global memory buffers, like the innodb_buffer_pool_size, are shared across the entire instance. These should be large (typically 60–80% of total system RAM). However, memory is also consumed dynamically by individual sessions whenever a query runs.

Each connection reserves its own memory for:

Sort Buffer: Used for ORDER BY and GROUP BY operations.
Join Buffer: Used when full table scans are required for joins.

If you have 500 active connections and each consumes a massive sort_buffer_size, you will quickly exhaust system RAM.

Strategy in Practice

Consider a server with 16GB of RAM. If you set sort_buffer_size to 2MB and join_buffer_size to 1MB, a single complex query might consume 3MB. If 1,000 users connect simultaneously, that is 3GB of memory committed purely to per-connection buffers, on top of your InnoDB Buffer Pool.

Use the following approach to calculate your safe limit:

-- Global buffers (Set these once)
SET GLOBAL innodb_buffer_pool_size = 8G;

-- Per-connection buffers (Keep these lean)
SET GLOBAL sort_buffer_size = 256K;
SET GLOBAL join_buffer_size = 256K;

When tuning, the goal is to set these buffers just large enough to handle typical queries without needing to spill to disk, but small enough that (Max_Connections * Per_Connection_Buffers) + Global_Buffers remains well under your physical RAM limit. If your queries consistently require massive sort buffers, it is often a sign that you need better indexing rather than more memory.

Key Takeaway: To prevent system swapping, allocate global buffers for your working set while keeping per-connection buffers lean to account for the total multiplier of concurrent sessions.