Inside Jidometa: Concurrency with Mosquitto (MQTT) and SQLite

We’re beginning a series of articles to discuss the Jidometa internals.

We want to begin by talking about how Jidometa handles concurrency.

In the past…

Our initial SaaS service - Jidoteki - was built using Ruby, Redis, and Resque for concurrent builds. We quickly ran into issues when aiming to scale beyond one server. Those technologies were definitely great, but quite limiting in our ability to build across a cluster of servers (without implementing our own wacky scaling scheme - no thanks).

We replaced Resque with RabbitMQ, which served us extremely well for a few years. The wonderful administration interface, its somewhat low overhead, and the fact that all our apps (ruby, nodejs, bash) could easily talk to it were a plus.

The present..

With our move from SaaS to On-Prem appliance (Jidometa), we realized RabbitMQ would be overkill for such a deployment. We wanted to build an appliance with minimal dependencies, and a tiny footprint.

We chose to replace Redis and RabbitMQ, with SQLite and Mosquitto (MQTT).

Weird stack

I haven’t found any public articles on doing concurrency in SQLite using Mosquitto, so it was an adventure attempting to do it without guidance. Perhaps our stack is weird, considering we mostly write our code in PicoLisp now, but since we were already quite familiar with it, the decision was simple.

As many know, SQLite on its own is not ideal for write concurrency. Some best practices include: 

Doing concurrent writes

To solve the problem of concurrent writes in SQLite, we opted to serialize our database writes over an MQTT “queue”. I quote queue because it’s not technically a queue, although with the correct settings, it can be (mosquitto.conf):

Those are some options which will allow you to have a safe, FIFO-based MQTT “queue”. Since database writes are really important, it’s necessary to use QoS 2 on both ends (publisher and subscriber).

Our publisher and subscriber

This is where PicoLisp comes in. At the moment we’re not using a native library to access SQLite from within PicoLisp. We’re using a much less efficient system command: (call ‘sqlite3).

We wrote a subscriber daemon which listens for messages on the mosquitto/MQTT queue, and then processes the messages, validates the data, and performs the DB write. The messages can arrive concurrently, but only one message will ever be processed at a time - thus enabling serial DB writes. You can think of it as Unicorn with only 1 worker.

On the other end, the Jidometa API and backend scripts publish messages to the queue whenever something needs to be processed and written to the database. Those published messages can occur concurrently, at very high rates, without worrying about messages being missed, lost, or dropped (since it’s all occurring locally over localhost).

All DB reads are made directly to the SQLite database file, since reads can be concurrent and won’t affect the database in any way.

Big advantages

The added benefit of enabling MQTT behind the scenes, is the mosquitto server also ships with native built-in Websocks and TLS support.

We’ve opted to use that for reading and publishing build log messages, status updates, and other things which update the browser. On the browser side, we’re using a slightly modified version of the Paho MQTT JavaScript library. Our modifications include the ability to store messages in cookies, and making LocalStorage optional.

Moreover, we can easily extend the worker to perform other tasks when it receives certain messages, such as running integration scripts, performing basic maintenance, sending out alerts..

No locked database

Since implementing our concurrency in sqlite using mqtt, we’ve eliminated “database is locked” errors and are able to use the tiny file-based database the way we want.

Of course, we could have avoided this by using a different relational database (ex: PostgreSQL), but it wouldn’t have given us the wonderful properties of a tiny footprint, Websockets, and MQTT.

See part 2, where we provide more details regarding the Jidometa internals.