The page came at 3 a.m.: Can't create more than max_prepared_stmt_count statements. The application team was adamant they ran about forty distinct queries, which was true, and completely beside the point. Four hundred pooled connections each holding forty server-side prepared statements is sixteen thousand statement handles, and the default cap is 16382. We were one small deploy away from the ceiling and nobody had budgeted anything, because prepared statements on MySQL are per-connection state governed by a global limit, and that combination is invisible until it errors.
Why are server-side prepared statements per-connection state?
When a client prepares a statement over the binary protocol, the server parses and plans it and returns a statement handle that lives on exactly that connection. No other connection can execute it, share it, or see it, and when the connection closes the statement dies with it. There is no server-wide statement cache to warm. That is the fundamental shape of the feature: prepare once per connection, execute many times on that connection. Every sizing consequence follows from it — the number of prepared statements on your server is distinct statements multiplied by live connections, and pools multiply rather than share. If your mental model came from PostgreSQL's named statements or Oracle's shared cursors, If your mental model came from PostgreSQL's named statements or Oracle's shared cursors, recalibrate before it pages you.
The lifecycle is worth stating precisely, because it is where the leaks live. A statement handle is released in exactly two ways: an explicit DEALLOCATE from the client, or the death of the connection. Nothing ages out, nothing is LRU-evicted, and the server never reclaims an idle statement on a living connection. An application that prepares and forgets therefore accumulates handles for as long as its pool keeps connections open — which, with a well-behaved pool, is effectively forever.
What does max_prepared_stmt_count exhaustion look like?
Error 1461, with Prepared_stmt_count pinned at the cap. The default maximum is 16382, which sounds generous until you do the per-connection multiplication above. Check both numbers before touching anything:
SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count';
SHOW GLOBAL VARIABLES LIKE 'max_prepared_stmt_count';Raising the cap is a valid tourniquet and a bad fix. Every prepared statement holds server memory — parse structures, plan state, per-execution scratch — so sixteen thousand idle statements are sixteen thousand small memory leases. If the count climbs monotonically between deploys, you do not have a capacity problem, you have a leak, and the limit just schedules when the leak finishes eating the server. I have raised the cap twice in production. Both times it bought exactly the days needed to find the real leaker, and in the case where we fixed nothing, the count was back at the new ceiling within the week.
How do you find the leaker in performance_schema?
The prepared_statements_instances table lists every live server-side prepared statement: its SQL text, the thread that owns it, how often it has executed, and how often it has been reprepared. Aggregate by statement shape and join back to threads to see who is holding what:
SELECT t.processlist_user AS user,
t.processlist_host AS host,
LEFT(p.sql_text, 60) AS statement,
COUNT(*) AS copies,
SUM(p.count_execute) AS executions
FROM performance_schema.prepared_statements_instances p
JOIN performance_schema.threads t
ON t.thread_id = p.owner_thread_id
GROUP BY t.processlist_user, t.processlist_host, LEFT(p.sql_text, 60)
ORDER BY copies DESC
LIMIT 20;The signature you are hunting is copies high and executions low — statements prepared constantly and executed once or twice. The classic producer is an ORM or driver configured to prepare every query but never DEALLOCATE: I have seen it from a Python driver creating a prepared cursor per statement inside a loop, and from PDO with emulated prepares disabled on persistent connections, where every request re-prepares and nothing lets go. The count_reprepare column is a secondary tell, since statements reprepare after DDL churn, but copies is the leak. The fix is almost always in the client: enable the driver's statement cache, or stop preparing one-shot queries.
When does the binary protocol actually pay?
Arithmetic, not faith. A prepared execution costs a COM_STMT_PREPARE round trip plus a COM_STMT_EXECUTE round trip; a plain text query costs one round trip with the parse included. Preparing therefore loses money unless the statement executes many times per prepare on the same connection, amortizing the parse and unlocking the genuine win: binary result transfer, which skips text encoding on the server and text parsing on the client, and is worth real CPU on wide rows. For run-once queries — most of what a typical web request issues — server-side prepare is pure tax. My position: turn on the driver's prepared-statement cache (cachePrepStmts in JDBC and its equivalents elsewhere) or use plain queries, and treat per-query prepare as the worst of both protocols.
The numbers make the trade concrete. On a healthy datacenter network with 0.3 millisecond round trips, preparing a run-once query adds an extra round trip of latency — call it 0.3 milliseconds — to a statement that might itself take 0.8. Executed once, that is a latency tax north of 30 percent for zero benefit. Executed five thousand times per connection, the prepare cost rounds to nothing and the binary transfer starts paying you back on every wide result set. The feature is neither good nor bad in the abstract; the executes-per-prepare ratio decides, and that ratio is a client-side design choice you can measure before you ship.
How do connection pools change the math?
Pools multiply statement counts and invalidate caches on their own schedule. Statements die with their connection, so a pool that recycles connections on a maxLifetime throws away every prepared statement on that connection and pays the prepare cost again on the fresh one — a slow churn of prepare traffic that looks, from the server side, like a mild leak. Aggressive recycling combined with a big driver-side cache is a particularly silly arrangement: you pay to warm caches you are about to destroy. Pool sizing interacts with the cap directly, so the arithmetic in connection pool sizing should include prepared statements, not just threads. And when SQL-level PREPARE, EXECUTE, and DEALLOCATE PREPARE show up in your digests — the text-protocol version of the feature — that is a smell: it counts against the same cap, almost never caches usefully, and usually means an ORM is doing something nobody reviewed.
What should you watch day to day?
One ratio covers most of the risk: Prepared_stmt_count over max_prepared_stmt_count, trended, with an alert around seventy percent. Steady growth between deploys means a leak; a sudden step means a pool or driver config change; the per-statement aggregation above tells you which statement to go fix. If performance_schema is not already enabled with sane consumers, the low-overhead setup gets you there without the instrumentation bill people fear, and prepared_statements_instances is cheap insurance compared to a 3 a.m. error storm.
Where MonPG stands on MySQL
I build MonPG, so the honest note: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The prepared-statement ratio, per-statement copies, and pool-driven prepare churn are precisely the class of signal the MySQL work is meant to trend — slow-moving resource exhaustion that only ever pages you at the worst moment. The MySQL monitoring (coming soon) page is where that lands as it ships. Until then, the same evidence-first monitoring runs on the PostgreSQL side, and more MySQL field notes live on the blog.