#!/bin/bash
# alpha-dbtune — size MariaDB's memory to THIS box instead of a hardcoded number.
#
# WHY THIS EXISTS: the tuning file used to carry a literal
# `innodb_buffer_pool_size = 1536M`, chosen for one 6 GB server. That number is
# wrong everywhere else, and wrong in the dangerous direction: on a 2 GB VPS it
# hands MariaDB 75 % of RAM, and the first PHP worker that wants memory takes
# the whole box down with it. Debian's own default is the opposite failure —
# 128 MB no matter how big the machine is, so a multi-GB database re-reads every
# page from disk and pegs the CPU.
#
# So the size is COMPUTED, from two facts the box can tell us:
#   1. how much RAM there is       (/proc/meminfo)
#   2. how much data there is      (information_schema, per storage engine)
# and then clamped. Never more than the profile's share of RAM, and never more
# than the data can actually fill — a 40 MB database on a 16 GB box gets a small
# pool, because the other 4 GB would just be reserved and never touched.
#
# The caches that are NOT InnoDB get the same treatment. Debian ships
# key_buffer_size at 128 MB (MyISAM) and aria_pagecache_buffer_size at 128 MB,
# and both are allocated whether or not a single table uses those engines. On a
# pure-InnoDB server that is a quarter of a gigabyte reserved for nothing.
#
#   alpha-dbtune                      re-tune with the saved profile, restart if needed
#   alpha-dbtune --profile lean       12 % of RAM  — memory is scarce, CPU can suffer
#   alpha-dbtune --profile balanced   25 % of RAM  — the default: DB shares the box
#   alpha-dbtune --profile perf       45 % of RAM  — DB is the point of the server
#   alpha-dbtune --pool 512M          pin the pool explicitly, ignore the profile
#   alpha-dbtune --dry-run            print the plan and the file, change nothing
#   alpha-dbtune --no-restart         write the config, leave the running server alone
#   alpha-dbtune --json               machine-readable summary (for the panel)
#
# ⚠️ MariaDB 10.11 will NOT resize the buffer pool live — `SET GLOBAL
# innodb_buffer_pool_size` answers "Truncated incorrect value" and silently keeps
# the old size. A restart is the only way, so this script validates the config
# with `mariadbd --help --verbose` BEFORE touching a running server: a config
# that fails to parse would otherwise turn a tuning tweak into an outage.
set -eu

CNF=/etc/mysql/mariadb.conf.d/60-alpha-tuning.cnf
PROFILE_FILE=/var/www/alpha-private/dbtune.profile
PROFILE=""; PIN_POOL=""; DRY=0; RESTART=1; JSON=0

while [ $# -gt 0 ]; do
  case "$1" in
    --profile) PROFILE="${2:-}"; shift 2 ;;
    --profile=*) PROFILE="${1#*=}"; shift ;;
    --pool) PIN_POOL="${2:-}"; shift 2 ;;
    --pool=*) PIN_POOL="${1#*=}"; shift ;;
    --dry-run|-n) DRY=1; shift ;;
    --no-restart) RESTART=0; shift ;;
    --json) JSON=1; shift ;;
    -h|--help) sed -n '2,40p' "$0"; exit 0 ;;
    *) echo "BAD_ARG: $1" >&2; exit 2 ;;
  esac
done

# ---- profile ----------------------------------------------------------------
[ -z "$PROFILE" ] && [ -r "$PROFILE_FILE" ] && PROFILE="$(tr -dc 'a-z' < "$PROFILE_FILE")"
[ -z "$PROFILE" ] && PROFILE=balanced
case "$PROFILE" in
  lean)     RAM_PCT=12 ;;
  balanced) RAM_PCT=25 ;;
  perf)     RAM_PCT=45 ;;
  *) echo "BAD_PROFILE: $PROFILE (lean|balanced|perf)" >&2; exit 2 ;;
esac

# ---- what the box has -------------------------------------------------------
RAM_MB=$(( $(awk '/^MemTotal:/{print $2}' /proc/meminfo) / 1024 ))
[ "$RAM_MB" -gt 0 ] || { echo "NO_MEMINFO" >&2; exit 3; }

# ---- what the data needs ----------------------------------------------------
# Reachable? The panel's own DB is on the unix socket as root. If MariaDB is
# down (fresh install, mid-provision) we still write a sane RAM-only config.
# `mysql` is a compatibility symlink that a future MariaDB may drop, so prefer
# the real client name and fall back.
if command -v mariadb >/dev/null 2>&1; then MYSQL=mariadb
elif command -v mysql >/dev/null 2>&1; then MYSQL=mysql
else MYSQL=""; fi

