{"action":"created","issue":{"url":"https://api.github.com/repos/duplicati/duplicati/issues/3194","repository_url":"https://api.github.com/repos/duplicati/duplicati","labels_url":"https://api.github.com/repos/duplicati/duplicati/issues/3194/labels{/name}","comments_url":"https://api.github.com/repos/duplicati/duplicati/issues/3194/comments","events_url":"https://api.github.com/repos/duplicati/duplicati/issues/3194/events","html_url":"https://github.com/duplicati/duplicati/issues/3194","id":318314756,"node_id":"MDU6SXNzdWUzMTgzMTQ3NTY=","number":3194,"title":"Canary 2.0.3.6 and slow queries","user":{"login":"mr-flibble","id":5197625,"node_id":"MDQ6VXNlcjUxOTc2MjU=","avatar_url":"https://avatars0.githubusercontent.com/u/5197625?v=4","gravatar_id":"","url":"https://api.github.com/users/mr-flibble","html_url":"https://github.com/mr-flibble","followers_url":"https://api.github.com/users/mr-flibble/followers","following_url":"https://api.github.com/users/mr-flibble/following{/other_user}","gists_url":"https://api.github.com/users/mr-flibble/gists{/gist_id}","starred_url":"https://api.github.com/users/mr-flibble/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mr-flibble/subscriptions","organizations_url":"https://api.github.com/users/mr-flibble/orgs","repos_url":"https://api.github.com/users/mr-flibble/repos","events_url":"https://api.github.com/users/mr-flibble/events{/privacy}","received_events_url":"https://api.github.com/users/mr-flibble/received_events","type":"User","site_admin":false},"labels":[{"id":575308759,"node_id":"MDU6TGFiZWw1NzUzMDg3NTk=","url":"https://api.github.com/repos/duplicati/duplicati/labels/performance%20issue","name":"performance issue","color":"0e8a16","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":29,"created_at":"2018-04-27T08:03:58Z","updated_at":"2018-08-29T19:15:47Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"Hello, this problem started just after updating to Duplicati - 2.0.3.6_canary_2018-04-23\r\n\r\n\r\n\r\n- **Duplicati version**: Duplicati - 2.0.3.6_canary_2018-04-23\r\n- **Operating system**: Windows 7\r\n- **Backend**: SMB\r\nSource:164.11 GB\r\nBackup:118.73 GB / 40 Versions\r\n\r\n## Description\r\n<!-- Describe the issue that you are experiencing below. -->\r\nOn Canary 2.0.3.5 backup was working fine, backup size is 160GB and backup time was 30 -90 min\r\n\r\nOn 2.0.3.6 backup took \t**11 hours** and it was stuck on Verifying backend data ...\r\n\r\nThis query was ran **six times** and it always took 1 to 2 hours\r\n\r\n_\r\n\r\n> Line 21089: 2018-04-26 13:33:20 +02 - [Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExcuteReader]: ExecuteReader: SELECT \"A\".\"Hash\", \"C\".\"Hash\" FROM (SELECT \"BlocklistHash\".\"BlocksetID\", \"Block\".\"Hash\", * FROM  \"BlocklistHash\",\"Block\" WHERE  \"BlocklistHash\".\"Hash\" = \"Block\".\"Hash\" AND \"Block\".\"VolumeID\" = ?) A,  \"BlocksetEntry\" B, \"Block\" C WHERE \"B\".\"BlocksetID\" = \"A\".\"BlocksetID\" AND  \"B\".\"Index\" >= (\"A\".\"Index\" * 3200) AND \"B\".\"Index\" < ((\"A\".\"Index\" + 1) * 3200) AND \"C\".\"ID\" = \"B\".\"BlockID\"  ORDER BY \"A\".\"BlocksetID\", \"B\".\"Index\" took **0:02:13:26.624**_ \r\n\r\n--\r\n\r\n\r\n\r\n## Debug log\r\nOccurrence of this long query in log (took time is at end of line)\r\n\r\n\t`Line 21089: 2018-04-26 13:33:20 +02 - [Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExcuteReader]: ExecuteReader: SELECT \"A\".\"Hash\", \"C\".\"Hash\" FROM (SELECT \"BlocklistHash\".\"BlocksetID\", \"Block\".\"Hash\", * FROM  \"BlocklistHash\",\"Block\" WHERE  \"BlocklistHash\".\"Hash\" = \"Block\".\"Hash\" AND \"Block\".\"VolumeID\" = ?) A,  \"BlocksetEntry\" B, \"Block\" C WHERE \"B\".\"BlocksetID\" = \"A\".\"BlocksetID\" AND  \"B\".\"Index\" >= (\"A\".\"Index\" * 3200) AND \"B\".\"Index\" < ((\"A\".\"Index\" + 1) * 3200) AND \"C\".\"ID\" = \"B\".\"BlockID\"  ORDER BY \"A\".\"BlocksetID\", \"B\".\"Index\" took 0:02:13:26.624\r\n\tLine 371167: 2018-04-26 15:33:11 +02 - [Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExcuteReader]: ExecuteReader: SELECT \"A\".\"Hash\", \"C\".\"Hash\" FROM (SELECT \"BlocklistHash\".\"BlocksetID\", \"Block\".\"Hash\", * FROM  \"BlocklistHash\",\"Block\" WHERE  \"BlocklistHash\".\"Hash\" = \"Block\".\"Hash\" AND \"Block\".\"VolumeID\" = ?) A,  \"BlocksetEntry\" B, \"Block\" C WHERE \"B\".\"BlocksetID\" = \"A\".\"BlocksetID\" AND  \"B\".\"Index\" >= (\"A\".\"Index\" * 3200) AND \"B\".\"Index\" < ((\"A\".\"Index\" + 1) * 3200) AND \"C\".\"ID\" = \"B\".\"BlockID\"  ORDER BY \"A\".\"BlocksetID\", \"B\".\"Index\" took 0:01:44:38.428\r\n\tLine 484220: 2018-04-26 17:56:10 +02 - [Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExcuteReader]: ExecuteReader: SELECT \"A\".\"Hash\", \"C\".\"Hash\" FROM (SELECT \"BlocklistHash\".\"BlocksetID\", \"Block\".\"Hash\", * FROM  \"BlocklistHash\",\"Block\" WHERE  \"BlocklistHash\".\"Hash\" = \"Block\".\"Hash\" AND \"Block\".\"VolumeID\" = ?) A,  \"BlocksetEntry\" B, \"Block\" C WHERE \"B\".\"BlocksetID\" = \"A\".\"BlocksetID\" AND  \"B\".\"Index\" >= (\"A\".\"Index\" * 3200) AND \"B\".\"Index\" < ((\"A\".\"Index\" + 1) * 3200) AND \"C\".\"ID\" = \"B\".\"BlockID\"  ORDER BY \"A\".\"BlocksetID\", \"B\".\"Index\" took 0:02:14:46.368\r\n\tLine 484436: 2018-04-26 19:07:36 +02 - [Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExcuteReader]: ExecuteReader: SELECT \"A\".\"Hash\", \"C\".\"Hash\" FROM (SELECT \"BlocklistHash\".\"BlocksetID\", \"Block\".\"Hash\", * FROM  \"BlocklistHash\",\"Block\" WHERE  \"BlocklistHash\".\"Hash\" = \"Block\".\"Hash\" AND \"Block\".\"VolumeID\" = ?) A,  \"BlocksetEntry\" B, \"Block\" C WHERE \"B\".\"BlocksetID\" = \"A\".\"BlocksetID\" AND  \"B\".\"Index\" >= (\"A\".\"Index\" * 3200) AND \"B\".\"Index\" < ((\"A\".\"Index\" + 1) * 3200) AND \"C\".\"ID\" = \"B\".\"BlockID\"  ORDER BY \"A\".\"BlocksetID\", \"B\".\"Index\" took 0:01:11:21.798\r\n\tLine 497246: 2018-04-26 21:10:58 +02 - [Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExcuteReader]: ExecuteReader: SELECT \"A\".\"Hash\", \"C\".\"Hash\" FROM (SELECT \"BlocklistHash\".\"BlocksetID\", \"Block\".\"Hash\", * FROM  \"BlocklistHash\",\"Block\" WHERE  \"BlocklistHash\".\"Hash\" = \"Block\".\"Hash\" AND \"Block\".\"VolumeID\" = ?) A,  \"BlocksetEntry\" B, \"Block\" C WHERE \"B\".\"BlocksetID\" = \"A\".\"BlocksetID\" AND  \"B\".\"Index\" >= (\"A\".\"Index\" * 3200) AND \"B\".\"Index\" < ((\"A\".\"Index\" + 1) * 3200) AND \"C\".\"ID\" = \"B\".\"BlockID\"  ORDER BY \"A\".\"BlocksetID\", \"B\".\"Index\" took 0:01:58:34.964\r\n\tLine 498851: 2018-04-26 23:03:58 +02 - [Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExcuteReader]: ExecuteReader: SELECT \"A\".\"Hash\", \"C\".\"Hash\" FROM (SELECT \"BlocklistHash\".\"BlocksetID\", \"Block\".\"Hash\", * FROM  \"BlocklistHash\",\"Block\" WHERE  \"BlocklistHash\".\"Hash\" = \"Block\".\"Hash\" AND \"Block\".\"VolumeID\" = ?) A,  \"BlocksetEntry\" B, \"Block\" C WHERE \"B\".\"BlocksetID\" = \"A\".\"BlocksetID\" AND  \"B\".\"Index\" >= (\"A\".\"Index\" * 3200) AND \"B\".\"Index\" < ((\"A\".\"Index\" + 1) * 3200) AND \"C\".\"ID\" = \"B\".\"BlockID\"  ORDER BY \"A\".\"BlocksetID\", \"B\".\"Index\" took 0:01:52:02.663\r\n`\r\n\r\n\r\n**This is log from log page**\r\n```\r\nDeletedFiles: 7\r\nDeletedFolders: 0\r\nModifiedFiles: 32\r\nExaminedFiles: 2491\r\nOpenedFiles: 38\r\nAddedFiles: 6\r\nSizeOfModifiedFiles: 0\r\nSizeOfAddedFiles: 0\r\nSizeOfExaminedFiles: 176208452248\r\nSizeOfOpenedFiles: 23401607077\r\nNotProcessedFiles: 0\r\nAddedFolders: 0\r\nTooLargeFiles: 0\r\nFilesWithError: 0\r\nModifiedFolders: 0\r\nModifiedSymlinks: 0\r\nAddedSymlinks: 0\r\nDeletedSymlinks: 0\r\nPartialBackup: False\r\nDryrun: False\r\nMainOperation: Backup\r\nCompactResults:\r\n    DeletedFileCount: 0\r\n    DownloadedFileCount: 0\r\n    UploadedFileCount: 0\r\n    DeletedFileSize: 0\r\n    DownloadedFileSize: 0\r\n    UploadedFileSize: 0\r\n    Dryrun: False\r\n    MainOperation: Compact\r\n    ParsedResult: Success\r\n    EndTime: 26.4.2018 23:08:46 (1524776926)\r\n    BeginTime: 26.4.2018 23:07:33 (1524776853)\r\n    Duration: 00:01:13.1766892\r\n    Messages: [\r\n        2018-04-26 11:19:21 +02 - [Information-Duplicati.Library.Main.Controller-StartingOperation]: The operation Backup has started,\r\n        2018-04-26 11:19:48 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Started:  (),\r\n        2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Completed:  (4.83 KB),\r\n        2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.Operation.Backup.RecreateMissingIndexFiles-RecreateMissingIndexFile]: Re-creating missing index file for duplicati-b84733bd4fc52468c890cd9954498e5de.dblock.zip,\r\n        2018-04-26 13:33:27 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: Put - Started: duplicati-ib46e4cab519b4b2eb050b9adcf7c7ef9.dindex.zip (35.59 KB),\r\n...\r\n    ]\r\n    Warnings: []\r\n    Errors: []\r\n    TaskReader: null\r\n    BackendStatistics:\r\n        RemoteCalls: 18\r\n        BytesUploaded: 254463608\r\n        BytesDownloaded: 52719985\r\n        FilesUploaded: 12\r\n        FilesDownloaded: 3\r\n        FilesDeleted: 1\r\n        FoldersCreated: 0\r\n        RetryAttempts: 0\r\n        UnknownFileSize: 0\r\n        UnknownFileCount: 0\r\n        KnownFileCount: 4962\r\n        KnownFileSize: 127489279138\r\n        LastBackupDate: 26.4.2018 11:19:22 (1524734362)\r\n        BackupListCount: 40\r\n        TotalQuotaSpace: 3000591446016\r\n        FreeQuotaSpace: 14693453824\r\n        AssignedQuotaSpace: -1\r\n        ReportedQuotaError: False\r\n        ReportedQuotaWarning: False\r\n        ParsedResult: Success\r\n        Messages: [\r\n            2018-04-26 11:19:21 +02 - [Information-Duplicati.Library.Main.Controller-StartingOperation]: The operation Backup has started,\r\n            2018-04-26 11:19:48 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Started:  (),\r\n            2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Completed:  (4.83 KB),\r\n            2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.Operation.Backup.RecreateMissingIndexFiles-RecreateMissingIndexFile]: Re-creating missing index file for duplicati-b84733bd4fc52468c890cd9954498e5de.dblock.zip,\r\n            2018-04-26 13:33:27 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: Put - Started: duplicati-ib46e4cab519b4b2eb050b9adcf7c7ef9.dindex.zip (35.59 KB),\r\n...\r\n        ]\r\n        Warnings: []\r\n        Errors: []\r\n        TaskReader: null\r\nDeleteResults:\r\n    DeletedSets: [\r\n        Item1: 40\r\n        Item2: 22.2.2018 0:00:03 (1519254003)\r\n    ]\r\n    Dryrun: False\r\n    MainOperation: Delete\r\n    ParsedResult: Success\r\n    EndTime: 26.4.2018 23:08:46 (1524776926)\r\n    BeginTime: 26.4.2018 23:03:59 (1524776639)\r\n    Duration: 00:04:47.8055660\r\n    Messages: [\r\n        2018-04-26 11:19:21 +02 - [Information-Duplicati.Library.Main.Controller-StartingOperation]: The operation Backup has started,\r\n        2018-04-26 11:19:48 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Started:  (),\r\n        2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Completed:  (4.83 KB),\r\n        2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.Operation.Backup.RecreateMissingIndexFiles-RecreateMissingIndexFile]: Re-creating missing index file for duplicati-b84733bd4fc52468c890cd9954498e5de.dblock.zip,\r\n        2018-04-26 13:33:27 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: Put - Started: duplicati-ib46e4cab519b4b2eb050b9adcf7c7ef9.dindex.zip (35.59 KB),\r\n...\r\n    ]\r\n    Warnings: []\r\n    Errors: []\r\n    TaskReader: null\r\nRepairResults: null\r\nTestResults:\r\n    MainOperation: Test\r\n    Verifications: [\r\n        Key: duplicati-20180426T091922Z.dlist.zip\r\n        Value: [],\r\n        Key: duplicati-i77b5b957730840fc874d65fabf048f8c.dindex.zip\r\n        Value: [],\r\n        Key: duplicati-bd91d6d1699884bf086fd4a13a381fd49.dblock.zip\r\n        Value: []\r\n    ]\r\n    ParsedResult: Success\r\n    EndTime: 26.4.2018 23:08:57 (1524776937)\r\n    BeginTime: 26.4.2018 23:08:53 (1524776933)\r\n    Duration: 00:00:03.7838309\r\n    Messages: [\r\n        2018-04-26 11:19:21 +02 - [Information-Duplicati.Library.Main.Controller-StartingOperation]: The operation Backup has started,\r\n        2018-04-26 11:19:48 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Started:  (),\r\n        2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Completed:  (4.83 KB),\r\n        2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.Operation.Backup.RecreateMissingIndexFiles-RecreateMissingIndexFile]: Re-creating missing index file for duplicati-b84733bd4fc52468c890cd9954498e5de.dblock.zip,\r\n        2018-04-26 13:33:27 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: Put - Started: duplicati-ib46e4cab519b4b2eb050b9adcf7c7ef9.dindex.zip (35.59 KB),\r\n...\r\n    ]\r\n    Warnings: []\r\n    Errors: []\r\n    TaskReader: null\r\nParsedResult: Success\r\nEndTime: 26.4.2018 23:08:57 (1524776937)\r\nBeginTime: 26.4.2018 11:19:21 (1524734361)\r\nDuration: 11:49:35.4379851\r\nMessages: [\r\n    2018-04-26 11:19:21 +02 - [Information-Duplicati.Library.Main.Controller-StartingOperation]: The operation Backup has started,\r\n    2018-04-26 11:19:48 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Started:  (),\r\n    2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: List - Completed:  (4.83 KB),\r\n    2018-04-26 11:19:53 +02 - [Information-Duplicati.Library.Main.Operation.Backup.RecreateMissingIndexFiles-RecreateMissingIndexFile]: Re-creating missing index file for duplicati-b84733bd4fc52468c890cd9954498e5de.dblock.zip,\r\n    2018-04-26 13:33:27 +02 - [Information-Duplicati.Library.Main.BasicResults-BackendEvent]: Backend event: Put - Started: duplicati-ib46e4cab519b4b2eb050b9adcf7c7ef9.dindex.zip (35.59 KB),\r\n...\r\n]\r\nWarnings: []\r\nErrors: []\r\nTaskReader:\r\n    ProgressAsync:\r\n        Result: True\r\n        Factory:\r\n            CancellationToken:\r\n                IsCancellationRequested: False\r\n                CanBeCanceled: False\r\n                WaitHandle:\r\n                    Handle: 668\r\n                    SafeWaitHandle:\r\n                        IsInvalid: False\r\n                        IsClosed: False\r\n            Scheduler: null\r\n            CreationOptions: None\r\n            ContinuationOptions: None\r\n        Id: 38\r\n        Exception: null\r\n        Status: RanToCompletion\r\n        IsCanceled: False\r\n        IsCompleted: True\r\n        CreationOptions: None\r\n        AsyncState: null\r\n        IsFaulted: False\r\n    TransferProgressAsync:\r\n        Result: True\r\n        Id: 39\r\n        Exception: null\r\n        Status: RanToCompletion\r\n        IsCanceled: False\r\n        IsCompleted: True\r\n        CreationOptions: None\r\n        AsyncState: null\r\n        IsFaulted: False\r\n```\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/duplicati/duplicati/issues/comments/417072206","html_url":"https://github.com/duplicati/duplicati/issues/3194#issuecomment-417072206","issue_url":"https://api.github.com/repos/duplicati/duplicati/issues/3194","id":417072206,"node_id":"MDEyOklzc3VlQ29tbWVudDQxNzA3MjIwNg==","user":{"login":"StevenKSanford","id":26028992,"node_id":"MDQ6VXNlcjI2MDI4OTky","avatar_url":"https://avatars0.githubusercontent.com/u/26028992?v=4","gravatar_id":"","url":"https://api.github.com/users/StevenKSanford","html_url":"https://github.com/StevenKSanford","followers_url":"https://api.github.com/users/StevenKSanford/followers","following_url":"https://api.github.com/users/StevenKSanford/following{/other_user}","gists_url":"https://api.github.com/users/StevenKSanford/gists{/gist_id}","starred_url":"https://api.github.com/users/StevenKSanford/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/StevenKSanford/subscriptions","organizations_url":"https://api.github.com/users/StevenKSanford/orgs","repos_url":"https://api.github.com/users/StevenKSanford/repos","events_url":"https://api.github.com/users/StevenKSanford/events{/privacy}","received_events_url":"https://api.github.com/users/StevenKSanford/received_events","type":"User","site_admin":false},"created_at":"2018-08-29T19:15:47Z","updated_at":"2018-08-29T19:15:47Z","author_association":"NONE","body":"FYI... Still seeing very slow backups for my main OSX (SSD) drive in Duplicati 2.0.3.9 (30 Jun 2018).\r\n\r\nBackups which took an a few hours in Duplicati 2.0.3.5 now take about 37 hours (a day and a half). Since this is a daily backup, I get subsequent backups of the same source drive queued up and run again immediately, which means that this one backup is taking over the Duplicati engine. Other backup sets, when I can squeeze them in, run a little longer than in 2.0.3.5, but these other backups have very little new data.\r\n\r\nI've deleted and rebuilt my database twice, each rebuild taking about 2 weeks (!). Duplicati reports source is 580GB, target is 4TB, 350 versions on Google Drive. OSX 10.13.6, MacBook Pro (2014) w/2.8 GHz i7 processor, 16GB RAM, 1TB SSD."}}
{"actor":{"display_login":"StevenKSanford"}}