Smokedetector: Shall we move from pickles to SQLite3 for local data storage?

[iBug]

We're currently running all kinds of local storage on Python pickles. While there hasn't been significant problems with them, there's some room for improvement.

I came up with the idea of employing a light-weight, easy-to-use DB (I vote for SQLite 3). This way we control the local storage better since there's only one .db file, SQLite 3 is also easy to setup - no setup needed actually :smiley:.

We aren't going to make it too complex - one table may contain only 3 or so columns. As I initially thought, we may be using only SELECT, INSERT, UPDATE and DELETE - Smokey's data isn't any complex at all, not even JOIN or such queries, making this easy for people with virtually zero DB knowledge to maintain (I'm one of them).

Benefits we're getting:

• Less RAM usage
• Easier data inspection and maintenance (use sqlite3 CLI tool)
• Easier backup, migration, adding new stored content (like logs), and intra-instance transfer
• Potentially less disk I/O (dubious)
• No more pickling/unpickline errors, unless the whole .db file is corrupt, which happens way less than a pickle file corrupting
• Potentially easier migration to Helios (if it's still alive)

What we're paying for this:

• More frequent disk I/O
• Potential difficulty in expanding columns
• Potential performance degradation (disk is always slower than RAM)
  I don't this one will be very much - the majority of CPU are spent running regexes, and the majority of idle time are spent waiting network responses. In case a server has a slow disk, this would be an issue. Otherwise, not much.

An early draft of an example is in the db branch that contains the infrastructure, as well as migrated blacklisted users from pickles to DB. CI is passed and it can be safely merged now.

Is it a good idea?

[tripleee]

pickle already uses DBM internally. It's not an SQL database, but it's certainly a database.

[iBug]

@tripleee I don't get it. Isn't pickle a library for Python object serialization? IMO it just serializes Python objects so they're suitable for storage, unlike an SQL DB which allows querying without loading the whole DB (object). If there's a way to query a pickle, that's surely an improvement.

[tripleee]

The operative term here is SQL. If you want to be able to query the database with SQL, that's one advantage which SQLite provides. But the current code already sucks the data into Python from (what is in fact) a database container and then you can do what you like with it. I don't see any direct benefit from SQL as such; if you want the data to be human-readable, JSON seems like a much simpler and more natural solution.

[iBug]

@tripleee Yeah I'm also not steadily firm on that. But IMO we're going to benefit more than handling existing data: we can even create a DB for error logs and have different processes R/W simultaneously. This is more a long-term move than a simple migration.

[ArtOfCode-]

This is part of a wider discussion. We've had questions before about whether we should use a database; we've put them off because Helios was/is slated to provide this kind of functionality.

So. The discussion we need to have is "Is Helios likely to see enough work that it's worth continuing to put Smokey upgrades off?".

Personally, I'm in favour of having a database for Smokey. That may be because it's a medium with which I'm more familiar, but also because it does provide a bunch of advantages and synchronizability that we don't have with pickles.

[Undo1]

We've talked about this before. I definitely think introduces a bunch of new concerns (and ones we haven't even thought of yet). This line is telling:

we may be using only SELECT, INSERT, UPDATE and DELETE

That's not really a database, that's a fancy text file. It's my opinion that work would be better allocated on fixing pickle issues. That said, I could be swayed with a few solid upsides. Better async support might be one of those.

The people best suited to weighing the pros and cons at a technical level probably aren't me. @tripleee and others are well suited for that. I just want to be a voice of "ya'll really want to open that can of worms?".

And standard rules apply: I won't try to keep anyone from working on something that interests them - I just can't promise it gets merged.

[iBug]

@Undo1 The first paragraph reads:

While there hasn't been significant problems with them (pickles)

And yeah, that's an important reason I'm asking first. I am highly unsure if it's immediately beneficial, but I have a feeling that it will be useful sooner or later.

I am also a near-zero-DB man and my only experiences are modifying data of random Android apps (they use SQLite3 intensively), so I can't tell if my judgments are fair or just "looks like".

[Undo1]

Pickles do have the sync problem, where it'd be nice to move data between instances in some way. I'm saying I'd rather divert work to fixing the problems using the existing pickle solution unless there's some huge blocker there that requires real databases.

[angussidney]

Kinda related: https://github.com/Charcoal-SE/SmokeDetector/issues/657

[iBug]

@Undo1 Maybe take a look at #2532? Are you sure this isn't just another "I was not in favor of this at first, but it turns out to be handy" thing?

[AWegnerGitHub]

Chiming in on the Helios points:

I'd love to move over to Helios. It's close to a year since the initial framework was set up and that's where it (mostly) died. Interest in converting to it seems to have come to a halt. I have no problem with this - it is a rather large change (and even more so now that my Smokey branch is a year out of date), it requires changes to both Smokey and Metasmoke and effectively adds a third peg on which the entire solution stands on.

If we aren't moving to a cloud based database (aka. Helios), I am fully in support of moving away from pickles (and config data, but I already argued that point too). @Undo1 is right that there is some synchronization issues, but they are going to be related to the config data I mentioned. In terms of keeping the blacklists in sync, I don't think we'd see anything different than we do today.

@iBug Something that would be helpful to see is a comparison in terms of file sizes. SQLite has the vacuum command that can optimize space, but I don't know how well it works, especially after adding/removing "a lot" (trademark pending) of entries. How does this single database compare in terms of on disk usage and in terms of memory usage when loading data into memory.

[iBug]

@AWegnerGitHub My idea is to only move program-altered data to SQLite, and therefore does not contain configuration and blacklists.

I haven't coded much of it (primarily because Undo's disagreement made me hesitate), but I have some estimates.

For the file size part, I am fairly sure that a single SQLite3 database will be slightly bigger than all the pickle files summed up, due to the nature of an SQL DB, although I'm not sure by how much it will be bigger, maybe tens of KBs or up to 100KB more. Also, I've taken VACUUM into consideration.

Memory usage reduction is anticipated. Analyzing existing pickles, I think there will be a reduction from 5 MB to 20 MB, depending on how much data stored in those pickles (remember: we're currently loading all pickles at startup, and the real Python objects can take up to 8x as much space when in memory vs. serialized). This is also listed in the first comment as a spotlight.

If a general consensus is formed, I may continue working on that and do more benchmarks.

For the Helios thing, I don't have any idea about it - haven't even taken a look yet :( so can't say anything.

[tripleee]

To the extent that this adds benefits, those seem to be shadowed by the benefits that Helios would bring. So let's try to revive the Helios branch instead, shall we?

[ArtOfCode-]

Gonna [status-decline] this for now. I don't think we have the development hours to properly write, test, and support a major feature change like this. Helios... ain't happening, that much is for sure, but there's also no _pressing_ need to move to SQL.