Better-sqlite3: Worried about synchronicity

[wmertens]

So, the entire API is synchronous, right?

So if you have a query that takes a while to run, say 0.5s-2s or so, that means the entire node process is hanging until the database returns?

Is that not problematic for web servers?

Would it be possible to have an asynchronous mode for queries that are expected to take a long time?

[JoshuaWise]

The sqlite3 C API serializes all operations (even reads) within a single process. You can parallelize reads to the database but only by having multiple processes, in which case one process being blocked doesn't affect the other processes anyways. In other words, because sqlite3 serializes everything, doing things asynchronously won't speed up database access within a process. It would only free up time for your app to do other things (like HTTP requests to other servers). Unfortunately, the overhead imposed on sqlite3 to serialize asynchronous operations is quite high, making it disadvantageous 95% of the time.

Here are some good guidelines to follow:

• If your web server primarily does slow queries (0.5s-2s) and your web server does a lot of other IO-related things (HTTP/TCP communication to other servers), then an asynchronous API will be better for you. This package may not be for you.
• If your web server occasionally does slow queries, then the best performance you can get is by using this package and separating the slow and fast queries to separate processes (using IPC to pass around socket handles).
• In any other situation, using this package will be the fastest in terms of performance.
  Because:
  
  
  
  • when running fast read queries, the overhead imposed on sqlite3 to serialize asynchronous operations would surpass any benefit gained by making them asynchronous (you can verify this in the benchmark against node-sqlite3)
  
  
  • when running fast write queries, sqlite3 serializes these even across multiple processes, making it impossible to optimize through asynchronicity/parallelization. Attempting to do so would only cause more overhead
  
  

[JoshuaWise]

All that said, what kind of queries are you doing for them to be so slow? sqlite3 is an extremely fast database engine. On a macbook pro, I'm able to benchmark reading 1MB of data in only 6 milliseconds using better-sqlite3, with cache disabled. If you're indexing your queries correctly, I can't see a reason for them to take so long.

[wmertens]

Hmmm good points, however I remain worried.

I am doing multi process access, a reader and a writer; the writer stores
inbound network data in a transaction, and it causes reads to be delayed
sometimes, if the transaction becomes big.

Also, if I'm doing a query like name like "%foo%" that results in a full
table scan, but of course I should implement that as a FTS table. (It takes
30ms-1s to run this query on my db in the terminal)

So on the surface, it seems like it should not be a problem.

My worries are:

• I don't know how big my db will get, and what that will mean for query
  speed over time
• Having the API be synchronous means that the app is less forgiving
  about mistakes, and forces me to code defensively in multi-processes much
  earlier, which has its own problems
• I am surprised that async processing has so much overhead, since Node
  is dissuading users from even fs.statSync
• Could it be that node-sqlite3 is trying too hard, and providing a
  non-parallel FIFO async query interface is also performant?

So it seems that for very small web apps that serve files, SSR and graphql,
an async API would be easier, and if the app becomes more loaded it should
become multi-process, with multiple graphql processes serving as database
readers. Right?

On Wed, May 17, 2017 at 8:19 PM Joshua Wise [e-mail]
wrote:

All that said, what kind of queries are you doing for them to be so slow?
sqlite3 is an extremely fast database engine. On a macbook pro, I'm
able to benchmark reading 1MB of data in only 6 milliseconds using
better-sqlite3, with cache disabled. If you're indexing your queries
correctly, I can't see a reason for them to take so long.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-302182378,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWlgkvlhRZFBYz9fwDayeqP2DpWBM-ks5r6zo-gaJpZM4Ndj6K
.

[JoshuaWise]

To address some of your concerns:

I am doing multi process access, a reader and a writer; the writer stores
inbound network data in a transaction, and it causes reads to be delayed
sometimes, if the transaction becomes big.

You should use WAL mode. This will allow your reader process to run queries even while the writer process does a long transaction. They'll be able to access the database in parallel. To activate WAL mode, run db.pragma('journal_mode = WAL') after opening the database.

Also, if I'm doing a query like name like "%foo%" that results in a full
table scan, but of course I should implement that as a FTS table. (It takes
30ms-1s to run this query on my db in the terminal)

You should definitely use FTS, or at least create an index for that query. That should reduce the query time by orders of magnitude.

I don't know how big my db will get, and what that will mean for query
speed over time

sqlite3 databases typically do not meaningfully slow down as their size increases, as long as you create indexes for your queries.

I am surprised that async processing has so much overhead

Nodejs is not slow. The problem is that sqlite3 has to serialize everything within a single process, which means that it has to deal with intense resource contention. If sqlite3 could handle parallel queries on the same connection then it wouldn't be a problem. Sqlite3 is fast, Nodejs is fast, but when you combine the two in an asynchronous way, it causes these problems.

Could it be that node-sqlite3 is trying too hard, and providing a
non-parallel FIFO async query interface is also performant?

In theory, yes, this would work. Unfortunately, when node schedules an async task (via libuv), it does not guarantee FIFO order. It would be possible to implement this at the library level. Perhaps it could be a feature in the future.

