content-sqlite: preallocate database space

[garlick]

Problem: when the disk containing the sqlite database fills up on rank 0, flux does not handle it well, e.g.

• content.flush: allow return of "doomsday" errors #6155
• kvs.err[0]: content_load_completion: content_load_get: No such file or directory #6154
• rc3: can hang if disk out of space (ENOSPC) #6100
• idea: do not start broker if content store ENOSPC on startup #6099
• content: pending stores are stuck after ENOSPC from backing store #5978 (edit via @chu11)

@trws suggested providing an option to pre-allocate space in the database.

One approach is to write a huge blob to the db and remove it:
https://stackoverflow.com/questions/827010/sqlite-pre-allocating-database-size

Some steps may also need to be taken to ensure writes to content.sqlite-wal do not fail. More study required on that one:
https://www.sqlite.org/wal.html

[chu11]

i was thinking about this last night and my initial intuition was that kind of trick wouldn't work. I thought that in most modern databases they constantly append new data and the only way to reclaim disk space is through "compacting"/"defragmenting" it. In sqlite that appears to be via "vacuum" (https://www.sqlite.org/lang_vacuum.html).

And even if we did this, we might be racing against the thingie filling up disk (e.g. errant logging). But it's better than nothing.

The hack above seems to work around this fact by pre-allocating an entire database table then dropping it. So perhaps this will work around it, but it seems to suggest we need to pre-allocate the entire database table before creating it the first time? Perhaps this idea might work if we pre-allocate critical "checkpoint" and similar things to a separate database table than the core KVS? (I guess this would be via some other backing store ... Or maybe a way to load backing store module twice?)

Nevertheless, it's more than worthwhile to experiment with this idea and see.

[garlick]

We should do an experiment, but based on those stack overflow comments, it seems like sqlite must reuse space within the db file, and vacuum just shrinks the file to not contain any free space.

And even if we did this, we might be racing against the thingie filling up disk (e.g. errant logging). But it's better than nothing.

I was thinking this would be done up front when flux starts. The sys admins would configure content.sqlite.preallocate = "20G" or something and if that cannot be grabbed when the module is initialized it fails and flux fails to start. So if the disk fills up later, we're immune.

Same basic idea as a partition but apparently a partition is more of a pain for the sys admins.

[chu11]

So the first test didn't seem to work.

Created a 5M tmpfs.

