Skip to content

PLANETSCALE 2023-03-27

Read original ↗

PlanetScale — Connection pooling in Vitess

Summary

Harshit Gangal (Vitess core maintainer, PlanetScale, 2023-03-27) walks through how connection pooling is implemented inside Vitess's vttablet and the architectural problem that forced it to evolve: the tension between centralised connection pooling and MySQL's per-session system-variable semantics. The post reads as a mini-history of the subsystem — three distinct design eras answering the same underlying question: how do you keep pool benefits when callers modify connection state?

Era 1 (pre-v7): ignore session settings. Vitess centralised connections in vttablet behind a lockless pool using atomic operations and non-blocking data structures, and simply dropped client SET statements that would modify connection state. Safe for the pool, but incompatible with ORMs that assume standard MySQL session semantics.

Era 2 (v7.0): reserved connections. To support ORMs when Vitess started speaking the MySQL wire protocol, Vitess honoured SET statements by taking the connection out of the pool for the duration of the session — a reserved connection. Safe for correctness but catastrophic for scaling: reserved connections grow without bound, MySQL runs out of its 16,000-connection ceiling, database becomes unavailable. Two stopgaps: (a) pre-check with select 0 from dual where @@var != value to avoid reserving a connection when the SET is a no-op; (b) rewrite subsequent queries in the session to use MySQL 8.0's SET_VAR optimizer hint — insert /*+ SET_VAR(unique_checks=0) */ into user ... — applying the setting per query without tainting the connection. Not all system variables are SET_VAR-eligible, so reserved connections remain the fallback.

Era 3 (v15.0): the settings pool. Vitess introduced a new pool that tracks and manages connections that have had system settings modified, matching each incoming query to a connection with the right settings applied (or applying them on demand). Transparent to the application — "Vitess can retrieve the correct connection from the connection pool, with or without settings applied, based on the settings specified by that application on that session." Feature is behind the queryserver-enable-settings-pool vttablet flag. PlanetScale reports "improvements in query latency and load on Vttablet for customers who previously relied on reserved connections due to their application ORMs" with two attached graphs showing query-latency + load-average drops after deploying the settings pool.

The post is architecturally substantive: three named design eras, two named mechanisms (SET_VAR rewrite + settings pool), one named primitive (lockless atomic pool), one production outcome (customer latency + load drop). It is the Vitess-internals-altitude disclosure of the connection-pooling primitive that Jarod Reyes named but did not specify in the 2021 Comparing AWS's RDS and PlanetScale post — the mechanism underwriting the "nearly limitless database connections per database" claim + the 1M-concurrent-connections benchmark.

