Database monitoring: how to find and fix slow queries in MySQL and PostgreSQL

Database monitoring is the discipline of measuring how your databases behave under real load and finding the queries, locks and resource limits that make users wait. When a checkout page or an API endpoint becomes slow, the database is a frequent suspect, but rarely the whole story: the cause is often an application pattern, a missing index or a connection pool that has run dry.

This guide is practical and tool-neutral. It covers the metrics worth collecting, the built-in instruments of MySQL and PostgreSQL for finding slow queries, how to connect a slow statement to the request that issued it, connection pooling with PgBouncer and ProxySQL, sensible alerts, and a slow query triage checklist you can copy into your runbook.

The key metrics of database monitoring

A short list of metrics covers most incidents. Collect them per instance and, where possible, per database or query digest:

• Query latency percentiles: p50, p95 and p99 of statement duration. Averages hide the slow tail; a percentile shows what the slowest requests experience. Track the latency of your most frequent query digests separately, not just one instance-wide number.
• Throughput: queries or transactions per second, split by read and write. A latency spike with flat throughput points to contention; a spike together with a throughput jump points to load.
• Connections and pool saturation: active connections compared with max_connections, plus clients waiting for a pooled connection. Waiting time in the pool is invisible in database-side statistics but very visible to users. Connection limits are a classic saturation signal.
• Locks and deadlocks: lock wait time, number of blocked sessions, deadlock count and the age of the oldest open transaction.
• Replication lag: how far replicas are behind the primary. Lag causes stale reads and makes failovers risky.
• Buffer and cache hit ratio: share of page reads served from memory. A falling ratio suggests the working set no longer fits in memory or a query scans far more data than before.
• Disk I/O: read and write IOPS, throughput, I/O latency and queue depth, plus temporary files written by sorts and hashes.

Finding slow queries in MySQL

The slow query log

The slow query log records statements that take longer than long_query_time seconds. It is disabled by default, and the default threshold of 10 seconds is far too high for most web applications. The value accepts fractions down to microsecond resolution, so a threshold such as 0.5 or 0.2 seconds is common:

[mysqld]
slow_query_log                = 1
slow_query_log_file           = /var/log/mysql/slow.log
long_query_time               = 0.5    # seconds, example value
log_queries_not_using_indexes = 1      # optional, can be noisy
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit        = 1000   # example value
log_slow_extra                = 1      # extra fields in FILE output

log_output controls whether entries go to a file, the mysql.slow_log table or both. Tools such as mysqldumpslow or Percona's pt-query-digest group the log by normalized statement, which is far more useful than reading single entries.

performance_schema and the sys schema

The slow log only sees statements above the threshold. performance_schema, enabled by default in current MySQL versions, aggregates every statement by digest. The timer columns are in picoseconds, and MySQL 8.0 and later also store percentile columns:

SELECT digest_text,
       count_star                       AS calls,
       ROUND(sum_timer_wait / 1e12, 1)  AS total_s,
       ROUND(avg_timer_wait / 1e9, 2)   AS avg_ms,
       ROUND(quantile_95 / 1e9, 2)      AS p95_ms,
       sum_rows_examined,
       sum_no_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

Sort by total time first: a 5 ms query executed two million times an hour often costs more than one 3-second report. The sys schema offers ready-made views such as sys.statement_analysis and sys.statements_with_full_table_scans. For locks, SHOW ENGINE INNODB STATUS shows the latest detected deadlock, and innodb_print_all_deadlocks writes every deadlock to the error log. Replication lag appears as Seconds_Behind_Source in SHOW REPLICA STATUS.

Finding slow queries in PostgreSQL

pg_stat_statements

The pg_stat_statements extension is the most important single tool for PostgreSQL monitoring. It must be loaded at server start and then created in the database:

# postgresql.conf (requires a restart)
shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on

-- then, in the database
CREATE EXTENSION pg_stat_statements;

