0xMiden / 0xMiden/node

Many `sqlite` statements are uncacheable

Ouverte
#1,966 5 commentaires 0 réactions 1 personne assignée Réclamée par @SantiagoPittella Voir sur GitHub
store
Langage dominant
Rust
Étoiles
104
Forks
138
Merge moyen
1 j 13 h
PR mergées (30 j)
56

Description

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

Guide de contribution

Ouvrir le guide de contribution

Évaluation

Cette issue n'a pas encore été évaluée.

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.