Description:
bug#78270 brings up an interesting topic in MySQL and there are other places where this comes to play.
MySQL 5.7 has made progress in adding memory metrics to MySQL in performance_schema. Now we can see where memory gets allocated and how often this happens.
The main problem is that the mysqld process' memory footprint can change significantly depending on usage. This can have a very negative impact if we try to use "as much RAM as possible" for mysqld in an attempt to get the best performance.
On a dedicated server I generally set the mysqld memory size (via the buffer pool configuration) to use most of the system's RAM, leaving a bit of spare for other processes on the server and a bit of free space.
So issues like bug#78270 are an indication of an underlying problem. Innodb does not provide itself with a pool of memory from which all memory allocations are made.
Therefore according to usage the memory footprint of the server may suddenly change.
If it increases the server may run out of memory and the kernel may kill the process, and if you adjust the buffer pool to take this into account you are not using (most of the time) memory which would otherwise be available.
How to repeat:
see above.
Suggested fix:
My suggestion is to make InnoDB allocate _all_ it's memory out of the buffer pool (plus any associated housekeeping this needs).
Doing this would mean that memory usage by InnoDB would be limited to a certain fixed size (I'm aware that mysqld uses memory in other places as well) and tasks such as the one indicated in bug#78270 would not use extra memory but may force some pages to be flushed out to allow the operation to continue. I would prefer that as I'd then be able to safely configure my server to use a larger buffer pool which would be used in normal processing outside of these times.
Dealing with the memory pressure and figuring out what to flush out is going to be a bit trickier but this would simplify configuring the server as adjustments which are made currently to take certain workloads into account would probably no longer be needed.
This is not going to happen in 5.7 but it's something I'd love to see in 5.8.