# q <sql> — one scalar, or empty. Never fails the script: this whole file is
# best-effort measurement and a RAM-only answer is still a correct answer.
q() { [ -n "$MYSQL" ] || return 0; "$MYSQL" -N -B -e "$1" 2>/dev/null || true; }
# num <value> <fallback> — guard every arithmetic input, because `$(( / 1024 ))`
# on an empty string is a syntax error that would abort under `set -e`.
num() { case "${1:-}" in ''|*[!0-9]*) echo "$2" ;; *) echo "$1" ;; esac; }

INNODB_MB=-1; MYISAM_MB=-1; ARIA_MB=-1
if [ -n "$MYSQL" ] && "$MYSQL" -N -B -e "SELECT 1" >/dev/null 2>&1; then
  SIZES="$(q "
  SELECT
    IFNULL(ROUND(SUM(IF(engine='InnoDB', data_length+index_length,0))/1048576),0),
    IFNULL(ROUND(SUM(IF(engine='MyISAM', data_length+index_length,0))/1048576),0),
    IFNULL(ROUND(SUM(IF(engine='Aria',   data_length+index_length,0))/1048576),0)
  FROM information_schema.tables
  WHERE table_schema NOT IN ('information_schema','performance_schema');")"
  read -r _i _m _a <<EOF || true
$SIZES
EOF
  INNODB_MB=$(num "${_i:-}" -1); MYISAM_MB=$(num "${_m:-}" -1); ARIA_MB=$(num "${_a:-}" -1)
fi

# round DOWN to a whole number of 128 MB chunks: MariaDB rounds the pool up to a
# multiple of innodb_buffer_pool_chunk_size anyway, and a value that gets
# silently changed is a value you cannot verify afterwards.
chunk() { local mb="$1"; mb=$(( mb / 128 * 128 )); [ "$mb" -lt 128 ] && mb=128; echo "$mb"; }

# ---- the pool ---------------------------------------------------------------
if [ -n "$PIN_POOL" ]; then
  case "$PIN_POOL" in
    *[Gg]) POOL_MB=$(( ${PIN_POOL%[Gg]} * 1024 )) ;;
    *[Mm]) POOL_MB=${PIN_POOL%[Mm]} ;;
    *)     POOL_MB=$PIN_POOL ;;
  esac
  case "$POOL_MB" in ''|*[!0-9]*) echo "BAD_POOL: $PIN_POOL" >&2; exit 2 ;; esac
  POOL_MB=$(chunk "$POOL_MB")
  POOL_WHY="pinned by --pool $PIN_POOL"
else
  POOL_MB=$(( RAM_MB * RAM_PCT / 100 ))
  POOL_WHY="${RAM_PCT}% of ${RAM_MB} MB RAM ($PROFILE)"
  # Never reserve more than the data could ever put in it. +25 % headroom so a
  # growing database is not re-tuned on every insert.
  if [ "$INNODB_MB" -ge 0 ]; then
    CAP=$(( INNODB_MB + INNODB_MB / 4 + 128 ))
    if [ "$CAP" -lt "$POOL_MB" ]; then
      POOL_MB=$CAP; POOL_WHY="capped to data (${INNODB_MB} MB InnoDB + 25% headroom)"
    fi
  fi
  # Absolute ceiling. Past a few GB the pool stops being the bottleneck and the
  # RAM is worth more to everything else on the box.
  [ "$POOL_MB" -gt 8192 ] && { POOL_MB=8192; POOL_WHY="ceiling (8 GB)"; }
  POOL_MB=$(chunk "$POOL_MB")
fi

# ---- the caches that are usually pure waste ---------------------------------
# key_buffer_size only ever caches MyISAM *indexes*. No MyISAM tables → the
# 128 MB Debian default is 128 MB reserved for nothing at all.
if [ "$MYISAM_MB" -lt 0 ]; then KEY_MB=32                       # DB unreachable: modest
elif [ "$MYISAM_MB" -eq 0 ]; then KEY_MB=16                     # none: minimum that works
else
  KEY_MB=$(( MYISAM_MB / 2 + 16 )); [ "$KEY_MB" -gt 256 ] && KEY_MB=256
fi

