Safe way to clean up 32M orphan visibility rows left by #9784 (1.25.2, MySQL)

Community Support Server Deployment
1

Environment

  • Temporal Server 1.25.2, self-hosted on Kubernetes
  • Standard (SQL) visibility on MySQL 8, same instance as the main temporal database
  • ~460 shards, 4 history replicas, maxConns: 100 per datasource

What happened

We hit #9784 — DeleteFromVisibility
in the MySQL plugin opens a transaction with db.BeginTxx() but runs its DELETE statements
on the pool, so each delete needs two connections at once and the pool deadlocks. Every
VisibilityTaskDeleteExecution fails with context deadline exceeded against the hardcoded
3s taskTimeout.

Because the failing statement is the first one (custom_search_attributes), the
executions_visibility DELETE never runs. Retention deletes the history and mutable state,
but the visibility row survives.

This has been happening since at least December 2024. Current state:

table rows data indexes
executions_visibility ~32.2M 34.8 GB 225.8 GB (22 indexes)
custom_search_attributes ~32.8M 15.8 GB 177.7 GB (28 indexes)

Only ~1.7M of those rows are live (running executions plus the last few days of closed
ones). The rest are orphans — temporal workflow describe returns not found for all of
them, including samples from 2024, 2025 and 2026.

What we tried

Lowering history.visibilityProcessorSchedulerWorkerCount from the 512 default to 20 breaks
the deadlock immediately — delete errors went to zero and deletions went from ~0 to 200/s.

But at 200/s the instance saturates: 3,600 read IOPS, 3,900 write IOPS, 82% CPU, and
GetWorkflowExecution on the main path starts failing. Index maintenance across 50 indexes,
32 million times, is simply more work than this instance can absorb. We rolled back.

Worker count turned out to be close to binary: 5 workers still produced ~190/s, and 1 worker
produced ~0. We found no setting in between that gives a sustainable rate.

What we are planning

Instead of deleting 32M rows, rebuild both tables and swap:

  1. CREATE TABLE ... LIKE for both tables
  2. Scale temporal-history to 0 (the only writer of visibility)
  3. INSERT ... SELECT the ~1.7M live rows, one statement per namespace, using
    FORCE INDEX (default_idx) with
    COALESCE(close_time, '9999-12-31 23:59:59') > UTC_TIMESTAMP() - INTERVAL 8 DAY
  4. RENAME TABLE all four atomically
  5. Scale history back up

Estimated window: under an hour.

Questions

  1. Is swapping these two tables underneath a stopped history service safe? Does the
    server hold any state that assumes continuity of those tables, beyond what lives in the
    shard records?

  2. What happens to the ~32M queued DeleteExecutionVisibilityTask after the swap? Our
    reading of 1.25.2 is that they succeed as no-ops: VisibilityStore.DeleteWorkflowExecution
    discards the sql.Result and only checks the error, and a DELETE matching zero rows is not
    an error. So the tasks should ack and drain. Is that right?

  3. Is there a supported way to drop or skip those queued tasks in bulk? tdbg shard remove-task works one task at a time, which is not practical at this volume. We did not
    find a command to advance a queue’s ack level.

  4. Is anything other than the Web UI and the CLI reading these tables? We confirmed our
    own services never query visibility, but we would rather not discover a server-side
    consumer during the window.

We know the real fix is upgrading to ≥ 1.31.0 and that is planned separately. The question
here is whether this cleanup approach is sound in the meantime, or whether we are missing
something.

2

Is swapping these two tables underneath a stopped history service safe?

Yes, would check tho whats your namespace retention period which dont know what it is and if you have multiple namespaces running on cluster whats retention period of which one. You might want to do cutoff based on ns retention + some margin for each ns. Might be good idea cause if you just do cutoff you may be missing vis entries in ui (workflow list) for example.

Just fyi tho, swap wont fix deadlock - opening the tx itself takes a conn if or not the delete matches a row. So basically youd end up with clean table and queue that still cant process…

