Metrics refresh full-scans okapi_outbox (COUNT(*) GROUP BY status incl. 7 days of DELIVERED) every 15s per instance

未關閉
  • 哪裡不對？
#136 0 則留言 0 個 reaction 已指派 0 人 在 GitHub 檢視

維護者通常 1 天內回覆

還沒有人認領這個 Issue。

評估

難度
4/5
預估耗時
1-2 天
新手友好度
42/100
Issue 類型
重構
描述清晰度
基本清楚
活躍度
活躍
技術堆疊
kotlin, mysql, postgresql
領域
databases, observability

研究方向

Start in MicrometerOutboxMetrics, where the refresh runs the two queries, and in OutboxStore.countByStatuses and findOldestCreatedAt, which hold the GROUP BY status SQL. Decide which statuses the metrics need, then rewrite the oldest-per-status lookup as an index-ordered LIMIT 1 query per status. Done when the queries no longer scan DELIVERED rows and the metrics tests still pass on both PostgreSQL and MySQL.

由索引模型根據 Issue 內容生成。

描述

enhancement

Severity: Medium (performance) · Area: okapi-micrometer MicrometerOutboxMetrics, OutboxStore.countByStatuses/findOldestCreatedAt · Found in: code review of main @ e5630a1

Problem

Every metrics refresh runs two queries per instance, every 15 s by default:

SELECT status, COUNT(*) FROM okapi_outbox GROUP BY status;
SELECT status, MIN(created_at) FROM okapi_outbox WHERE status IN (?,?,?) GROUP BY status;
  • COUNT(*) ... GROUP BY status has to visit every row. The table keeps 7 days of DELIVERED rows by default (purger retention), which is ~60 M rows at 100 msg/s. N application instances each run this full scan every 15 s.
  • The MIN(created_at) query is index-friendly on MySQL (loose index scan), but Postgres before v18 has no skip scan. GROUP BY status with IN can scan the whole (status, created_at) index, again dominated by DELIVERED rows nobody alerts on.

Load grows with retention × throughput × instance count, on the same primary that serves the application's writes.

Suggested fix

  • Restrict both queries to the statuses that matter operationally (PENDING, FAILED). DELIVERED count/lag is rarely useful, and when it is, it can come from the counters.
  • Compute oldest-per-status with per-status index-ordered lookups, e.g. (SELECT created_at FROM okapi_outbox WHERE status='PENDING' ORDER BY created_at LIMIT 1), which costs O(log n) on both databases.
  • Optionally report DELIVERED from planner estimates or drop it.
主要語言
Kotlin
星號
14
分支
0
平均合併
9 小時 54 分鐘
30 天內合併 PR
11

環境準備

這個專案沒有提供開發容器、Dockerfile 或貢獻指南，環境需要你自己搭建：先看它的 README，通用步驟見我們的新手貢獻指南。

從這裡開始

  1. 先讀完整個 Issue，再讀專案的貢獻指南。
  2. 在 Issue 下留言說明你要接手 —— 這能避免兩個人做同樣的事。
  3. Fork 儲存庫，在一個分支上完成修改。
  4. 送出 Pull Request，並在描述裡引用這個 Issue 編號。

softwaremill/okapi 的其他 Issue

查看 softwaremill/okapi 的全部 Issue

相似的 Issue

更多 Kotlin Issue

把新 issue 寄到你的電子郵件信箱

精選適合新手參與的 GitHub issue 摘要。