MySQL using more memory than it can?

Question

FROM: -- MYSQL PERFORMANCE TUNING PRIMER --
MySQL Version 5.6.24-72.2-log x86_64
...
MEMORY USAGE
Max Memory Ever Allocated : 70.35 G
Configured Max Per-thread Buffers : 972 M
Configured Max Global Buffers : 70.04 G
Configured Max Memory Limit : 70.99 G
Physical Memory : 125.88 G
Max memory limit seem to be within acceptable norms
...

TOP reports this:

3642 mysql     20   0  146g 120g 6300 R 48.1 95.8 125131:57 mysqld

If according to the tuner MySQL can never use more than 70.99 G. What else can I check for on what the MySQL process is doing to use more than double the memory?

Answer 1

You're confusing three completely different measurements:

1. How much allocated memory MySQL asks for. This is what you're configuring to 71GB.

2. How much virtual memory MySQL is using. This is the 146GB. There is no reason you should care about this, virtual memory is effectively free. (Memory mapping a file, for example, will increase virtual memory consumption.)

3. How much physical memory the OS decides to give MySQL. This is the 120GB. Operating systems frequently give programs more physical RAM than they ask for if they are not under memory pressure and think that it can reduce the amount of paging. When not under memory pressure, it's not unusual for the physical memory usage to rise up to near the virtual memory usage.

The OS could go through the effort to reclaim the physical memory, but it doesn't for three reasons:

1. Doing so would be a wasted effort, since there's no need do it.

2. Doing so would increase the page faults MySQL would experience because if it accessed any of those virtual pages and they aren't mapped to physical memory, that causes a page fault.

3. Doing so would not make it any easier to use that memory for something else later because the OS would have to go to the effort of transitioning it from free to used.

If you place the system under memory pressure, that will force the OS to reclaim the physical memory. You should see the physical RAM usage drop to around the level you configured, but there will likely be some increase in the number of page faults.

So nothing you're observing indicates anything wrong.