#3697: sqlite db: automatically enable vacuum
---------------------------+-------------------------
Reporter: mlennert | Owner: grass-dev@…
Type: enhancement | Status: new
Priority: normal | Milestone: 7.8.0
Component: Database | Version: unspecified
Keywords: sqlite vacuum | CPU: Unspecified
Platform: Unspecified |
---------------------------+-------------------------
I just noticed that a mapset sqlite db file was huge, despite the fact
that there was only one small vector file left in it. Running 'vacuum;' in
the sqlite database reduced the announced file size from 47G to 78K !
There is a pragma auto_vacuum which allows to enable an auto-vacuum mode,
which is not as efficient as calling the vacuum command, as it does not
defragment the database, but I would think that it would at least keep
file size to expected levels.
I would, therefore, plead for enabling this pragma by default at database
creation and setting it to 1 (full). I think this would be the behavior
expected by most users.
Replying to [ticket:3697 mlennert]:
> I just noticed that a mapset sqlite db file was huge, despite the fact
that there was only one small vector file left in it. Running 'vacuum;' in
the sqlite database reduced the announced file size from 47G to 78K !
>
> There is a pragma auto_vacuum which allows to enable an auto-vacuum
mode, which is not as efficient as calling the vacuum command, as it does
not defragment the database, but I would think that it would at least keep
file size to expected levels.
>
> I would, therefore, plead for enabling this pragma by default at
database creation and setting it to 1 (full). I think this would be the
behavior expected by most users.
This auto_vacuum would occur at every transaction commit. Maybe it is more
efficient to VACUUM the sqlite db now and then, e.g. when the database is
closed or when the GRASS session is terminated.
VACUUM when the database is closed would still be quite often, e.g. with
every SELECT statement or also just describing the table.
I would prefer to VACUUM the sqlite db when the GRASS session is
terminating, i.e. in lib/init/grass.py. This would not slow down standard
db operations and leaves the db in a clean state.
Replying to [comment:1 mmetz]:
> I would prefer to VACUUM the sqlite db when the GRASS session is
terminating, i.e. in lib/init/grass.py. This would not slow down standard
db operations and leaves the db in a clean state.
I would like that idea as well to add that during session termination.
Perhaps likewise also for PostgreSQL and mySQL/MariaDB?
Replying to [comment:1 mmetz]:
> I would prefer to VACUUM the sqlite db when the GRASS session is
terminating, i.e. in lib/init/grass.py. This would not slow down standard
db operations and leaves the db in a clean state.
+1
You are absolutely right, of course
For me this can be committed to trunk. We'll see then if it has any
negative consequences.
Replying to [comment:4 mlennert]:
> Replying to [comment:1 mmetz]:
> > I would prefer to VACUUM the sqlite db when the GRASS session is
terminating, i.e. in lib/init/grass.py. This would not slow down standard
db operations and leaves the db in a clean state.
Added benefit: already existing databases are also cleaned, while with
PRAGMA auto_vacuum=1 only newly created databases would be cleaned.
* lib/init: clean up sqlite db if existing, see #3697
* libpython: move clean_default_db() to setup
* libinit: use clean_default_db() from script.setup
* libpython: add functions to finish a GRASS session
* libinit: start rewriting to use functions in lib/python/scripts
* libinit: fix r73707, clean_all() takes no arguments
}}}