**What happens to the ~32M queued DeleteExecutionVisibilityTask after the swap?
Is there a supported way to drop or skip those queued tasks in bulk?
**

Yeah i think your reading is right but we should look if those tasks are actually still queued up or not first.
This important cause server has history.TaskDLQUnexpectedErrorAttempts set to 70 by default and on 70 continuous task processing failures this would DLQ the task, not park it in queue so queue reader could advance past it if DLQed. If thats the case there really is no 32 mill backlog so would be good to know.
Some of your readings is a bit interesting where idk if really backlog or not.

So pls lets do that first - 3 checks -
run

tdbg dlq list

from metrics:

sum(rate(dlq_writes{task_category="visibility"}[$__rate_interval]))

from your db:

SELECT table_schema, table_name, table_rows,

ROUND(data_length /1024/1024, 1) AS data_mb,

ROUND(index_length/1024/1024, 1) AS index_mb

FROM information_schema.tables

WHERE table_name IN ('visibility_tasks','queue_messages',

'executions_visibility','custom_search_attributes')

ORDER BY data_length + index_length DESC;

and report results pls

**Is anything other than the Web UI and the CLI reading these tables?

Yep, frontend, matching and some background cleanup system workflows. Noone writes

For server upgrades

You could also consider patching your server version yourself - basically apply the github issue small changes and rebuild server image, but ya this will prevent new “orphans” but wont deal with existing ones if there are really.

We found no setting in between that gives a sustainable rate.

i think you were looking for system.visibilityPersistenceMaxWriteQPS default 9000 and its per history pod. so you could try lowering that, throttling would produce retryable errors, so not count within history.TaskDLQUnexpectedErrorAttempts

Also pls share your full dynamic config if possible.

3

Thanks @tihomir. Here are the checks you asked for.

Table sizes

schema table rows data index
temporal_visibility executions_visibility 32,233,211 34.8 GB 225.8 GB
temporal_visibility custom_search_attributes 32,460,316 15.8 GB 177.7 GB
temporal visibility_tasks 144,588,475 34.7 GB —
temporal queue_messages 1,407,618 385 MB —

DLQ

tdbg dlq list

QUEUENAME MESSAGECOUNT
4_active_active_B8jqXXpO 1414013
1_active_active_B8jqXXpO 10833
2_active_active_B8jqXXpO 6968

Queued or DLQed

Both. 1.414.013 visibility tasks are in the DLQ, and visibility_tasks still holds
144.588.475 rows.

DLQ write metric

dlq_writes shows no samples over the last 7 days, so nothing has been enqueued to the
DLQ recently — the 1.41M accumulated earlier. Our metrics pipeline only goes back to
August 2026, so it does not cover the period when the DLQ filled.

Also worth noting: task_dlq_failures has no data at all, so no DLQ write has ever
failed. The DLQ works on MySQL, despite what the description of history.TaskDLQEnabled
suggests.

We have nothing in dynamic config

4

visibility_tasks — 144,588,475 rows, 34.7 GB.

Thanks for all this info - for ^^ yeah it does look like oldest unfinished task really has been basically failing since Dec 2024 on this cluster so nothing was removed since that time.
Backlog here isnt 34 million tasks, its size, its more like 145 million tasks, but note again these are tasks that completed, so this work happened, but it just never got cleaned up.
The thing tho for size, its growing, since watermark never moved since Dec 2024.

Yeah 1.4K vis tasks got dead lettered, but honestly thats only like 4% of your 30+mill delete tasks.

Recommendations

I’d patch the binary for that github issue and deploy. This I think becomes really important now for you if you cannot upgrade to when this fix for issue was released version. Atm task delete cannot complete that adds as mentioned visibility_tasks table otherwise it will keep growing.

Also you can keep your rebuild but do a cutoff per namespace retention duration. This can reduce size. What i mean per ns retention - your INTERVAL 8 DAYmight not be right num for every ns

