tursodatabase / tursodatabase/libsql
deletion of records causes a huge spike in CPU and locks up the database for a while
Nobody has claimed this yet.
- Dominant language
- C
- Stars
- 17.2k
- Forks
- 531
- Avg merge
- 1h 12m
- Merged PRs (30d)
- 1
Description
Hello,
I am using sqld to dump some events into a table. Once every 6 hours, I delete events older than 3 days. That causes about 20k records to get deleted. However, once in a while, the delete operation takes a long time to return (sometimes minutes). In that time, CPU spikes to 100% and no other client can do anything with the database. After the spike subsides, disk utilization drops.
What could be causing this?
From what I read, it can either be VACUUM or checkpointing that could be causing this.
But, I think sqld disables VACUUM. So is it checkpointing that's causing this? If so, how to avoid these lockups?
thanks
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the sqld workload described: periodically delete events older than three days and observe CPU, locks, disk activity, VACUUM, and checkpointing. Determine which operation causes the lockup and document a way to avoid it. No files or tests are named, so the investigation must first locate the relevant deletion and database-maintenance paths.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100