Vacuuming the node backup

[shawn-zil]

When a backup of a full node is taken, it ZIPs up the contents of the data directory as-is. There is an opportunity to use VACUUM, which is an opportunity to clean up and defragment the database.

The cons is that it will require more disk-space i.e. the free-space must be able to hold the entire defragmented database; and that it will take more time - factoring in the vacuuming.

The pros is that any node restored from this backup will have a, slightly smaller and more efficient database, clean of cruft.

An alternative argument could be made to vacuum at restore, rather than at backup. That depends on our use case for backup/restore operations e.g. disaster recovery, preventive maintenance, etc.

---

I will do a quick test with prototestnet data to see if it is worth the trouble.

[shawn-zil]

Quick test:

Time took ~8-mins.

sqlite> VACUUM INTO "vacuum.sqlite3";
Run Time: real 480.582 user 15.761978 sys 69.683466

Size is ~10% smaller.

ls -lh *.sqlite3
-rw-rw-r-- 1 shawn shawn 41G Jun 11 16:27 db.sqlite3
-rw-r--r-- 1 shawn shawn 37G Jun 12 11:26 vacuum.sqlite3

Took me more time to download the file ~25 MB/sec, than to vacuum it ~77 MB/sec. While this depends entirely on my broadband and disk I/O speed, it is something to consider when deciding whether to vacuum at backup/restore.

[shawn-zil]

@frankmeds / @pavlops would appreciate your thoughts on this issue - whether it is worth integrating a VACUUM during the normal backup process at the persistence node. It might also be beneficial with z2 backup too. cc @DrZoltanFazekas

Pros:

1. improves query times by defragmenting the DB file and reducing IOPs needed for queries;
2. reduces cost of long term storage by freeing up unused disk space caused by DELETE/UPDATE operations;
3. reduces transmission time to the storage bucket, and/or downloading over the internet, due to smaller file size;
4. benefits also accrue when a node is restored from the backup (faster download, smaller disk, faster queries, etc).

Cons:

1. The persistence node would require enough free disk space to hold a separate copy of the DB while vacuuming is in progress;
2. It takes more time to backup due to the extra step to vacuum it first (time saved from transmission might make it worthwhile).
3. Since the DB cannot be used while vacuuming is in progress, we cannot do this on the normal (api/validator) nodes.