Caching (Buffer, Redis)

A 16:30 incident. The database slow-query page fires at 17:15. The on-call engineer opens pg_stat_statements and sees the same handful of queries at the top that have been there all day. None of them are unusually slow. The buffer-cache panel, however, shows that the hit ratio dropped from 99.4% to 91.6% over the past two hours, and the eviction rate has climbed eight-fold. The database is not slow because its queries are slow. The database is slow because its cache is being evicted, and every query is now doing the I/O it used to skip.

This is the lesson cache metrics exist to teach. Latency is a lagging indicator; cache hit ratio is a leading indicator. A falling hit ratio precedes the slow-query page by minutes, and the team’s response is cheap if it sees the leading shape.

What caching is, in the database stack

Three caches sit between the application and the disk. Each caches a different layer; each has its own hit ratio; each fails in a different shape.

• In-process caches in the application. Per-host caches (Caffeine, Guava) for in-memory data structures; expensive to share, fast to populate. Not the focus of this lesson.
• Buffer pool inside the database engine. PostgreSQL shared_buffers; MySQL InnoDB innodb_buffer_pool_size. Caches disk blocks and is the dominant performance lever for disk-bound queries.
• Page cache on the operating system. What the database uses when its own buffer pool misses. Caches the same disk blocks at the file-system layer; “free” in that no application asked for it.

   Application       Database          OS             Disk
       |              buffer            page
       v              pool              cache
   ----in-process--> ----DB-buffer---> ----page-cache---> ----spinning/NVMe----
                                          |
                                    miss = disk I/O

A separate front-of-house cache (Redis, Memcached) sits in front of the database. It caches application-level objects (rows, sessions, computed values) and is not what this lesson is about; its metrics follow the same hit-ratio discipline.

The canonical metric for every cache is hit_ratio = hits / (hits + misses). The next-most-useful metric is evictions per second, which tells you whether the working set fits in the cache at all.

Why a sysadmin cares

A falling hit ratio is the most reliable leading indicator of a database slowdown that exists. Three reasons:

1. Latency is the lagging indicator. By the time the slow query panel is hot, the buffer pool has been evicted for minutes. The page fires when the user already feels it.
2. Hit ratio recovers fast when the cause is fixed. When the cause is working-set growth, the right action is either to grow the buffer pool or to evict unused schema (vacuum, partitioning, archiving). When the cause is a cold restart, the pool warms up in minutes. When the cause is eviction pressure (a heavy background writer), the action is to slow down the writer. All three are observable in the hit ratio first.
3. Hit ratio is immune to short-term noise. A single unlucky query against a cold page does not move the metric. The signal-to-noise ratio is the best of any database metric.

A second value of the same metric: a rising hit ratio can indicate that a hot key has been evicted from a separate cache (Redis, Memcached) and the database’s buffer pool is absorbing the load. The shape of the rise can be diagnostic — when the rate accelerates, the application has stopped caching in the front of the stack.

How it works

A buffer pool is a fixed-size array of disk blocks. The database reads a block into the pool when a query needs it; the database evicts a block when the pool is full and a new block is needed. Each of the four events (read, write, hit, miss) is a metric.

  Query needs block X
  |
  v
  block X in buffer pool?
  |yes                |no
  +-> HIT             +-> MISS
                          |
                          v
                        block X on OS page cache?
                        |yes               |no
                        +-> read from      +-> read from disk
                            page cache         (highest cost)
                        v
                        block X now in buffer pool
                        evict cold block if pool is full
                        (or hot block if write backlogs)
  v
  EVICT (cold block removed from pool)
  v
  WRITE (if block was dirty, flushed to disk)

The hit_ratio is computed as hits / (hits + misses). The exact arithmetic differs between PostgreSQL and MySQL — PostgreSQL reports the counts in pg_stat_database and the ratio is computed by the operator; MySQL reports the ratio and the counts in SHOW ENGINE INNODB STATUS and information_schema.innodb_metrics.

How to configure it

The exporter is configured through the standard PostgreSQL collectors. The MySQL exporter additionally collects the InnoDB pool metrics.

PostgreSQL collector set.

# /etc/default/prometheus-postgres-exporter
ARGS="--collector.database \
      --collector.stat_io \
      --collector.statio"

The exporter emits pg_stat_database_blks_hit_total and pg_stat_database_blks_read_total. Hit ratio is computed as a recording rule, not as a sample.

