Hi Rob, I think Clemens suggestion may be worth investigating, in case you do not want to stop the updates (which probably means a change in your workflow and some effort at other places anyways).
I think this may work: - Use WAL, and turn off automatic checkpointing (https://www.sqlite.org/wal.html). The default behavior is to do a commit after 1000*4096(pagesize) which is round about 4MB. Instead of using the default auto checkpoint, create a checkpoint every now and then on your own in your code (e.g. simply after every n-th commit, every 10 minutes, or whatever fits). - Do *not* do checkpointing at the time you copy your db, in order to avoid changing the db while copying the file. Changes are written to WAL files exclusively at this time. I think it needs just reasonable effort to trigger these event from the outside to have the app know when a backup starts and stops - or it could be done as simple as implement within the checkpoint code a rule like ?don?t make a checkpoint between 2:30am and 4:00am?. Regards, Ben Am 04.05.16, 14:39 schrieb "sqlite-users-bounces at mailinglists.sqlite.org on behalf of Rob Willett" unter <sqlite-users-bounces at mailinglists.sqlite.org on behalf of rob.sqlite at robertwillett.com>: >Clemens, > >We have 8GB of memory which is the most our VPS provider allows. We?d >like 32GB but its not an option for us. Our desktops have more than >that, but the VPS provider is reasonably priced :) > >We hadn?t considered the WAL mode, my conclusion is that a simple >change to our workflow is actually simpler, we stop the database updates >for 15 mins out of hours, cp and then restart. Its not ideal but we?re >not running a nuclear power station or a 24/7 medical facility. Users >*may* not get traffic updates for 15 mins at 03:00 in the morning. The >world will keep spinning. > >Rob > >On 4 May 2016, at 12:58, Clemens Ladisch wrote: > >> Rob Willett wrote: >>> We?re trying to backup a 10GB live running database >>> ?as-fast-as-we- >>> possibly-can? without stopping updates coming in. >> >> How much memory do you have? I guess you can't simply read the entire >> database file to force it into the file cache? >> >> In WAL mode, a writer does not block readers. You have to decide >> whether you can live with its restrictions: >> http://www.sqlite.org/wal.html >> >> >> Regards, >> Clemens >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >_______________________________________________ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
