On 4 May 2016 at 20:39, Rob Willett <rob.sqlite at robertwillett.com> wrote:
> 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.
Are users really forced to wait for a database update to receive traffic?
Assuming you run a BEGIN IMMEDIATE to prevent updates while the copy is in
progress -- which you must no matter how fast you can copy the database,
otherwise a transaction committing during the copy will likely result in a
corrupted backup -- connections that are reading the database can still
proceed.
Note that you can prevent the sqlite3_backup process from restarting when
the database changes by passing -1 as the number of pages to
sqlite3_backup_step ("copy all pages at once"). If you take this approach
you don't need BEGIN IMMEDIATE because sqlite will take care of the
locking, but I'm not sure how the i/o performance compares to cp or rsync.
-Rowan