SELECT query, calls,
       ROUND(total_exec_time::numeric, 0) AS total_ms,
       ROUND(mean_exec_time::numeric, 2)  AS mean_ms,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Since PostgreSQL 13 the columns are called total_exec_time and mean_exec_time; older guides use total_time. A high shared_blks_read relative to shared_blks_hit means the query reads from disk or the operating system cache rather than shared buffers.

Logging and auto_explain

log_min_duration_statement logs every statement that runs longer than the given value in milliseconds (-1 disables it, 0 logs everything). The auto_explain module goes one step further and logs the execution plan of slow statements, which is invaluable for queries that are only slow with certain parameters:

log_min_duration_statement = 500          # ms, example value
log_lock_waits             = on           # logs waits longer than deadlock_timeout
session_preload_libraries  = 'auto_explain'
auto_explain.log_min_duration = 1000      # ms, example value
auto_explain.log_analyze   = on           # adds timing overhead
auto_explain.log_buffers   = on
auto_explain.sample_rate   = 0.1          # explain 10% of eligible statements

auto_explain.log_analyze measures every plan node and can add noticeable overhead, so sample it or keep it for limited periods. For locks, pg_stat_activity with wait_event_type and the function pg_blocking_pids() show who blocks whom; pg_stat_database counts deadlocks, temporary files and cache hits; pg_stat_replication reports write_lag, flush_lag and replay_lag.

EXPLAIN (ANALYZE, BUFFERS)

Once you have a suspect, run it with real parameters on a safe environment or replica:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 4711 AND status = 'open'
ORDER BY created_at DESC LIMIT 20;

Compare estimated and actual row counts, look for sequential scans on large tables, sorts spilling to disk and nested loops with high loop counts. ANALYZE really executes the statement, so wrap data-modifying statements in a transaction you roll back. Since PostgreSQL 18, buffer information is included automatically when ANALYZE is used. In MySQL, EXPLAIN ANALYZE (8.0.18 and later) gives a comparable view.

Symptom, likely cause, where to look

Use this table as a first-pass map when a database-related alert fires:

Symptom	Likely cause	Where to look
p99 latency up, throughput flat	Lock contention, plan change, cold cache	Lock waits, pg_stat_activity / performance_schema waits, EXPLAIN of top digests
Many identical fast queries per request	N+1 query pattern in the ORM	Trace spans per request, calls per digest
Single query slow, high rows examined	Missing or unusable index	EXPLAIN, sum_no_index_used, sequential scans in pg_stat_user_tables
App timeouts, database looks idle	Pool exhausted or connection leak	Pool wait time, PgBouncer SHOW POOLS, idle-in-transaction sessions
Connections near max_connections	Missing pooling, too many app instances	Threads_connected, pg_stat_activity by client
Deadlock errors	Transactions locking rows in different orders	SHOW ENGINE INNODB STATUS, PostgreSQL server log
Stale reads on replicas	Replication lag from heavy writes or long queries	Seconds_Behind_Source, pg_stat_replication
Hit ratio falling, disk reads rising	Working set larger than memory, new scan	Buffer pool metrics, shared_blks_read, I/O latency
Temp files and sort spills	Large sorts or hashes, low work memory	temp_files in pg_stat_database, Created_tmp_disk_tables

Correlating slow queries with application traces

Database statistics tell you which statements are expensive. They do not tell you which endpoint, user action or deployment caused them. That is the job of distributed tracing: OpenTelemetry instrumentation for database clients creates a span per query with attributes such as the database system and the sanitized query text, nested under the HTTP request that triggered it.

With traces next to database metrics, three common problems become obvious:

• N+1 queries: one request issues one query for a list and then one query per item. Each statement is fast, so it never reaches the slow log, but a trace shows hundreds of nearly identical spans. Our glossary entry on the N+1 query explains the pattern and typical fixes such as eager loading or batching.
• Missing indexes after a release: a new filter or sort order in the code produces a full table scan. Comparing the trace before and after the deployment shows the new span, and EXPLAIN confirms the missing index.
• ORM side effects: lazy loading, selecting every column, implicit transactions held open across remote calls, or retries that multiply load. These are visible as long gaps or repeated spans inside a trace.

