deletion of records causes a huge spike in CPU and locks up the database for a while

Open
#2,203 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 11 days

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
25/100
Issue type
Bug
Clarity
Needs clarification
Activity status
Stale
Tech stack
sql
Domain
databases

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.

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

Dominant language
C
Stars
17.3k
Forks
562
Avg merge
10d 15h
Merged PRs (30d)
1

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from tursodatabase/libsql

All issues in tursodatabase/libsql

Similar issues

More C issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.