MySQL Connection Capacity

1. Overview

One MySQL InnoDB Cluster in the mysql namespace serves the whole estate. Production, stage, development, the WordPress sites and the reporting integration all connect to the same Group Replication cluster through the same MySQL Router.

That makes max_connections a single shared budget, not a per-environment one. A development service that holds forty idle pool connections is spending production’s capacity. When the budget runs out, the server refuses every new connection regardless of which environment asked for it.

Connection details and the tunnel used to reach the cluster are covered by the mysql-idealogic-prod skill at ~/dev/ai-skills-infra/skills/mysql-idealogic-prod/SKILL.md; that procedure is not repeated here.

2. Failure signature

When the budget is exhausted the server answers new connections with:

ERROR 1040 (HY000): Too many connections

The visible casualty is almost never the service that caused it.

Workload shape Behaviour at exhaustion

WordPress (connection per request)

Fails immediately and completely — every page returns Error establishing a database connection with HTTP 500. WordPress opens a fresh connection on every request, so it is the first thing to break and the canary for this failure mode.

Spring services (pooled)

Keep working. Their pool connections were established before the limit was reached and are never released, so the application appears healthy while nothing new can connect.

Two properties make this worse than it looks:

  • Kubernetes does not notice. A WordPress pod probed with tcpSocket on its HTTP port stays Ready throughout — the web server is alive, only the database is unreachable. The pod is never restarted and never removed from its Service.

  • The service that fails is not the service at fault. The workload holding the most connections is the one still running.

3. How pool sizing adds up against the server limit

At exhaustion the connection list is dominated by idle pool connections, not by active queries. A HikariCP pool with a large minimum-idle holds its connections open indefinitely whether or not the service is serving traffic.

The arithmetic is unforgiving on a server left at MySQL’s compiled default of 151. A production account holding 60, a development account holding 40 and a stage account holding 40 consumes 140 of them before a single real query is counted — and the two non-production accounts account for more than half of that.

Three levers control this, and all three are needed:

  1. Right-size the pools. maximum-pool-size, minimum-idle, idle-timeout and max-lifetime in each service’s configuration.

  2. Cap each account. max_user_connections per database user, so no one tenant can starve the others.

  3. Raise the server limit to a value that accommodates the sum of the caps plus reserve. This is the band-aid, not the fix — apply it alongside the other two, never instead of them.

3.1. Where the pool settings live

Pool sizing is a runtime posture concern, so it belongs in the Spring profile files, not in the deployment manifest — see SpringApplication Bootstrap for the posture-versus-environment rule and its consequences.

File Scope

application-kubernetes.yml

Applies to every deployed environment. Carries the settings that should hold everywhere: a small minimum-idle, a short idle-timeout, a keepalive interval, and a max-lifetime comfortably below any network or proxy idle timeout.

application-dev.yml

The development posture only. Carries the reduced maximum-pool-size.

Stage runs the prod profile, so there is no stage-only lever. The Helm charts do not template the HikariCP keys and offer no passthrough that can reach them, so a manifest override cannot supply them either. Stage and production inherit pool changes only on their next release.

Only services that own a datasource matter here. Portal and MCP services with no database, the shared JPA libraries, and local command-line tools hold no pool.

4. The trap: the operator does not apply a changed server configuration

The InnoDBCluster custom resource has a spec.mycnf field, and it is natural to assume that raising max_connections there raises it on the server. It does not.

The operator renders spec.mycnf into the cluster’s <cluster>-initconf ConfigMap — the 99-extra.cnf fragment — only at initial cluster provisioning. spec.mycnf is not in the operator’s watched-field list, so there is no handler, no reconcile and no restart. Editing it on a running cluster is inert.

The result is a cluster that reads as configured in Git while running on compiled defaults, with nothing anywhere reporting the discrepancy. Confirm what the ConfigMap actually holds rather than what the CR declares:

kubectl -n mysql get cm <cluster>-initconf -o jsonpath='{.data.99-extra\.cnf}'

Values recorded in spec.mycnf are still worth keeping current — they are what a rebuilt or disaster-recovered cluster comes up with — but they are documentation of intent on a live cluster, not configuration of it.

5. The fix: persisted server variables plus per-account caps

5.1. Server limit

Set the limit with SET PERSIST, which writes mysqld-auto.cnf and therefore survives a restart and supersedes the stale my.cnf. This is the same mechanism the operator itself uses to persist instance variables.

SET PERSIST max_connections = 400;
SET PERSIST max_user_connections = 20;   -- global backstop

SET GLOBAL alone is runtime-only and silently reverts to the compiled default on the next pod restart.

SET PERSIST is per-instance and does not replicate. Run it on every member of the cluster. Applying it only to the primary leaves the secondaries on compiled defaults, and the next failover — or the next rolling restart, which promotes a secondary — puts the whole estate behind a 151-connection primary.

