0xMiden / 0xMiden/node

Many `sqlite` statements are uncacheable

Aperta
#1,966 5 commenti 0 reazioni 1 assegnatario Rivendicata da @SantiagoPittella Vedi su GitHub
store
Lingua principale
Rust
Stelle
104
Fork
138
Merge medio
1g 13h
PR unite (30g)
56

Descrizione

Local node profiling revealed that many of our sqlite queries are not cached, leading to statement parsing being a non-trivial amount of processing, and heavy contention of a mutex internal to `sqlite`. This kneecaps performance.

The internal `mutex` we can disable via some `sqlite` setting. This should be step one.

Step two would be fixing all our queries. Unfortunately this looks to be a lot harder.

The main pain point is that statements containing `IN (..N)` are uncacheable, though we _can_ cache such statements if `N` is kept constant. Unfortunately, many of our queries use this.

Another problem appears to be that `diesel` eagerly translates several other constructs into using `IN` even though at the surface these don't appear to be required.

--------------------

@kkovaacs was able to identify such cases using a patched `diesel` to emit the statement cacheability.

Some cases are also.. strange. For example, its unclear why the following statement is considered uncacheable:

```sql
Query is not safe to cache:

SELECT ...
FROM (`notes` LEFT OUTER JOIN `note_scripts` ON
(`notes`.`script_root` = `note_scripts`.`script_root`))
WHERE (
(
(
(
(`notes`.`network_note_type` = ?) AND
(`notes`.`target_account_id` = ?)
) AND
(`notes`.`committed_at` <= ?)
) AND
(
(`notes`.`consumed_at` IS NULL) OR
(`notes`.`consumed_at` > ?)
)
) AND
notes.rowid >= ?
)
ORDER BY notes.rowid ASC LIMIT ?
```

Guida per i contributori

Apri la guida per i contributori

Valutazione

Questa issue non è ancora stata valutata.

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.