So it seems that for very small web apps that serve files, SSR and graphql,
an async API would be easier, and if the app becomes more loaded it should
become multi-process, with multiple graphql processes serving as database
readers. Right?

There's nothing wrong with this approach, but as I stated before, unless you're doing very slow read queries (which you should avoid regardless), the synchronous API will be very efficient for the use case. If you have slow write queries in another process, use WAL mode.

[wmertens]

Thanks much! I just remembered that the issue with the lock was a small
write that had to wait for the transaction. I'm already using WAL mode but
of course two writes will block.

There is no index you can make for the LIKE "%foo%" case unfortunately,
it always requires a table scan. I have yet to implement my first FTS,
initializing and maintaining the table seems a bit of work.

For the async stuff, I should probably not be armchair programming here,
but could you not implement the resource management on the Node side,
managing the queue of queries, and the native lib only handling a single
query at a time, calling a callback when done?

Also, you could spawn threads in the native library so you can do parallel
queries (over multiple connections), right? But that's probably a nightmare
to get right…

On Wed, May 17, 2017, 11:13 PM Joshua Wise [e-mail] wrote:

To address some of your concerns:

I am doing multi process access, a reader and a writer; the writer stores
inbound network data in a transaction, and it causes reads to be delayed
sometimes, if the transaction becomes big.

You should use WAL mode https://www.sqlite.org/wal.html. This will
allow your reader process to run queries even while the writer process does
a long transaction. They'll be able to access the database in parallel. To
activate WAL mode, run db.pragma('journal_mode = WAL') after opening the
database.

Also, if I'm doing a query like name like "%foo%" that results in a full
table scan, but of course I should implement that as a FTS table. (It takes
30ms-1s to run this query on my db in the terminal)

You should definitely use FTS, or at least create an index for that
query. That should reduce the query time by orders of magnitude.

I don't know how big my db will get, and what that will mean for query
speed over time

sqlite3 databases typically do not meaningfully slow down as their size
increases, as long as you create indexes for your queries.

I am surprised that async processing has so much overhead

Node is not slow. The problem is that sqlite3 has to serialize everything
within a single process, which means that it has to deal with intense resource
contention https://en.wikipedia.org/wiki/Resource_contention. If
sqlite3 could handle parallel queries on the same connection then it
wouldn't be slow. Sqlite3 is fast, node is fast, but when you combine the
two in an asynchronous way, it causes these problems.

Could it be that node-sqlite3 is trying too hard, and providing a
non-parallel FIFO async query interface is also performant?

In theory, yes, this would work. Unfortunately, when node schedules an
async task (via libuv), it does not guarantee FIFO order. It would be
possible to implement this at the library level. Perhaps it could be a
feature in the future.

So it seems that for very small web apps that serve files, SSR and graphql,
an async API would be easier, and if the app becomes more loaded it should
become multi-process, with multiple graphql processes serving as database
readers. Right?

There's nothing wrong with this approach, but as I stated before, unless
you're doing very slow read queries (which you should avoid regardless),
the synchronous API will be very efficient for the use case. If you have
slow write queries in another process, use WAL mode
https://www.sqlite.org/wal.html.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-302233369,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWlpg6VYnLBU1h9j2MJfnp7iiGDUGTks5r62MCgaJpZM4Ndj6K
.

[wmertens]

Hmm, I keep thinking about this, it seems so simple, I'm probably missing
some roadblock.

So suppose that when you instantiate a DB, it spawns a single thread which
opens the connection and runs queries. You specify a callback that receives
results, and you can send a query only if the thread is not busy, guarded
by a simple boolean that is only written to from the thread.

If you want more threads, instantiate more DBs. This is especially handy if
you have more than one DB file.

The async model is managed by JS glue, so that .all() returns a Promise for
the result, and .each() creates a readable object stream.

Would that be a lot of work? Is that inherently slow somehow? I'd gladly
help with the JS glue part…

On Thu, May 18, 2017 at 12:13 AM Wout Mertens [e-mail]
wrote:

Thanks much! I just remembered that the issue with the lock was a small
write that had to wait for the transaction. I'm already using WAL mode but
of course two writes will block.

There is no index you can make for the LIKE "%foo%" case unfortunately,
it always requires a table scan. I have yet to implement my first FTS,
initializing and maintaining the table seems a bit of work.

For the async stuff, I should probably not be armchair programming here,
but could you not implement the resource management on the Node side,
managing the queue of queries, and the native lib only handling a single
query at a time, calling a callback when done?

Also, you could spawn threads in the native library so you can do parallel
queries (over multiple connections), right? But that's probably a nightmare
to get right…

On Wed, May 17, 2017, 11:13 PM Joshua Wise [e-mail]
wrote:

To address some of your concerns:

I am doing multi process access, a reader and a writer; the writer stores
inbound network data in a transaction, and it causes reads to be delayed
sometimes, if the transaction becomes big.

You should use WAL mode https://www.sqlite.org/wal.html. This will
allow your reader process to run queries even while the writer process does
a long transaction. They'll be able to access the database in parallel. To
activate WAL mode, run db.pragma('journal_mode = WAL') after opening the
database.

