Ran some tests with variations of Clemens's backup exemple, and adding
sqlite3_exec(src, "PRAGMA cache_size=1", NULL, NULL, NULL);
sqlite3_exec(dst, "PRAGMA synchronous=OFF", NULL, NULL, NULL);
sqlite3_exec(dst, "PRAGMA cache_size=1", NULL, NULL, NULL);
seems to provide the best performance, which is about 4 to 5 times faster
than using ".backup" in the CLI.
While the effect of synchronous=OFF on the destination backup db was
something I expected, the cache_size of 1 was not.
Using large cache_size had a detrimental effect, both on source or
destination, between cache_size 1 and the default of SQLite (-2000) there
is a 20% difference in performance during backup.
Note that this is in Windows, on SSD and with lots of free RAM, so it seems
that the OS is better at handling cache than SQLite in that particular use
case.
Eric
On Wed, Oct 5, 2016 at 8:42 AM, Eric Grange <zar...@gmail.com> wrote:
> Thanks for the tool Clemens!
>
> > No, this is what makes the backup restart. With a step size of -1, it
> would never restart.
>
> Hmm, given WAL mode, this brings another question: why the sqlite CLI does
> not do that but uses 100 pages?
> Is using a backup_step N parameter only useful if you want to provide some
> progress indicator or pace the backup? (ie. pace I/O bandwidth ?)
>
> Or in other words, given WAL mode, what do we "lose" when using -1 and to
> copy everything at once during a backup?
>
>
> On Tue, Oct 4, 2016 at 5:33 PM, Clemens Ladisch <clem...@ladisch.de>
> wrote:
>
>> Eric Grange wrote:
>> > If all else fail, I could also suspend DB writes during backups
>> (suspending
>> > DB reads would be more problematic).
>>
>> With WAL, the backup reader does not block writers.
>>
>> >> Use the backup API, and copy everything in one step.
>> >> (The restart-on-write feature should not be necessary with WAL.)
>> >
>> > That was what I thought initially, but I can only explain the
>> multi-hours
>> > backups with it: usually the backup API takes 4-5 minutes. It is just
>> once
>> > in a while that a very long backup occurs.
>> >
>> >> It calls sqlite3_backup_step() with a size of 100 pages.
>> >
>> > Ok, so I guess the huge cache is overkill with the default CLI!
>>
>> No, this is what makes the backup restart. With a step size of -1,
>> it would never restart.
>>
>> Use a tool like the one below to do the backup in one step, without
>> restarts.
>>
>>
>> Regards,
>> Clemens
>> --
>>
>> #include <stdio.h>
>> #include <sqlite3.h>
>>
>> int main(int argc, char *argv[])
>> {
>> sqlite3 *src = NULL;
>> sqlite3 *dst = NULL;
>> sqlite3_backup *backup;
>> int rc;
>> int ok = 0;
>>
>> if (argc != 3) {
>> fputs("I want two file names: source, destination\n",
>> stderr);
>> goto error;
>> }
>>
>> rc = sqlite3_open_v2(argv[1], &src, SQLITE_OPEN_READONLY, NULL);
>> if (rc != SQLITE_OK) {
>> fprintf(stderr, "Cannot open %s: %s\n", argv[1],
>> sqlite3_errmsg(src));
>> goto error;
>> }
>>
>> rc = sqlite3_open_v2(argv[2], &dst, SQLITE_OPEN_READWRITE |
>> SQLITE_OPEN_CREATE, NULL);
>> if (rc != SQLITE_OK) {
>> fprintf(stderr, "Cannot open %s: %s\n", argv[2],
>> sqlite3_errmsg(dst));
>> goto error;
>> }
>>
>> sqlite3_exec(src, "PRAGMA busy_timeout=10000", NULL, NULL, NULL);
>> sqlite3_exec(dst, "PRAGMA busy_timeout=10000", NULL, NULL, NULL);
>>
>> backup = sqlite3_backup_init(dst, "main", src, "main");
>> if (backup == NULL) {
>> fprintf(stderr, "Cannot initialize backup: %s\n",
>> sqlite3_errmsg(dst));
>> goto error;
>> }
>>
>> do {
>> rc = sqlite3_backup_step(backup, -1);
>> } while (rc == SQLITE_BUSY || rc == SQLITE_LOCKED);
>>
>> rc = sqlite3_backup_finish(backup);
>> if (rc == SQLITE_OK) {
>> ok = 1;
>> } else {
>> fprintf(stderr, "Backup failed: %s\n",
>> sqlite3_errmsg(dst));
>> }
>>
>> error:
>> sqlite3_close(dst);
>> sqlite3_close(src);
>>
>> return ok ? 0 : 1;
>> }
>> _______________________________________________
>> 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
