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.
