[IGN-6097] Internal DB maintenance-SQLite VACUUM

I have a config.idb that is 1.9gb; however if I do a test vacuum operation on it it will shrink to ~90mb.
Is it safe to do this? Is there a different, approved means of compacting the db?
The gateway is 8.1.17.
I’m assuming I’d need to stop the gateway, backup the db, etc, to do this, which isn’t a problem as I need to update anyway.
There is a warning:
The VACUUM command may change the [ROWIDs] of entries in any tables that do not have an explicit [INTEGER PRIMARY KEY].
https://www.sqlite.org/lang_vacuum.html
I just don’t know if that is something that matters…
EDIT: the version is actually 8.0.17, not 8.1.17

The gateway should be periodically being VACUUM`ed by Ignition itself.

Can you contact support about this? It would be good if we could see what’s in your IDB before and after it shrinks, and maybe your logs to see why our auto maintenance isn’t working.

What is the size of the config.idb autobackups in the ${IGNITION_INSTALL_LOCATION}/data/db/autobackup/ folder? From taking a look at things, it looks like the VACUUM operation is performed against newly created auto-backups.

The autombackup files are getting vaccumed.


image
Yes, I can contact support about it. Is there anything in particular to look for in the logs?

Actually, I was wrong, and we’re apparently not doing the vacuum directly on the config.idb.

I think it should be totally safe to do (to an offline system), though I would recommend having a backup of the full-fat .idb just in case something does go wrong.

Does this mean you don’t need the active and backup dbs/logs?
(IE it seems like the root cause is known, the db isn’t vacuumed automatically, only the backup files)
Just about to upload the 1.4gb zip file

It’ll mainly be an exercise to see what is consuming 1.4gB in your DB (that’s apparently not needed, since it’s flushed out by a VACUUM). Figuring out what’s causing that bloat should be a separate task (in addition to, in my opinion, introducing a vacuum on the config.idb itself).

Roger that, uploading now. I suspect that it got bloated by having a bunch of images loaded into it at one point, then stripped out.

Whoops, 50mb limit on attachments.

Support should be able to generate a link for you to upload the file to.

Can try-my IT department blocks almost all methods. If it is useful, I have no issue pursuing

You could also download the CLI sqlite-tools yourself:
https://www.sqlite.org/download.html
And run sqlite3_analyzer against your IDB. If you pipe it to a file, that’s probably going to tell us as much as the original file would.

Just did, the request has a link to this thread and references your name; just got the ticket number as 49024

1 Like

For anyone finding this later, I was able to stop the gateway, run vacuum using the browser
https://sqlitebrowser.org/ and restart it. No issues observed.