Also, if I'm doing a query like name like "%foo%" that results in a full
table scan, but of course I should implement that as a FTS table. (It
takes
30ms-1s to run this query on my db in the terminal)

You should definitely use FTS, or at least create an index for that
query. That should reduce the query time by orders of magnitude.

I don't know how big my db will get, and what that will mean for query
speed over time

sqlite3 databases typically do not meaningfully slow down as their size
increases, as long as you create indexes for your queries.

I am surprised that async processing has so much overhead

Node is not slow. The problem is that sqlite3 has to serialize everything
within a single process, which means that it has to deal with intense resource
contention https://en.wikipedia.org/wiki/Resource_contention. If
sqlite3 could handle parallel queries on the same connection then it
wouldn't be slow. Sqlite3 is fast, node is fast, but when you combine the
two in an asynchronous way, it causes these problems.

Could it be that node-sqlite3 is trying too hard, and providing a
non-parallel FIFO async query interface is also performant?

In theory, yes, this would work. Unfortunately, when node schedules an
async task (via libuv), it does not guarantee FIFO order. It would be
possible to implement this at the library level. Perhaps it could be a
feature in the future.

So it seems that for very small web apps that serve files, SSR and
graphql,
an async API would be easier, and if the app becomes more loaded it should
become multi-process, with multiple graphql processes serving as database
readers. Right?

There's nothing wrong with this approach, but as I stated before, unless
you're doing very slow read queries (which you should avoid regardless),
the synchronous API will be very efficient for the use case. If you have
slow write queries in another process, use WAL mode
https://www.sqlite.org/wal.html.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-302233369,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWlpg6VYnLBU1h9j2MJfnp7iiGDUGTks5r62MCgaJpZM4Ndj6K
.

[JoshuaWise]

For the async stuff, I should probably not be armchair programming here,
but could you not implement the resource management on the Node side,
managing the queue of queries, and the native lib only handling a single
query at a time, calling a callback when done?

You have the right idea, but it's more complicated than that because of two main factors:

• It's not safe to use v8 in multiple threads, which means for each sqlite3 data type I'd need to create an intermediate container that's used to store the values coming from the database, until the operation is complete. After that, I can return to the main thread and convert the values to Javascript values. Unfortunately, this requires a fair amount of ceremony and an additional copy of each piece of data. For large strings and buffers, the overhead could be substantial. There are ways of getting around this limitation, but it increases complexity.
• Using both asynchronous and synchronous styles in the same library creates some design pattern concerns. For example, if you try to run multiple queries asynchronously, it would be easy to just queue them and execute them in FIFO order. However, imagine someone trying to run an asynchronous query and then a synchronous one immediately after (before the async query finishes). The library would have to block the main thread until the asynchronous one finishes, which implies the use of mutexes and communicating between threads. It's not the most difficult problem in the world, but it could get complicated mixing the two paradigms within a single database connection.