# /etc/prometheus/rules/caching.yaml (excerpt)
groups:
  - name: caching
    rules:
      # 5-minute hit ratio from the database counters.
      - record: pg:database:buffer_hit_ratio:5m
        expr: |
          sum by (datname) (
            rate(pg_stat_database_blks_hit_total[5m])
          )
          /
          sum by (datname) (
            rate(pg_stat_database_blks_hit_total[5m])
              + rate(pg_stat_database_blks_read_total[5m])
          )

      # 24-hour baseline, used as comparison.
      - record: pg:database:buffer_hit_ratio:24h
        expr: |
          sum by (datname) (
            rate(pg_stat_database_blks_hit_total[1d] offset 1d)
          )
          /
          sum by (datname) (
            rate(pg_stat_database_blks_hit_total[1d] offset 1d)
              + rate(pg_stat_database_blks_read_total[1d] offset 1d)
          )

      # Alert when hit ratio drops more than 4 percentage points.
      - alert: BufferHitRatioFalling
        expr: |
          pg:database:buffer_hit_ratio:5m
            < 0.96
          and
          pg:database:buffer_hit_ratio:5m
            < on(datname) pg:database:buffer_hit_ratio:24h - 0.04
        for: 15m
        labels:
          severity: ticket
        annotations:
          summary: 'Buffer hit ratio on {{ $labels.datname }} below 96% for 15m'

      # Alert when eviction rate is sustained (background writer is
      # under pressure).
      - alert: DatabaseEvictionRateHigh
        expr: |
          rate(pg_stat_io_evictions_total[5m]) > 1000
        for: 10m
        labels:
          severity: ticket

      # Cold cache after restart: hit ratio well below baseline.
      - alert: ColdCacheAfterRestart
        expr: |
          on(instance)
          time() - on(instance) pg_postmaster_start_time_seconds
            < 600
          and
          pg:database:buffer_hit_ratio:5m < 0.80
        labels:
          severity: ticket

MySQL exporter.

# /etc/default/prometheus-mysqld-exporter
ARGS="--collect.info_schema.innodb_metrics \
      --collect.global_status"

The exporter emits mysql_global_status_innodb_buffer_pool_read_requests_total and mysql_global_status_innodb_buffer_pool_reads_total. The hit ratio follows the same recording rule shape as PostgreSQL.

Redis (when applicable, not the focus).

# /etc/default/prometheus-redis-exporter
ARGS="--redis.addr=10.0.4.5:6379"

The exporter emits redis_keyspace_hits_total and redis_keyspace_misses_total. Hit ratio is computed the same way.

How to validate it

Every step is READ-ONLY.

# 1. Read PostgreSQL's buffer hit ratio from pg_stat_database.
psql -h 10.0.4.10 -U db_exporter -d app <<'SQL'
SELECT
  datname,
  blks_hit,
  blks_read,
  CASE
    WHEN blks_hit + blks_read = 0 THEN NULL
    ELSE round(
      blks_hit::numeric / (blks_hit + blks_read),
      4
    )
  END AS hit_ratio
FROM pg_stat_database
WHERE datname = 'app';
SQL

 datname |  blks_hit  | blks_read  | hit_ratio
---------+------------+------------+----------
 app     | 8394817592 |   18329474 |   0.9978

# 2. Read the InnoDB buffer pool hit ratio.
mysql -h 10.0.4.10 -u db_exporter -e \
  "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

 Variable_name                              | Value
--------------------------------------------+---------
 Innodb_buffer_pool_read_requests           | 1847293822
 Innodb_buffer_pool_reads                   |  18329474

The MySQL hit ratio is the same: 1 - 18,329,474 / 1,847,293,822 ≈ 99%.

# 3. Confirm the exporter emits the counter pair.
curl -sf http://10.0.4.7:9187/metrics \
  | grep -E '^pg_stat_database_blks_(hit|read)'

# 4. Compute the hit ratio live from the recording rule.
curl -sG http://prometheus:9090/api/v1/query \
  --data-urlencode 'query=pg:database:buffer_hit_ratio:5m{datname="app"}'

# 5. Confirm the alert rules parse.
promtool check rules /etc/prometheus/rules/caching.yaml

The outputs confirm: the database has the expected counter shape, both engines expose the counts, the exporter is publishing, the recording rule gives the live ratio, and the alerts parse.

How it can fail

Five failure shapes cover the overwhelming majority of production cache incidents.

