[kgeist]

They use WAL in SQLite. If I continuously perform reads/writes so that they overlap with no gaps, I can make their VM go down because SQLite will not have time to initiate a checkpoint to trim the WAL file. SQLite waits for a time window without any active reads/writes before starting a WAL checkpoint. If there isn't one, the WAL will grow indefinitely, eating up all the disk space on the VM.

It's in SQLite's documentation, and almost no one switching to Sqlite seems to be aware of it because no one discusses it in blog posts like these. I guess most projects switching to SQLite have very low traffic and no malicious users (yet)

[mikae1]

> I guess most projects switching to SQLite have very low traffic and no malicious users (yet)

Lobsters is invite only. That helps. :)

[pushcx]

Thanks for highlighting this failure mode. We don't have a supervisor thread in a position to slow readers and writes like you described in your other thread, so I knocked up a job to force a checkpoint if needed. https://github.com/lobsters/lobsters/commit/cdf9397f8067fc21... Other things would fall over long before we reached 14k RPS so hopefully this suffices for us.

[Onavo]

In your previous thread the solution was either WAL2

https://news.ycombinator.com/item?id=40688336

[HackerThemAll]

You can force explicit CHECKPOINTs to combat that.

[abetusk]

Could you go into more detail about this issue and provide some links to the documentation?

I'm absolutely no expert, I'm just reading about it now, but from the SQLite WAL documentation [0]:

> 7. There is the extra operation of checkpointing which, though automatic by default, is still something that application developers need to be mindful of.

and from their "checkpointing" documentation [1]:

> By default, SQLite does a checkpoint automatically when the WAL file reaches a threshold size of 1000 pages.

I'm only skimming but I see no mention of waiting for an idle time window.

Have you been successful in DoS'ing lobste.rs?

[0] https://sqlite.org/wal.html

[1] https://sqlite.org/wal.html#ckpt

[kgeist]

On the page you linked:

>However, if a database has many concurrent overlapping readers and there is always at least one active reader, then no checkpoints will be able to complete and hence the WAL file will grow without bound.

>This scenario can be avoided by ensuring that there are "reader gaps": times when no processes are reading from the database and that checkpoints are attempted during those times.

Dunno, maybe Rails has a built-in workaround for this.

My workaround was to run a separate thread that monitored the WAL size on disk every second. If it went above the target size of 8 MB, my framework would enter "slow down" mode, where all reads and writes were artificially delayed by calling "sleep()", starting at 16 ms and gradually increasing the sleep time based on a few heuristics.

This allowed the application to have short gaps with no reads or writes, so the checkpointer could actually proceed.

[abetusk]

Thanks, this is really embedded in the documentation and is not at all even hinted at in the above documentation.

This seems like a huge DoS security hole.

I encourage you to write a blog post about this. It seems especially relevant.

[inigyou]

Why not grab a rwlock so that no readers or writers can proceed and all existing ones are drained, and then force a checkpoint? What's with the exponentially increasing sleep?

[chrismarlow9]

https://github.com/openclaw/openclaw/issues/72774

Seems to indicate a collection cron with a manual query to truncate can fix it. This is fun little things I like to learn. SRE Easter eggs.

[gregoryl]

Those SRE people live on excitement, its basically a gift ;)

[chaz6]

Is that solved with wal2 mode?

[shirol]

That's only true in theory. In practice, you'll be rate-limited first. Even if you aren't, the database won't be the bottleneck because SQLite is so fast. Their Ruby on Rails stack will suffer thread starvation way before the WAL grows. Rapid requests still leave microsecond gaps between context switches for SQLite to run an automatic checkpoint. To actually make SQLite stall, you'd need an endpoint that lets you hold a massive write transaction open indefinitely, which lobste.rs doesn't expose anyway (I hope).