0xMiden / 0xMiden/node

Many `sqlite` statements are uncacheable

Offen
#1,966 5 Kommentare 0 Reaktionen 1 zugewiesene Person Beansprucht von @SantiagoPittella Auf GitHub ansehen
store
Vorherrschende Sprache
Rust
Sterne
104
Forks
138
Ø Merge
1 T. 13 Std.
Gemergte PRs (30 T.)
56

Beschreibung

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

Beitragsleitfaden

Beitragsleitfaden öffnen

Bewertung

Dieses Issue wurde noch nicht bewertet.

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.