Sqlbox Crack

0 views

Skip to first unread message

Marguerite Gilbeau

unread,

Aug 3, 2024, 5:13:10 PM8/3/24

to octennutur

ksql was started as a way to allow sqlite3database 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 aseparate 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, thenclosing an in-memory database.(These graphs also show that the process of allocating the database is quite expensive!)

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

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 manyresults as possible while waiting.After returning a cached payload, the application simply steps through result rows in memory and neednot contact the database application.

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

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

Unlike ksql, sqlbox has a robust set of regression teststo 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.

In the code itself, another significant difference is that (with very few exceptions) requests andresponses 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 witha 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.)

The benefit of the first graph (full-cycle operation) is not so clear considering that theunderlying 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-freeoperation.

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.

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.