0xMiden / 0xMiden/node

Many `sqlite` statements are uncacheable

Open
#1,966 5 comments 0 reactions 1 assignee Claimed by @SantiagoPittella View on GitHub
store
Dominant language
Rust
Stars
104
Forks
138
Avg merge
1d 13h
Merged PRs (30d)
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 ?
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.