Incredibly slow backups problem and perhaps a solution

Support
1

Hi,

First, let me start with the solution to my issue. Not sure it will work long term, but my backup after doing it took 19 minutes:

[root@voyageur Duplicati]# sqlite3 QFUVPZGKBK.sqlite
SQLite version 3.7.17 2013-05-20 00:56:22
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> pragma optimize;
sqlite> analyze;
sqlite> .exit
[root@voyageur Duplicati]#

The problem? My blocksize was set to 100kb and I increased it without to 5Mb and the backups did work but they seemed to take increasingly longer and longer to finish. Since the option says you can’t change it, I decided to export the backup json, delete the backup files, and the database and start over.

When I did that, I think that first backup took ~5 hours. When I did the next backup, it was 8 hours. Today I did the pragma optimize; analyze; and the backup took just 19 minutes.

While I don’t know if I continue to expect this performance, but it is clear to me that this did something not normally done by a regular backup, even starting from an initial database.

Question: Is there any issue with performing these on a periodic basis? From what I can see, it would be a benefit to performance by doing this before any operation and especially before starting some operations like say compaction. I’m not a DB expert, but perhaps this is worthy of a backup configuration option so people could choose to “optimize” before starting.

Now, I’m not running the latest version so perhaps this has already been addressed. i’m running Version: 2.0.7.1 (2.0.7.1_beta_2023-05-25)

2

Looks like Duplicati automatically does “pragma optimize” before closing the connection. It was merged in August 2020:

It doesn’t explicitly run “analyze”, but “pragma optimize” may do it automatically according to sqlite documentation.

1 Like
3

Okay, I may have found the issue and it explains SOOOOO much. Unless Duplicati contains its’ own version of sqlite3, the answer seems to be this:

[root@voyageur Duplicati]# sqlite3 QFUVPZGKBK.sqlite
SQLite version 3.7.17 2013-05-20 00:56:22

and this:

Pragma statements supported by SQLite (archive.org)

Answer: pragma optimize doesn’t yet exist IF Duplicati is using the systems’ sqlite libraries and not its’ own. the OS here is (due to be replaced I know) CentOS 7

4

Release History for SQLite supports the thinking:

2017-03-30 (3.18.0)

  1. Added the PRAGMA optimize command

Current Beta (and many prior) ship only a Windows SQLite. On Linux, it uses the system copy.
This is (I think) changing with current Canary, although I don’t know for sure if change will stay:

Feel free to test Canary if you like, but note that they’re first exposures of new fixes and bugs.

EDIT:

Solidify SQLite dependencies #4024 discusses the drawback of running on a system’s SQLite. Current emerging direction of Duplicati is fewer dependencies. Doesn’t even need mono either.

5

Makes sense! As hinted by @ts678 you can use the canary builds which ship with the updated SQLite binaries for all platforms.