For the reasons above, I am interested in creating an asynchronous version of better-sqlite3 (one that doesn't have the pitfalls of node-sqlite3) but I think it would be more appropriate for it to be a separate package.

[JoshuaWise]

Especially since the benefits of making it asynchronous are fairly limited, given the nature of sqlite3.

[wmertens]

Aha, interesting!

• To get around the v8 threading, only talk to v8 in the main thread. A
  worker thread is only used to run sqlite_step (the only potentially slow
  thing), and signal the main thread when a result came in. The main thread
  then gets the data and calls the callback.
• For the async+sync: Simply refuse to run the sync query if no DB
  connections are available? If the dev is using async + sync calls, they
  should be aware this can happen and retry or add connections.

Since you already proved that everything else is super fast, only using a
thread to wait for sqlite_step() seems like the right thing to do, and very
simple, no?

On Thu, May 18, 2017 at 2:36 AM Joshua Wise [e-mail]
wrote:

Especially since the benefits of making it asynchronous are fairly
limited, given the nature of sqlite3

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-302268540,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWlkeJFGSSHV8hO8qy6fhvLqzFzQ0tks5r65KhgaJpZM4Ndj6K
.

[wmertens]

BTW, on the "storing intermediate values" problem, I'm assuming that you
mean the JS API's .all(), so that one can either be handled in the async
JS API, or, after the first sqlite_step() returns, in the main thread.

The JS APi could even offer both options…

On Thu, May 18, 2017 at 7:53 AM Wout Mertens [e-mail] wrote:

Aha, interesting!

• To get around the v8 threading, only talk to v8 in the main thread.
  A worker thread is only used to run sqlite_step (the only potentially slow
  thing), and signal the main thread when a result came in. The main thread
  then gets the data and calls the callback.
• For the async+sync: Simply refuse to run the sync query if no DB
  connections are available? If the dev is using async + sync calls, they
  should be aware this can happen and retry or add connections.

Since you already proved that everything else is super fast, only using a
thread to wait for sqlite_step() seems like the right thing to do, and very
simple, no?

On Thu, May 18, 2017 at 2:36 AM Joshua Wise [e-mail]
wrote:

Especially since the benefits of making it asynchronous are fairly
limited, given the nature of sqlite3

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-302268540,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWlkeJFGSSHV8hO8qy6fhvLqzFzQ0tks5r65KhgaJpZM4Ndj6K
.

[JoshuaWise]

BTW, on the "storing intermediate values" problem, I'm assuming that you
mean the JS API's .all(), so that one can either be handled in the async
JS API, or, after the first sqlite_step() returns, in the main thread.

What I mean is that sqlite3 will only provide one row of data at a time. After the second row is available, the first row has already been invalidated by the sqlite3 C API. This means the pseudo code for .all() would look like this:

// enter async thread
std::vector<IntermediateValue*> values;
int column_count = sqlite3_column_count(stmt);
int status;
while ((status = sqlite3_step(stmt)) == SQLITE_ROW) {
  for (int i = 0; i < column_count; ++i) {
    // all data is copied here, because on the next iteration of the while-loop,
    // these row values will not longer be available
    values.push_back(new IntermediateValue(stmt, i));
  }
}
// return to main thread
v8::Array array = v8::Array::New(values.size());
v8::Object row = v8::Object::New();
int row_index = 0;
int column_index = 0;
for (IntermediateValue* value : values) {
  if (column_index < column_count) {
    // IntermediateValue::toJavaScriptValue() copies each value again here, to convert them to
    // their v8 representation
    row->Set(v8::String::New(sqlite3_column_name(stmt, column_index)), value->toJavaScriptValue());
  } else {
      array->Set(row_index++, row);
      row = v8::Object::New();
      column_index = 0;
  }
}
return array;

This is not optimized code, but it demonstrates that each value must be copied twice to overcome v8's lack of thread safety.

[wmertens]

I was thinking that the async thread should be used only to run a single
sqlite3_step() call, returning the ready-to-read stmt. Only once the main
thread copied the row data to JS values, the next sqlite3_step() is called
in the async thread (using the v8 thread pool?)

This means that for .all() it will take longer between each sqlite3_step(),
since it has to wait for the main thread to be available, but your app will
be more responsive and use less CPU. If you use multiple connections, you
can handle multiple queries at the same time so it will be ok.

As an optimization, if you know the query will be smallish, you can wait
for only the first _step to complete, and then read all the other values in
one go. The first step will have primed the cache, calculated the query
plan etc so I would expect it to be the slowest. That would be optional,
per-query.

On Thursday, May 18, 2017, Joshua Wise [e-mail] wrote:

BTW, on the "storing intermediate values" problem, I'm assuming that you
mean the JS API's .all(), so that one can either be handled in the async
JS API, or, after the first sqlite_step() returns, in the main thread.

What I mean is that sqlite3 will only provide one row of data at a time.
After the second row is available, the first row has already been
invalidated by the sqlite3 C API. This means the pseudo code for .all()
would look like this:

// enter async thread
std::vector values;
int column_count = sqlite3_column_count(stmt);
int status;
while ((status = sqlite3_step(stmt)) == SQLITE_ROW) {
for (int i = 0; i < column_count; ++i) {
// all data is copied here, because on the next iteration of the while-loop,
// these row values will not longer be available
values.push_back(new IntermediateValue(stmt, i));
}
}
// return to main thread
v8::Array array = v8::Array::New(values.size());
v8::Object row = v8::Object::New();
int row_index = 0;
int column_index = 0;
for (IntermediateValue* value : values) {
if (column_index < column_count) {
// IntermediateValue::toJavaScriptValue() copies each value again here, to convert them to
// their v8 representation
row->Set(v8::String::New(sqlite3_column_name(stmt, column_index)), value->toJavaScriptValue());
} else {
array->Set(row_index++, row);
row = v8::Object::New();
column_index = 0;
}
}
return array;

This is not optimized code, but it demonstrates that each value must be
copied twice to overcome v8's lack of thread safety.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-302426291,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWltiq2Se3cFYs2jzr3AlKpbSwO11-ks5r7FnHgaJpZM4Ndj6K
.

[JoshuaWise]

Ahh I see what you're saying. That's a good option for maximizing throughput, but it costs latency. I'd be interested in creating a benchmark to see the speed differences across different loads and query sizes. It'll be a fun experiment

[wmertens]

The best part is that it can 100% coexist with the sync API, so you can mix
and match depending on the scenario…

On Thu, May 18, 2017 at 5:14 PM Joshua Wise [e-mail]
wrote:

Ahh I see what you're saying. That's a good option for maximizing
throughput, but it costs latency. I'd be interested in creating a benchmark
to see the speed differences across different loads and query sizes. It'll
be a fun experiment

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-302434788,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWlrna1BhGVzLp3cdoQ1TE-riD5RMiks5r7GBAgaJpZM4Ndj6K
.

[JoshuaWise]

I think I'll start working on these ideas after I finish my current TODO list:
1) allow people to register custom aggregate SQL functions
2) remove nan, which will open the door for many performance optimizations
3) optimize use of CPU cache by separating the less commonly used database fields to separate structures, via a pointer

[wmertens]

In the mean time, I'll be upgrading my app with better-sqlite3, wrapping it
in a fake async interface :)

On Thu, May 18, 2017 at 5:22 PM Joshua Wise [e-mail]
wrote:

