MySQL service still runs with the default 128M innodb_buffer_pool_size

[imc67]

On one of my servers I gave the MySQL service a 10 GB memory limit, but innodb_buffer_pool_size is still MySQL's own default of 128M. All InnoDB data of all apps on that server together is 366 MB, so nothing stays cached and every query reads from disk again.

What I measured on FreeScout (38k rows in threads, 332 MB of message bodies): a search took 7.8 seconds. I raised the buffer pool to 1G at runtime and the same search took 1.4 seconds. I've left it at 1G for now, but it won't survive a restart of the service.

Would it be possible to derive innodb_buffer_pool_size from the memory limit that is already configured for the service? That setting already says how much memory I want it to use. I can't make it stick myself, since /etc/mysql/my.cnf points into /run.

[imc67]

To show this isn't just the one server: on another Cloudron of mine the MySQL service holds 2.4 GB of InnoDB data across all apps, also on the default 128M buffer pool. The four biggest are a monitoring app(LAMP) (689 MB), MainWP (569 MB), Matomo (443 MB) and a geocoding cache (LAMP) (386 MB), so this isn't a "FreeScout only" thing. Memory limit there is 8 GB as well.

I've now set 3G there and 2G on a third server, sized to how much data is actually in them. All of it goes back to 128M as soon as the service restarts, which is the main reason I'd rather see the value derived from the memory limit than set by hand.

[imc67]

The risk of forgetting and setting this by hand after every reboot is real, so I put a small systemd timer on all three servers. Sharing it in case it's useful to someone else, and to show how little is needed if the value were just set at startup.

The script only issues a runtime SET GLOBAL, the same thing I was typing by hand. It doesn't touch any Cloudron file:

#!/bin/bash
set -euo pipefail

WANT_GB=3
WANT=$(( WANT_GB * 1024 * 1024 * 1024 ))

CT=$(docker ps --format '{{.Names}}' | grep -ix mysql | head -1)
[ -z "$CT" ] && exit 0

CUR=$(printf '%s\n' 'SELECT @@innodb_buffer_pool_size;' \
  | docker exec -i "$CT" bash -c 'mysql -uroot -p"$CLOUDRON_MYSQL_ROOT_PASSWORD" -N' 2>/dev/null | tail -1) || exit 0
case "$CUR" in ''|*[!0-9]*) exit 0 ;; esac
[ "$CUR" -ge "$WANT" ] && exit 0

printf '%s\n' "SET GLOBAL innodb_buffer_pool_size=$WANT;" \
  | docker exec -i "$CT" bash -c 'mysql -uroot -p"$CLOUDRON_MYSQL_ROOT_PASSWORD"'
logger -t mysql-bufferpool "buffer pool restored: $CUR -> $WANT"

With a timer on OnBootSec=3min and OnUnitActiveSec=15min. The quarter-hourly check is there because the service also restarts on platform updates, not just on reboot. It does nothing when the value is already fine, and the password stays inside the container.

WANT_GB is a constant I fill in once per server, at install time, from how much data is actually in there:

DATA_MB=$(printf '%s\n' 'SET SESSION information_schema_stats_expiry=0;
  SELECT ROUND(SUM(data_length+index_length)/1024/1024) FROM information_schema.tables
   WHERE table_schema NOT IN ("mysql","information_schema","performance_schema","sys");' \
  | docker exec -i mysql bash -c 'mysql -uroot -p"$CLOUDRON_MYSQL_ROOT_PASSWORD" -N' | tail -1)

# data x 1.25, rounded up to whole GB, min 1, capped at half the service limit and at 4
WANT_GB=$(( (DATA_MB * 125 / 100 + 1023) / 1024 ))

That gives 3G, 3G and 2G on my three servers (1855, 2077 and 1215 MB of data). The script itself never runs this, it only reads the constant. A bug in a runtime calculation could set something absurd and OOM the container, and this value changes maybe once a year.

For Cloudron it could be much simpler than this, because you don't need to know how much data is in there: a fraction of the service memory limit the admin has already configured would be fine. My formula only looks at the data because that's the part I can measure from outside.

One correction to my first post: the "366 MB" I quoted for that server was wrong. That was the FreeScout database on its own, not the total. All apps together on that server are 2077 MB, so the default 128M was even further off than I said. If you check this yourself, note that information_schema.tables caches its statistics for 24 hours by default (information_schema_stats_expiry), which is easy to trip over when measuring before and after.

Still hoping the value can be derived from the service memory limit, so this timer can go away.

[james]

Hello @imc67
Thanks for the report.
I am thinking about setting a dynamic capped max value for innodb_buffer_pool_size.
Something like clamp(0.5 * memory_limit, 512M, 4G) in words, take half the memory limit, but never go below 512 MB and never above 4 GB.

Do you have thoughts on that approach?

[imc67]

That shape looks right to me. Two things from having done it by hand here.

It needs to be applied on every start of the service, not just once at install. That is the actual problem I ran into: the value drops back to 128M whenever the mysql service restarts, so a one-off calculation would put people back at the default after the next update.

innodb_buffer_pool_size is a dynamic variable, so you can apply it straight after someone changes the memory limit too, without restarting mysql. I resized three servers live yesterday and Innodb_buffer_pool_resize_status tells you when it has finished. One detail worth knowing: MySQL rounds the value up to a multiple of the chunk size, so the effective figure can differ slightly from what the formula produces.

[james]

Hello @imc67
The idea was to determin this value on the start up of the MySQL service container and write that into the cnf file.
That should be just fine, right?

[imc67]

Yes, that covers it. Writing it into the cnf when the container starts is exactly the piece that is missing today, because starting up is the moment the value falls back to 128M.

It also fits how things already work: /etc/mysql/my.cnf is a symlink into /run, which gets rebuilt on every start anyway, so there is no new mechanism needed for it.

One question while you are in there: does changing the memory limit of a service actually restart its container? In my eventlog a service.configure with a new memoryLimit isn't always followed by a rebuild or a restart. If it isn't, then someone who raises the limit because things feel slow would not see the buffer pool follow until the next restart, which could be months later. Since innodb_buffer_pool_size is a dynamic variable, setting it live at that moment as well would cover that case.

[girish]

Thanks for reporting, I have fixed this now. I have also had to make the container rebuild when the memory limit is changed, so that mysql can adjust itself appropriately on start up.