Support automatic VACUUM in rqlite about rqlite HOT 7 CLOSED

The proposal is to support the periodic VACUUM of the SQLite database managed by rqlite. The feature would work be controlled follows:

• Two new command line flags would be added to rqlited, which would allow operators to control the automatic and periodic VACUUM of the SQLite database managed by rqlite:
  • First command line flag would be -auto-vacuum-int and would accept a Go-format time duration string, such as 7d. This would set the interval between VACUUM attempts. If not set, then no automatic VACUUMs are executed on that node.
  • The second command line flag would be -auto-vacuum-size which would specify the minimum size (in bytes) that the SQLite database file must be before a VACUUM is executed. 0 is a valid value to set, and means a VACUUM will be executed every cycle.

The VACUUM flags should be set on every node on the cluster, including read-only nodes, for which VACUUM is required. It will not be necessary to have identical VACUUM settings on each node however.

Each node will persistently store the last time a VACUUM successfully executed, so even if the node is restarted, it will next attempt a VACUUM on schedule.

VACUUM is an IO-intensive operation, and will also require a fair amount of free disk space. Operators should ensure that there is at least 2 times the amount of free disk space as the size of the SQLite database about to be VACUUMed. Writes will also be blocked while a VACUUM operation is taking place. No error will be returned, instead the HTTP API will not respond until the VACUUM has completed, the the pending write request completes.

I may also add a HTTP endpoint that allows a VACUUM to be executed on demand, on that specific node.

from rqlite.

LGTM!

from rqlite.

Some notes for my own use. It appears that when VACUUM is done, only the WAL file is affected. This is as written in the SQLite VACUUM docs. However there is enough ambiguity about whether the main SQLite file in touched too that a full snapshot is the only way to ensure the system is correct. Furthermore, for large VACUUMs, it would be too memory intensive anyway, so full snapshot remains the conservative approach.

from rqlite.

Unit test in #1615 confirms VACUUM introduces uncertainty, so going with the conservative approach of a full snapshot post a VACUUM makes sense.

from rqlite.

Update - unit test has been fixed, a full snapshot may not actually be necessary. More testing required.

from rqlite.

@jmordica -- after doing more research, I'd like to propose the following change: instead of simply initiating a VACUUM when the database reaches a certain size, instead initiative a VACUUM when:

(number of free pages)/(number of total pages) >= some fraction

that's what VACUUM really does -- it rewrites the database so there are no free pages, and all the existing pages are contiguous. There would still be a check interval. So you can imagine launching a node like so:

rqlited -auto-vacuum-int=1d -auto-vacuum-free=50

which would tell rqlite to consider doing a VACUUM every day, and only proceeding if the fraction of free pages has hit 50%.

Sound good @jmordica ? This would avoid any useless VACUUMs just because the database reached a certain size, but free page count was actually quite low.

from rqlite.

I think we can make this even easier to operate, and ditch the timer. We simply state that rqlite will perform an automatic vacuum anytime the free/total page ratio gets above the specified fraction. Since VACUUM (as best that I can tell) ends up reducing the free count to zero, rqlite will naturally wait until it needs to VACUUM again. It's basically like JVM, Go, etc and garbage collection. It's more-or-less self-rate-limiting.

from rqlite.