Re: Cuirass WAL size issues

[Christopher Baines]

Mathieu Othacehe <othacehe@gnu.org> writes:

> 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?

No, that's fustrated me as well. Given sqlite-prepare resets cached
statements, I did try removing most of the sqlite-reset calls from the
Guix Build Coordinator, but that went very badly so I reverted it
shortly after. I was also struggling to find documentation on what to
do/not do.

I'll neaten up these commits later and push them, hopefully it'll help
with the WAL size and/or query performance.

Chris

signature.asc
Description: PGP signature