How to Monitor MySQL & PostgreSQL Server Performance (Complete Guide)

Your database is “up.” The health check passes, the port responds, uptime shows 47 days. And yet support tickets are piling in because a report that used to take two seconds now takes twenty, and nobody noticed until customers started complaining. This is the most common failure mode in database operations: you can monitor MySQL and PostgreSQL performance without ever checking whether the server is “alive,” because alive and fast are two completely different questions. This guide walks through exactly what to watch, how to check it on each engine, and which tools do the heavy lifting for you.

Most tutorials pick a side — either MySQL or PostgreSQL — and leave you to translate the concepts yourself when your stack (or your next job) uses the other one. Since a huge number of teams run both somewhere in their infrastructure, this guide covers the metrics that matter on either engine, then breaks out the exact commands and queries you need for each one specifically.

Key metrics to track

Regardless of which database you run, the same categories of problems cause almost all performance incidents. If you only have time to build a handful of dashboards or alerts, start here.

Query latency and slow queries

The single most direct signal of a performance problem is how long queries actually take. Track average and p95/p99 latency, and keep a running list of queries that exceed a defined threshold (100ms–1s, depending on your workload). A rising slow-query count is usually the earliest warning sign you’ll get, often days before users notice anything.

Connections

Every connection consumes memory and, on both engines, can become a bottleneck once you approach the configured maximum. Watch active vs. idle connections separately — a pile of idle-in-transaction sessions is a classic sign of an application that isn’t closing transactions properly, and it silently eats into your connection budget until new connections start getting rejected.

Replication lag

If you run read replicas (and most production setups do), lag between primary and replica directly affects data freshness and failover safety. A replica that’s seconds or minutes behind can serve stale reads to users or, worse, leave you with data loss if it’s promoted during an incident.

Cache hit ratio

Both MySQL’s InnoDB buffer pool and PostgreSQL’s shared buffers exist to keep hot data in memory instead of hitting disk. A falling cache hit ratio means more physical reads, which means slower queries — and it’s often the first measurable effect of a dataset outgrowing its allocated memory.

Disk I/O

Databases are I/O-bound systems at heart. Track read/write throughput, IOPS, and — critically — disk latency. A database that looks fine on CPU and memory graphs can still be crawling because the underlying storage is saturated.

Lock contention

Locks are how databases keep concurrent transactions consistent, but excessive lock waits mean queries are queuing up behind each other instead of running in parallel. Spikes in lock wait time or a growing count of blocked sessions point to a query, transaction, or schema design that needs attention.

Table and index bloat

Both engines accumulate “dead” space over time — MySQL through fragmentation in InnoDB tables, PostgreSQL through dead tuples left by its MVCC model until vacuumed. Bloated tables and indexes waste disk, slow down scans, and hurt cache efficiency, so tracking bloat percentage over time helps you schedule maintenance before it becomes a crisis.

Buffer pool / shared buffer usage

Closely related to cache hit ratio, this is about sizing: is your buffer pool (MySQL) or shared_buffers (PostgreSQL) actually large enough for your working set? Consistently high utilization paired with a falling hit ratio is a strong signal it’s time to allocate more memory.

To make this concrete, here’s how these metrics map to something you can actually check on each engine today, before you’ve set up any dedicated tooling:

None of these require third-party software — they’re built into each engine — which makes them a good starting point even before you roll out dedicated monitoring infrastructure.

MySQL-specific monitoring

MySQL gives you three main windows into what’s happening right now: SHOW PROCESSLIST for a live snapshot, Performance Schema for deep, ongoing instrumentation, and the slow query log for a historical record you can analyze later.

SHOW PROCESSLIST

This is your first stop when something feels slow right now. It lists every active thread, what it’s doing, and how long it’s been doing it.

SHOW FULL PROCESSLIST;

Pay attention to the Time column (how many seconds the query has been running) and the State column, which tells you what stage the query is actually in — “Sending data,” “Waiting for lock,” “Copying to tmp table,” and so on. Using FULL shows the entire query text instead of truncating it at 100 characters, which matters when you’re trying to identify exactly which query is stuck.

Performance Schema

For aggregate, ongoing visibility rather than a one-off snapshot, Performance Schema is where MySQL keeps detailed statistics on every query pattern the server has executed. This query surfaces your worst offenders by total execution time, which is usually more useful than sorting by single-query latency:

