SQLite WAL File for Events Growing Until Lotus Restart

[snissn]

Description:
The SQLite Write-Ahead Logging (WAL) file for storing events in the Lotus client continues to grow indefinitely until the client is restarted. This can lead to excessive disk usage and potential performance degradation. It is not the state file but a write ahead log that should be periodically reset. Restarting lotus clears the file. events.db-wal was 14GBs from running over a < 24 hour period and was deleted and reset after restarting lotus. It continues to grow from 0 bytes after restarting lotus

Steps to Reproduce:

1. Run the Lotus client.
2. Monitor the size of the SQLite WAL file over time.
3. Observe that the WAL file grows continuously without being truncated.

Expected Behavior:
The SQLite WAL file should be periodically checkpointed and truncated to prevent uncontrolled growth.

Environment:

• Lotus version: master
• OS: ubuntu

Additional Information:
Implementing a mechanism to manually trigger checkpoints or reviewing the configuration settings related to SQLite checkpointing may help mitigate this issue. The following config parameter should force the WAL to be vacuumed automatically https://sqlite.org/pragma.html#pragma_wal_autocheckpoint. Also as helpful information a query can be sent directly to sqlite to check point via PRAGMA wal_checkpoint(FULL);

Possible Solutions:

1. Investigate and fix any bugs preventing automatic checkpointing related to configuration of pragma_wal_autocheckpoint.
2. Implement automatic checkpointing every N minutes as a temporary workaround that executes PRAGMA wal_checkpoint(FULL);

[rvagg]

I spent some time tinkering with this because I've been running the db since nv22 and my WAL is now over 200G which is definitely not normal. Unfortunately I couldn't figure out a straightforward solution and get it solved! But I do think this is urgent and we should spend some time figuring this out because it likely impacts performance, aside from just being a pain in the backside for node operators.

My understanding and some of the things I've tried:

• Our current settings should automatic checkpoint an should compact the WAL periodically because of it; but it doesn't seem to
• It won't checkpoint and compact if there are open read operations. I did find one potentially unclosed transaction in index.go but dealing with it didn't seem to make a difference. Perhaps there are more subtle bugs with our transactions in there.
• Fiddling with other WAL settings didn't seem to make much difference, including putting a hard max on it!
• I haven't tried turning off auto checkpointing, I didn't really want to come up with a checkpoint frequency algorithm, but it could be something as straightforward like "every X epochs".

Possibly some weird interaction with one of our other PRAMAs in there. Debugging this is obviously quite difficult; trial and error and time. Unless someone with more insight sees something obvious there.

Will assign this to myself since i have a good working setup and started looking into this. But @snissn if you have a brilliant idea then I'm all ears!

[rvagg]

Tinkering here #12090