With patch in binary and your table rebuild things should start draining, you should be able to pace the drain via history.visibilityProcessorSchedulerWorkerCount.
Once its clear you can purge the dlqed vis tasks

tdbg dlq purge --dlq-type 4

From your answer there is also some other task type in dlq we maybe should look into.
Please show

tdbg dlq read --dlq-type 1 --max-message-count 20

tdbg dlq read --dlq-type 2 --max-message-count 20
5

hi @tihomir thanks a lot for your help, here is what we got from dlq

--dlq-type 1 (oldest 20): all TASK_TYPE_TRANSFER_WORKFLOW_TASK. 19 of 20 inside one 9-minute window, 2025-03-19 21:34–21:43 UTC; the other is 2025-03-16. 18 distinct shards, 18 in automation and 2 in default.

--dlq-type 2 (oldest 20): all TASK_TYPE_ACTIVITY_RETRY_TIMER, same window (21:36–21:47 UTC), all in automation. schedule_attempt from 57 to 441.

Both reads start at message_id 0, so this is the oldest end only.

We looked up four of these workflows by (namespace_id, run_id): all four are TERMINATED, closed 2025-03-28 to 2025-04-01. Someone here terminated them a week or two after the incident.

The queue is draining

We are running history.visibilityProcessorSchedulerWorkerCount: 20 on the unpatched 1.25.2, and it is working.

  • ~309 deletes/s in executions_visibility. Dropping to 3 workers only takes it to ~229/s, so at that scale the database is the constraint, not the worker count.
  • ~33.5M orphan rows at the start, ~27.3M now.
  • MIN(task_id) per shard moved from ~1B to 17–20B overnight, on all 20 shards we sampled. Per-shard row counts in visibility_tasks are still 240–320k, so most of that table is still waiting to be swept behind the watermark, but it is no longer stuck.

Our reading is that the bug only bites at high concurrency: each delete needs two connections, our pool is maxConns: 100, and the default worker count is 512 — so it deadlocks and tasks die on the 3s taskTimeout. At 20 workers, 40 connections fit and the deletes complete. That is consistent with nothing having drained since Dec 2024 at the default.

Because of that we would rather not maintain a patched build. We want to drain first, then upgrade to a version that already has the fix.

Questions

  1. Since those DLQ workflows are already terminated, merge on types 1 and 2 should be a no-op, which would make purge right for them too. Does that hold?
  2. Is our reading of the bug correct — can we drain to completion unpatched, just by keeping the worker count low?
  3. Afterwards, is the normal delete rate low enough that unpatched is safe until we upgrade?
  4. This now rests on a dynamic config value. If it goes back to 512, the deadlock returns. Anything else of that kind we should be watching?
6

The queue is draining

Yeah setting history.visibilityProcessorSchedulerWorkerCount to 20 removed the cause, with 20 workers means 40 conns (without the fix) vs your 100 conn pool.

Would probably look at lowering this to 3-5 workers, not 20. Will take a bit longer to drain but is safer imho.

One thing I dont fully get, initially you said

But at 200/s the instance saturates: 3,600 read IOPS, 3,900 write IOPS, 82% CPU, and
GetWorkflowExecution on the main path starts failing

now you mentioned

~309 deletes/s in executions_visibility

and you dont see issue. just making sure you checked db iops and cpu with 20 workers to make sure its all good now (still would lower to 3-5 just fyi)

Both reads start at message_id 0, so this is the oldest end only.

Yeah sorry wanted to see tail end but it wasnt too useful. Can see:

tdbg dlq read --dlq-type 1 --max-message-count 11000 --output-filename transfer-dlq.json
tdbg dlq read --dlq-type 2 --max-message-count 7000 --output-filename timer-dlq.json

so newest and wanna check if those workflow ids are still for running executions or not. If is, then we know if merging tasks from dlq instead of purging can help.

For your questions:

Since those DLQ workflows are already terminated, merge on types 1 and 2 should be a no-op, which would make purge right for them too. Does that hold?

