Bug #114604 innodb_dedicated_server: need an option to lower the 'detected server memory'

• Submitted: 10 Apr 2024 5:15
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 8.0.36
• OS: Any
• CPU Architecture: Any

[10 Apr 2024 5:15] Shane Bester

Description:
With reference to:
 https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

On small servers (e.g. < 64G RAM), using 75% of detected server memory for innodb_buffer_pool_size ends up starving the OS and even backup software.

We require another option,  such as :

innodb_dedicated_server_reserve=8G   so this can exclude 8G as a fixed amount,
from the calculation and leave that as the base amount to use for OS and other mysql buffers (GR, optimizer, routine + table cache, etc).

e.g. on a 16G server:

current: 16 * 0.75 = 12G used for innodb_buffer_pool_size is too much.
proposed: (16 - innodb_dedicated_server_reserve) * 0.75 = 6G is more appropriate for some.

How to repeat:
Run load test on GR with innodb_dedicated_server=ON on a 16G server, server will run out of memory pretty fast.

Reason is innodb_dedicated_server doesn't take into account anything else using RAM within the server itself.  So it's overly confident.

Suggested fix:
revise the algorithm use in establishing how much of the detected RAM is safe to use.

On massive servers 75% is okay, because 25% left for the OS + other mysql internals is enough.   On small servers < 64G it gets to be a tight fit.

[24 Jul 19:32] MySQL Verification Team

I see 9.4.0 got this:

https://dev.mysql.com/doc/refman/9.4/en/server-system-variables.html#sysvar_server_memory

I would still prefer my suggested FR here.