Implement Buffer Pool Auto-Scaling Based on RAM Availability

Description

Enable the buffer pool to adjust innodb_buffer_pool_size based on available
RAM, targeting 75–80% by default, with configurable scaling factors and limits
for stability. See MDEV-36194 for the originating use case.

The motivation is environments where RAM available to the server is not static
— most notably Kubernetes pods, cgroup-limited containers, and single hosts
running multiple MariaDB instances that share memory dynamically.

Tasks

• Implement periodic RAM detection (syscalls or OS notifications).
• Add system variables:
  • {{buffer_pool_auto_scale_method = {OFF, RAM_percentage}

    }} (default: OFF).

  • buffer_pool_auto_scale_factor = N (default: 0.75, range: 0.1–1.0).
  • buffer_pool_min_size (default: 128MB), buffer_pool_max_size
    (default: unlimited).
• Set innodb_buffer_pool_size = RAM * buffer_pool_auto_scale_factor,
  respecting limits.
• Reject manual SET GLOBAL innodb_buffer_pool_size when auto-scaling is
  active.
• Make polling frequency configurable (e.g., once per second, per minute),
  defaulting to once per minute.

Testing strategy

Standard benchmarks are insufficient to validate this feature, because they
don't exercise the behaviour the feature controls:

• HammerDB — dataset grows linearly; memory pressure is monotonic, not
  variable.
• Sysbench — constant thread count and table size; no up/down RAM
  fluctuation.

A dedicated test framework will need to be developed as part of this work.
Concretely:

• Variable-load workload generator. A driver that can produce controlled
  memory-pressure curves — ramp up, ramp down, sustained pressure, bursty
  pressure, sawtooth — so the auto-scaling response can be measured at each
  phase rather than at steady state.
• Multi-instance harness on a single machine. Two or more MariaDB instances
  on the same host (mirroring the K8s pod / multi-tenant case) with overlapping
  scaling configs; verify that one instance's auto-scaling decisions don't
  starve the other, and that buffer_pool_min_size is respected under
  contention.
• Container / cgroup limited runs. Validate that the RAM-detection path reads
  the cgroup limit (not host RAM) under Kubernetes-like constraints; verify
  behaviour at the cgroup boundary and during cgroup-limit changes.
• Stability under polling intervals. Verify that 1-second and 1-minute
  polling produce stable, non-oscillating buffer-pool sizes; check that the
  scaling decision damps thrashing when free RAM hovers near a threshold.
• Interaction with other memory consumers. Behaviour when the host is also
  under pressure from non-server processes (page cache, sibling instances,
  external workloads).

The above is in addition to the standard MTR coverage for the new SQL surface
(variable defaults, range validation, rejection of manual SET GLOBAL while
active).

Acceptance Criteria

• buffer_pool_auto_scale_method=RAM_percentage activates RAM-based scaling.
• Example: 16GB RAM, buffer_pool_auto_scale_factor=0.75,
  innodb_buffer_pool_size=12GB.
• buffer_pool_auto_scale_factor configurable between 0.1–1.0.
• Respects buffer_pool_min_size and buffer_pool_max_size.
• Manual SET GLOBAL innodb_buffer_pool_size fails with an error when
  auto-scaling is active.
• System variables are manually configurable.
• Validation: the test framework above demonstrates correct behaviour under
  at least the following scenarios — ramp-up pressure, sustained pressure,
  ramp-down pressure, two MariaDB instances on one host, and cgroup-limited
  container.