Opened 11 months ago

Last modified 7 months ago

#3697 reopened enhancement

sqlite db: automatically enable vacuum

Reported by: mlennert Owned by: grass-dev@…
Priority: normal Milestone: 7.6.2
Component: Database Version: svn-releasebranch76
Keywords: sqlite vacuum Cc:
CPU: Unspecified Platform: Unspecified

Description

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.

Attachments (1)

sqlite_vacuum_atexit.patch (766 bytes) - added by mmetz 11 months ago.
VACUUM sqlite db when GRASS is terminating

Download all attachments as: .zip

Change History (14)

comment:1 in reply to:  description ; Changed 11 months ago by mmetz

Replying to 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.

Changed 11 months ago by mmetz

Attachment: sqlite_vacuum_atexit.patch added

VACUUM sqlite db when GRASS is terminating

comment:2 in reply to:  1 Changed 11 months ago by neteler

VACCUMing is a great improvement!

Replying to 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?

comment:3 Changed 11 months ago by neteler

Kitchen talk: grass.core.start_command() might be even more fun launched upon session termination :-)

comment:4 in reply to:  1 ; Changed 11 months ago by mlennert

Replying to 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.

comment:5 Changed 11 months ago by mmetz

In 73692:

lib/init: clean up sqlite db if existing, see #3697

comment:6 in reply to:  4 Changed 11 months ago by mmetz

Replying to mlennert:

Replying to 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.

comment:7 Changed 11 months ago by mlennert

Resolution: fixed
Status: newclosed

This seems to be solved, so closing.

Thanks, MarkusM !

comment:8 Changed 11 months ago by neteler

Resolution: fixed
Status: closedreopened

I'd like to see this in 7.6 as well... Any objections?

comment:9 in reply to:  8 Changed 11 months ago by mlennert

Replying to neteler:

I'd like to see this in 7.6 as well... Any objections?

You're right, that would be nice. I don't know if the solution implemented in trunk is transferable as is to 7.6 ?

comment:10 Changed 11 months ago by neteler

In 73736:

sqlite db: automatically enable vacuum, trac #3697 (bundle backport: trunk r73692, r73703, r73704, r73706, r73707, r73708)

  • 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

comment:11 Changed 11 months ago by neteler

Milestone: 7.8.07.6.0
Version: unspecifiedsvn-releasebranch76

Should be all backported (see above), pls test.

comment:12 Changed 9 months ago by martinl

Milestone: 7.6.07.6.1

Ticket retargeted after milestone closed

comment:13 Changed 7 months ago by martinl

Milestone: 7.6.17.6.2

Ticket retargeted after milestone closed

Note: See TracTickets for help on using tickets.