This includes newly added members. A member added by a scale-up joins on the compiled defaults max_connections=151 and max_user_connections=0, because neither the persisted variables nor the frozen 99-extra.cnf carry them. Applying the persisted variables to the new member is part of the scale-up step, not a follow-up.

5.2. Per-account caps

Per-account caps are what actually prevent one tenant starving the others, and they would prevent the WordPress outage even on an unraised server limit:

ALTER USER '<account>'@'%' WITH MAX_USER_CONNECTIONS <n>;

The caps in force size each account to its real need and sum to roughly three-quarters of the server limit, leaving headroom:

Account Cap Rationale

event-membership-prod

80

Production Spring services; the largest legitimate consumer.

event-membership-stage

50

Stage services on the production posture.

dev

50

Development services.

tdw

40

WordPress — bounded so a traffic spike cannot monopolise the server.

wpca_prod

40

WordPress.

jasper-reports

20

Reporting integration.

event-site-stage

20

Stage WordPress.

(global backstop)

20

max_user_connections — the default for any account without an explicit cap.

ALTER USER … WITH MAX_USER_CONNECTIONS is DDL, so it does replicate to every member over GTID — run it once. The global max_user_connections backstop set with SET PERSIST does not replicate — run that on every member. The two look alike and behave differently.

The per-account caps live only in mysql.user. They are not in Git, and a cluster rebuilt from the custom resource comes up without them. Re-applying them is part of any rebuild or disaster-recovery procedure.

6. Verification

6.1. Are the values actually persisted?

This is the check that matters, and it is not the obvious one.

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME IN ('max_connections', 'max_user_connections');

Do not use performance_schema.variables_info.VARIABLE_SOURCE for this. It describes how the running value was set, so it reads DYNAMIC after a successful SET PERSIST and only flips to PERSISTED once a restart has reloaded the file. A cluster can therefore report exactly what a correctly-persisted cluster reports while being one pod restart away from reverting to the compiled default.

persisted_variables is file-backed and is the authoritative answer. Never record a SET PERSIST as applied without reading it back from that table.

If reading mysqld-auto.cnf directly, note that max_connections is written into a third section — mysql_dynamic_parse_early_variables — not mysql_dynamic_variables. A check that inspects only the static and dynamic sections reports a false negative.

6.2. Running values, on every member

Run on each pod, not just the primary. Authentication inside the mysql container is by unix socket as localroot, so no secret lookup is needed; the cluster administrator credentials in the idealogic-prod-privsecrets Secret are an alternative route.

for p in $(kubectl -n mysql get pods -l component=mysqld -o name); do
  echo "== $p"
  kubectl -n mysql exec "${p#pod/}" -c mysql -- mysql -u localroot -N -B -e \
    "SELECT @@hostname, @@max_connections, @@max_user_connections;
     SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.persisted_variables;"
done

6.3. Who is actually holding the connections?

-- connections per account, highest first
SELECT USER, SUBSTRING_INDEX(HOST, ':', 1) AS client, COUNT(*) AS conns
FROM information_schema.PROCESSLIST
GROUP BY USER, client
ORDER BY conns DESC;

-- how much of the budget is in use
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

-- connections the server has refused, by reason
SHOW GLOBAL STATUS LIKE 'Connection_errors_%';

-- the caps currently in force
SELECT User, Host, max_user_connections FROM mysql.user ORDER BY max_user_connections DESC;

A Threads_connected figure dominated by rows in Sleep state is pool idle, not load.

7. Monitoring

The highest-value signal is MySQL’s own count of connections it refused:

increase(mysql_global_status_connection_errors_total{error="max_connections"}[5m]) > 0

This is direct evidence of the failure rather than a utilisation proxy, and it fires only when capacity has actually been exceeded. A companion rule catches the silent regression — a member that came up on the compiled default:

mysql_global_status_max_connections < 400

Both require the metrics exporter to be running and correctly pinned; see MySQL Operator & Server Upgrade § Metrics export. Connection utilisation against max_connections is also charted on the mysql-innodb-cluster dashboard. For how to reach the dashboards and alert state, see Observability Access.

8. Longer-term direction

Raising the limit and capping accounts contains the blast radius; it does not remove the shared fate. The structural fix is to stop pointing non-production services at the production cluster at all, so that development and stage pool sizing cannot reach production capacity under any misconfiguration.

9. Related Documentation

  • MySQL Operator & Server Upgrade — the roll that exposes an unpersisted variable, and the scale-up step that adds a member on defaults

  • SpringApplication Bootstrap — why pool sizing is a profile concern and what a Helm chart can and cannot override

  • Observability Access — reading the alerts and dashboards referenced above

  • ~/dev/ai-skills-infra/skills/mysql-idealogic-prod/SKILL.md — connecting to the cluster

  • ~/dev/ai-skills-infra/skills/mysql-operator-backup-restore/SKILL.md — backup and restore