I think I'll start working on these ideas after I finish my current TODO
list:

1. allow people to register custom aggregate SQL functions
2. remove nan https://github.com/nodejs/nan, which will open the
   door for many performance optimizations
3. optimize use of CPU cache by separating the less commonly used
   database fields to separate structures, via a pointer

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-302437172,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWllvF1aYAlrFeuvScDFkv7rrzvTztks5r7GIggaJpZM4Ndj6K
.

[barrycaceres]

@JoshuaWise -- so I am thinking that if somebody was using your better-sqlite3 and they had some long running query that they could always use setTimeout(someFunction(), 0) to queue it up to happen asynchronously and just use a promise to notify when complete:

getLogEntries(db:Database) : Promise<LogEntry[]> {
  return Promise<LogEntry[]>((resolve,reject) => {
       setTimeout(() => {
           try {
               let rows = db.prepare("SELECT ....").all(...);
               let result : LogEntry[] = [];
               rows.forEach(row => {
                  ....
               });
               resolve(result);
           } catch (e) {
              reject(e);
           }
       }, 0);
  });
}

Is there any reason we couldn't do something like this?

[JoshuaWise]

@barrycaceres
setTimeout() will indeed queue it to happen when the current call stack is finished, but it won't prevent the query from blocking the main thread while it's running. You need to use libuv in C++ for that. All JavaScript code runs in the main thread, even if it's passed to setTimeout().

[barrycaceres]

@JoshuaWise -- still setTimeout() seems like a good workaround to prevent blocking the pending items on the current call stack and deferring it until the current call stack is complete.

But I see your point that eventually you are going to have to pay the piper and have the main thread blocked doing the work of the SQL query.

I was thinking in terms of during app initialization that you can make sure any HTTP requests you want to fire are fired and running and not waiting on a long SQL query... then the SQL query can run afterward while the REST API backend is doing its work -- i think it would buy some time savings.

[JoshuaWise]

