fix(usage): batch ClickHouse writes like cloud by loks0n · Pull Request #13764 · appwrite/appwrite

loks0n · 2026-09-18T15:26:11Z

What does this PR do?

Standardizes self-hosted's usage write path on the pattern appwrite-labs/cloud already runs, and opens the worker up so cloud can extend it instead of copying it. The self-hosted port in #13286 flushed a fresh Accumulator per queue message, producing one synchronous ClickHouse insert — and one ClickHouse part — per completed API request. The resulting part/merge churn cost ~15% RPS on the benchmark (analysis on the project board item Investigate benchmark regressions in PRs merged Aug 18 – Sep 18).

Three commits:

Batch writes like cloud. Worker-scoped accumulator flushed on a threshold/interval instead of per message, and asyncInserts: true, asyncInsertWait: false on the ClickHouse adapter (matching cloud's app/worker.php) so parts coalesce server-side. One deliberate improvement over cloud's copy: the accumulator is detached before flushing, since flush() yields on the insert and the worker runs stats-usage at 8 coroutines — flushing the shared buffer directly can double-write a snapshot (cloud's copy has this latent race).
Env-driven flush policy. _APP_USAGE_FLUSH_THRESHOLD (default 10000; 1 in development so local and E2E reads see usage at once) and _APP_USAGE_FLUSH_INTERVAL (default 20s), documented in app/config/variables.php.
Seams for cloud. Cloud's StatsUsage duplicates this worker (~300 lines) to add premium geo tags and resource-path parsing — porting that copy is how the per-message flush shipped in the first place. The worker now exposes narrow protected hooks (normalizeMetric(), resolveGeoTags(), extracted flush(), protected timestamp()) so the cloud class becomes a thin subclass. Companion cloud PR (draft, depends on this one): appwrite-labs/cloud#5954.

Semantics

Usage delivery stays best-effort (unchanged): a worker crash now loses at most one flush window, and entries retained by a failed flush are dropped with the detached buffer, consistent with the existing no-retry policy.

Test plan

composer lint and composer analyze pass on the changed files.
tests/e2e/Services/Usage reads via assertEventually, and development mode keeps per-message flushing, so E2E behavior is unchanged.
CI's benchmark job should show the RPS recovery; comparing against the feat: add self-hosted usage service #13286 numbers (233.7 → 198.7 RPS) is the acceptance check.

Related PRs and issues

Regression introduced in feat: add self-hosted usage service #13286
Companion: appwrite-labs/cloud#5954 (draft) extending this worker

🤖 Generated with Claude Code

The self-hosted usage port flushed a fresh accumulator per queue message, producing one synchronous ClickHouse insert (and one part) per API request. On the benchmark host the resulting merge churn cost ~15% RPS (#13286). Standardize on cloud's write path: a worker-scoped accumulator flushed at 10,000 entries or every 20 seconds (per message in development, so local and E2E reads stay immediate), and async inserts on the adapter so ClickHouse coalesces parts server-side. The accumulator is detached before flushing so concurrent worker coroutines cannot double-write a snapshot. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

greptile-apps · 2026-09-18T15:30:00Z

Confidence Score: 4/5

The PR is not yet safe to merge because buffered usage can remain unflushed indefinitely when traffic stops before the threshold is reached.

Findings

Interval Requires Another Message ▶
Flush Settings Never Reach Workers ▶

Fix with agent prompt

### Issue 1
src/Appwrite/Platform/Workers/StatsUsage.php:149-159
The 20-second interval is checked only while processing a queue message. If production traffic stops with fewer than 10,000 unique entries buffered, the deadline does not trigger a flush: usage remains unavailable until another message arrives and is lost if the worker exits first. The aged accumulator needs to be flushed independently of later traffic.

### Issue 2
app/config/variables.php:1858-1875
The new settings are read from the worker process environment, but neither the combined nor separate Docker Compose worker environment forwards them. On a normal self-hosted deployment, setting `_APP_USAGE_FLUSH_THRESHOLD` or `_APP_USAGE_FLUSH_INTERVAL` therefore has no effect, so usage flushing silently keeps its in-container defaults.

---

For each issue above, determine whether it is valid and should be fixed. If so, fix it directly.

Summary

Standardizes usage ingestion around a worker-scoped accumulator and asynchronous ClickHouse inserts to reduce write-part churn.

Adds threshold- and interval-based accumulator flushing with extension hooks for cloud-specific metric normalization and geo enrichment.
Adds environment-driven flush settings and now forwards them through the combined worker service; the separate usage worker inherits that environment.
Enables fire-and-forget asynchronous ClickHouse inserts.
The prior environment-forwarding finding is fixed, but the previously reported idle-buffer issue remains outstanding because interval checks still occur only while processing messages.

_{Reviews (3) · Last reviewed commit: "fix(usage): pass flush env vars to the w..."}

greptile-apps · 2026-09-18T15:30:08Z

+            if ($accumulator->count() >= $this->flushThreshold() || $accumulator->elapsedSeconds() >= self::FLUSH_INTERVAL) {
+                // Detach before flushing: flush() yields on the insert, and the
+                // worker runs several coroutines — another message reaching this
+                // point mid-flush must not snapshot (and double-write) the same
+                // entries. Entries a failed flush retains are dropped with the
+                // detached buffer, consistent with the no-retry policy below.
+                $this->accumulator = null;
+                if (!$accumulator->flush()) {
+                    Console::error('Usage event flush returned false');
+                }
            }


Interval Requires Another Message

The 20-second interval is checked only while processing a queue message. If production traffic stops with fewer than 10,000 unique entries buffered, the deadline does not trigger a flush: usage remains unavailable until another message arrives and is lost if the worker exits first. The aged accumulator needs to be flushed independently of later traffic.

Knowledge Base Used: Background processing and scheduling

Prompt To Fix With AI

This is a comment left during a code review. Path: src/Appwrite/Platform/Workers/StatsUsage.php Line: 149-159 Comment: **Interval Requires Another Message** The 20-second interval is checked only while processing a queue message. If production traffic stops with fewer than 10,000 unique entries buffered, the deadline does not trigger a flush: usage remains unavailable until another message arrives and is lost if the worker exits first. The aged accumulator needs to be flushed independently of later traffic. **Knowledge Base Used:** [Background processing and scheduling](https://app.greptile.com/appwrite/-/custom-context/knowledge-base/appwrite/appwrite/-/docs/background-processing.md) --- For each issue above, determine whether it is valid and should be fixed. If so, fix it directly.

github-actions · 2026-09-18T15:33:11Z

✨ Benchmark results

Comparing main (before) → fix/usage-write-batching (after).

Metric	Before	After	Change
🚀 Requests/sec	`260.62`	`226.53`	🔴 `-13.1%`
⏱️ Latency P50	`67.2 ms`	`76.31 ms`	🔴 `+13.6%`
⏱️ Latency P95	`155.64 ms`	`178.45 ms`	🔴 `+14.7%`

Per-scenario breakdown & investigation details

Metrics below reflect the current branch (after). Δ P95 compares against the base.

Scenario	P50 (ms)	P95 (ms)	Requests	RPS	Δ P95 (ms)
API total	`76.31`	`178.45`	`14,079`	`226.53`	`+22.81`
Account	`140.59`	`272.25`	`741`	`12.29`	`+40.29`
TablesDB	`73.89`	`142.51`	`7,657`	`125.17`	`+19.66`
Storage	`69.37`	`156.69`	`3,705`	`62.38`	`+15.76`
Functions	`107.84`	`219.39`	`1,976`	`33.95`	`+27.79`

Top API waits (after)

API request	Max wait (ms)
functions.variables.update	`421.59`
account.name.update	`419.09`
account.prefs.update	`393.38`
functions.create	`337.22`
storage.buckets.create	`323.77`

Replace the fixed flush constants with _APP_USAGE_FLUSH_THRESHOLD and _APP_USAGE_FLUSH_INTERVAL. Thresholds default to 10000 entries / 20s; development defaults to flushing every message so reads stay immediate. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

Cloud's StatsUsage duplicates this worker (~300 lines) to add premium geo tags and resource-path parsing; a port of that copy is how the per-message flush regression shipped. Give the worker the narrow protected seams the cloud subclass needs — normalizeMetric() and resolveGeoTags() no-op hooks, an extracted flush(), and a protected timestamp() — so cloud can extend instead of copy. No behavior change on self-hosted. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

greptile-apps · 2026-09-18T15:41:30Z

+            [
+                'name' => '_APP_USAGE_FLUSH_THRESHOLD',
+                'description' => 'Number of buffered usage entries that triggers a ClickHouse write. Defaults to 10000, or 1 in development so reads see usage at once.',
+                'introduction' => '',
+                'default' => '10000',
+                'required' => false,
+                'question' => '',
+                'filter' => ''
+            ],
+            [
+                'name' => '_APP_USAGE_FLUSH_INTERVAL',
+                'description' => 'Maximum age in seconds of buffered usage entries before they are written to ClickHouse.',
+                'introduction' => '',
+                'default' => '20',
+                'required' => false,
+                'question' => '',
+                'filter' => ''
+            ],


Flush Settings Never Reach Workers

The new settings are read from the worker process environment, but neither the combined nor separate Docker Compose worker environment forwards them. On a normal self-hosted deployment, setting _APP_USAGE_FLUSH_THRESHOLD or _APP_USAGE_FLUSH_INTERVAL therefore has no effect, so usage flushing silently keeps its in-container defaults.

Knowledge Base Used: Platform configuration and policies

Prompt To Fix With AI

This is a comment left during a code review. Path: app/config/variables.php Line: 1858-1875 Comment: **Flush Settings Never Reach Workers** The new settings are read from the worker process environment, but neither the combined nor separate Docker Compose worker environment forwards them. On a normal self-hosted deployment, setting `_APP_USAGE_FLUSH_THRESHOLD` or `_APP_USAGE_FLUSH_INTERVAL` therefore has no effect, so usage flushing silently keeps its in-container defaults. **Knowledge Base Used:** [Platform configuration and policies](https://app.greptile.com/appwrite/-/custom-context/knowledge-base/appwrite/appwrite/-/docs/platform-configuration.md) --- For each issue above, determine whether it is valid and should be fixed. If so, fix it directly.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

greptile-apps Bot reviewed Sep 18, 2026

View reviewed changes

loks0n and others added 2 commits September 18, 2026 16:35

greptile-apps Bot reviewed Sep 18, 2026

View reviewed changes

fix(usage): pass flush env vars to the worker container

7c75e10

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

Navigation Menu

fix(usage): batch ClickHouse writes like cloud - #13764

fix(usage): batch ClickHouse writes like cloud#13764
loks0n wants to merge 4 commits into
mainfrom
fix/usage-write-batching

loks0n commented Sep 18, 2026 •

edited

Loading

Uh oh!

greptile-apps Bot commented Sep 18, 2026 •

edited

Loading

Uh oh!

greptile-apps Bot Sep 18, 2026

Uh oh!

github-actions Bot commented Sep 18, 2026 •

edited

Loading

Uh oh!

greptile-apps Bot Sep 18, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Conversation

loks0n commented Sep 18, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What does this PR do?

Semantics

Test plan

Related PRs and issues

Uh oh!

greptile-apps Bot commented Sep 18, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Confidence Score: 4/5

Findings

Summary

Uh oh!

greptile-apps Bot Sep 18, 2026

Choose a reason for hiding this comment

Uh oh!

github-actions Bot commented Sep 18, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

✨ Benchmark results

Uh oh!

greptile-apps Bot Sep 18, 2026

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

loks0n commented Sep 18, 2026 •

edited

Loading

greptile-apps Bot commented Sep 18, 2026 •

edited

Loading

github-actions Bot commented Sep 18, 2026 •

edited

Loading