a month ago
Postgres hitting waves of extreme disk I/O latency for the past 3-4 days. evidence points at the storage host. Details below...
Service info
Project: 25d9b7d2-8e29-4d0d-b5bd-54a8709e7891 (capable-purpose)
Service: 222a9593-a45b-488a-b2c9-42e5f2b59620 (Postgres, postgres-ssl:16 image, PG 16.14)
Environment: 6855203f-8fbf-43a3-a0c9-ba45f43ffce2 (production), US East
Volume: coil-volume
Public endpoint: maglev.proxy.rlwy.net:52625
What we're seeing
During a bad wave, a single file fsync takes up to 85 seconds and a checkpoint flushing under 2 MB takes 5 to 9 minutes. Minutes later the same operations take milliseconds, then it degrades again. From the service deploy logs on Jul 8 (UTC):
checkpoint complete: wrote 85 buffers (0.5%); sync=278.376 s, total=329.785 s; sync files=88, longest=36.090 s
checkpoint complete: wrote 58 buffers (0.4%); sync=226.856 s, total=337.443 s; sync files=80, longest=40.539 s
checkpoint complete: sync=327.931 s, total=536.013 s; sync files=84, longest=84.754 s
19:16 checkpoint complete: wrote 14 buffers; sync=0.085 s, total=12.462 s (healthy)
19:21 checkpoint complete: wrote 249 buffers; sync=2.565 s, total=115.914 s (degrading again)
Same workload, latency swinging by 1000x. That pattern sits below Postgres, at the disk layer.
What we've ruled out on our side
The database is 8.2 GB, CPU is near zero, memory is normal, around 120 connections, stock template config (shared_buffers 128MB, checkpoint_timeout 5min). There are no write bursts and no app deploys that line up with the onset. The instance ran clean for the first 10 days after its Jun 23 restart. pg_stat_bgwriter shows 4,457 checkpoints since then with 67 total minutes of sync time, and nearly all of that accrued during these recent waves.
Our production API stalls during waves. Every COMMIT blocks on the WAL fsync, the connection pool saturates, and all requests slow to 1s+ per query. Please check the storage host backing coil-volume for degradation or a noisy neighbor, and migrate the volume to a healthy node. We can take a restart or maintenance window at any time, just tell us when. Known wave windows so far, Jul 8 UTC: roughly 17:30 to 19:10, recurring intermittently since.
2 Replies
a month ago
This thread has been opened as a bounty so the community can help solve it.
Status changed to Open Railway • about 1 month ago
a month ago
The checkpoint logs are the strongest indicator here. Syncing less than 2 MB but spending several minutes in fsync points away from query performance and toward the storage path.
To strengthen that conclusion, capture pg_stat_io (PG16) and pg_stat_checkpointer during both a healthy period and a bad wave. Compare the checkpoint sync times, WAL write latency, and backend fsync activity. If only the sync phase spikes while the workload stays the same, that helps isolate the issue below PostgreSQL.
Also check whether the stalls line up with autovacuum or checkpoints by enabling log_checkpoints (if it isn't already) and comparing the timestamps. That will help rule out workload-driven spikes before looking elsewhere.
dawinz
The checkpoint logs are the strongest indicator here. Syncing less than 2 MB but spending several minutes in fsync points away from query performance and toward the storage path. To strengthen that conclusion, capture pg_stat_io (PG16) and pg_stat_checkpointer during both a healthy period and a bad wave. Compare the checkpoint sync times, WAL write latency, and backend fsync activity. If only the sync phase spikes while the workload stays the same, that helps isolate the issue below PostgreSQL. Also check whether the stalls line up with autovacuum or checkpoints by enabling log_checkpoints (if it isn't already) and comparing the timestamps. That will help rule out workload-driven spikes before looking elsewhere.
a month ago
log_checkpoints is already on, thats where the sync numbers came from. pg_stat_checkpointer is PG17, this is 16.14 (pg_stat_bgwriter shows 4460 checkpoints since Jun 23, ~67 min total sync time, almost all from this week). pg_stat_io is empty, track_io_timing off and no superuser. Autovacuum ruled out already, the stalling checkpoints flush <2MB.
Anyway went below postgres. Sampled /proc/diskstats in the container every 25s, volume is a ZFS zvol. Caught a wave at 20:30 UTC:
20:29:06-20:30:26 our zvol 0.2-0.6ms avg write | host 1-5ms | 0 other zvols >100ms
20:30:26-20:30:52 our zvol 20.7ms | host 67.6ms | 8 other zvols >100ms
20:30:52-20:31:19 our zvol 0.1ms | host 192ms | 9 other zvols >100ms
20:31:19-20:31:45 our zvol 3,140ms | host 1,015ms | 2 other zvols >100ms1000x write latency inflation in under a minute, hitting multiple volumes on the host at once, host throughput down 10x. Not postgres, not our workload. Degraded storage host, i think needs a volume migration from Railway's side.