# Aria is what MariaDB uses for its own system tables, so it is never zero —
# but 3 MB of system tables do not need a 128 MB page cache.
if   [ "$ARIA_MB" -lt 0 ];  then ARIA_MB_CACHE=32
elif [ "$ARIA_MB" -le 64 ]; then ARIA_MB_CACHE=32
else ARIA_MB_CACHE=$(( ARIA_MB / 2 + 32 )); [ "$ARIA_MB_CACHE" -gt 256 ] && ARIA_MB_CACHE=256; fi

# ---- per-connection buffers -------------------------------------------------
# These are the ones that bite: they are allocated PER CONNECTION, so the true
# worst case is max_connections × (sort + join + read + read_rnd + tmp). On a
# small box 151 connections is a promise the RAM cannot keep.
if   [ "$RAM_MB" -lt 2048 ]; then MAXCONN=60;  TMP_MB=16
elif [ "$RAM_MB" -lt 4096 ]; then MAXCONN=100; TMP_MB=32
else                              MAXCONN=0;   TMP_MB=64      # 0 = leave the default
fi

WORST_MB=$(( POOL_MB + KEY_MB + ARIA_MB_CACHE + 128 ))         # + fixed server overhead
SUMMARY="RAM ${RAM_MB} MB · profile ${PROFILE} · pool ${POOL_MB} MB (${POOL_WHY}) · key_buffer ${KEY_MB} MB · aria ${ARIA_MB_CACHE} MB · ≈${WORST_MB} MB resident"

# ---- render -----------------------------------------------------------------
TMP="$(mktemp)"; trap 'rm -f "$TMP"' EXIT
{
cat <<HEADER
# Alpha Panel — MariaDB memory tuning.  GENERATED FILE — DO NOT EDIT BY HAND.
#
# Written by /usr/local/sbin/alpha-dbtune. Re-run it to change these values;
# hand edits are overwritten on the next panel update or provisioning run.
#
#   generated for : ${RAM_MB} MB RAM, profile "${PROFILE}"
#   data measured : InnoDB ${INNODB_MB} MB · MyISAM ${MYISAM_MB} MB · Aria ${ARIA_MB} MB   (-1 = server was down)
#   pool decision : ${POOL_WHY}
#   worst case    : roughly ${WORST_MB} MB resident before per-connection buffers
#
# Two failure modes this file exists to avoid:
#   · Debian ships innodb_buffer_pool_size COMMENTED OUT, so a multi-GB database
#     runs on the 128 MB default and burns CPU re-reading pages from disk.
#   · A pool sized for someone else's server takes RAM this box needs for
#     PHP-FPM, nginx and containers — and the OOM killer picks the victim.
#
# ⚠️ innodb_log_file_size is deliberately NOT set: resizing the redo logs needs a
# clean shutdown, and a mistake there costs a startup rather than a slow query.

[mysqld]

# ---- the page cache: the setting that actually matters ----
innodb_buffer_pool_size         = ${POOL_MB}M

# Don't let the OS cache the same pages a second time — the pool already holds
# them and the duplicate copy is pure RAM waste.
innodb_flush_method             = O_DIRECT

# SSD: no value in writing "neighbouring" pages together.
innodb_flush_neighbors          = 0

# ---- caches sized to the data that actually exists ----
# MyISAM index cache. ${MYISAM_MB} MB of MyISAM data on this server.
key_buffer_size                 = ${KEY_MB}M
# Aria page cache (MariaDB's own system tables). ${ARIA_MB} MB of Aria data.
aria_pagecache_buffer_size      = ${ARIA_MB_CACHE}M

# Big GROUP BY / COUNT should stay in memory instead of spilling to an on-disk
# temp table — but this is allocated per connection, so it scales with the box.
tmp_table_size                  = ${TMP_MB}M
max_heap_table_size             = ${TMP_MB}M
HEADER

if [ "$MAXCONN" -gt 0 ]; then
cat <<CONN

# Small box: 151 connections × per-connection buffers is more RAM than exists.
# Refusing the 61st connection is survivable; being OOM-killed is not.
max_connections                 = ${MAXCONN}
thread_cache_size               = ${MAXCONN}
CONN
fi

cat <<TAIL

# Make the next slow query findable instead of a mystery. Cheap: it only writes
# when a statement actually exceeds the threshold.
slow_query_log                  = ON
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 2
TAIL
} > "$TMP"

