Cuirass WAL size issues

Cuirass WAL size issues

[Christopher Baines]

[-- Attachment #1: Type: text/plain, Size: 1381 bytes --]

Hey,

I've been chasing some performance issues in the Guix Build Coordinator
and thought that the size of the WAL file was a contributing factor.

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 think I'm making some progress on that. It seems that if you don't
reset or finalise statements after you're finished with them, bad things
happen. I think one of these bad things is that WAL checkpointing stops
happening. When I got that to happen in the Guix Build Coordinator, I'm
no longer seeing errors when I try and manually run checkpoints now.

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.

1: https://git.cbaines.net/guix/cuirass/log/?h=sqlite-statement-reset-fixes
2: https://git.cbaines.net/guix/cuirass/commit/?h=sqlite-statement-reset-fixes&id=e09bc606f25bad11b39c9fae49139ac03b88697c

If this makes sense, I can spend the time writing some changelog entries
for the commits, and push them. What do you think?

Thanks,

Chris

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 987 bytes --]

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

Re: Cuirass WAL size issues

[Christopher Baines]

[-- Attachment #1: Type: text/plain, Size: 1601 bytes --]


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

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 987 bytes --]