10x performance drop for single inserts on Mac

[teleclimber]

Hello,

This benchmark test that inserts a single row in an open database takes almost 10x longer on Mac than on a Linux VM:

import {
    bench,
    runBenchmarks,
} from "https://deno.land/std@0.117.0/testing/bench.ts";

import { DB } from "https://deno.land/x/sqlite@v3.1.3/mod.ts";

bench({
    name: "insert once in simple db",
    runs: 100,
    func: (b): void => {
        const db = new DB("simple.db", {mode:"write"});
        b.start();
        db.query("INSERT INTO users (name, balance) VALUES (:name, :balance)", 
        {
            name: "Some Person",
            balance: 12
        });
        b.stop();
        db.close();
    },
});

// create new simple db.
try {
    await Deno.remove("simple.db");
} catch(e) {
    console.error(e);
}
const db = new DB("simple.db", {mode:"create"});
db.query(
  "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, balance INTEGER)",
);
db.close();

runBenchmarks();

Results:

Mac:

running 1 benchmark ...
benchmark insert once in simple db ... 
    100 runs avg: 21.82ms
benchmark result: DONE. 1 measured; 0 filtered

Linux:

running 1 benchmark ...
benchmark insert once in simple db ... 
    100 runs avg: 2.52ms
benchmark result: DONE. 1 measured; 0 filtered
running 0 tests from file:///home/developer/tests/bench.ts

By comparison, the existing bench.ts in this repo shows very little difference between Mac and Linux for me:

Mac:

running 4 benchmarks ...
benchmark insert 10 000 (named) ... 
    100 runs avg: 65.24ms
benchmark insert 10 000 (positional) ... 
    100 runs avg: 36.7ms
benchmark select 10 000 (select all) ... 
    100 runs avg: 4.78ms
benchmark select 10 000 (select one) ... 
    100 runs avg: 16.12ms
benchmark result: DONE. 4 measured; 0 filtered
running 0 tests from https://deno.land/x/sqlite@v3.1.3/bench.ts

Linux:

running 4 benchmarks ...
benchmark insert 10 000 (named) ... 
    100 runs avg: 61.46ms
benchmark insert 10 000 (positional) ... 
    100 runs avg: 36.68ms
benchmark select 10 000 (select all) ... 
    100 runs avg: 4.84ms
benchmark select 10 000 (select one) ... 
    100 runs avg: 17.2ms
benchmark result: DONE. 4 measured; 0 filtered
running 0 tests from https://deno.land/x/sqlite@v3.1.3/bench.ts

I'm running Deno 1.16.4 on both Linux and Mac.

[dyedgreen]

Interesting. 100 runs is not that many, have you tried running the benchmark a few more times to avoid background tasks interfering with the results?

I’ll have a look as well though, to see if I can reproduce the issue 😅

[teleclimber]

Yes I ran it a bunch of times. On the first run it was showing 50ms on Mac, then stabilized at 22ish. I started investigating because I was seeing surprisingly long request times in a project and narrowed it down to the db. I have seen it take as long as 80ms for a single insert, 60 being common. Curious to know if you can reproduce.

[dyedgreen]

I just tried this locally, and I’m getting the same results on Mac.

I’m not sure why performance is so much worse on Mac vs Linux (it might be interesting to try different Deno versions to see if they have a regression somewhere).

In the meantime, running your inserts inside a transaction and using prepared statements should give you much better performance compared to just using query 😅

[dyedgreen]

Could this be a relative performance gain vs Mac? https://deno.com/blog/deno-in-2021#an-optimized-core

(I haven’t investigated this properly, just saw this in the blog and thought it might be related)

[teleclimber]

Checking in on this.

I'm still seeing the same numbers while using Deno v 1.21.

I also moved query prep to outside the timed part of the benchmark, with no noticeable effect.

I wonder if we should get the Deno devs to have a look at this?

[dyedgreen]

Yeah, I think the issue is most likely in the file system API in Deno, since the performance issue seems to only occur on Mac, but not on Linux.

Interestingly, some other recent benchmarks seem to suggest that this library is actually pretty fast / on-par with better sqlite: https://twitter.com/jarredsumner/status/1525105862997442560.

But I don't think these benchmarks are of especially high quality.

[mxcl]

Just as a heads up, this is still present with deno 1.39.4, mac is slow, linux running in an emulated intel docker container is much less slow.