# ---- validate BEFORE it can hurt a running server ---------------------------
# mariadbd parses every conf.d file at startup, so a bad directive here means
# the next restart never comes back — and the restart might be weeks later, by
# which point nobody connects it to this change. Parse it now, with the real
# binary. This runs for --dry-run too: a dry run that does not tell you the
# config is broken is not a rehearsal of anything.
STAGE="$(mktemp -d)"; trap 'rm -rf "$TMP" "$STAGE"' EXIT
install -m 0644 "$TMP" "$STAGE/60-alpha-tuning.cnf"
if command -v mariadbd >/dev/null 2>&1; then
  ERR="$(mariadbd --defaults-file="$STAGE/60-alpha-tuning.cnf" --help --verbose 2>&1 >/dev/null | grep -v '^$' || true)"
  if [ -n "$ERR" ]; then
    echo "INVALID_CONFIG — not written:" >&2; echo "$ERR" >&2; exit 4
  fi
fi

if [ "$DRY" = 1 ]; then
  echo "PLAN: $SUMMARY"
  echo "     config parses cleanly (mariadbd --help --verbose)"
  echo "--- would write $CNF ---"
  cat "$TMP"
  exit 0
fi

CHANGED=0
if [ -f "$CNF" ] && cmp -s "$TMP" "$CNF"; then
  :
else
  mkdir -p "$(dirname "$CNF")"
  [ -f "$CNF" ] && cp -a "$CNF" "$CNF.bak"
  install -o root -g root -m 0644 "$TMP" "$CNF"
  CHANGED=1
fi
mkdir -p /var/log/mysql && chown mysql:adm /var/log/mysql 2>/dev/null || true

# ---- restart only if the RUNNING server disagrees ----------------------------
# Comparing against the live variables (not the file) means a re-run after a
# restart is a no-op, and an edited-but-not-restarted box gets fixed.
NEED_RESTART=0
if [ -n "$MYSQL" ] && "$MYSQL" -N -B -e "SELECT 1" >/dev/null 2>&1; then
  LIVE_POOL=$(( $(num "$(q 'SELECT @@innodb_buffer_pool_size')"    0) / 1048576 ))
  LIVE_KEY=$((  $(num "$(q 'SELECT @@key_buffer_size')"            0) / 1048576 ))
  LIVE_ARIA=$(( $(num "$(q 'SELECT @@aria_pagecache_buffer_size')" 0) / 1048576 ))
  [ "$LIVE_POOL" != "$POOL_MB" ]       && NEED_RESTART=1
  [ "$LIVE_KEY"  != "$KEY_MB" ]        && NEED_RESTART=1
  [ "$LIVE_ARIA" != "$ARIA_MB_CACHE" ] && NEED_RESTART=1
else
  NEED_RESTART=$CHANGED
fi

RESTARTED=0
if [ "$NEED_RESTART" = 1 ] && [ "$RESTART" = 1 ] && command -v systemctl >/dev/null 2>&1; then
  if systemctl is-active --quiet mariadb 2>/dev/null; then
    systemctl restart mariadb && RESTARTED=1
    # Give it a moment to accept connections again before anyone believes us.
    for _i in 1 2 3 4 5 6 7 8 9 10; do
      mysql -N -B -e "SELECT 1" >/dev/null 2>&1 && break; sleep 1
    done
  fi
fi

# persist the profile so a bare re-run keeps the operator's choice
if [ -d "$(dirname "$PROFILE_FILE")" ]; then
  printf '%s\n' "$PROFILE" > "$PROFILE_FILE" 2>/dev/null || true
  chown www-data:www-data "$PROFILE_FILE" 2>/dev/null || true
fi

if [ "$JSON" = 1 ]; then
  printf '{"ok":true,"profile":"%s","ram_mb":%d,"pool_mb":%d,"key_buffer_mb":%d,"aria_mb":%d,"tmp_mb":%d,"max_connections":%d,"innodb_data_mb":%d,"worst_case_mb":%d,"changed":%s,"restarted":%s,"reason":"%s"}\n' \
    "$PROFILE" "$RAM_MB" "$POOL_MB" "$KEY_MB" "$ARIA_MB_CACHE" "$TMP_MB" "$MAXCONN" "$INNODB_MB" "$WORST_MB" \
    "$([ "$CHANGED" = 1 ] && echo true || echo false)" \
    "$([ "$RESTARTED" = 1 ] && echo true || echo false)" "$POOL_WHY"
else
  echo "OK: $SUMMARY"
  [ "$CHANGED" = 1 ] && echo "     wrote $CNF" || echo "     $CNF already correct"
  if [ "$RESTARTED" = 1 ]; then echo "     mariadb restarted — new sizes are live"
  elif [ "$NEED_RESTART" = 1 ]; then echo "     ⚠ restart mariadb to apply (the pool cannot be resized live)"
  fi
fi
