PATTERN Cited by 1 source

Two-connection profiling setup¶

Two-connection profiling setup is the pattern of using one MySQL connection to run the workload under test and a separate connection to observe it. Required for any foreground-blocking diagnostic, because the workload connection is busy running the workload and can't execute observation queries at the same time.

(Source: sources/2026-04-21-planetscale-profiling-memory-usage-in-mysql.)

Why two connections¶

MySQL's client protocol is synchronous per connection — one in-flight statement at a time. While SELECT alias FROM chat.message ORDER BY alias DESC LIMIT 100000 is executing on connection A, connection A cannot execute a second statement. Observation must come from connection B.

This is not unique to memory profiling — it applies to any live observation technique where the subject is a running foreground statement:

Per-thread memory sampling (Dicken's canonical case)
Stage-level query profiling via events_stages_history_long
Lock-wait inspection via performance_schema.data_lock_waits
Plan inspection via EXPLAIN FOR CONNECTION <id>
KILL QUERY <connection_id> to cancel a runaway statement

Canonical setup¶

Verbatim from the Dicken post:

At this point, you should set up two separate connections to your MySQL server on the command line. The first is the one that will execute the query you want to monitor memory usage for. The second will be used for monitoring purposes.

Operational shape:

Connection A (subject) — mysql -h ... -u ...
On A: capture its identity for observation.

SET @cid = (SELECT CONNECTION_ID());
SET @tid = (SELECT thread_id
              FROM performance_schema.threads
              WHERE PROCESSLIST_ID = @cid);
SELECT @cid, @tid;

See concepts/mysql-connection-id-vs-thread-id. 3. Connection B (observer) — second terminal / second mysql client. 4. On B: run observation queries scoped to A's thread_id or connection_id. 5. On A: execute the target query. 6. On B: repeatedly poll for the duration of A's workload.

Generalisation¶

Postgres: the same pattern applies — pg_stat_activity is observed from a second session, the first runs the workload.
Oracle / SQL Server: same — sessions are synchronous, observation tables are cross-session.
Redis: MONITOR from a second connection observes the first.
Kafka: consumer-group lag is observed from a separate admin client, not from the consumer itself.

When one connection is sufficient¶

Read-your-own-counters after the fact — if the workload completes + you only want the final state, one connection works: run the workload, then read memory_summary_by_thread_*. But you lose the time-series, and you're bounded by what the counter retains (often nothing, since many counters are live snapshots).
Async clients — some drivers support multiple concurrent statements per connection via pipelining. Not standard on MySQL's wire protocol.

Caveats¶

Observer overhead counts — the observer connection runs its own queries; those show up in their own thread's memory_summary_* rows. Doesn't pollute the subject's per-thread counters because of per-thread grain, but does increase global performance_schema load.
Connection-drop during sampling — if connection A dies mid-workload (timeout, kill, network blip), B continues sampling a now-idle or re-used thread. Long-running scripts should periodically re-validate A's liveness.
Privilege requirements — observing another session typically requires PROCESS privilege (to see other sessions in SHOW PROCESSLIST) and SELECT on performance_schema.*. Restricted users can only observe themselves.

Seen in¶

PlanetScale's Profiling memory usage in MySQL (2024-04-11). Dicken makes the two-connection requirement explicit up-front and both Python scripts presuppose the shape: a user runs the workload in one shell, the script connects separately to sample. (Source: sources/2026-04-21-planetscale-profiling-memory-usage-in-mysql.)