Key takeaways

  • Connection pooling's ~50 ms per-connection MySQL SSL handshake is the direct cost application-level pooling eliminates. "This initial handshake phase can add up to 50ms of overhead. However, by implementing connection pooling, applications can significantly reduce their response time per request by saving the 50ms overhead." Canonical wiki datum for why connection pooling is an OLTP latency-floor primitive, not just a throughput optimisation.

  • Application-level pooling is insufficient beyond one server. Once an application horizontally scales from one server to "hundreds or thousands," and once multiple applications share the same database, application-level pools can't enforce a global connection ceiling — each pool independently caps at its own value, and the aggregate can overwhelm MySQL. Vitess's answer is a centralised proxy tier (vttablet) that caller applications connect to directly; the proxy owns the upstream MySQL connections. The cap is enforced once, at vttablet, not N times at N applications.

  • YouTube (2010) is the origin story of the Vitess centralised pool. "In 2010, YouTube encountered similar challenges, leading to the development of Vitess and its first component, Vttablet. Vttablet acted as a MySQL proxy and was primarily responsible for managing the connection pool." Canonical wiki framing for Vitess's first component being the pool, not the sharding.

  • The Vitess pool is lockless, using atomic operations + non-blocking data structures. "To handle concurrent requests at scale, the connection pool implementation in Vttablet was designed to be lockless, using atomic operations and non-blocking data structures with lock-free algorithms. This approach enables Vitess to efficiently manage large numbers of concurrent requests, further improving its scalability and performance." First wiki disclosure of the lockless-pool primitive; the implementation detail behind the "nearly limitless connections" scaling claim.

  • MySQL session-level system settings are per-connection state that "taints" a pooled connection. "when using connection pooling, all connections in the pool share the same settings. Any modifications made to a connection will render it unusable for other requests, as it becomes 'tainted'." Canonical wiki framing of the tainted-connection problem — if a caller mutates the connection's session state, returning it to the pool would leak the mutation to unrelated subsequent callers. The pool's three options: (a) restore the setting on return (expensive, error-prone, not all settings can be cleanly restored); (b) close the connection (defeats the pool); (c) don't return it to the pool at all — the Vitess Era-2 answer, at the cost of unbounded connection growth.

  • Reserved connections break the pool's scaling invariant and can exhaust MySQL. "the connection is taken out of the pool and cannot be returned, this turns off the connection pooling benefits for that session. As these kinds of reserved connections are no longer part of the connection pool, they can grow without any upper limit on their numbers, eventually leading to MySQL running out of connections and making the database unavailable for application use." Canonical wiki production-failure-mode framing: reserved connections are a pool-bypass mechanism and every bypassed connection subtracts from the fixed MySQL max_connections budget. Multi-ORM workloads with different default settings per ORM make MySQL-default alignment ineffective.

  • Pre-v15 mitigations: the no-op elision + SET_VAR rewrite. Pre-checking select 0 from dual where @@unique_checks != 0 avoids reserving a connection when the SET would not change anything. SET_VAR rewriting transforms every subsequent query in the session to carry the setting as a query-scoped optimizer hint — insert /*+ SET_VAR(unique_checks=0) */ into user (id, name) values (1, 'foo') — applying the setting per query rather than per connection, avoiding the taint entirely. Two complementary optimisations: (a) elision reduces the number of sessions that need reserved connections at all; (b) SET_VAR rewriting eliminates the reserved connection for the subset of settings that are SET_VAR-eligible. Both are necessary but not sufficient — many session variables are not SET_VAR-eligible and still force reservation.

  • The Vitess 15 settings pool is the structural fix. "Vitess now tracks and manages connections in which system settings have been modified. This process is transparent to the application but provides all the advantages of connection pooling while still allowing per-connection settings. When an application submits a query to Vitess for execution, Vitess can retrieve the correct connection from the connection pool, with or without settings applied, based on the settings specified by that application on that session, and then execute the query." Canonical wiki framing of a settings-aware connection pool: connections are not uniform — they carry settings tags — and the matching logic selects or mutates a connection to satisfy the caller's settings profile, returning it to the pool afterwards. The pool is still shared; it is just indexed by settings rather than treating every connection as fungible. Feature is behind queryserver-enable-settings-pool in vttablet — gated rollout rather than default-on.

  • Production outcome: reduced query latency + reduced vttablet load for customers previously on reserved connections. "At PlanetScale, we have started to roll out this feature and are already seeing improvements in query latency and load on Vttablet for customers who previously relied on reserved connections due to their application ORMs." Two graphs attached (query latency drop + load-average drop). No quantified deltas in the post text — the graphs carry the numbers. Canonical wiki framing: the settings pool moved a class of ORM-driven workloads from the reserved-connection path (pool-bypass) back into the pooled path (pool benefits preserved), with observable latency + load wins at vttablet.

Systems / concepts / patterns surfaced

  • New systems: (none — all named systems have canonical pages: systems/vitess, systems/mysql, systems/planetscale).
  • New concepts (4):
  • concepts/tainted-connection — a pooled connection mutated by a caller such that returning it to the pool would leak state to unrelated subsequent callers. The general problem every session-stateful connection pool must solve.
  • concepts/reserved-connection — the Vitess-era-2 answer: pull the tainted connection out of the pool for the session's duration, accept it won't be reused by anyone else until the session ends. Safe but unbounded.
  • concepts/session-level-system-setting — MySQL's per-connection variable surface (SET @@session.foo = bar / SET foo = bar), the specific taint source in Vitess's production workloads.
  • concepts/set-var-hint — MySQL 8.0's /*+ SET_VAR(var=value) */ optimizer hint that applies a session variable for the duration of a single query without modifying connection state. The per-query-rather-than-per-connection escape hatch that the Vitess proxy uses to avoid reservation where possible.
  • New patterns (2):
  • patterns/connection-multiplexing-proxy — generalisation of the Vitess vttablet architectural move: put a proxy tier between applications and the database that owns the upstream connections, lets the application see no connection state, and enforces the upstream connection cap once at the proxy rather than N times at N applications. The proxy is the choke point that converts O(total application workers) connection pressure into O(unique in-flight queries) connection pressure.
  • patterns/settings-aware-connection-pool — the Vitess-v15 shape: connection pool where entries are indexed by their settings profile. Matching logic selects a connection whose settings already match the caller's session settings (fast path) or applies the settings on demand (slow path, but one-time per unique settings profile). Preserves pool benefits through session-level setting mutations.
  • Extended:
  • systems/vitess — first canonical wiki disclosure of the lockless atomic connection pool (the Vitess first component) + the three-era connection-pooling-design narrative + v15 settings pool behind the queryserver-enable-settings-pool flag.
  • systems/mysql — first canonical wiki citation of the ~50 ms MySQL SSL handshake latency floor as the why of application-level connection pooling + SET_VAR hint (MySQL 8.0) as a per-query setting escape hatch.
  • systems/planetscale — canonical wiki attribution of PlanetScale's rollout of the Vitess settings pool and its production outcome (query-latency + load reduction for ORM-driven workloads on the reserved-connection path).
  • concepts/connection-pool-exhaustion — canonical Vitess-internals-altitude disclosure of the pool-design mechanism underwriting the "nearly limitless connections" claim from the 2021 Reyes post; fills in the mechanism (lockless pool + settings-aware pool) for what the Reyes post only named.
  • concepts/query-consolidation — same "the proxy tier owns the upstream connections" structural substrate as consolidation, different primitive (consolidation collapses simultaneous identical reads; connection pooling caps concurrent upstream connection count regardless of query distinctness). Sister proxy-tier primitives.

