Identifying which specific queries are slow — and why — is essential for meaningful database performance improvement. This guide covers monitoring tools and techniques across major database systems.
Why This Matters More Than General Server Tuning
Server-level configuration tuning (see How to Optimize PostgreSQL Performance for a VPS) has diminishing returns if the actual bottleneck is a handful of genuinely inefficient queries — identifying and fixing specific slow queries often provides far more impact than general tuning alone.
Enabling the Slow Query Log (MySQL/MariaDB)
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnfslow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1long_query_time sets the threshold (in seconds) above which a query is logged as slow.
Reviewing the Slow Query Log
sudo mysqldumpslow /var/log/mysql/slow.logSummarizes and ranks slow queries, helping identify the most impactful ones to optimize first.
Enabling Slow Query Logging (PostgreSQL)
log_min_duration_statement = 1000Logs any query taking longer than 1000ms (adjust threshold as appropriate) directly to PostgreSQL's standard log.
Using pg_stat_statements (PostgreSQL)
CREATE EXTENSION pg_stat_statements;SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;Provides aggregated statistics across all queries, identifying both individually slow queries and frequently-run queries whose cumulative impact is significant even if each individual execution is fast.
Using EXPLAIN to Understand Why a Query Is Slow
EXPLAIN ANALYZE SELECT ...;See How to Write and Optimize SQL Queries: Indexing Basics for interpreting execution plans and identifying missing indexes or inefficient query structure.
Monitoring Connection and Lock Contention
-- PostgreSQL
SELECT * FROM pg_stat_activity WHERE state != 'idle';Shows currently active queries — useful for spotting long-running queries or lock contention in real time during an active performance issue.
Integrating with Broader Monitoring (Prometheus/Grafana)
See How to Set Up Prometheus and Grafana for VPS Monitoring — dedicated database exporters exist for most major databases, letting you build dashboards tracking query performance, connection counts, and resource usage alongside your other infrastructure metrics.
Setting Up Alerts for Query Performance Degradation
Rather than only reviewing logs reactively, configure alerting (see How to Set Up Effective Server Alerting) on key database metrics (connection count approaching limits, replication lag, elevated average query time) for proactive awareness.
A Practical Investigation Workflow
- Review slow query logs/pg_stat_statements to identify the worst offenders
- Use EXPLAIN on identified queries to understand the actual execution plan
- Add missing indexes or restructure genuinely inefficient queries
- Re-measure to confirm the improvement
Common Errors
Slow query log shows queries that seem simple — a simple-looking query can still be slow due to missing indexes on a large table, or lock contention from concurrent operations; always verify with EXPLAIN rather than assuming based on query appearance alone.
Continue Reading
- How to Write and Optimize SQL Queries: Indexing Basics
- How to Optimize PostgreSQL Performance for a VPS
- How to Set Up Prometheus and Grafana for VPS Monitoring
Browse more articles in Databases.
