Database Monitoring, Troubleshooting, and Diagnostics Questions

Direct answer

Five metrics cover the core questions a responder needs answered first: is the database slow (a high-percentile query or write latency), is it actually serving load correctly (throughput and error rate), is it running out of room (connection and CPU or memory saturation), is its working set still fitting in memory (buffer or cache hit rate), and, if replicas exist, is it falling behind (replication lag). Pick alert thresholds relative to the workload's own historical baseline and its SLO (service-level objective, an internal target for how the system should perform), not a generic industry number, and expect an OLTP workload (online transaction processing, many small, fast operations) and an OLAP workload (online analytical processing, fewer, larger, longer-running queries) to need different baselines for the same metric.

Structured elaboration

On a managed database

A managed relational database service typically removes OS shell access, but the same five categories are still visible, just organized through the provider's own console or API instead of OS commands: host-level metrics (CPU, memory, disk, network, exposed as instance-level monitoring), database-level metrics (connections, buffer hit ratio, via the engine's own views proxied through the console), query-level metrics (top SQL by load, via a performance-insight feature layered on top rather than raw direct access to the engine's own statistics views), and replication metrics (lag, as a first-class console metric since the provider manages the replica topology). Losing shell access doesn't mean losing observability; it means the same five things live in a different namespace. Know where each one lives before an incident, not during one.

OLTP versus OLAP baselines

The same five metrics need structurally different thresholds depending on workload shape. An OLTP system, many small, fast, latency-sensitive operations, should alert tight on p99 latency, since milliseconds matter, and treat connection saturation as urgent, since a starved OLTP system fails user-facing requests immediately; its throughput baseline is usually fairly steady and predictable. An OLAP system, fewer, larger, longer-running analytical queries, should alert loose on p99 latency, since a single query legitimately taking minutes is normal, but tight on sustained resource saturation, CPU, memory, or I/O held near capacity for an extended period is the actual OLAP health signal, since a single slow query is expected but the whole system staying pinned means queuing is building, and on queue depth (how many queries are waiting to run) rather than on individual query latency. Applying an OLTP-shaped p99-latency alert to a warehouse would page constantly on completely normal behavior, and applying an OLAP-shaped hourly-average view to an OLTP system would miss a real user-facing latency spike entirely.

Worked example

Turning the p99-latency alert condition into concrete numbers, showing why a baseline-relative threshold rather than a fixed one is the right shape:

p99_baseline_ms = 42   # trailing 7-day baseline
multiple = 3
threshold_ms = p99_baseline_ms * multiple
print(threshold_ms)

126

At a 42ms trailing baseline, the alert fires above 126ms. If legitimate growth naturally moves the baseline to, say, 55ms next quarter, the threshold recalculates to 165ms automatically, whereas a fixed ">100ms" rule set today would already be uncomfortably close to normal, healthy behavior by then and would need someone to remember to revisit it.

Trade-offs and pitfalls

• A fixed threshold copied from a blog post or another team's dashboard, rather than derived from this workload's own baseline, is the most common reason alerts are either constantly noisy (too tight for this workload's normal variance) or dangerously quiet (too loose, so real incidents don't page). Baseline-relative thresholds avoid this, but need enough historical data to compute a meaningful baseline; don't set them from one week of a system still finding its steady state right after launch.
• Alerting only on averages instead of a high percentile hides exactly the tail-latency problems users actually feel; a p99 spike can coexist with a perfectly healthy-looking average if it affects a small fraction of requests, which is often the fraction most likely to matter most.
• Five metrics is a floor, not a ceiling. This is the minimum viable health picture, not a complete observability strategy; deeper investigation, specific slow-query identification, lock waits, log growth, still needs its own dedicated tooling once one of these five flags a problem.

Direct answer

pg_stat_activity is a system view, one row per active database connection (called a backend), showing what that connection is doing right now: its process ID, current state, what it's waiting on if anything, when its current query started, where it connected from, and the query text itself. It's the first place I look to answer "what is the database doing at this exact moment," especially for finding queries that have been running too long or sessions that are blocking each other.

Structured elaboration

Reading the key columns.

• pid: the backend's process ID. This is the identifier you'd pass to pg_cancel_backend(pid) (asks the query to stop, like a graceful interrupt) or pg_terminate_backend(pid) (forcibly ends the whole connection) if you decide to intervene.
• state: the backend's overall status. active means it's currently executing a query. idle means it's connected but waiting for the application to send the next command. idle in transaction is the one worth watching closely: a transaction is open but nothing is currently running inside it, which usually means application code forgot to commit or roll back, and it can hold locks the whole time it sits idle.
• wait_event_type and wait_event: if the backend isn't just burning CPU, these say what it's blocked on. A Lock wait means it's waiting on another session (contention); an IO wait means it's waiting on a disk read or write; a Timeout wait with event PgSleep means, unsurprisingly, it's inside a pg_sleep() call.
• query_start: when the currently running statement started. Subtracting this from now() gives you how long the current query has been running, which is the basis for the "longer than 5 minutes" filter below.
• client_addr: the IP address the connection came from. If this is null, it means the connection came in over a local Unix socket on the database server itself, not that the information is missing; a lot of same-host tooling and some connection poolers connect this way.
• application_name: whatever the connecting application chose to set it to (most drivers let you set this in the connection string). It's the single most useful column for triage in a system with more than one application talking to the database, because it turns an anonymous process ID into "this is the reporting job" or "this is the checkout service."

The query.

SELECT pid,
       state,
       wait_event_type,
       wait_event,
       query_start,
       now() - query_start AS running_for,
       client_addr,
       application_name,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE now() - query_start > interval '5 minutes'
  AND state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

pid <> pg_backend_pid() excludes the very session running this query, since that query obviously isn't "long-running" relative to itself. Filtering state <> 'idle' keeps the result set to sessions actually doing something (a genuinely idle connection sitting there for hours isn't the problem this query is hunting for; idle in transaction for a long time is, and it passes the filter because its state literally is idle in transaction, not idle).

