In this blog post, we’ll discuss best practices for configuring optimal MySQL memory usage.
Correctly configuring memory is critical for MySQL performance and stability. Default settings in MySQL 5.7 use very little memory, which is inefficient—but over-allocation can cause instability or crashes.
Key rule: MySQL should never cause the operating system to swap. Even small amounts of swapping activity can severely degrade performance.
Some swap usage is fine (for idle OS processes), but active swapping—visible in the si and so columns of vmstat—should be avoided.
Example: No Significant Swapping
Example: Heavy Swapping
If you’re using Percona Monitoring and Management, you can monitor swap activity directly.
Swap spikes over ~1MB/sec or sustained activity indicate memory misconfiguration.
MySQL memory usage includes:
Because of this complexity, it’s best to monitor actual usage via VSZ:
|
1 |
ps aux | grep mysqld |
|
1 |
mysql 3939 30.3 53.4 11635184 8748364 ? Sl Apr08 9106:41 /usr/sbin/mysqld |
The 5th column shows VSZ (~11GB here).
Recommendation: Keep mysqld VSZ under ~90% of system memory.
Typically, MySQL should use no more than 90% of system memory to leave room for the OS and other processes.
Use less if:
Memory allocation considerations:
Main components:
innodb_buffer_pool_sizekey_buffer_sizequery_cache_sizetable_open_cacheNote: InnoDB buffer pool uses an additional 5–10% overhead.
System: 16GB RAM
Final recommendation:
|
1 |
innodb_buffer_pool_size=12G |
Keep swap enabled (minimum ~4GB or 25% RAM):
Reduce swap usage tendency:
|
1 |
echo 1 > /proc/sys/vm/swappiness |
Prevent MySQL from being killed first:
|
1 |
echo -800 > /proc/$(pidof mysqld)/oom_score_adj |
On multi-socket systems:
|
1 |
innodb_numa_interleave=1 |
Or use:
|
1 |
numactl --interleave=all |
