Metrics and activity
DBGorilla keeps two kinds of telemetry for each database.
Metrics are numbers recorded over time: memory usage, commit counts, and WAL volume. They answer “what did the last six hours look like?”
Activity is a repeated sample of what your sessions were doing. It answers “what was running at 14:32, and what was it waiting on?”
Neither comes from your database’s log files. DBGorilla does not read your logs. There is no log file collector, and nothing in the collector’s configuration enables one. Activity comes from sampling PostgreSQL’s own view of its sessions.
Turn it on
Section titled “Turn it on”There is nothing to turn on. Metrics and activity samples start arriving when the collector connects.
Find a metric
Section titled “Find a metric”Each component has about 60 to over 300 metric names, depending on where it runs. Instead of guessing a name, list the available metrics first.
Metrics arrive under three broad families:
Your database. Commits, rollbacks, deadlocks, temp files, blocks read and hit, rows inserted and updated, table and index sizes, sequential and index scans, vacuum and analyze times, WAL bytes and records, transaction ID age.
Your host or container. CPU, memory, filesystem, volume capacity and network. What is available here depends on where the database runs, and for a Kubernetes-hosted database it includes pod and container limits. This helps distinguish “the database is slow” from “the pod is at its CPU limit”.
Your platform. Whatever your hosting publishes about itself: replication lag, replication slots, backup recoverability points, archiver status.
Each metric carries its unit and the dimensions you can break it down by. An index metric can be grouped by index name and relation, a replication metric by the standby’s application name and client address, and a WAL metric not at all.
Database metrics can be narrowed to one database or left aggregated across all of them. Host and server metrics belong to the machine, so they return no data when narrowed to one database. Ask for them for the whole component or for one server.
Read a counter, a ratio or a correlation
Section titled “Read a counter, a ratio or a correlation”Use rates for counters, ratios for proportions, and correlation to find metrics that moved together.
Most counters are cumulative and increase indefinitely. A per-second rate is what you usually want to inspect, so ask for the rate and DBGorilla does the arithmetic.
Ratios
Section titled “Ratios”A ratio divides one metric by the sum of others. This gives you a cache hit ratio from two
counters or a memory available fraction from two gauges. Name the metrics, then say how each
time bucket is reduced: rate or increase for a counter, and avg, min, max or count
for a gauge.
Correlation
Section titled “Correlation”Given one metric and a time range, DBGorilla finds the other metrics that moved with it. Use it when something spiked at 03:00 and you do not know what else did.
See what sessions were doing
Section titled “See what sessions were doing”Activity is Active Session History: a repeated sample of what every session was doing. This connects “the database was busy” to what it was busy with.
What was the load made of?
Section titled “What was the load made of?”A breakdown gives you average active sessions over a window, grouped by a dimension you choose: wait class, wait event, state, session type, user, application name, client address or database. Start with wait class, which separates time spent on CPU, I/O, locks and the client. Each class points to a different investigation.
How many connections, and whose?
Section titled “How many connections, and whose?”A connection count is a different question from load, and conflating them is the usual mistake. Nine hundred idle connections are not load. Count by state, database, user or application name, and narrow by state to ask things like “idle connections per database”.
Which sessions exactly?
Section titled “Which sessions exactly?”Samples give you the individual sessions in each snapshot, with their session ids and query fingerprints. They are split into active, blocked and idle-in-transaction, along with the connection summary.
What did one session do?
Section titled “What did one session do?”A session timeline is the ordered list of snapshots for a single session with summary statistics. Use it after a breakdown or a blocking episode has given you a session id.
Find what is blocking what
Section titled “Find what is blocking what”When queries stop finishing and nobody can tell you why, the question is which session holds the lock that everything else is waiting for. DBGorilla answers this from activity history, so you can ask
about an incident that has already finished. pg_locks shows the locks held right now, while activity history lets you inspect a blocking episode after it has ended.
Blocking episodes are collapsed across samples, so one long block is one episode rather than forty rows. You can filter by minimum duration. Set it to zero to see everything, including momentary contention, or to five seconds or more to see only longer episodes.
Use this sequence:
- Ask for blocking episodes over the window the incident covers.
- Take the blocking session’s id from the worst episode.
- Ask for that session’s timeline to see what it was doing and for how long.
- Take its query fingerprint to Query performance for the query itself and its plan.
A wait class breakdown over the same window is useful too. If the load was mostly
Lock, follow the blocking sessions and their queries. If it was mostly IO, look at storage
activity before attributing the incident to blocking.
How far back you can look
Section titled “How far back you can look”Raw metrics and activity samples are kept for 30 days by default. That is the window you can go back and sample at full resolution.
Summaries last longer. Hourly summaries of metrics and activity, and daily summaries of metrics, remain after the raw samples have rolled off, so questions about how load has trended over months can still be answered.
Two other data types remain after the raw window and affect what you should capture during an incident:
- Topology snapshots are kept as long as they exist, so schema history is not limited by this window.
- Investigations write the measurements they used into the report itself, so an investigation run during an incident captures that evidence in its report.
Use it from your editor
Section titled “Use it from your editor”An assistant connected through Connect your editor has a tool for
each step above: discover_metrics, get_metric_timeseries, get_metric_ratio_timeseries and
get_correlated_metrics for metrics, and get_activity_breakdown, get_activity_connections,
get_activity_samples, get_blocking_sessions and get_session_timeline for activity.
Related
Section titled “Related”- Query performance: the query behind a fingerprint.
- Topology: the servers and databases these metrics belong to.