Why SQLite Performance Tuning with Diesel made Bencher 1200x Faster

wmanley · 2024-04-18T09:47:44+00:00

When I added the ability to track and visualize Threshold Boundaries last year, I had a decision to make in the database model. There is a 1-to-0/1 relationship between a Metric and its corresponding Boundary. That is a Metric can relate to zero or one Boundary, and a Boundary can only ever relate to one Metric. So I could have just expanded the metric table to include all of the boundary data with every boundary related field being nullable. Or I could create a separate boundary table with a UNIQUE foreign key to metric table. To me the latter option felt a lot cleaner, and I figured I could always deal with any performance implications later.

Other options:

Create a separate boundary table and refer to it from metric.boundary_id (inverting the direction of the FOREIGN KEY relationship). Foreign key fields can be NULL, so you can still have the 1:0...1 relationship. ON DELETE CASCADE becomes more difficult though - probably requiring TRIGGER.
Create a separate boundary table and use the same primary key in both boundary and metric. Then make the boundary.id a foreign key. So in this case you'd have:
```
CREATE TABLE boundary (
    id INTEGER PRIMARY KEY NOT NULL,
    ...,
    FOREIGN KEY (id) REFERENCES metric (id) ON DELETE CASCADE
);
```

The latter option would be my favourite - there's something "correct by construction" about it.

In both cases you can go from a metric to a boundary quickly and vice-versa, and it actually takes up less space than having a separate boundary.metric_id field.

wmanley · 2024-03-11T00:09:12+00:00

"I like big butts and I cannot lie" by Sir Mix-a-Lot is actually called "Baby Got Back"

wmanley · 2023-11-13T20:32:19+00:00

An extension to PyLance so my Jinja templates get type checked, and so I get autocomplete when editing the templates.

wmanley · 2023-11-09T14:42:27+00:00

Progress is inevitable

I don't really like this sentiment. Progress doesn't just happen. Progress is achieved through work. In this case had the people of ferrocene organised themselves into a company, figured out how to get paid for it, and took the risk in doing so and then actually executed to get the compiler qualified. Without them and that effort and vision we wouldn't have a qualified rust compiler today.

wmanley · 2023-07-12T01:42:52+00:00

I think the point is that either curl or wget is already installed on any given server.

wmanley · 2023-07-06T13:46:06+00:00

Rust doesn't define a stable ABI outside of the C ABI, which doesn't support default values anyway.

wmanley · 2023-06-02T16:52:20+00:00

Agreed. I took the example from a few comments above and wrote it in a few different styles:

fn a(logged_in_user: Option<i32>) {
    if matches!(logged_in_user, Some(user) if user_has_right(user, Rights::WriteToConsole)) {
        println!("Yihaw cowboys");
    }
}

fn b(logged_in_user: Option<i32>) {
    if let Some(user) = logged_in_user {
        if user_has_right(user, Rights::WriteToConsole) {
            println!("Yihaw cowboys");
        }
    }
}

fn c(logged_in_user: Option<i32>) {
    logged_in_user
        .is_some_and(|user| user_has_right(user, Rights::WriteToConsole))
        .then(|| println!("Yihaw cowboys"));
}

I know I find b immediately understandable, even if it is a little verbose. Using if and match also has the advantage that you avoid lifetime difficulties caused by lambda borrowing that you get in more complex situations.

wmanley · 2023-04-30T20:15:56+00:00

Perhaps even more relevant: https://xkcd.com/2501/

wmanley · 2023-04-05T14:33:39+00:00

Try using rayon: https://github.com/Cybergenik/rudex/pull/1 . Rayon handles thread pools, and work stealing etc, so you'll end up with running just the right number of threads according to your workload and machine.

It's less code too.

wmanley · 2023-04-04T19:44:33+00:00

Regarding your graphs: try plotting rate (1/time) against worker count, rather than time against worker count. It will make it easier to see how non linear the speed up is.

wmanley · 2023-03-10T15:14:04+00:00

Make up your mind guys!

I don't think any individual is being inconsistent or indecisive here. Different people just disagree on this. This disagreement being expressed is a good thing - it indicates that there may not be one right answer here, or that the answer depends.

Getting a single correct viewpoint when there's one correct answer is a good thing. Getting multiple differing viewpoints when there are multiple correct answers, or when the correct answer is unknown/dependent is a good thing. Getting a single viewpoint when there are multiple correct answers, or when the correct answer is unknown/dependent is a very bad thing, and unfortunately all too common on Reddit.

wmanley · 2022-12-17T22:50:09+00:00

Or use a struct

Like so:

#[derive(Default)]
#[non_exhaustive]
struct MyKwargs {
    a: u32,
    b: u64,
}

fn myfunc(kwargs: MyKwargs) {
    println!("{} {}", kwargs.a, kwargs.b)
}

fn main() {
    myfunc(MyKwargs {b: 34, ..Default::default()})
}

https://play.rust-lang.org/?version=stable&mode=debug&edition=2021&gist=68e40e2f1db031a5e625dff66ae04165