You're free to do this in your application if you think it'll help a specific latency problem (it certainly won't improve throughput), but if/when better-sqlite3 supports an asynchronous API it will be non-blocking.

[barrycaceres]

@JoshuaWise nice... you seem to be very actively maintaining better-sqlite3. I am in the process of switching over to it right now....

I got tired of not being able to easily determine when the last statement in a serious of statements completed. With node-sqlite3, if I used loops to run statements where I had parent records that had to insert first, it got very messy and my code was starting to look like a pyramid of doom with all the indentation of callbacks. To learn that I was actually losing performance as well because of it made me realize I needed to make the switch early and try out better-sqlite3.

BONUS: I also like that if I choose to wrap a bunch of better-sqlite3 operations in a promise that the promise constructor is already setup to catch exceptions and trigger a rejection. Since better-sqlite3 throws exceptions to indicate failures it makes the code even that much cleaner.

[wmertens]

Note that if you use async/await and promises, the problematic flow you
describe becomes very much like synchronous code.

I will also be switching, soonish, but I can't afford the main thread
blocking so either I will be adding the async capability, or I will do
database access in a different process.

On Thu, May 25, 2017, 8:36 PM barrycaceres [e-mail] wrote:

@JoshuaWise https://github.com/joshuawise nice... you seem to be very
actively maintaining better-sqlite3. I am in the process of switching over
to it right now....

I got tired of not being able to easily determine when the last statement
in a serious of statements completed. With node-sqlite3, if I used loops to
run statements where I had parent records that had to insert first, it got
very messy and my code was starting to look like a pyramid of doom with all
the indentation of callbacks. To learn that I was actually losing
performance as well because of it made me realize I needed to make the
switch early and try out better-sqlite3.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-304088561,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWlmQv7kHk-7XMbGAHIK-3-0u3wP4vks5r9coWgaJpZM4Ndj6K
.

[barrycaceres]

@wmertens -- Whenever I am using better-sqlite3, I am also using Electron, so I can probably create a worker thread that manages a queue of inbound sqlite3 requests, processes them synchronously, and posts the results back.

This will prevent my main thread from locking up. I just need to make sure I do ALL better-sqlite3 operations in the worker thread to avoid multi-threaded issues common in native modules. I don't want two separate threads trying to call into better-sqlite3 at the same time.

[wmertens]

V8 only allows working with JS values in the main thread. Your only option
is multiple processes… see
https://nodeaddons.com/how-not-to-access-node-js-from-c-worker-threads/

On Fri, May 26, 2017, 6:32 PM barrycaceres [e-mail] wrote:

@wmertens https://github.com/wmertens -- Whenever I am using
better-sqlite3, I am also using Electron, so I can probably create a worker
thread that manages a queue of inbound sqlite3 requests, processes them
synchronously, and posts the results back.

This will prevent my main thread from locking up. I just need to make sure
I do ALL better-sqlite3 operations in the worker thread to avoid
multi-threaded issues common in native modules. I don't want two separate
threads trying to call into better-sqlite3 at the same time.

—
You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-304328187,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWluR2wvdWCCPF4Y19Vd6VVJqOxRX3ks5r9v6JgaJpZM4Ndj6K
.

[slanska]

As far as web server is running on multi cpu with high load, practical solution would be to use cluster or use nginx as reverse proxy server. In the latter case, nginx is capable to split load between processes that are less loaded at the moment. Overall idea seems very interesting, I amgoing to give it a try and run benchmarks on my laptop.

[slanska]

Does better-sqlite3 currently allow loading extensions? node-sqlite3 does.

[JoshuaWise]

@slanska, if you have a high write load, make sure to only do writes in one process. Sqlite3 is slow when dealing with resource contention (if multiple process are writing to the database at the same time)

And no, runtime-loaded extensions currently aren't supported. But they probably will be soon

[slanska]

Yes, I am aware of locking entire database during write. Just to be clear - in WAL mode the lock occurs only during commit time, so for typical small-to-medium write batch size, commit (and db lock) should not last too long. Anyway, I think this issue is relevant for both node-sqlite3 and better-sqlite3 as all db operations are serialized (as you pointed out earlier). Idea of using cluster or reversed proxy is to avoid big latency for concurrent read operations, so that long select (like 0.5-2 secs mentioned above) will not block other read requests (which would be processed in this case by other process(es)). Surely, there is overhead (memory etc.) when running multiple processes, but I believe it would be comparable with overhead in your proposed custom solution. Besides, this overhead can be minimized to mostly additional SQLite connections, if SHARED CACHE mode is used (btw, does your library support it?).

[slanska]

So, before investing time for implementing custom async mode, I'd benchmark cluster/nginx mode to get an idea about possible performance improvement and extra memory consumption. My gut tells me that results should be very reasonable, so no special async mode would be required. We should not forget what niche SQLite is designed to fill - small to medium websites. For small traffic websites 2 processes with occasional long selects would work just fine. If this is not sufficient, then a different kind of database should be considered. Just 2 my cents.

[slanska]

Have you considered Berkeley DB with SQLite API? For whatever reason this combination is less known than mainstream SQLite, and BDB should be much more efficient on concurrent writing and on read operations overall. The only drawback would be its licensing - AGPL (same as Mongo or MySQL).

[slanska]

Another thought is that node-sqlite3 compatible API would be helpful for better adoption of better-sqlite3, so that existing ORMs could be reused with this library (I am thinking about Knex, Bookshelf, Sequelize etc.)

[wmertens]

@slanska

• It's not always easy to cluster apps, especially if the app doesn't
  assume to be clustered from the start. Caching, race conditions etc.
• The proposal here is only a very minimal async api: just (optionally)
  wait for the sqlite_step() in a thread, but read the result in the main
  thread. Small code change.
• A node-sqlite3 compatible API should be possible with a JS wrapper, but
  it may be some work.

On Sat, Jun 10, 2017 at 5:59 AM slanska [e-mail] wrote:

Another thought is that node-sqlite3 compatible API would be helpful for
better adoption of better-sqlite3, so that existing ORMs could be reused
with this library (I am thinking about Knex, Bookshelf, Sequelize etc.)

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-307540105,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWloQYNnP8pBI7MAdaWk60EukAZmzpks5sChSsgaJpZM4Ndj6K
.

[wmertens]

BTW, I checked on the sqlite mailing list, the slowest step is that first sqlite_step() call, the others will be much faster, except when you are running on HDD outside of cache, or you are doing a badly indexed query.

So that would mean offering 3 async modes: sync, async first, async all. Most web apps would be fine with async first I think.

[JoshuaWise]

@slanska shared cache is currently not supported (except among in-memory databases, which automatically share cache if they have the same name), but it would be trivial to add.

So, before investing time for implementing custom async mode, I'd benchmark cluster/nginx mode to get an idea about possible performance improvement and extra memory consumption

You have a good point. The other drawback of an async solution is that the libuv thread pool (which is used for filesystem access and user-defined C bindings, but not for unix socket/network I/O) has a default of only 4 threads, limiting the default parallelism compared to having multiple processes.

Have you considered Berkeley DB with SQLite API?

This is not something I've looked into very much, but perhaps I should do some research on it.

Another thought is that node-sqlite3 compatible API would be helpful for better adoption of better-sqlite3

Good idea, I'd like to add that at some point.

@wmertens

The proposal here is only a very minimal async api: just (optionally)
wait for the sqlite_step() in a thread, but read the result in the main
thread. Small code change.

I've been working out how this would be built into better-sqlite3, and it turns out it would essentially require a complete rewrite. Consider these complications:

• Every exposed API function now must have the notion of "database is async busy" as well as "database is sync busy", instead of just "database is busy". Most operations would need to throw errors under both "async busy" and "sync busy" states, but the async methods would only throw errors under "sync busy" states (for example in the callback for .each() or during custom aggregate functions, which we already have protection for).
• When queuing async calls, the parameter bindings must be bound immediately (synchronously) because otherwise someone could mutate a bound Buffer before the query is executed. This means we'd have to bind parameters while another thread is potentially executing a query, which means we'd have to remove the SQLITE_THREADSAFE=0 compilation option, which the sqlite3 website says "is the single compile-time option that makes the most difference in optimizing the performance of SQLite".
• In order to access the database while a long-running async query is running (which I assume is the main point of having an async API), you'd need to have a connection pool, which would be an entirely new concept for better-sqlite3. This creates MANY other complications. What if you want to run a PRAGMA? Now it needs to be run on every connection in the pool. What if you run a PRAGMA to set up some configuration, and then you create a new connection in the pool? That new connection would be configured differently because it didn't exist when the PRAGMA was run. To make matters worse, not all PRAGMA are configuration. Some PRAGMAs you want to only run on a single connection (such as those that return data, rather than setting configuration). This would require more API bloat.
• Let's say we forget the connection pool concept. So a database connection just queues async queries and executes them FIFO. What if someone requests the same data from your web server twice in a row? You'd want to reuse the same prepared statement, but now you can't, because remember, the parameters must be bound immediately when an operation is queued (synchronously), but that prepared statement might be in use so we can't bind parameters to it! This means you wouldn't be able to reuse prepared statements, which would slow down simple queries by orders of magnitude.
• Earlier, you said a sync operation should just throw an error if an async operation is running, but in a web server context you wouldn't be able to avoid that situation because a web server implies random access. Your only solution would be to only use async calls.
• I'd have to completely rewrite the current C++ error-throwing system, since not all errors are thrown anymore (some must be propagated into promises). This has implications throughout the entire package right now.
• I'd have to completely rewrite the .get(), .all(), .each(), and .run() methods to be abstract enough to use in the async and sync contexts. Otherwise I'd have a lot of duplicated logic for each routine.

In the end, there's an incredible amount of complications, performance drawbacks, and API bloat required to make an async API work. And it would be very difficult to use correctly, without shooting yourself in the foot.

The combination of sqlite3 and nodejs BEGS for a synchronous workflow. Anything else ends up being an antithesis to the problems that are trying to be solved. I highly recommend going with @slanska's proposal of using multiple processes. Not only does this solve the "long running query" problem, but it also provides more parallelism than libuv would be able to provide.

Heck, I might even create a new package specifically for this purpose. It could be a replacement for the cluster module, intelligently passing write requests to the correct process, and distributing read-only requests in a round-robin fashion to the other processes.

[wmertens]

Argh.

I was thinking that the heavy lifting for the queuing happens in a js
wrapper, and the only async call is .each. Would that not be a minimal
change?

On Wed, 14 Jun 2017, 7:29 PM Joshua Wise, [e-mail] wrote:

@slanska https://github.com/slanska shared cache is currently not
supported (except among in-memory databases, which automatically share
cache if they have the same name), but it would be trivial to add.

So, before investing time for implementing custom async mode, I'd
benchmark cluster/nginx mode to get an idea about possible performance
improvement and extra memory consumption

You have a good point. The other drawback of an async solution is that the
libuv thread pool (which is used for filesystem access and user-defined C
bindings, but not for unix socket/network I/O) has a default of only 4
threads, limiting the default parallelism compared to having multiple
processes.

Have you considered Berkeley DB with SQLite API?

This is not something I've looked into very much, but perhaps I should do
some research on it.

Another thought is that node-sqlite3 compatible API would be helpful for
better adoption of better-sqlite3

Good idea, I'd like to add that at some point.

@wmertens https://github.com/wmertens

The proposal here is only a very minimal async api: just (optionally)
wait for the sqlite_step() in a thread, but read the result in the main
thread. Small code change.

I've been working out how this would be built into better-sqlite3, and it
turns out it would essentially require a complete rewrite. Consider these
complications:

• Every exposed API function now must have the notion of "database is
  async busy" as well as "database is sync busy", instead of just "database
  is busy". Most operations would need to throw errors under both "async
  busy" and "sync busy" states, but the async methods would only throw errors
  under "sync busy" states (for example in the callback for .each() or
  during custom aggregate functions, which we already have protection for).
• When queuing async calls, the parameter bindings must be bound
  immediately (synchronously) because otherwise someone could mutate a bound
  Buffer before the query is executed. This means we'd have to bind
  parameters while another thread is potentially executing a query, which
  means we'd have to remove the SQLITE_THREADSAFE=0 compilation option,
  which the sqlite3 website says "is the single compile-time option that
  makes the most difference in optimizing the performance of SQLite".
• In order to access the database while a long-running async query is
  running (which I assume is the main point of having an async API), you'd
  need to have a connection pool, which would be an entirely new concept for
  better-sqlite3. This creates MANY other complications. What if you
  want to run a PRAGMA? Now it needs to be run on every connection in the
  pool. What if you run a PRAGMA to set up some configuration, and then you
  create a new connection in the pool? That new connection would be
  configured differently because it didn't exist when the PRAGMA was run. To
  make matters worse, not all PRAGMA are configuration. Some PRAGMAs you
  want to only run on a single connection (such as those that return
  data, rather than setting configuration). This would require more API bloat.
• Let's say we forget the connection pool concept. So a database
  connection just queues async queries and executes them FIFO. What if
  someone requests the same data from your web server twice in a row? You'd
  want to reuse the same prepared statement, but now you can't, because
  remember, the parameters must be bound immediately when an operation is
  queued (synchronously), but that prepared statement might be in use so we
  can't bind parameters to it! This means you wouldn't be able to reuse
  prepared statements, which would slow down simple queries by orders
  of magnitudes.
• Earlier, you said a sync operation should just throw an error if an
  async operation is running, but in a web server context you wouldn't be
  able to avoid that situation because a web server implies random access.
  Your only solution would be to only use async calls.
• I'd have to completely rewrite the current C++ error-throwing
  system, since not all errors are thrown anymore (some must be propagated
  into promises). This has implications throughout the entire package right
  now.
• I'd have to completely rewrite the .get(), .all(), .each(), and
  .run() methods to be abstract enough to use in the async and sync
  contexts. Otherwise I'd have a lot of duplicated logic for each routine.

In the end, there's an incredible amount of complications, performance
drawbacks, and API bloat required to make an async API work. And it would
be very difficult to use correctly, without shooting yourself in the foot,
performance-wise.

The combination of sqlite3 and nodejs BEGS for a synchronous workflow.
Anything else ends up being an antithesis to the problems that are trying
to be solved. I highly recommend going with @slanska
https://github.com/slanska's proposal of using multiple processes. Not
only does this solve the "long running query" problem, but it also provides
more parallelism than libuv would be able to provide.

Heck, I might even create a new package specifically for this purpose. It
could be a replacement for the cluster, intelligently passing write
requests to the correct process, and distributing read-only requests in a
round-robin fashion to the other processes.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-308502485,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWllmmv4nFcvcMkiH0o3DfUCbw2HbUks5sEBh8gaJpZM4Ndj6K
.

[JoshuaWise]

@wmertens, unfortunately it's not that simple. The queuing aspect is not the difficult part, the problems lie in everything else I listed.

SQLite does not neatly fit into the regular nodejs async pattern, without big sacrifices. I recommend optimizing your queries, instead of hiding the problem behind async calls. If your queries cannot be optimized to an acceptable speed, running multiple processes is the best solution. A second-best solution would be to use node-sqlite3 for your long-running queries, and better-sqlite3 for everything else.

If you need to do other async operations in parallel with your database query, you can use a pattern like this:

var filePromise = readFile(filename);
var httpPromise = request(someAddress);
var data = preparedStatement.get('SELECT * from table');
Promise.all([filePromise, httpPromise]).then(function ([file, httpResponse]) {
  // do something with `file`, `httpResponse`, and `data`
});

[JoshuaWise]

Actually, I just realized something. It's almost impossible to support an async API while also supporting custom SQL functions, because custom SQL functions must have access to the main nodejs thread while the query is running. In the C++ custom function wrapper I would have to sleep() until the v8 thread is ready, orchestrate running the JavaScript function within a synchronous C++ context, continue sleeping until that function is finished, and then resume the thread to finish the query. That would be terrible for so many reasons.

[JoshuaWise]

Closing this. I feel that the issue has been sufficiently ironed out, and that it's clear that the disadvantages would heavily outweigh the advantages of supporting an async interface. Feel free to comment with any more questions/concerns, though.

[JoshuaWise]

@slanska I know this is off-topic, but to give an updated answer to one of your questions, extensions are now supported.

[slanska]

Thanks for letting know, Joshua. I will check it out.

From: Joshua Wise [e-mail]
Reply-To: JoshuaWise/better-sqlite3 [e-mail]
Date: Monday, August 28, 2017 at 5:23 AM
To: JoshuaWise/better-sqlite3 [e-mail]
Cc: slanska [e-mail], Mention [e-mail]
Subject: Re: [JoshuaWise/better-sqlite3] Worried about synchronicity (#32)

@slanska I know this is off-topic, but to give an updated answer to one of your questions, extensions are now supported.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[JoshuaWise]

@wmertens, I was cleaning out old issues and I realized there's an approach that might solve your desire for an async API in better-sqlite3: https://github.com/JoshuaWise/better-sqlite3/issues/234#issuecomment-471659307

[wmertens]

Ah yes interesting approach, but since sqlite querying is lumpy, it can still cause multi-second hangs, right?

[JoshuaWise]

If your bottleneck is query complexity, then yeah it won't solve your problem. But if your bottleneck is retrieving/inserting/updating too many rows per query, then it should alleviate the thread blocking.

[dforsber]

Just out of curiosity, how hard would it be to make the C API async for simply reading purposes? For allowing IOPS saturation on cloud environments? Currently, I’m doing this with worker_threads, but would be better to do this natively. Or would it require too much rewriting?

[wmertens]

@dforsber I think https://github.com/JoshuaWise/better-sqlite3/issues/32#issuecomment-308800644 shows this to be nearly impossible.
By using worker threads you're also able to post-process data before providing it to the main thread, which might be a net win.

[dforsber]

@dforsber I think #32 (comment) shows this to be nearly impossible.
By using worker threads you're also able to post-process data before providing it to the main thread, which might be a net win.

I was more thinking about concurrent page fetching, ie concurrent async fs io. Which would mean that sqlite3 should be able to traverse index and form a list of pages to fetch (batch) and fetch them simultanoeusly.