CREATE TEMPORARY TABLE takes forever

This has now taken 2,5 hours on a Synology with plenty of ram… 1 cpu 100%… :open_mouth:

  1. dec 2017 14:14: Starting - ExecuteNonQuery: CREATE TEMPORARY TABLE “UsageReport-8C689749FCD1AC458811BB7E556EC14A” AS SELECT “VolumeID” AS “VolumeID”, SUM(“ActiveSize”) AS “ActiveSize”, SUM(“InactiveSize”) AS “InactiveSize”, MAX(“Sortime”) AS “Sorttime” FROM (SELECT “A”.“ActiveSize” AS “ActiveSize”, 0 AS “InactiveSize”, “A”.“VolumeID” AS “VolumeID”, CASE WHEN “B”.“Sorttime” IS NULL THEN 0 ELSE “B”.“Sorttime” END AS “Sorttime” FROM (SELECT SUM(“Block”.“Size”) AS “ActiveSize”, “Block”.“VolumeID” AS “VolumeID” FROM “Block”, “Remotevolume” WHERE “Block”.“VolumeID” = “Remotevolume”.“ID” AND “Block”.“ID” NOT IN (SELECT “Block”.“ID” FROM “Block”,“DeletedBlock” WHERE “Block”.“Hash” = “DeletedBlock”.“Hash” AND “Block”.“Size” = “DeletedBlock”.“Size”) GROUP BY “Block”.“VolumeID” ) A LEFT OUTER JOIN (SELECT “VolumeID” AS “VolumeID”, MIN(“Sorttime”) AS “Sorttime” FROM (SELECT “Block”.“VolumeID” AS “VolumeID”, “Fileset”.“Timestamp” AS “Sorttime” FROM “Fileset”, “FilesetEntry”, “File”, “BlocksetEntry”, “Block” WHERE “FilesetEntry”.“FileID” = “File”.“ID” AND “File”.“BlocksetID” = “BlocksetEntry”.“BlocksetID” AND “BlocksetEntry”.“BlockID” = “Block”.“ID” AND “Fileset”.“ID” = “FilesetEntry”.“FilesetID” UNION SELECT “Block”.“VolumeID” AS “VolumeID”, “Fileset”.“Timestamp” AS “Sorttime” FROM “Fileset”, “FilesetEntry”, “File”, “BlocksetEntry”, “Block”, “Metadataset” WHERE “FilesetEntry”.“FileID” = “File”.“ID” AND “File”.“MetadataID” = “Metadataset”.“ID” AND “Metadataset”.“BlocksetID” = “BlocksetEntry”.“BlocksetID” AND “BlocksetEntry”.“BlockID” = “Block”.“ID” AND “Fileset”.“ID” = “FilesetEntry”.“FilesetID” ) GROUP BY “VolumeID” ) B ON “B”.“VolumeID” = “A”.“VolumeID” UNION SELECT 0 AS “ActiveSize”, SUM(“Size”) AS “InactiveSize”, “VolumeID” AS “VolumeID”, 0 AS “SortScantime” FROM “DeletedBlock” GROUP BY “VolumeID” UNION SELECT 0 AS “ActiveSize”, 0 AS “InactiveSize”, “Remotevolume”.“ID” AS “VolumeID”, 0 AS “SortScantime” FROM “Remotevolume” WHERE “Remotevolume”.“Type” = ? AND “Remotevolume”.“State” IN (?, ?) AND “Remotevolume”.“ID” NOT IN (SELECT “VolumeID” FROM “Block”) ) GROUP BY “VolumeID”

This is during Deleting unwanted files… on ACD and WebDAV

Any ideas?

:slight_smile:

HC Jehg

Well, assuming it hasn’t finished yet I’d start by asking what version of Duplicati you’re using and if you have a particularly big (in size, file count, or versions) backup.

The newer canary versions have some of their SQL optimized compared to the older (and beta) versions. Not that I’m not sure your SQL above is included in the optimized sections…

It is building a map over which blocks are in use and which are not, in an attempt to free up space on the remote destination if possible.

Short-term fix is to set --no-auto-compact as that skips the check completely, and lets you run it manually if/when you feel like it.

1 Like

I’ll try that right away *<|:-)

HC

It helped to get rid of the waiting time :slight_smile:
but - of course now no auto compact :slight_smile:
HC

Do you still get stuck on the temporary table issue if you do a manual compact?