$ rm -rf /test/tmpfs-5m/statedir/*
$ df -h /test/tmpfs-5m/
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           5.0M     0  5.0M   0% /test/tmpfs-5m
$ src/cmd/flux start -o,-Sstatedir=/test/tmpfs-5m/statedir
(inside instance)
$ exit
$ ls -lh /test/tmpfs-5m/statedir/
total 20K
-rw-r----- 1 chu11 chu11 20K Aug  5 23:30 content.sqlite

so we got about a 20K database file. Lets do the trick listed in the stackoverflow.

$ sqlite3 /test/tmpfs-5m/statedir/content.sqlite
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> create table large (a);
sqlite> insert into large values (zeroblob(1024*1024));
sqlite> drop table large;
sqlite> .q
$ ls -lh /test/tmpfs-5m/statedir/
total 1.1M
-rw-r----- 1 chu11 chu11 1.1M Aug  5 23:31 content.sqlite

ok good, now the database is listed as about 1 meg.

re-start the instance w/ this statedir, and fill up the statedir with some kruft via script I wrote.

$ src/cmd/flux start -o,-Sstatedir=/test/tmpfs-5m/statedir
$ ./filldir.sh /test/tmpfs-5m/statedir/
$ df -h /test/tmpfs-5m/
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           5.0M  5.0M     0 100% /test/tmpfs-5m
$ ls -lh /test/tmpfs-5m/statedir/
total 5.0M
-rw-r----- 1 chu11 chu11 1.1M Aug  5 23:31 content.sqlite
-rw-r----- 1 chu11 chu11  32K Aug  5 23:31 content.sqlite-shm
-rw-r----- 1 chu11 chu11  57K Aug  5 23:31 content.sqlite-wal
-rw-r----- 1 chu11 chu11 100K Aug  5 23:31 foo0.txt
-rw-r----- 1 chu11 chu11 100K Aug  5 23:31 foo1.txt
-rw-r----- 1 chu11 chu11 100K Aug  5 23:31 foo10.txt
<snip>

So if sqlite is re-using the deadspace, I should be able to run a job. But it didn't work

$ flux run hostname
Aug 05 23:32:38.790233 UTC content-sqlite.err[0]: store: executing stmt: database or disk is full(13)
Aug 05 23:32:38.790273 UTC content-sqlite.err[0]: store: executing stmt: database or disk is full(13)

My suspicion is that this issue is b/c we use the write-ahead-log. There may technically be space in the actual database that can be re-used, but there is no space for the write ahead log.

Further playing around / investigation to be done.

---

As an aside, I tried using VACUUM

$ sqlite3 /test/tmpfs-5m/statedir/content.sqlite
SQLite version 3.26.0 2018-12-01 12:34:55
sqlite> vacuum;
sqlite> .q
$ ls -lh /test/tmpfs-5m/statedir/
total 4.0M
-rw-r----- 1 chu11 chu11  20K Aug  5 23:33 content.sqlite

$ src/cmd/flux start -o,-Sstatedir=/test/tmpfs-5m/statedir
$ flux run hostname
fluxorama
$ flux run hostname
Aug 05 23:34:23.608985 UTC content-sqlite.err[0]: store: executing stmt: database or disk is full(13)

so that did do the trick to free up some space.

[trws]

Ouch, I forgot about the separate -wal file, that makes the whole thing a lot less plausible. We might legitimately be better off making ourselves a little loopback or fuse filesystem or something. It's not as nice for any number of reasons, but we could do it entirely without admin involvement if we need to. This is what I usually do to get a 1gb ext4 FWIW:

$ mkdir mnt
$ dd if=/dev/zero of=loopbackfile.img bs=100M count=10
$ mkfs.ext4 loopbackfile.img
$ unshare -rm bash
# fuse2fs loopbackfile.img mnt

The trick would be we'd have to both do the mount and launch the broker under the unshare. Not sure if it's worth it, but it would work.

[chu11]

Confirmed that turning journaling off (PRAGMA journal_mode=OFF) the pre-allocation trick works.

[chu11]

@trws I assume the goal of using unshare is to hide the mount from other users. Is it strictly necessary? Especially on rank 0, (i.e. mgmt node that normal users aren't on)?

[garlick]

Let's not do a loopback mount. The fact that our admins are inexplicably resistant to cordoning off space on elcapi doesn't seem like justification for adding this to Flux, and it would create complications for access from other than the broker, e.g. during recovery or backup.

Plus it feels like we're taking a "no" from the admins and turning it into a "yes" with jedi mind tricks. They control the flux config and the fstab. If I were an admin, I would summarily dismiss this option and either create the fstab entry or not.

[trws]

@trws I assume the goal of using unshare is to hide the mount from other users. Is it strictly necessary? Especially on rank 0, (i.e. mgmt node that normal users aren't on)?

That's a side-effect, but no that's not the reason. It's there to create a mount namespace so that we can mount the fuse filesystem without admin access. It makes it completely doable as just the flux user, in the systemd unit or a script or similar.

To your point @garlick it's clearly not at all ideal, I do get that. If we could grab a library and use it to just open a regular file as a backing store to use to let us do this, that would be a whole lot more satisfying, just trying to throw out whatever I can think of in hopes maybe we'll come up with something better. 😕

There are such things, but I don't know if there's a good way to hook them into sqlite. Maybe that's something to look at, alternate storage hooks backend for sqlite that would let just point it at a pre-allocated file? Will do some digging.

[garlick]

just trying to throw out whatever I can think of in hopes maybe we'll come up with something better.

Gotcha.

We might try again asking for a proper file system and avoid expending too much effort here.

[chu11]

A random idea that crossed my mind, so I'm just going to throw it out there so i don't forget (super related to issue related to #6154).

The pre-allocate trick might be something we can use for "desperate" and "important situations", e.g. we're tearing down and trying to checkpoint final rootrefs. In those desperate situations, could just turn off write-ahead-log, etc. We could perhaps just allocate a few megs in the sqlite database for these desperate situations and delete it if needed.

[garlick]

Oh that's a good idea! Couldn't we just pre-allocate whatever large amount of space we want, then if we ever get ENOSPC, turn off the WAL and try again?

[chu11]

Oh that's a good idea! Couldn't we just pre-allocate whatever large amount of space we want, then if we ever get ENOSPC, turn off the WAL and try again?

Oh that's a fair point, I didn't even think of that. If we hit ENOSPC, we could just turn off WAL in general.

I guess in my mind the general risks associated with not having WAL wasn't worth it in the general sense. We only want to use it in "desperate times".

[garlick]

Maybe that's something to look at, alternate storage hooks backend for sqlite that would let just point it at a pre-allocated file? Will do some digging.

The first hit that came up was this. Pluggable sqlite backend store is...a KVS! Yow. This is what it feels like when you realize you might have one two many abstraction layers.

[chu11]

Oh that's a fair point, I didn't even think of that. If we hit ENOSPC, we could just turn off WAL in general.

Hmmmm. If we pre-allocate, is it safe if we turn off WAL and turn synchronous to "FULL"?

FULL (2)
When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. FULL is the most commonly used synchronous setting when not in WAL mode.

from https://www.sqlite.org/pragma.html

I'm thinking it is .... I can't convince myself otherwise right now.

Edit: in otherwords, when we reach this ENOSPC bad state ... making sqlite slower is an acceptable trade off to ensure we continue operations.

Edit 2: Hmmmm, maybe not as safe as we'd like.

The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The ROLLBACK command no longer works; it behaves in an undefined way. Applications must avoid using the ROLLBACK command when the journal mode is OFF. If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt. Without a journal, there is no way for a statement to unwind partially completed operations following a constraint error. This might also leave the database in a corrupted state. For example, if a duplicate entry causes a CREATE UNIQUE INDEX statement to fail half-way through, it will leave behind a partially created, and hence corrupt, index. Because OFF journaling mode allows the database file to be corrupted using ordinary SQL, it is disabled when SQLITE_DBCONFIG_DEFENSIVE is enabled.

So journal_mode = OFF is more risky. Setting journaling to PERSIST may be possible?

The PERSIST journaling mode prevents the rollback journal from being deleted at the end of each transaction. Instead, the header of the journal is overwritten with zeros. This will prevent other database connections from rolling the journal back. The PERSIST journaling mode is useful as an optimization on platforms where deleting or truncating a file is much more expensive than overwriting the first block of a file with zeros. See also: PRAGMA journal_size_limit and SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT.

I'm not sure if we could "pre-allocate" a sqlite journal flie so that it is always there.

Or there could be a "chain" of riskiness as we get more and more desperate

[garlick]

Yeah, that's kind of what I meant. I have no idea if the transition is safe. Might have to close the database (which I assume would fully process the WAL) and reopen it again with different pragmas?