Yes but think checked only like 20 out of 17K messages. If we can make sure its all from march 25 then purge would be ok but merge i think still safer option

Is our reading of the bug correct — can we drain to completion unpatched, just by keeping the worker count low?

Yes

Afterwards, is the normal delete rate low enough that unpatched is safe until we upgrade?

Yes

This now rests on a dynamic config value. If it goes back to 512, the deadlock returns. Anything else of that kind we should be watching?

Id honestly look at a custom dyn config provider going forward..you can see samples

and

And have a default dyn config values that are used when cluster starts up.
Downside is you have to use custom image build, so if dont want to, put dyn config in your custom version control system thats fine too, whatever works.
Id also look at server dashboard and server alerts in GitHub - tsurdilo/temporal-server-operations: Temporal metrics docs and skills · GitHub (you can also look at current playbooks and pls also give suggestions for new ones so can put them on todo list)
to get ideas from and see if can create alert on burst of deletes. think your are looking for
task_attemptmetric for operation VisibilityTaskDeleteExecution(looking for bursts over time)

7

Reading the tail end changed the answer. Thanks for pushing on it. The oldest 20 messages were not representative.

Full queues: 10,833 transfer + 6,968 timer = 17,801 messages, in four episodes (2025-03, 04, 06 and 10). The newest message in either queue is 2025-10-02, so nothing has been dead-lettered in the twelve months since.

We checked every run_id against executions_visibility, and 4,114 came back RUNNING — 2,892 from the timer queue, 1,222 from the transfer queue. workflow describe showed two shapes:

Timer queue: an activity stuck in Scheduled, e.g. Attempt 289 of 500, scheduled 2 years ago. Our retry policy caps the interval at 1 hour, so those attempts should have spanned about 12 days. The activity was not retrying at all — its retry timer was in the DLQ at schedule_attempt: 289, the same number. The workflow could neither progress nor fail.

Transfer queue: nothing pending at all, and StateTransitionCount between 32k and 124k against HistoryLength of 17 to 51. The workflow task had been retried tens of thousands of times before being dead-lettered.

All 4,114 turned out to be executions that were safe to close, so we terminated them rather than merging — each with a reason string recording why. With those closed, every message in both queues was a dead reference, so we purged:

tdbg dlq purge --dlq-type 1
tdbg dlq purge --dlq-type 2

tdbg dlq list now shows both at zero. Type 4 stays as it is until the visibility drain finishes.

On your IOPS and CPU question

The missing piece is that the database is not the same machine. We resized the instance before this run, specifically to get through the backlog. That is why 309/s does not saturate it now where 200/s did before.

It is still working hard: CPU sat at 70–80% - around 5,000 read and 5,200 write IOPS.

We took your advice anyway and dropped to 3 workers during business hours. That only took the rate from ~309/s to ~229/s, which says the database is the constraint at this scale rather than the worker count — the extra 17 workers were buying very little. We will leave it at 3–5 from here.

One note for anyone landing here: checking 17k run_ids against executions_visibility needs FORCE INDEX (PRIMARY) and chunks of ~1,000. Without forcing it, the optimizer picked a full scan of 24M rows :grimacing:.

8

Correct, activity retries managed via timer task, and when this one gets dead lettered the activity retry dont fire. Don’t have a direct playbook for this yet, but worked with users before that ran into situation so one is on the way. Will link when done.

For type 4 merging is safe, so id merge and not purge.
The alert for your server version should be

sum(rate(dlq_writes{task_category=~"transfer|timer"}[5m])) > 0

Ok so finding 4K running and stuck executions on top of everything else - great job. Hope this thread was helpful.
Great catch on FORCE INDEX too.

9

Thank you for your help, @tihomir.

We have finished processing the backlog of deletions.

We should schedule an upgrade to the Temporal Server soon. For now, we will keep visibilityProcessorSchedulerWorkerCount at 3, which should be sufficient to handle new deletions as they come in.