1

I currently have a 9Gb Sqlite3 database, I have a 'maintenance' task that runs at random intervals in the evening to VACUMM; the database, (on average about once a week). The database is on a Windows 10 machine.

One morning I saw that the task was still running. As expected, the VACUMM; command was using double the amount of disk space, but what I was not expected is for it to run for +2 hours and to use 100% of the disk utilization.

My questions are,

Is it normal to use 100% of the disk for an ~9Gb database and to run for over 2 hours? (the database is not really growing anymore).

Is there a way I can VACUUM; the database without killing my drive in the process? (a low priority VACUUM maybe or something to that effect?)

Simon Goodman
  • 1,174
  • 1
  • 8
  • 35

2 Answers2

2

You can try using VACUUM INTO instead of VACUUM, and then just deleting the original db file and renaming the newly-created one. That will eliminate half of the disk thrashing.

Alternatively, you might consider not using VACUUM quite as much. If, as you say, the db has stabilized with respect to size, then a few empty pages is not really a cause for much concern. You could start by reducing it to monthly intervals instead of weekly ones, and see if you even notice a difference in query performance. Odds are, you won't.

Mark Benningfield
  • 2,800
  • 9
  • 31
  • 31
0
Is it normal to use 100% of the disk for an ~9Gb database and to run for over 2 hours? (the database is not really growing anymore).

Nå i don't think så, still i'd like you to benchmark it against doing a manual (and non-transnational) version of VACUUM: 

echo ".dump" | sqlite3 old_db.db3 | sqlite3 new_db.db3; rm old_db.db3; mv new_db.db3 old_db.db3;

how does that perform in comparison to a proper VACUUM? still over 2 hours at 100% cpu? if this performs significantly better, i guess it's a sqlite3 VACUUM performance bug somewhere..

Is there a way I can VACUUM; the database without killing my drive in the process? (a low priority VACUUM maybe or something to that effect?)

yeah.. but it will be even slower, obviously, this should work and run at a very low io-priority and very low cpu priority:

echo "VACUUM;" | ionice -c 3 nice -n 19 sqlite3 db.db3
hanshenrik
  • 19,904
  • 4
  • 43
  • 89