0xMiden / 0xMiden/node

Many `sqlite` statements are uncacheable

未关闭
#1,966 5 条评论 0 个 reaction 已指派 1 人 已被 @SantiagoPittella 认领 在 GitHub 查看
store
主要语言
Rust
星标
104
派生
138
平均合并
1 天 13 小时
30 天内合并 PR
56

描述

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

贡献指南

打开贡献指南

评估

这个 Issue 还没有评估数据。

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。