SELECT digest_text, count_star,
       ROUND(sum_timer_wait / 1000000000000, 2) AS total_sec,
       ROUND(avg_timer_wait / 1000000000, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

Sorting by total time rather than average time matters because a query that runs in 5ms but fires 500,000 times a day can cost you far more aggregate database time than one slow query that runs twice.

The slow query log

While Performance Schema gives you a live view, the slow query log gives you a durable, file-based record you can analyze after the fact — invaluable for spotting patterns that only show up under specific load conditions. Enable it and set a threshold:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Once you’ve collected some data, run it through Percona’s pt-query-digest to get a ranked report of your worst queries by total impact rather than wading through raw log entries by hand:

pt-query-digest /var/log/mysql/slow.log > slow-report.txt

A quick sanity check worth running alongside all of the above is SHOW ENGINE INNODB STATUS, which surfaces buffer pool hit rate, pending I/O, and any active deadlocks in one dense but useful block of text. And if you just want a quick snapshot of server-wide counters without digging into Performance Schema, mysqladmin extended-status gives you the same underlying data from the command line:

mysqladmin extended-status | grep -E 'Threads_connected|Slow_queries|Innodb_buffer_pool'

PostgreSQL-specific monitoring

PostgreSQL’s equivalent toolkit centers on two system views: pg_stat_activity for what’s happening right now, and the pg_stat_statements extension for aggregated query statistics over time.

pg_stat_activity

This view exposes every current connection and exactly what it’s doing, making it the fastest way to spot long-running queries, sessions stuck idle-in-transaction, or blocking conflicts:

SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Watch the state column for idle in transaction sessions — these hold locks and prevent vacuum from cleaning up dead rows, and they’re one of the most common causes of mysterious PostgreSQL slowdowns. The wait_event column tells you exactly what a query is blocked on if it isn’t actively running.

pg_stat_statements

This extension isn’t enabled by default, but it should be on every production instance. It tracks execution statistics for every distinct query shape the server runs, which lets you find your worst offenders by total time rather than guessing:

-- one-time setup (requires a restart after adding to shared_preload_libraries)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- find the queries costing you the most total time
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Once you’ve spotted a candidate, run it through EXPLAIN ANALYZE to see the actual execution plan and find out whether it’s missing an index, doing a sequential scan it shouldn’t, or getting a bad row estimate from stale statistics:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

It’s also worth checking table and index health directly, since PostgreSQL’s MVCC design means dead rows accumulate until autovacuum cleans them up. pg_stat_user_tables tells you exactly how far behind vacuum has fallen:

SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

A table with a high ratio of dead to live tuples and an old or missing last_autovacuum timestamp is a strong candidate for the mysterious “why did this query suddenly get slower” ticket.

Tools that make this easier

Running these queries manually is fine for a one-off investigation, but nobody wants to paste SQL into a terminal every time something feels slow. A proper monitoring stack collects these metrics continuously, alerts you before users notice, and keeps history so you can spot trends. A few worth knowing:

• Percona Monitoring and Management (PMM) — free and open source, purpose-built for MySQL and PostgreSQL (plus MongoDB), with query analytics built directly on top of Performance Schema and pg_stat_statements.
• pganalyze — a PostgreSQL-specific service that turns pg_stat_statements and EXPLAIN plans into readable, actionable index and query recommendations.
• Datadog — broad infrastructure monitoring with dedicated MySQL and PostgreSQL integrations, useful if you want database metrics sitting next to the rest of your application and infrastructure telemetry.
• Zabbix — a mature, self-hosted option that, paired with the community MySQL/PostgreSQL exporters, gives you flexible alerting without a subscription.

Each of these takes a slightly different angle. PMM is the closest thing to a drop-in replacement for manually querying Performance Schema and pg_stat_statements — it visualizes the exact metrics covered above without you writing a single query. pganalyze goes a layer deeper on the PostgreSQL side specifically, automatically flagging missing indexes and regressions in query plans over time, which is genuinely useful if PostgreSQL is your primary datastore. Datadog and Zabbix both trade some database-specific depth for breadth: they’re a better fit if you’d rather see database metrics alongside application traces, server CPU, and network graphs in one place, rather than maintaining a separate database-only tool.

Whichever route you take, the goal is the same: replace manual SQL queries run during an incident with dashboards and alerts that catch the problem before an incident happens at all.

If you’re still evaluating which platform fits your stack, our roundup of best database monitoring tools compares these and others head to head.

FAQ

How often should I check MySQL and PostgreSQL performance metrics?

Core metrics like connections, replication lag, and cache hit ratio should be scraped continuously (every 10–60 seconds) by a monitoring agent, with alerts on thresholds. Deeper query analysis — reviewing your top slow queries — is worth a manual look weekly, or immediately after any incident.

What’s a healthy cache hit ratio?

As a rule of thumb, aim for above 99% on both InnoDB’s buffer pool and PostgreSQL’s shared buffers for read-heavy workloads. Anything consistently below 95% usually means your working set has outgrown allocated memory.

Can I use the same monitoring tool for both MySQL and PostgreSQL?

Yes. Percona PMM, Datadog, and Zabbix (with the right exporters) all support both engines from a single dashboard, which is a real advantage if your infrastructure runs a mix of the two.

What causes replication lag to suddenly increase?

The usual suspects are a burst of large write transactions on the primary, a slow or under-provisioned replica, network latency between nodes, or a long-running query on the replica blocking the apply process. Check the replica’s own resource usage before assuming the primary is at fault.

Do I need pg_stat_statements enabled by default, or is it opt-in?

It’s opt-in. You need to add it to shared_preload_libraries in postgresql.conf and restart the server before running CREATE EXTENSION. It’s low-overhead enough that it’s worth enabling on every production PostgreSQL instance from day one.

Is it safe to run these diagnostic queries on a production server?

Yes, in general. SHOW PROCESSLIST, pg_stat_activity, and reads against Performance Schema or pg_stat_statements are lightweight and designed for production use. The one thing to be careful with is EXPLAIN ANALYZE on write queries (INSERT/UPDATE/DELETE) — it actually executes the statement, so wrap it in a transaction and roll back if you’re testing against live data.

Manually running these commands gets you unstuck during an incident, but the real fix is continuous visibility so you catch degradation before it becomes an outage. If you want a broader view of your entire stack rather than just the database layer, see the best all-round server monitoring platforms we recommend.