1. Working-set growth beyond the buffer pool. A new feature references a different table; the buffer pool can no longer hold the union of hot tables; eviction rate doubles. Symptom: blks_read rate rises; hit ratio falls; query latency unchanged. Fix: enlarge shared_buffers (gated by host RAM) or partition / archive cold data.
2. Cold cache after restart. A maintenance window or a fail-over restarts the database. The first minutes of traffic are entirely cold. Symptom: hit ratio starts near zero and climbs to baseline over 5-15 minutes. Fix: pre-warm the cache, or accept the cost (and alert on the cold-case shape).
3. Cold cache after fail-over. A replica takes over; its buffer pool is colder than the primary’s. Symptom: hit ratio falls; query latency rises; queries that normally hit the buffer pool now read from disk. Fix: redirect read traffic to the (still-warm) primary during the warm-up; pre-warm where possible.
4. Eviction pressure from background work. A VACUUM FULL, an ANALYZE on a large table, or an archive job evicts pages on the buffer pool. Symptom: eviction rate spikes; pg_stat_io_evictions_total rises sharply; hit ratio falls in lockstep. Fix: schedule heavy background work off-peak or use a server-side accelerator (pg_prewarm, pg_repack).
5. Cache stampede on a hot key. A cold application cache (Redis) sees a hot key expire; every application instance fetches it from the database; the buffer pool is overwhelmed by the same blocks. Symptom: Redis hit ratio falls (the application is bypassing the cache); database hit ratio falls (the application is bypassing the buffer pool); eviction rate spikes. Fix: TTL jitter or single-flight on the application side.

How to troubleshoot it

Diagnose in this order; each step is cheaper than the next.

1. Read the live hit ratio. If it has not fallen, this is not the lesson’s incident.
2. Read the eviction rate. A spike is the cause; a flat rate is something else.
3. Read pg_stat_io to see the per-backend-type counters: the eviction is the background writer (bgwriter) or the autovacuum worker, not user queries. The lesson in Disk Latency Observability expands this.
4. Read the OS page cache hit rate. If the page cache hit ratio is high while the buffer pool hit ratio is falling, the buffer pool is too small but the OS is absorbing the cost. The database is paying the latency tax; the disk is not paying the I/O tax.
5. Identify recent changes. A deployment that introduced a new query against a large table; a batch job that ran against the same table; a fail-over minutes ago. The cause is almost always one of these.
6. Compare against the 24-hour baseline. A “5-minute hit ratio of 99%” looks healthy but is unhealthy if the 24-hour baseline was 99.7%. The recording rule pg:database:buffer_hit_ratio:24h exposes the baseline.

Security implications

The buffer pool is not a security boundary, but it does expose two surfaces.

• Buffer-pool contents and side channels. A cold cache read by an attacker timing block access can leak information about which blocks are hot or cold. Bind the exporter to an internal address and rate-limit; treat the database’s internal cache structure as implementation detail.
• pg_stat_io, pg_stat_database exposure. The counters are aggregate; they do not leak data. The endpoint is still a confidential service surface.

Performance implications

Performance comes from three levers:

• Sizing. shared_buffers and innodb_buffer_pool_size are the dominant levers. Both are bound by host RAM and by the OS page cache budget. A 64 GiB host with a 32 GiB buffer pool is over-sized on PostgreSQL; the OS page cache will not fit.
• Eviction policy. PostgreSQL uses a clock-sweep algorithm; MySQL’s InnoDB uses a variation of LRU with a midpoint insertion. Both do well for steady workloads and poorly for cache stampedes; the lesson on caches covers this in detail.
• Pre-warming. pg_prewarm, warm-up scripts, or a --shared-buffers filled from a saved file. Used at fail-over or after restart; expensive to maintain; cheap in the steady state.

The cost of an oversized buffer pool is host memory pressure that pushes the OS page cache out. The cost of an undersized buffer pool is I/O that no other cache absorbs.

Production guidance

• Always pair shared_buffers with the OS page cache budget. Don’t size the database buffer pool at the expense of the OS page cache.
• Use a 5-minute recording rule for hit ratio; use a 24-hour recording rule as the baseline; alert on the delta, not on the level.
• Size shared_buffers and innodb_buffer_pool_size to the working set, not to the database size.
• Pre-warm after a fail-over or a migration. The first minutes of traffic on a cold cache is a guaranteed slowdown.
• Set pg_prewarm to a startup-time operation where the data is well-known; avoid query-time prewarming.

Verification

You should now be able to answer:

• What is the difference between a buffer pool hit and an OS page cache hit, and why does it matter for the reported ratio?
• What is the right alerting metric for buffer pressure?
• What are the four common cache failure shapes?
• Why is hit ratio a leading indicator of slow queries rather than a lagging one?
• What is the cost of over-sizing the buffer pool on a Linux host?

Quiz