Re WAL mode trick. I think you would want to complete a checkpoint and then do the backup, ensuring that no check-points are done during your backup time. This way, you know that your committed transactions prior to the backup are in the file being backed up.
regards, Adam On Sat, May 7, 2016 at 7:32 AM, Stadin, Benjamin < Benjamin.Stadin at heidelberg-mobil.com> wrote: > 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 > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- -------------- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
