BCHS Logo

BCHS

While ksql has been great to work with, it's too slow for my current needs. This article introduces sqlbox, which has a different API geared for much higher performance while offering the same (if not slightly better) security. This document is based on sqlbox v0.1.5.

background

ksql was started as a way to allow sqlite3 database access within a file-system constrained application process (typically as enacted with pledge). Since sqlite3 is both in-process and on the file-system, this necessitated a separate process with file-system access. Parent and child would communicate synchronously over sockets.

The library has served this purpose admirably. But as my application needs grew into performance alongside security, I found it difficult for ksql to keep up. Several issues arose, which would be difficult to address without drastic API or backend changes:

The performance issues may be visualised by the penalty of creating, opening, operating upon, then closing an in-memory database. (These graphs also show that the process of allocating the database is quite expensive!)

graph of full database cycle graph of full database cycle

The real cost may be seen in preparing and executing statements, with each step (har, har) along the way being communicated synchronously over a socket communication.

graph of result gathering graph of result gathering

There are similar penalties in acquiring resulting rows, but that I'll discuss later.

To fix some of the issues would require completely overhauling the implementation, such as split-process mode. Others, like performance, would require a significant API change. Since ksql has shown itself to be a very stable piece of software, it makes more sense to simply start again with a new API in a new library, inheriting if not code, then at least backend processes from the origin.

design considerations

In writing sqlbox, I focussed on several points:

Wait-free operation stipulates that the exchange of data between the application and database process, where the former waits for a response from the latter, is limited to only necessary steps. This allows both processes to perform work in between requests, greatly increasing performance.

Caching responses linearly increases performances by having the database process pre-step as many results as possible while waiting. After returning a cached payload, the application simply steps through result rows in memory and need not contact the database application.

For optimum performance, the database process collects results after responding to a prior step request that returns results. This takes place during the waiting period while the application is operating on existing data.

API redesign compresses the binding operation into statement preparation or execution. This means that instead of invoking one round-trip operation per column, there's only one per statement.

implementation

Like ksql, sqlbox uses native operating system security features to constrain the database process. The application is responsible for doing so itself. On OpenBSD, the platform of choice, this is enacted by pledge. There are options for similar security levels on FreeBSD using Capsicum and overriding how sqlite3 opens files, but this is not currently on the roadmap.

Unlike ksql, sqlbox has a robust set of regression tests to verify that the behaviour stipulated in its manpages is consistent with the implementation. To date, there are over 150 tests. This framework ensures that changes to the implementation do not affect expected behaviour. It also ensures that border conditions are properly handled.

% make regress
test-alloc-bad-defrole... ok
test-alloc-bad-filt-stmt... ok
test-alloc-bad-role... ok
test-alloc-bad-src... ok
[...]
% make valgrind # "regress" but under valgrind

In the code itself, another significant difference is that (with very few exceptions) requests and responses use fixed-sized minimum packet sizes for communication. At the moment, this is fixed at 1024 B. This means that both processes read at least 1K for each communication. Why? It allows up to 1K of request data (parameters, response rows, etc.) to be written into each packet with a single read() instead of requiring one for size, one for data. Transmissions of greater than 1K read only the remainder. The choice of 1K is simply to be less than the default socket buffer size. (It can be tuned at compile time.)

results

Even at this early stage of development, the results are quite impressive. The following chart the overhead of ksql versus sqlbox over the native sqlite3.

graph of full database cycle graph of prep-bind-step cycle

The benefit of the first graph (full-cycle operation) is not so clear considering that the underlying expense of creating the database outweighs the communication costs. The second graph (prepare-bind-step cycle) is more visible due to the API reflow and wait-free operation.

The following shows the benefit of caching step statements. The first graph does not cache; the second does. Both use the same code for generating the ksql case. We see, in the first, simply the cost of two synchronous operations transferring bytes. sqlbox is approximately twice as fast because it this in one step.

without multi-select with multi-select

It's clear that stuffing result rows has a noticable benefit in terms of performance.

future work

With a cleaner API and back-end, it's much easier to add further improvements. First, the implementation should compute and exchange the minimum buffer size at startup. This is also necessary for actual socket communication where the buffer sizes will not be symmetric.

Second, the multi-step result caching can be considerably improved. For now it just uses a fixed-sized buffer for gathering results (10K, specifically). This should be determined at run-time.