To jump from a database log line to the trace, some teams add the trace context as a SQL comment, for example with the SQLCommenter convention. Check how your database tooling handles comments, since normalization may strip or keep them.

Connection pooling with PgBouncer and ProxySQL

Every PostgreSQL connection is a separate server process, and every MySQL connection consumes a thread and memory. Dozens of application pods with their own pools can exhaust the server long before CPU becomes a problem. A pooler between application and database solves this:

• PgBouncer for PostgreSQL. In pool_mode = transaction a server connection is assigned only for the duration of a transaction, which lets many clients share few connections. Session state such as SET or session-level advisory locks does not survive between transactions, so check your driver and ORM settings. SHOW POOLS in the admin console shows cl_waiting and maxwait, the most direct indicators of pool saturation.
• ProxySQL for MySQL. It multiplexes connections, can route reads to replicas with query rules and records per-digest statistics in stats_mysql_query_digest.

Monitor the pooler like a service of its own: waiting clients, wait time, server connections in use and errors. Also monitor the application-side pool, because the first queue a request meets is usually there.

Alerting suggestions for databases

Alert on symptoms users feel and on saturation that predicts an outage. The thresholds below are example starting points, not recommendations for every system; tune them against your own baseline:

• p95 or p99 latency of critical query digests or database spans above baseline for 10 minutes.
• Connections above 80% of max_connections, or pool clients waiting for more than one minute.
• Replication lag above what your read-after-write logic tolerates, for example 30 seconds.
• Any transaction or idle-in-transaction session older than, for example, 15 minutes.
• Deadlock rate clearly above its normal level, rather than a single deadlock.
• Disk space forecast to run out within a few days, and I/O latency sustained above baseline.

Where possible, tie database alerts to service level objectives instead of raw thresholds; our article on SLOs in practice shows how, and the guide to reducing alert fatigue helps keep the list short.

Slow query triage checklist

Copy this checklist into your runbook and work through it in order:

SLOW QUERY TRIAGE CHECKLIST
[ ] 1. Scope: which service, endpoint and time window? Since a deployment?
[ ] 2. User impact: p95/p99 latency, error rate, timeouts at the app
[ ] 3. Saturation: connections vs max_connections, pool wait time,
       CPU, I/O latency, disk space
[ ] 4. Top digests by total time (pg_stat_statements /
       events_statements_summary_by_digest): new entry or changed one?
[ ] 5. Calls per request in traces: N+1 pattern or retries?
[ ] 6. Locks: blocked sessions, oldest transaction, deadlocks
[ ] 7. Plan: EXPLAIN (ANALYZE, BUFFERS) / EXPLAIN ANALYZE with real
       parameters; estimated vs actual rows
[ ] 8. Index: missing, unused or wrong column order? Stale statistics?
[ ] 9. Replication lag if reads go to replicas
[ ] 10. Fix: index, query rewrite, batching, pool size, timeout
[ ] 11. Verify: compare latency and digest stats before/after
[ ] 12. Prevent: add alert, regression test or query review step

How Obseria helps

Obseria collects database metrics, logs and traces through OpenTelemetry and stores them together in Obseria Lake, so a slow query, the request that issued it and the deployment that changed it are in one place. The database monitoring feature and the integrations for PostgreSQL and MySQL cover the metrics described above.

• Query statistics linked to trace spans, so N+1 patterns and per-endpoint cost are visible.
• Obseria Pipeline masks literal values and personal data in query text before storage.
• Obseria Intelligence correlates latency changes with deployments, locks and saturation and shows the evidence.
• SLOs with burn-rate alerts for database-backed services.

You can try this on your own databases with the free trial.