SQLite retention

[venkatamutyala]

When using SQLite it looks like my database keeps growing indefinitely. Is there a way to control the retention/history of the kine table?

[brandond]

It grows in proportion to the amount of data in your datastore. Old revisions (prior versions of resources) should be removed after they have been superseded or deleted for ~10 minutes.

Check your k3s service logs for messages with the word COMPACT. Removal of old data has been seen to time out and fail on nodes with a very slow disk, causing old data to accumulate.

[venkatamutyala]

@brandond thanks. I've been monitoring our environment closely for the past couple of days and it appears my SQLite database grows by 200-500mb per hour. Sometimes even 1GB. I do see a number of records in the kine table increasing however I'm not seeing the COMPACT in any logs. It's worth noting I am using k3ds so i suspect some of the important log information is being abstracted away from me...

[venkatamutyala]

Yeah not finding anything in the logs. I've opened up an issue in k3ds to see if i can get some help on using the abstraction to get the logs i need from k3s service.

[brandond]

The logs would just be in the k3s server output. There WILL be some sort of COMPACT message, every 5 minutes or so.

[venkatamutyala]

Alright I see the logs and it's currently working. I just need it to break on me like it did the last 10-15x. ✊🏾 Given I have the logs enabled now i'm hoping to see the error you described with COMPACT and ideally see an error as you describe above

vscode ➜ /workspaces/glueops $ cat k3s.log | grep COMPACT
time="2025-03-17T01:10:29Z" level=info msg="COMPACT deleted 1016 rows from 1000 revisions in 290.174247ms - compacted to 201982/204893"
time="2025-03-17T01:10:29Z" level=info msg="COMPACT compactRev=201982 targetCompactRev=202947 currentRev=204893"
time="2025-03-17T01:10:29Z" level=info msg="COMPACT deleted 965 rows from 965 revisions in 189.740726ms - compacted to 202947/204893"
time="2025-03-17T01:10:29Z" level=info msg="COMPACT compacted from 200982 to 202947 in 2 transactions over 481ms"
time="2025-03-17T01:15:29Z" level=info msg="COMPACT compactRev=202947 targetCompactRev=203947 currentRev=206843"
time="2025-03-17T01:15:29Z" level=info msg="COMPACT deleted 1000 rows from 1000 revisions in 335.177011ms - compacted to 203947/206843"
time="2025-03-17T01:15:29Z" level=info msg="COMPACT compactRev=203947 targetCompactRev=204893 currentRev=206843"
time="2025-03-17T01:15:29Z" level=info msg="COMPACT deleted 947 rows from 946 revisions in 327.623519ms - compacted to 204893/206843"
time="2025-03-17T01:15:29Z" level=info msg="COMPACT compacted from 202947 to 204893 in 2 transactions over 664ms"

[brandond]

Yep, that's what you should be seeing! Keep an eye out, if the individual transactions creep up over 5 seconds the transaction will get cancelled and roll back, which will cause data to fail to delete and further degrade performance.

If this is the case, your choices are either to reduce load on the datastore, or get faster disk.