When MySQL's Memory Usage Doesn't Match the my.cnf Settings You Set

The Buffer Pool Paradox

You've just deployed MySQL on a new server with 16GB RAM. You carefully set innodbbufferpool_size = 4G in my.cnf, restarted the service, and expected to see MySQL using roughly 4-5GB including overhead. Instead, ps aux shows the mysqld process consuming 8GB and climbing.

This isn't a configuration error or a runaway query. It's MySQL doing exactly what it should - but using memory pools most administrators don't know exist.

The Hidden Memory Consumers

MySQL's memory usage extends far beyond the InnoDB buffer pool. The query cache, thread buffers, temporary tables, and connection overhead each claim their share. More importantly, these allocations happen dynamically based on actual workload, not your static configuration.

The performanceschema consumes memory proportional to your maxconnections setting. Set it to 1000 "just in case" and you've allocated several hundred megabytes before a single client connects. Each connection thread gets its own read buffer, sort buffer, and join buffer according to your session variables.

Temporary tables for complex queries bypass your carefully tuned buffer pool entirely. A single GROUP BY on an unindexed column can allocate gigabytes of RAM for intermediate results.

What the Process Lists Don't Show

Linux's memory reporting adds another layer of confusion. The RSS (resident set size) in ps includes shared libraries that might be counted multiple times across processes. The VSZ (virtual size) includes memory-mapped files that aren't actually resident.

MySQL's own SHOW ENGINE INNODB STATUS reports buffer pool usage accurately, but it doesn't account for the dozen other memory pools running alongside it. The gap between your buffer pool size and actual memory usage reveals where the real consumption occurs.

Tracking the Real Culprits

Monitor these specific metrics rather than just total memory usage:

• Innodbbufferpoolbytesdata vs innodbbufferpool_size - shows actual vs allocated buffer pool memory
• Connectionerrorsmax_connections - indicates if you're hitting connection limits that drive memory allocation
• Createdtmpdisktables vs Createdtmptables - reveals when temporary tables exceed tmptable_size and spill to disk instead of consuming more RAM
• Threads_connected multiplied by your per-thread buffer sizes gives you the baseline connection overhead

The key insight is monitoring memory allocation patterns over time rather than point-in-time snapshots. Server Scout's MySQL plugin tracks these relationships automatically, correlating buffer pool efficiency with overall memory consumption patterns.

Right-Sizing Your Configuration

Start with conservative buffer sizes and monitor actual usage patterns. A 4GB buffer pool that's 60% utilised performs better than an 8GB pool that forces the system into swap when connection counts spike.

Set max_connections based on your actual concurrent connection patterns, not theoretical maximums. Every unused connection slot still consumes memory for potential thread buffers.

Consider your query patterns when setting temporary table limits. OLAP workloads with large aggregations need different memory allocation strategies than OLTP systems with predictable query sizes.

The goal isn't minimising memory usage - it's predictable memory usage that scales with your actual workload rather than your configuration maximums.

If you're running multiple MySQL instances or managing hosting environments, proper memory monitoring becomes even more critical. Start with Server Scout's free trial to get visibility into these memory allocation patterns across your infrastructure.