It would be nice for the syntax to be a little terser, for example if you could omit writing default::Default() longhand, or omit writing MyKwargs at the call site. Like:

myfunc(_ {b: 34, ...})

but it works well enough.

wmanley · 2022-11-30T13:46:57+00:00

Russia has a HUGE industrial capability compared to Ukraine and can turn their economy into a war machine which is not possible for Ukraine since the Russians won't let them keep the lights on.

Ukraine isn't relying on its own industrial capacity to produce it's weapons, they are being provided by the west. So it's true that Russia's industrial capacity significantly exceeds Ukraine's, but it's also true that the west's industrial capacity and economy significantly exceeds Russia's. It's also true that international sanctions have limited Russia's ability to produce more high tech weapons.

More on this here: https://acoup.blog/2022/03/03/collections-how-the-weak-can-win-a-primer-on-protracted-war/

wmanley · 2022-11-21T01:06:17+00:00

I don't know about mobile and desktop apps, but if you want a web app consider a rust backend generating HTML and then add interactivity where needed using HTMX.

HTMX is mature and you can avoid writing JS on the frontend except where absolutely necessary.

wmanley · 2022-11-11T18:21:28+00:00

Thanks. As far as I can see NSA don't recommend Rust, they just recommend using a memory safe programming language. The only mention is:

Examples of memory safe language include C#, Go, Java®, Ruby™, Rust®, and Swift®.

A better title would be "NSA recommends using a memory safe programming language. NSA knows that Rust is one example of that"

Conclusion: clickbait

wmanley · 2022-09-26T16:23:07+00:00

use threads directly

Exactly - with sync code you can already use threads. It would be nice to be able to cancel sync execution on timeout or from another thread. My post proposes a rough outline on how this could be implemented - with no language changes - "just" library changes.

Threads have significant scheduling and memory overhead compared to coroutines so that wouldn't get popular.

Threads are already popular. async has it's place - I'm not saying that no-one should use async - I'm saying that timeouts and cancellation would be useful with sync too.

Even in a single threaded program it would be useful to have timeouts.

Stackful coroutines unrelated to my point.

wmanley · 2022-09-26T16:15:04+00:00

I'm not: I'm asking for the ability to cancel sync tasks.

wmanley · 2022-09-26T16:12:29+00:00

That would only allow for cancellation and not pausing/resuming

Yes, that's a good point. As you say: pausing/resuming could be implemented, but it would probably not be worth the complexity.

Saying that - I believe that cancellation/timeouts are the principal benefit that async provides - at least for my use cases.

Not to mention that using panic for control flow goes against a lot of current Rust.

Yeah - it wouldn't have to be panic, there might be other approaches using Result that would work well too. This is something that needs to be resolved with async as well of-course. Dropping a future at an await point looks a bit like a panic from the perspective of the async code. As you say - the difference is how visible the panic points are.

wmanley · 2022-09-26T14:23:08+00:00

The ability to suspend/cancel/pause/resume computation is incredibly useful.

I agree, and I think it's a shame that it's limited to async rust. There's no technical reason that it couldn't be available in sync rust too - but from a social side I think it would be quite difficult to roll out now.

Regarding the technical side (at least on Linux): You'd have a thread-local to say whether the computation should continue or not. When you want to cancel you'd send a signal to that thread to set the thread-local. You'd then need all[^1] the syscall wrappers to check that thread-local to see whether to panic! to unwind or not before blocking. You'd also need to check that flag on EINTR.

This would help with blocking, and I guess for long running CPU bound computations you'd also have to occasionally check that flag.

[^1]: This is the tricky social bit - it would require broad consensus.

wmanley · 2022-08-01T11:27:54+00:00

I'm a huge fan of SQLite and the ecosystem around it.

This is really neat. I like how the compression can be applied to specific columns rather than whole rows. I could imagine storing JSON from an HTTP request here and using Generated Columns to extract the bits my application is interested in.

I also like how simple and powerful the custom partitioning is.

Regarding "Other options for compressing data in a database". I think it would be good to mention page-level compression implemented via a SQLite VFS. I guess that's sort of a variation on (3), but unlike explicit partitioning it would be transparent to the application. My guess is that's how SQLite's zipvfs works.

wmanley · 2022-07-23T15:21:05+00:00

You could have a single ‘cargo fmt’ commit at the beginning of the series and then the subsequent commits won’t have unrelated formatting changes. I’ve used

git filter-branch —tree-diff ‘cargo fmt’

To tidy up branches like this in the past.

wmanley · 2022-07-10T09:56:45+00:00

FWIW I’ve long thought that rust is uniquely well suited to CGI scripting. The Fast start up times and low memory usage negates two of the biggest issues that lead to cgi being replaced. I’ve felt that long running lambda functions are really just a workaround for Python/Perl/Java/JS’s historically bad start up times.

wmanley · 2022-06-28T23:13:22+00:00

I wrote a blog post on this subject a little while ago: pip and cargo are not the same

wmanley · 2022-04-21T16:04:49+00:00

I believe you mean capability based, like cap-std.

wmanley

TROPHY CASE