Database backup with writers present?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Database backup with writers present?

Daniel Polski
Hello,
When reading the docs I don't get a clear understanding on what strategy
I could/should use to backup a heavily used database (with frequent
reads & writes).

I have one application (A) with multiple threads reading & writing to
the database. Application (A) handles write locking internally, giving
write access to only 1 thread at a time.

I have another application (B) which among other things is responsible
for backing up the database from time to time. Application (B) is
currently using the backup API, but sometimes it gets starved so it
never reach completion.

Is there some way to take a snapshot of a database regardless if there
comes new writes / transactions while doing the backup, and just write a
copy of that snapshot as it was into a new database file?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backup with writers present?

Richard Hipp-3
On 2/27/19, Daniel Polski <[hidden email]> wrote:
>
> Is there some way to take a snapshot of a database regardless if there
> comes new writes / transactions while doing the backup, and just write a
> copy of that snapshot as it was into a new database file?

I assume you are using WAL mode?  If not, you should be.  Just making
that one change might solve your problem.

In WAL mode, run the entire backup inside a transaction.  Or use the
new VACUUM INTO feature to make your backup.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backup with writers present?

Stephen Chrzanowski
Does write blocking still come into play when using "vaccum into", or are
writes put into the WAL file?  I know that with the regular backup API, any
writes to the DB restarts the backup process, but I hadn't thought about
putting either into a transaction.  (I'm an I-D-TEN-T, I know)

On Wed, Feb 27, 2019 at 10:20 AM Richard Hipp <[hidden email]> wrote:

>
> I assume you are using WAL mode?  If not, you should be.  Just making
> that one change might solve your problem.
>
> In WAL mode, run the entire backup inside a transaction.  Or use the
> new VACUUM INTO feature to make your backup.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backup with writers present?

Richard Hipp-3
On 2/27/19, Stephen Chrzanowski <[hidden email]> wrote:
> Does write blocking still come into play when using "vaccum into",

The VACUUM INTO command is a reader.  So (in WAL mode) some other
process can continue writing while the VACUUM INTO is running.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backup with writers present?

Simon Slavin-3
On 27 Feb 2019, at 4:16pm, Richard Hipp <[hidden email]> wrote:

> On 2/27/19, Stephen Chrzanowski <[hidden email]> wrote:
>> Does write blocking still come into play when using "vaccum into",
>
> The VACUUM INTO command is a reader.  So (in WAL mode) some other
> process can continue writing while the VACUUM INTO is running.

The advantage is that a write from another thread doesn't force a restart in VACUUM.  So the VACUUM process may cause a short delay in the writing process, but the VACUUM process completes in one operation and then the database is free until the next backup.

WAL mode seems to be the right mode for you, unless you have limited disk space for the journal file.

Which is best for you depends on the frequency of your reads, writes and backups, and on how fast your storage medium is.  It's not something which can be usefully predicted for 'average use on average hardware'.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backup with writers present?

wmertens
One option, if you are on a filesystem supporting extents (macOS's apfs and
Linux's btrfs only, currently, with xfs and bcachefs support on the
horizon): do a copy with reflinks (cp -c on macOS, cp --reflink=auto
elsewhere). That should be super fast since all it does is point to
existing data and duplicate the metadata.

Then, you can do an operation that collapses the WAL log like `sqlite3
copiedfile.sqlite3 .schema` and that should normally fail if the WAL log
was copied at an inopportune time; in that case just try again.

This way you can make space-efficient copies of the db on the same disk,
and you can copy them to a safe location at your leisure. Keep a few copies
around and you have pretty granular snapshots.

Wout.


On Wed, Feb 27, 2019 at 6:03 PM Simon Slavin <[hidden email]> wrote:

> On 27 Feb 2019, at 4:16pm, Richard Hipp <[hidden email]> wrote:
>
> > On 2/27/19, Stephen Chrzanowski <[hidden email]> wrote:
> >> Does write blocking still come into play when using "vaccum into",
> >
> > The VACUUM INTO command is a reader.  So (in WAL mode) some other
> > process can continue writing while the VACUUM INTO is running.
>
> The advantage is that a write from another thread doesn't force a restart
> in VACUUM.  So the VACUUM process may cause a short delay in the writing
> process, but the VACUUM process completes in one operation and then the
> database is free until the next backup.
>
> WAL mode seems to be the right mode for you, unless you have limited disk
> space for the journal file.
>
> Which is best for you depends on the frequency of your reads, writes and
> backups, and on how fast your storage medium is.  It's not something which
> can be usefully predicted for 'average use on average hardware'.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database backup with writers present?

wmertens
I meant reflinks not extents. I should go to bed ;)

Wout.


On Thu, Feb 28, 2019 at 11:57 PM Wout Mertens <[hidden email]>
wrote:

> One option, if you are on a filesystem supporting extents (macOS's apfs
> and Linux's btrfs only, currently, with xfs and bcachefs support on the
> horizon): do a copy with reflinks (cp -c on macOS, cp --reflink=auto
> elsewhere). That should be super fast since all it does is point to
> existing data and duplicate the metadata.
>
> Then, you can do an operation that collapses the WAL log like `sqlite3
> copiedfile.sqlite3 .schema` and that should normally fail if the WAL log
> was copied at an inopportune time; in that case just try again.
>
> This way you can make space-efficient copies of the db on the same disk,
> and you can copy them to a safe location at your leisure. Keep a few copies
> around and you have pretty granular snapshots.
>
> Wout.
>
>
> On Wed, Feb 27, 2019 at 6:03 PM Simon Slavin <[hidden email]> wrote:
>
>> On 27 Feb 2019, at 4:16pm, Richard Hipp <[hidden email]> wrote:
>>
>> > On 2/27/19, Stephen Chrzanowski <[hidden email]> wrote:
>> >> Does write blocking still come into play when using "vaccum into",
>> >
>> > The VACUUM INTO command is a reader.  So (in WAL mode) some other
>> > process can continue writing while the VACUUM INTO is running.
>>
>> The advantage is that a write from another thread doesn't force a restart
>> in VACUUM.  So the VACUUM process may cause a short delay in the writing
>> process, but the VACUUM process completes in one operation and then the
>> database is free until the next backup.
>>
>> WAL mode seems to be the right mode for you, unless you have limited disk
>> space for the journal file.
>>
>> Which is best for you depends on the frequency of your reads, writes and
>> backups, and on how fast your storage medium is.  It's not something which
>> can be usefully predicted for 'average use on average hardware'.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users