# ---------------------------------------------------------------------------
# MySQL 8.0 configuration for the PMS production stack.
#
# Mounted at /etc/mysql/conf.d/pms.cnf - the mysql:8.0 image had NO custom
# config at all before this, so it ran on defaults: a 128M buffer pool and
# innodb_io_capacity=200 (a spinning-disk assumption) on NVMe.
#
# Sized for a 4 vCPU / 8 GB host that ALSO runs PHP-FPM, two nginx containers
# and the frontend. The common "buffer pool = 70% of RAM" advice assumes a
# dedicated database server and would OOM this box.
#
# Binary logging is deliberately left untouched (see the note at the bottom).
# ---------------------------------------------------------------------------

[mysqld]

# --- Buffer pool -----------------------------------------------------------
# The database is currently ~50 MB. 1536M is roughly 30x the present working
# set and comfortably covers a 12-month growth projection driven by
# activity_logs and job_posts. That is 19% of RAM, which is correct for a
# SHARED box - PHP needs the memory more than InnoDB does here.
innodb_buffer_pool_size = 1536M

# NOT the default. MySQL 8.0 auto-selects 8 instances once the pool is >= 1 GB,
# which would split 1536M into 8 x 192M partitions where a hot page can only
# live in the partition its space-id hashes to. With 4 vCPU and at most ~29
# real connections there is no mutex contention to relieve, so partitioning
# only fragments the cache.
# Note 1536 / 128 chunk / 1 instance = 12 (an integer) - required, or MySQL
# silently rounds the pool size up.
innodb_buffer_pool_instances  = 1
innodb_buffer_pool_chunk_size = 128M
innodb_change_buffer_max_size = 10

innodb_log_buffer_size = 32M

# --- Durability -------------------------------------------------------------
# Kept at 1 (full ACID) on purpose. Setting 2 is the usual "speed" tweak and
# would help the cache/session write load, but it accepts up to one second of
# committed transactions being lost on host power failure. For a proposal
# system that is the wrong trade, and this box is nowhere near I/O bound.
# The right way to cut write traffic is to move cache/sessions off MySQL.
innodb_flush_log_at_trx_commit = 1

# --- Disk I/O ---------------------------------------------------------------
# The data directory is a bind mount, so I/O passes straight through to the
# host filesystem with no overlayfs in the path. Without O_DIRECT every data
# page is buffered twice (InnoDB buffer pool + Linux page cache), which is
# exactly wrong when the pool is deliberately kept small.
#
# !! If mysqld fails to start after this change, THIS is the line to remove.
#    O_DIRECT requires filesystem support - fine on xfs/ext4 (AlmaLinux
#    defaults to xfs), but verify with `df -T` if startup fails.
innodb_flush_method = O_DIRECT

# NVMe: the defaults (200 / 2000) describe a 7200 RPM disk.
innodb_io_capacity     = 2000
innodb_io_capacity_max = 4000
# Pointless on flash - there is no rotational latency to amortise.
innodb_flush_neighbors = 0

# --- Connections ------------------------------------------------------------
# DERIVATION: 18 PHP-FPM workers + 2 future queue workers + 3 scheduler/artisan
# + 1 entrypoint migration + 5 admin/backup = 29 actually required. Doubled for
# connection-close lag and backup overlap = 58, rounded to 64.
#
# The default of 151 is not merely wasteful (per-connection buffers can reach
# ~4 MB each); it removes the backstop. With pm.max_children = 18 you can never
# legitimately need 151, so the excess only lets a runaway artisan loop exhaust
# the box.
#
# !! NEVER set this below pm.max_children + queue workers + admin headroom,
#    or the application will start returning "Too many connections" 500s.
max_connections   = 64
thread_cache_size = 32

# --- Table caches -----------------------------------------------------------
# 38 tables across the 29 migrations. Formula: max_connections x tables_per
# query = 64 x 6 = 384 -> 400. The default of 4000 is sized for a schema about
# 100x this one.
table_open_cache           = 400
table_open_cache_instances = 4
table_definition_cache     = 400

# --- Temporary tables -------------------------------------------------------
# This is the biggest hidden memory risk on the box, and it is NOT
# tmp_table_size. MySQL 8.0 uses the TempTable engine by default, governed by
# temptable_max_ram, whose default is 1 GB - meaning MySQL can grow a full
# gigabyte BEYOND the buffer pool for internal temp tables. It is a global
# pool, not per-connection, so 128M is generous for this workload; beyond it
# MySQL spills to mmap and then to disk.
temptable_max_ram = 128M

# These two only apply to the older MEMORY-engine path. They must match each
# other, since MySQL uses the lesser of the two.
tmp_table_size      = 32M
max_heap_table_size = 32M

# --- Slow query log ---------------------------------------------------------
# 500 ms is already a poor experience for an internal tool, and at this traffic
# level the log volume is trivial. Lands on the host bind mount, so include it
# in whatever log rotation you set up.
slow_query_log            = ON
slow_query_log_file       = /var/lib/mysql/slow.log
long_query_time           = 0.5
min_examined_row_limit    = 100
log_slow_admin_statements = ON

# Enable for the FIRST TWO WEEKS ONLY to surface unindexed tables, then comment
# out again - left on permanently it becomes pure noise.
#log_queries_not_using_indexes          = ON
#log_throttle_queries_not_using_indexes = 60

# --- Character set ----------------------------------------------------------
# config/database.php requests utf8mb4 / utf8mb4_unicode_ci on the connection,
# but MySQL 8.0's server default is utf8mb4_0900_ai_ci. Aligning the server
# stops future or manually-created tables drifting into a collation that forces
# conversion during joins.
character_set_server = utf8mb4
collation_server     = utf8mb4_unicode_ci

# --- Misc -------------------------------------------------------------------
# Disables the X Protocol listener on 33060 - saves ~15-30 MB and one port for
# a feature nothing in this stack uses.
mysqlx       = 0
local_infile = OFF

# ---------------------------------------------------------------------------
# DELIBERATELY NOT SET
#
# 1. Binary logging. log_bin is ON by default with sync_binlog=1, so every
#    commit currently costs two fsyncs. Disabling it (skip-log-bin) would be a
#    real write-path win but permanently destroys point-in-time recovery, and
#    binlog_expire_logs_seconds defaults to 30 days so the files also grow
#    unbounded. Left untouched by explicit decision - revisit separately.
#
# 2. Redo log sizing. MySQL >= 8.0.30 uses innodb_redo_log_capacity; older
#    releases use innodb_log_file_size, which requires a verifiably clean
#    shutdown before the size can change or InnoDB refuses to start. The exact
#    server version has not been confirmed on the production host, so no redo
#    setting is applied here. Run `SELECT VERSION();` first.
#
# 3. innodb_dedicated_server. Must stay OFF. It would auto-size the buffer pool
#    to 75% of 8 GB = 6 GB and immediately OOM everything else on the box.
# ---------------------------------------------------------------------------