Make synchronous SQLite setting configurable

[masih]

The current SQLite configuration in Lotus explicitly sets the synchronous setting to NORMAL.

lotus/chain/events/filter/index.go

Line 25 in 6bc04c8

"PRAGMA synchronous = normal",

lotus/chain/ethhashlookup/eth_transaction_hash_lookup.go

Line 18 in 395cdd7

"PRAGMA synchronous = normal",

lotus/chain/index/msgindex.go

Line 42 in b6a77df

"PRAGMA synchronous = normal",

While this setting provides a balanced trade-off between data safety and performance, it does not cater to all use cases. Specifically, high-traffic APIs, e.g. ethhashlookup, might require the ability to adjust this setting to optimise performance under heavy load conditions.

Make synchronous setting configurable through the application settings, allowing it to be easily adjusted based on specific requirements. The default setting should remain NORMAL to ensure data integrity for the majority of lightweight use cases.

In a case where high throughput is required (i.e. synchronous is set to off) WAL checkpointing can be used to mitigate the risk of potential data loss at the face of ungraceful shutdown (along with some strategic flushing which would need some code changes).

For now:

• this setting should be user's choice with a sensible default.
• this is a small change with large quality of life improvement for Lotus users.

[BigLep]

2024-06-18 triage note: @rvagg is this getting handled as part of #12098 ?

[rvagg]

no, tbh I'm not strongly convinced that this is all that useful

https://sqlite.org/pragma.html#pragma_synchronous

we're using WAL for all 3 databases, and in normal operation, we bypass sync operations on the main database, only synchronizing the WAL

behaviour of "off" with WAL isn't clearly defined by the docs but may mean that WAL checkpoints become unsafe, which just may not be worth it for relatively compact WAL files

[masih]

no, tbh I'm not strongly convinced that this is all that useful

https://sqlite.org/pragma.html#pragma_synchronous

we're using WAL for all 3 databases, and in normal operation, we bypass sync operations on the main database, only synchronizing the WAL

behaviour of "off" with WAL isn't clearly defined by the docs but may mean that WAL checkpoints become unsafe, which just may not be worth it for relatively compact WAL files

That makes sense for default behaviour. Does make sense for every client out there?

My sense would be to parameterise it.

[rvagg]

Maybe, it could become caveat emptor in the soup of config variables that need documenting and explaining. But my point is that I don't see this as a big enough value-add to bump up as a priority task, maybe a P3 until someone comes asking it—or better yet, comes and contributes this..

[masih]

Considering the small size of this task my recommendation would be to run a quick benchmark of eth API throughput with sync off, then prioritise.

Maybe something @snissn can measure?