Re: Cuirass WAL size issues

[Mathieu Othacehe]

Hello Chris,

> I believe SQLite checkpoints the WAL file after transactions commit, if
> the WAL is over 1000 pages in size. At least for the Guix Build
> Coordinator though, that didn't seem to be working/happening as the WAL
> file seemed to just grow and grow.

I have noticed that the WAL file size had a strong impact on Cuirass
performances on berlin[1].

My conclusion was that, WAL file is nice when doing a lot of writing,
but it makes reading really slow over time. I did set the checkpoint
value to 16 pages of 32KiB as recommended here[2].

> Switching focus to Cuirass, I think it's got similar issues. I've pushed
> a few commits to this branch [1], the important one being [2] which
> rewrites a few procedures to call sqlite-reset when the statement is
> finished with, rather than calling it before starting to read from the
> statement.

The first two commits look nice. Regarding the third one, it makes sense
to finalize or reset a statement once done with it, but I cannot find
anything in SQLite documentation insisting on that point.

Have you found some resources elaborating on that?

Thanks,

Mathieu

[1]: https://issues.guix.gnu.org/43564
[2]:
https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature