Re-generate database duration

Support
1

Hi
Thanks for this awsome software

For some reason (errors on database), I try to re generate the local database.
My backup is on the computer (another drive) so I though it was going to ba fast. Nevertheless, the process lasts for 12 hours and is not finished. The progress bar grows quickly in the first hour and then grows very slowly. I wonder if it will never end.
Is this normal ?
My backup is 350 GB.

Thanks

duplicati Duplicati - 2.0.4.5_beta_2018-11-28
windows 8

2

Hello @Adrienloyat and welcome to the forum!

Database Recreate can, unfortunately, take awhile, especially for huge backups (which yours is not IMO). Probably the best thing to do to see what’s happening is to use About --> Show log --> Live --> Profiling to observe activity. There are some cases (and an identified bug) that cause heavy downloads. Alternatively, database writes can be the bottleneck. It’s hard to say without measurement, so just check for progress… Fortunately I believe this code is getting rewritten, so perhaps it will be in good shape by next beta release.

3

I have an about 800GB backup. The recreate for my database has been running for just over two weeks now. I could have just deleted the whole backup and reran it at this point but I want to test that I can even recover from a corrupt database.

4

Thanks for your answers.

The log shows that one of the request last 1 minute ! I think the request is triggered once per dblock file. With 3500 dblock files, it should take at least 2.5 days.

the faulty request is :

     1 janv. 2019 21:34: ExecuteNonQuery: INSERT INTO "Block" ("Hash", "Size", "VolumeID") SELECT "FullHash" AS "Hash", "Length" AS "Size", -1 AS "VolumeID" FROM (SELECT "A"."FullHash", "A"."Length", CASE WHEN "B"."Hash" IS NULL THEN '' ELSE "B"."Hash" END AS "Hash", CASE WHEN "B"."Size" is NULL THEN -1 ELSE "B"."Size" END AS "Size" FROM (SELECT DISTINCT "FullHash", "Length" FROM (SELECT "BlockHash" AS "FullHash", "BlockSize" AS "Length" FROM ( 
      SELECT
      "E"."BlocksetID",
      "F"."Index" + ("E"."BlocklistIndex" * 3200) AS "FullIndex",
      "F"."BlockHash",
      MIN(102400, "E"."Length" - (("F"."Index" + ("E"."BlocklistIndex" * 3200)) * 102400)) AS "BlockSize",
      "E"."Hash",
      "E"."BlocklistSize",
      "E"."BlocklistHash"
      FROM
      (
      SELECT * FROM
      (
      SELECT 
      "A"."BlocksetID",
      "A"."Index" AS "BlocklistIndex",
      MIN(3200 * 32, ((("B"."Length" + 102400 - 1) / 102400) - ("A"."Index" * (3200))) * 32) AS "BlocklistSize",
      "A"."Hash" AS "BlocklistHash",
      "B"."Length"
      FROM 
      "BlocklistHash" A,
      "Blockset" B
      WHERE 
      "B"."ID" = "A"."BlocksetID"
      ) C,
      "Block" D
      WHERE
      "C"."BlocklistHash" = "D"."Hash"
      AND
      "C"."BlocklistSize" = "D"."Size"
      ) E,
      "TempBlocklist-35C7072813640E4E879022164011FB40" F
      WHERE
      "F"."BlocklistHash" = "E"."Hash"
      ORDER BY 
      "E"."BlocksetID",
      "FullIndex"
      ) UNION SELECT "BlockHash", "BlockSize" FROM "TempSmalllist-C4D68E6B1E95EA4ABFC3C5249B9186E3" )) A LEFT OUTER JOIN "Block" B ON "B"."Hash" = "A"."FullHash" AND "B"."Size" = "A"."Length" ) WHERE "FullHash" != "Hash" AND "Length" != "Size" took 0:00:00:57.695
5

I did a recreate on one of my ~6.6GB backup jobs within the past few months, and in my case it only took a few minutes (I was afraid it would take much longer). I don’t assume the recreate times scale linearly, but even so it seems to me like your numbers are on the high side.
Question: did it ever finish naturally?

6

I’m doing a recreate test right now and will let you know how that SQL looks at my end.

My guess is the sqlite engine changes it’s execution plan based on something that’s different between your, my, and @drakar2007’s systems and the new plan is slow than the old one.

With some focused testing and re-runs we MIGHT be able to narrow down the pain point (other than the obvious ugly SQL).

(BTW - I edit your post by replacing your “>” commenting on the SQL with “~~~ sql” before and “~~~” after the block to make it easier to read.)

Edit: OK - that went faster than I expected…

Using these parameters (to ease log searching) I see that SQL ran twice for me - once taking 11 seconds and then again for 2 seconds.

For me, 11 seconds is pretty long - most were under 1 second with the rest at 1, 2, 3, and 11 seconds.

--log-file=/RecreateTst_20190103.txt
--log-file-log-filter=+Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExecuteNonQuery*

 ) UNION SELECT "BlockHash", "BlockSize" FROM "TempSmalllist-636A48B08E15664F978780C7E31D83ED" )) A LEFT OUTER JOIN "Block" B ON "B"."Hash" =  "A"."FullHash" AND "B"."Size" = "A"."Length" ) WHERE "FullHash" != "Hash" AND "Length" != "Size"  took 0:00:00:11.378
 ) UNION SELECT "BlockHash", "BlockSize" FROM "TempSmalllist-636A48B08E15664F978780C7E31D83ED" )) A LEFT OUTER JOIN "Block" B ON "B"."Hash" =  "A"."FullHash" AND "B"."Size" = "A"."Length" ) WHERE "FullHash" != "Hash" AND "Length" != "Size"  took 0:00:00:02.023
1 Like