Re: [sqlite] Database backup with writers present?
I meant reflinks not extents. I should go to bed ;) Wout. On Thu, Feb 28, 2019 at 11:57 PM Wout Mertens 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 wrote: > >> On 27 Feb 2019, at 4:16pm, Richard Hipp wrote: >> >> > On 2/27/19, Stephen Chrzanowski 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 >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database backup with writers present?
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 wrote: > On 27 Feb 2019, at 4:16pm, Richard Hipp wrote: > > > On 2/27/19, Stephen Chrzanowski 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database backup with writers present?
On 27 Feb 2019, at 4:16pm, Richard Hipp wrote: > On 2/27/19, Stephen Chrzanowski 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database backup with writers present?
On 2/27/19, Stephen Chrzanowski 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database backup with writers present?
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 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 > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database backup with writers present?
On 2/27/19, Daniel Polski 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database backup with writers present?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
