libsql is extremely slow inserting data into a local file

[gregtbrown]

In comparing libsql to better-sqlite3 I wrote the following simple test code:

import DB from "libsql";
import DB2 from 'better-sqlite3';

async function run(testName, db, numRecs) {
  console.log('Running', testName)

  await db.exec('DROP TABLE IF EXISTS test');
  await db.exec('CREATE TABLE IF NOT EXISTS test (a INT, b INT, c INT)');
  await db.pragma('journal_mode = WAL');
  var stmt = await db.prepare('INSERT INTO test(a,b,c) VALUES(?,?,?)');
  var start = new Date();

  for (var i = 1; i < numRecs + 1; i++) {
    await stmt.run(i, i + 1, i + 2);

    if ( !(i % 1000) )
      console.log('writing:', i);
  }

  var end = new Date();
  console.log(`DONE in ${end - start}msec`);
}

const numRecs = 10000;
const dbOpts = {};

var db = new DB('libsql.db', dbOpts);
await run('libsql', db, numRecs);
await db.close();
      
console.log();

var db = new DB2('better-sqlite3.db', dbOpts);
await run('better-sqlite3', db, numRecs);
await db.close();

The results are:

Running libsql
writing: 1000
. . .
writing: 10000
DONE in 23322msec

Running better-sqlite3
writing: 1000
. . .
writing: 10000
DONE in 400msec

libsql is ~60x slower.

To verify if the issue is in the node wrapper, I put together the same test in rust code:

use libsql::Builder;
use std::time::Instant;

#[tokio::main]
async fn main() {
  let db = Builder::new_local("test.db").build().await.unwrap();
  let conn = db.connect().unwrap();

  conn.execute("CREATE TABLE IF NOT EXISTS test (a INT, b INT, c INT)", ()).await.unwrap();
  let _ = conn.execute("pragma journal_mode = WAL", ()).await;
  let mut stmt = conn.prepare("INSERT INTO test(a,b,c) VALUES(?,?,?)").await.unwrap();
  let now = Instant::now();
  let num_recs = 10000;

  for i in 1..num_recs + 1 {
    stmt.execute([i, i + 1, i + 2]).await.unwrap();
    if i % 1000 == 0 {
      println!("writing {i}");
    }
  }

  println!("Elapsed: {:.2?}", now.elapsed());
}

The results are:

cargo run --release
Finished `release` profile [optimized] target(s) in 0.27s
Running `target\release\test.exe`
writing 1000
. . .
writing 10000
Elapsed: 23.47s

Note that I'm not a rust coder, but the results are exactly the same, indicating that something is seriously wrong with the rust library itself.

These tests were done on windows, however I also ran the node tests in WSL (libsql is ~130x slower) and in a vbox linux VM (libsql ~50X slower).

[gregtbrown]

more info...
after running the same tests on a laptop that is not crapped up with IT software, the results are much better (but not great):

for 1000 individual inserts, all nodejs code:

• libsql to local file --> 3.2sec
• better-sqlite3 to local file --> 135msec (24x faster than libsql)
• libsql client to sqld on wsl --> 5.8sec
• @libsql/client client to sqld on wsl, using websockets --> 5.7sec
• node-fetch client serializing requests to local hapi server w/ better-sqlite3 --> 3sec (1.9x faster than libsql to sqld)
• websocket (ws lib) client serializing round-trip calls to local ws server w/ better-sqlite3 --> 690msec (8.3x faster than @libsql/client to sqld)

note that the times listed are only measuring actual insert time and not total execution time

[gregtbrown]

final note:
according to https://github.com/WiseLibs/better-sqlite3/blob/master/docs/performance.md
better-sqlite3 uses the compile option SQLITE_DEFAULT_WAL_SYNCHRONOUS=1 which is the same as adding await db.pragma('synchronous = NORMAL') in the js code for the libsql test. With the pragma added the libsql test runs in 154msec, just slightly slower than better-sqlite3