Operational numbers

  • ~50 ms MySQL SSL connection handshake cost per new connection. Eliminated by application-level pooling for the hot path.
  • Vitess v7.0 (2020) — the release that started honouring SET statements via reserved connections. Prior versions ignored SET for pool safety.
  • Vitess v15.0 (2022) — the release that introduced the settings pool. Gated behind queryserver-enable-settings-pool flag.
  • queryserver-enable-settings-pool — the vttablet flag to enable the v15 settings pool feature. Off by default at the time of writing.
  • No per-customer QPS or latency deltas disclosed in prose; two graphs (query-latency drop + load-average drop on vttablet after settings-pool rollout) carry the numbers but aren't transcribed.
  • "3 million people go to your YouTube video at once" — the canonical Vitess-origin-story scaling pressure (Reyes 2021-09-30 framing) underlies the 2010-at-YouTube context this post names.

Caveats

  • Mini-history voice, not performance engineering deep-dive. The post walks through three design eras with the logic of each, but doesn't go into the lockless-pool data structure (what queue / stack / ring-buffer? what atomic operations? lock-free algorithm citations?), doesn't quantify the pool's own scaling ceiling, doesn't disclose the lookup algorithm in the settings pool (hash-table keyed by settings profile? linear scan? LRU eviction?), doesn't specify the pool-size tuning (per-vttablet? per-tablet? per-keyspace?), doesn't address the settings-pool cache-hit rate in production. The graphs are presented without numerical annotations.
  • No failure-mode retrospective. The post describes mechanisms and their outcomes but not what happened when things went wrong. No incident walkthrough, no debugging narrative, no "we tried X and it broke" moment.
  • SET_VAR eligibility not enumerated. The post says "not all settings can be used with SET_VAR" and links to MySQL's server system variables documentation, but doesn't list the practical eligible subset for typical ORM workloads. The reader can't estimate what fraction of SET statements in their workload the rewrite mitigation would cover.
  • The 2010 YouTube origin story is lightly sourced. The post links to Vitess's own history page and the vttablet documentation, not to a contemporaneous engineering post from 2010. Treat as authorised Vitess-team framing of its own history, not independent archival evidence.
  • Reserved connections' scaling ceiling is asserted but not measured. "eventually leading to MySQL running out of connections and making the database unavailable" is a structural argument, not a documented incident. The specific trigger (threshold, traffic mix, ORM population) that would cause the exhaustion is unspecified.
  • Settings pool rollout-gate pressure not disclosed. "At PlanetScale, we have started to roll out this feature" is a 2023-03-27 snapshot — presumably later fully rolled out, but the post doesn't estimate what fraction of the PlanetScale fleet had the flag on at publication, nor what the rollout gating criteria were.
  • Vendor retrospective. Authored by Harshit Gangal in his Vitess-core-maintainer / PlanetScale voice; the post has a natural interest in framing the Vitess design as the canonical solution to the connection-pooling + ORM-compatibility problem. The architectural arguments are load-bearing and not oversold, but the "nearly limitless connections" framing pairs with the linked 1M-connections benchmark marketing post rather than with a reproducible customer-scale benchmark.
  • No cross-engine comparison. PgBouncer, ProxySQL, Aurora's proxy, RDS Proxy, HAProxy + MySQL — all perform overlapping connection-multiplexing functions with different designs. The post doesn't compare; the Vitess settings pool may or may not have analogues in those systems.
  • No mention of query consolidation. The Vitess consolidator (canonicalised on the wiki via the 2021 Reyes post) is the identical-query-dedup primitive; this post covers the connection-count-cap primitive. Both live in vttablet, both reduce upstream MySQL pressure, but the post doesn't draw the line between them — the connection-pooling disclosure is complementary to, not integrated with, the consolidator disclosure.

Source

Last updated · 347 distilled / 1,201 read