Worked example

I ran this against a real PostgreSQL 16 instance seeded with 2 million rows. I started a background session that set its application_name to reporting-svc and ran SELECT pg_sleep(20), count(*) FROM orders WHERE status = 'paid', then queried pg_stat_activity from a second session two seconds in (using a 1-second threshold instead of 5 minutes purely so the demo doesn't require an actual 5-minute wait; the query shape is identical). The real output:

 pid | state  | wait_event | query_start                    | running_for     | client_addr | application_name | query
-----+--------+------------+---------------------------------+------------------+-------------+------------------+-------------------------------
  74 | active | PgSleep    | 2026-09-25 05:05:49.564188+00 | 00:00:02.085267 |             | reporting-svc    | SET application_name = 'reporting-svc'; select pg_sleep(20),

Reading this row: pid 74 is the one to act on if I decide to cancel it. state is active, so it's genuinely executing, not idle in a transaction. wait_event is PgSleep, confirming it's inside the sleep, not blocked on a lock or I/O (in a real slow query you'd instead see either no wait event at all, meaning it's CPU-bound, or an IO event like DataFileRead, meaning it's waiting on disk). client_addr is blank, which by the definition above means this connected over a local Unix socket, not that I'm missing data. application_name immediately tells me this is the reporting service, not, say, the checkout path, which changes how urgently I'd intervene.

Trade-offs and pitfalls

pg_stat_activity shows the currently executing statement, not the whole transaction's history, so a session that ran three fast statements and is now stuck idle in a fourth, uncommitted transaction will show idle in transaction with an old xact_start but a recent-looking query_start from its last completed statement; check xact_start as well as query_start when a session looks suspicious. The query text shown is the literal text sent by the client, including any parameter placeholders the driver already substituted, so very generic-looking queries from an object-relational mapper (ORM, a library that translates between database rows and application objects) can be hard to trace back to a specific code path from the text alone; application_name and a request or correlation ID embedded in a SQL comment (many ORMs support this) close that gap. Running this query itself has negligible cost, but reflexively killing every session with pg_terminate_backend the moment it shows up in a long-running query check is a mistake: a five-minute analytical report or a maintenance job (like a VACUUM) is legitimately long-running, and killing it mid-write can force a rollback that costs more time than letting it finish.

Direct answer

Before touching anything, I spend sixty seconds orienting: what changed recently, and which of three buckets does this look like (database-level, infrastructure-level, or application-level)? Then I run one focused command sweep that covers sessions, locks, and I/O in a single pass, form a hypothesis, apply only the fixes that carry no downside if I'm wrong, and hand off a written timeline to whoever picks up the deeper work. The goal of the first five minutes is triage and evidence collection, not a permanent fix.

Structured elaboration

Step 0: pick a bucket before you start clicking around. A single noisy customer's query going slow could be a database-level problem (bad query plan, lock wait), an infrastructure-level problem (the underlying host or cloud volume is degraded), or an application-level problem (a new code path sends 50x more of that query, or a connection pool misconfiguration is serializing requests that used to run in parallel). Naming the bucket up front stops you from reflexively reaching for EXPLAIN (the command that shows the execution plan the database chose for a query, i.e. which indexes or scans it actually used) when the real problem is a saturated disk.

Step 1: correlate against the timeline, not just the symptom. Pull up whatever change history the team has (deploy log, config management history, database driver/ORM version in the last release, a recent schema migration, an autoscaling event) and line it up against when the pages started. A page that starts within minutes of a deploy is a different investigation than one that started three hours after the last deploy with no correlated change. If nothing correlates, that itself is a data point: it points toward a data-volume or traffic-driven cause rather than a code or config change.

Step 2: one sweep, not five separate lookups. I run a single investigative pass instead of jumping between five different consoles:

-- 1. What's running right now, and for how long
SELECT pid, state, wait_event_type, wait_event, now() - query_start AS running_for,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY query_start;

-- 2. Who's blocked, and by whom (idiomatic form)
SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- 3. Buffer cache health (is this a memory or an I/O problem?)
SELECT round(100.0 * sum(heap_blks_hit) /
       nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio_pct
FROM pg_statio_user_tables;

-- 4. Table-level activity shape (sequential scans vs index use, dead-row buildup)
SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

I ran this exact sweep against a seeded PostgreSQL 16 instance (2 million rows in an orders table, a separate sessions table carrying deliberate dead-row bloat from unvacuumed updates) and got real numbers back:

cache_hit_ratio_pct: 99.59

 relname  | seq_scan | seq_tup_read | idx_scan | n_live_tup | n_dead_tup
----------+----------+--------------+----------+------------+------------
 orders   |       18 |     12000000 |      515 |    2500000 |          2
 sessions |       14 |      2400072 |        0 |     200000 |    1200000

That last row is a real finding, not a hypothetical one: sessions has 1.2 million dead rows sitting on top of 200,000 live ones (a 6x dead-to-live ratio), and every one of its 14 sequential scans is walking past all of that dead weight. A 99.59% cache hit ratio tells you this isn't a memory-pressure problem (the working set fits in shared buffers); the CPU and latency cost here is from scanning bloat, not from disk fetches.

Step 3: apply only the fixes with no downside if your hypothesis is wrong. Three moves are safe to make live, before the deeper investigation is done:

• SET statement_timeout = '5s' (session- or role-scoped) so a single runaway query can't hold a lock or a connection slot indefinitely while you investigate; it doesn't fix the cause but it caps the blast radius.
• CREATE INDEX CONCURRENTLY if the sweep points at a genuinely missing index; it builds without taking the exclusive lock a plain CREATE INDEX would, so it doesn't add a second incident on top of the first. I ran this against the same instance (CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);) and it completed cleanly with indisvalid = true, no blocking.
• ANALYZE on the specific table you suspect, if n_dead_tup or query plans suggest stale statistics; it's a read of the table's rows plus a metadata write, not a schema change, so it's low-risk even under load (though on a very large table it does cost real I/O, so don't run it against ten tables at once during an active incident).

Step 4: name the shape of the root cause, including the ones you can't fix right now. Two shapes come up constantly: a query that used to be fine and now runs against 10x the data (data growth outpacing the original index or query design), or a table that was never really tuned, growing steadily, now hitting frequent full-table scans as it crosses a size threshold where the planner's old assumptions stop holding. Both are real and worth naming in your handoff, but neither is a five-minute fix: redesigning indexes or partitioning a monolithic table is medium-term (weeks-to-months) prevention work that belongs to query and schema tuning, not to this incident. Say so explicitly rather than trying to solve it while paged.

Step 5: check blast radius and communicate before you're fully done investigating. Don't wait for a root cause to tell people what you know. Check whether this is surfacing as errors in dependent services (application-side 500 responses, timeouts in a downstream queue consumer, elevated retry rates) so you can scope the incident correctly, and post a short, factual status update: what's affected, what you've ruled out, what you're checking next. Silence during an active incident is worse than an update that says "still narrowing it down."

Step 6: package the handoff. Whoever picks up the deeper investigation should get, in writing: the timeline of what changed and when, the raw output of your sweep (not just your interpretation of it), which of the three buckets you believe this is and why, what you've already ruled out, and the safe mitigations you already applied (so they don't get re-applied or accidentally reverted).

Worked example

Page comes in: "high CPU and slow queries" on the orders database. Bucket check: dashboard shows CPU climbing steadily over two hours, no step-function jump, so I lean database-level or data-volume-driven rather than infrastructure (no VM eviction or noisy-neighbor signature) or a single bad deploy. Timeline check: no deploy in the last six hours, but the on-call channel shows a bulk data-import job that landed 400,000 new rows into orders four hours ago. Sweep: pg_stat_user_tables shows orders at 18 sequential scans reading 12 million tuples total against a 2.5 million row table (the scans are individually cheap over a small table but expensive now that data grew 5x), and the cache hit ratio is a healthy 99.59%, ruling out a memory problem. Hypothesis: the import shifted the table's statistics enough that a query the planner used to route through an index is now taking a sequential-scan path, or an index that existed pre-import is no longer selective enough. Safe action: ANALYZE orders; to refresh statistics (cheap relative to the problem, no schema change), then re-check whether the hot query's plan changes. I'd hand off with: "CPU climbing over 2h, correlates with a 400k-row bulk import 4h ago, no recent deploy, cache hit ratio rules out memory pressure, ran ANALYZE as a safe first step, here's the sweep output, next person should pull EXPLAIN on the top three queries from pg_stat_statements to confirm the plan actually flipped."

Trade-offs and pitfalls

Tunnel vision on the first metric you see (usually CPU, because that's what paged you) is the single most common mistake: CPU is often a symptom of something else (a bad plan doing far more work than necessary, or the buffer cache thrashing) rather than the root cause itself. ANALYZE and CREATE INDEX CONCURRENTLY are low-risk but not free: ANALYZE on a huge table does real I/O and can itself add load during an already-strained window, and CREATE INDEX CONCURRENTLY can fail partway through and leave behind an invalid index that needs to be dropped and retried, so check pg_index.indisvalid afterward rather than assuming success. statement_timeout protects you from one runaway query but will also kill a legitimate long-running batch job if you set it too aggressively or forget to scope it to a role instead of applying it globally, so scope it narrowly and remove it once the incident is over rather than leaving it as an accidental permanent change.

Direct answer

pg_stat_statements tracks cumulative statistics per normalized query (the same query shape with different literal values counted as one entry), so ranking by mean execution time surfaces queries that are consistently slow every time they run, which is a different and often more actionable signal than ranking by total time, which is dominated by whatever runs most often rather than whatever is individually worst.

Structured elaboration

One important correction before the query itself: the column names in the question, total_time, mean_time, and max_time, are the pre-PostgreSQL-13 names. As of PostgreSQL 13, they were renamed to total_exec_time, mean_exec_time, and max_exec_time (with separate total_plan_time/mean_plan_time columns added alongside them, splitting out query-planning time from execution time, which didn't exist as a separate measurement before). I ran this against a real PostgreSQL 16 instance and confirmed the old names no longer exist; the query below uses the current names and aliases the output to match what was asked for, so the result still has the requested column labels.

SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_time_ms,
       round(mean_exec_time::numeric, 2)  AS mean_time_ms,
       round(max_exec_time::numeric, 2)   AS max_time_ms
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY mean_exec_time DESC
LIMIT 5;

The WHERE clause filters out the extension's own bookkeeping query from the results, since otherwise this query would show up ranking itself. pg_stat_statements must be loaded via shared_preload_libraries and its extension created (CREATE EXTENSION pg_stat_statements;) before it will collect anything; it's not on by default.

Interpreting the results. The query column shows the normalized query text with literal values replaced by placeholders ($1, $2, and so on), because pg_stat_statements groups executions of the same query shape together regardless of which specific values were passed; this is different from pg_stat_activity, which shows the literal text of whatever is currently running. calls tells you how many times that shape has executed since the last stats reset, which matters for prioritization: a query with a high mean_exec_time but only 3 calls total is a very different priority than one with a similarly high mean but 50,000 calls. max_exec_time catches queries whose average looks fine but that occasionally spike badly, often a sign of a plan that's sensitive to the specific parameter values passed (a classic cause is a skewed data distribution where one particular value matches far more rows than the planner's cached plan expected).

Prioritizing the ranked list. Ranking by mean_exec_time alone finds individually slow queries, but the actual optimization priority should weigh mean time against call volume: a query at 60ms mean called 50,000 times a day is consuming roughly 50 minutes of cumulative database time daily, while a query at 500ms mean called only 10 times a day costs about 5 seconds cumulative; the first is very likely the better use of tuning effort even though it ranks lower on mean time alone, so I'd look at both this query's mean_exec_time-ranked list and a separate total_exec_time-ranked list before deciding where to spend effort, not just the one the question asked for.

Worked example

I generated real, varied query activity against a seeded 2 million row orders table (a mix of an indexed point lookup, a full-table GROUP BY, an unindexed range scan with an ORDER BY and LIMIT, and a self-join), reset pg_stat_statements, ran each query a realistic number of times, then ran the query above. Actual output:

 query                                                                                                                 | calls | total_time_ms | mean_time_ms | max_time_ms
------------------------------------------------------------------------------------------------------------------------+-------+---------------+--------------+-------------
 SELECT count(*) FROM orders o1 JOIN orders o2 ON o1.customer_id = o2.customer_id WHERE o1.id < o2.id AND o1.id < $1  |     1 |        110.08 |       110.08 |      110.08
 SELECT status, count(*), avg(amount_cents) FROM orders GROUP BY status                                               |     2 |        126.62 |        63.31 |       64.05
 SELECT * FROM orders WHERE amount_cents > $1 ORDER BY created_at DESC LIMIT $2                                       |     1 |         27.72 |        27.72 |       27.72
 SELECT id, status FROM orders WHERE id = $1                                                                          |     5 |          2.59 |         0.52 |        1.22
 SELECT count(*) FROM orders WHERE customer_id = $1                                                                   |     3 |          1.05 |         0.35 |        0.75

Reading this: the self-join tops the list by mean time at 110ms, but it was only called once, so on its own this doesn't say much about production priority, it says this specific shape of query is expensive per-call, worth knowing if it's on a hot path. The GROUP BY query is second at 63ms mean but was called twice, both calls close to the mean (64.05ms max versus 63.31ms mean), so this one is consistently, not occasionally, expensive, a more confident signal that it needs a covering index (an index that already includes every column the query needs, so the database can answer it from the index alone without a separate trip to the table) or a materialized rollup (a precomputed, stored summary table that's refreshed periodically, so the GROUP BY is computed once and then just read) if it runs often in production. The point lookup by id and the count by customer_id are both fast and unremarkable, exactly what you'd expect from indexed equality lookups against a 2 million row table.

Trade-offs and pitfalls

pg_stat_statements numbers are cumulative averages since the last reset (or since the extension was created), so a query that was fast for months and only recently started degrading will still show an overall mean pulled down by its historical fast runs, masking a real, recent regression; for that, look at max_exec_time alongside the mean, or better, sample the view periodically and diff it over time rather than treating one snapshot as the full picture. mean_time ranking, as the direct answer notes, can mislead if read without call volume: it will surface a rarely-run, individually expensive report query above a frequently-run query whose cumulative cost to the system is actually far higher, so treat this ranked list as one input to prioritization, not the final answer.

Direct answer

Each slow query log entry in MySQL carries a header with four numbers that matter for prioritization: how long the statement took to execute, how long it spent waiting to acquire locks, how many rows it sent back to the client, and how many rows it had to examine internally to produce that result. The gap between rows examined and rows sent is usually the single most useful number in the entry, since a query that examines a million rows to return ten is telling you almost exactly where the missing index is.

Structured elaboration

Reading the entry. A typical header looks like:

# Query_time: 0.500000  Lock_time: 0.000100  Rows_sent: 42  Rows_examined: 10000
SET timestamp=1234567890;
SELECT * FROM users WHERE status='active';

• Query_time: total wall-clock time the statement took, in seconds (fractional, down to microsecond precision on modern MySQL). This is what actually determines whether the query crossed the logging threshold.
• Lock_time: how much of that total time was spent waiting to acquire a lock, as opposed to doing real work. A query with Lock_time close to Query_time is a contention problem (something else is holding a lock this query needs), not a query-design problem; a query with Lock_time near zero and all its time elsewhere is the opposite.
• Rows_sent: how many rows actually went back to the client.
• Rows_examined: how many rows the storage engine had to read through internally to produce that result, before any WHERE filtering or LIMIT cut it down. This is the number that most directly indicates whether an index is missing or not being used: a huge gap between Rows_examined and Rows_sent, as in the example above (10,000 examined to send 42), usually means the storage engine is scanning far more of the table than it needs to, either a missing index on the filter column or an existing index the planner isn't using.

Prioritizing tuning work from the log. I don't prioritize by Query_time alone, because a query that's slow purely due to Lock_time needs a completely different fix (resolve the contention, don't touch the query) than one that's slow due to a large Rows_examined-to-Rows_sent gap (add or fix an index). I'd bucket entries into those two categories first, then within the Rows_examined-heavy bucket, prioritize by how frequently that same query shape recurs in the log (a query that's individually only moderately slow but appears constantly is often worth more effort than a rare, one-off slow query).

Enabling slow query logging safely in production. Set the threshold and destination deliberately rather than accepting defaults:

-- how many seconds a query must take to be logged; default is 10s, often too loose to catch real problems
SET GLOBAL long_query_time = 2;
-- write to a file, not the mysql.slow_log table; writing to a table under load risks its own contention and open-file-descriptor pressure
SET GLOBAL log_output = 'FILE';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL slow_query_log = 1;
-- ignore queries that only touch a handful of rows even if they're slow for other reasons (lock waits mostly), keeps the log focused on genuine scan problems
SET GLOBAL min_examined_row_limit = 100;

Persist these with SET PERSIST (MySQL 8.0+) or in the server's configuration file so they survive a restart rather than silently reverting.

Worked example

Given the entry above (Query_time: 0.5, Lock_time: 0.0001, Rows_sent: 42, Rows_examined: 10000) for SELECT * FROM users WHERE status='active': Lock_time is negligible relative to Query_time, so this isn't a contention problem, it's a scan problem. 10,000 rows examined to return 42 is a 238-to-1 ratio, strongly suggesting status has no usable index, or the table is small enough that the optimizer decided a full scan was cheaper than an index lookup anyway (worth confirming with EXPLAIN (the command that shows the execution plan the database chose, i.e. which index or scan strategy it actually used) before assuming an index is missing, since sometimes it exists but isn't selective enough to be worth using). If this same query shape shows up dozens of times across a day's log with a similar ratio each time, that's a strong, low-risk case for adding an index on status: CREATE INDEX idx_users_status ON users (status);, built with ALGORITHM=INPLACE, LOCK=NONE where the storage engine supports it, to avoid locking the table during the build on a production system.

Correlating with application traces. Log entries carry a timestamp and the raw SQL text, but not, by default, any application-side request ID or trace context; the most reliable way to correlate a slow log entry with the specific application code path that issued it is to have the application inject an identifying SQL comment into the query itself (many object-relational mapping libraries and query builders support this directly, or it can be added manually), since that comment survives into the slow log verbatim and gives you a direct link back to the originating request or endpoint, rather than trying to line up timestamps after the fact.

Trade-offs and pitfalls

Enabling the slow query log is not free on a busy production server: writing each entry is an additional disk write per slow query, and under log_output = 'TABLE' specifically, each entry opens the internal mysql.slow_log table, which under high slow-query volume can itself contribute to file-descriptor pressure, one of the reasons FILE output is the safer production default. log_queries_not_using_indexes looks tempting (it logs every query that doesn't use an index, regardless of long_query_time) but is genuinely dangerous to turn on without log_throttle_queries_not_using_indexes set alongside it, since on a large, poorly-indexed table it can flood the log fast enough to itself become a performance and disk-space problem. MySQL writes each entry to the log only after the statement finishes executing and all its locks are released, so log order can differ from actual execution order; don't use log timestamps alone to reconstruct the precise sequence of events during an incident.