Very slow database rebuild


#1

Hi,

On the last couple of runs of one of my backup jobs, it was throwing various database errors. As I result I used the option to delete and rebuild the database.

This has currently been running for about 11 days. Looking at the sqlite database for this particular backup job, it appears to have grown to a similar size as it was before I cleared it. The timestamp on the files show they haven’t changed since 11:23 on 14th July (over two days ago). It seems to have been in this state for probably about a week. The update time on the sqlite databases does change occasionally. The Duplicati process also does appear to be consuming small amounts of CPU on a regular basis.

The backup job is around 1.8TB in size, containing around 2500 files. The backup destination is Jottacloud if it’s of any relevance.

Is this behaviour expected? Is there anything I can do to speed up the rebuild process? Is there any way to work out what it’s actually doing at the moment?

Thanks

Andy


#2

In my tests of a database recreate (My experience with Database Rebuild / Recreate) I found it took about 4.25 days to recreate an 853 MB sqlite file (built on ~1TB of source content). If what I suspect is correct, the time it takes for a rebuild will get progressively longer the larger the resulting database should be.

I believe most of the issues performance issue with a rebuild is in the local database reads checking if blocks already exist or still need to be added. If that’s correct, the only way I can think of to speed it up would be to have the database stored on an SSD or ram drive - neither of which is something you can do once the recreate is started.

Since an interrupted rebuild can’t be continued (you’d have to start over) I’d suggest just waiting it out.

If you want to make sure things are stalled, you could take a look at the “lastPgEvent” line of the “System info” page or use a filter like “Profiling” on the Live tab of the the main menu “About” -> “Show log” page.

But that won’t make it go any faster. :frowning:


#3

This is the lastPgEvent:

lastPgEvent : {"BackupID":"7","TaskID":36,"BackendAction":"Get","BackendPath":"duplicati-b2cfc6caf74574ed4ad9d408212c6e2ea.dblock.zip.aes","BackendFileSize":524305101,"BackendFileProgress":524305101,"BackendSpeed":12256,"BackendIsBlocking":false,"CurrentFilename":null,"CurrentFilesize":0,"CurrentFileoffset":0,"Phase":"Recreate_Running","OverallProgress":0.714285731,"ProcessedFileCount":0,"ProcessedFileSize":0,"TotalFileCount":0,"TotalFileSize":0,"StillCounting":false}

I guess that means it’s about 70% through?

Andy


#4

I believe that’s correct - of course it’s 2 days later now, hopefully it has progressed a little more…


#5

Hi,

Nope, still showing exactly the same progress.

Database file hasn’t been written to since morning of July 19th…

My backups haven’t been running as a result since 3rd July. Not ideal.

Andy


#6

Absolutely agree there - unfortunately, while we have heard from a small number of people about this issue we haven’t been able to figure out exactly what causes it.

Even worse, an aborted rebuild can’t be continued so you end up just starting over again.

@kenkendk, is there a more appropriate way to check on the status of a database rebuild than OverallProgress in the lastPgEvent property on the System Info page?


#7

Still going. Progress is now 0.7214286, so in a week it’s only gone up by about 0.7%.

This is going to take a long time to complete…

Andy


#8

I’m almost tempted just to bin this backup and start again. However it’s a lot of data so would take a long time to complete.

Is there any reason to expect this rebuild to complete soon? If it’s managing 0.7% a week, then extrapolating that suggests it could take something like 40 weeks to complete!

Andy


#9

That’s strange, I did big rebuild recently and it “only” took two days for 600GB. More info here Rebuilding Database Extremely Slow


#10

Well, there was some surprise that it had completed that quick. My backup is more like 2TB I think.

Progress is still 0.7214286, so no progress in the last 19 hours.

Andy


#11

Progress is now 0.7285714.

Is there any information / debugging I can get to you to try to work out why this is taking so long? I’m a developer myself, so if I need to do anything like provide an strace output or similar, I should be able to do that.

Is it even worth allowing this rebuild to complete? Would it be easier just to delete the backup and start again? I could probably live with that, as it’s just a lot of information that doesn’t change often, so I don’t need access to old versions etc.

I assume I can abandon the rebuild and then delete the backup set?

Thanks

Andy


#12

Yes, that’s an option - though the backup set stored at the destination can still be used to restore FROM even if the local database doesn’t exist, so if you have the space you might want to keep it around for a bit while the new job is “filling up”.

It’s possible @kenkendk might have some input or testing requests… We know this is an issue in some instances, but have yet to figure out why it happens only some of the time. :frowning:


#13

Ok

Best get those requests in son, because it’s now been bout a month with no backups. Can’t really allow this to continue for weeks more.

Andy


#14

I have seen this too. What is even more surprising, sometimes during recreate duplicati seems to be doing literally nothing. It doesn’t use CPU and windows shows neither disk nor network, seemingly for minutes or even hours…

Looking at some of the queries (using joins and other not so fast approaches) in the log I also wondered if dumb simple batch inserts wouldn’t be possible…


#15

Giving up on this now. Can’t afford to have it blocking all my other backups for any longer.

Will start again but keep the old files in place just in case.

Andy


#16

Thanks for letting us know. Good luck with the new backup - at least now you can review your processes and decide if you want to split or join things into smaller or larger backup sets. :slight_smile:

Also, remember the files in the destination can still be used as a restore source via “Direct restore from backup files” so if you don’t need the space right away, it might be a good idea to keep those old backup files around while the new backup “fills in”.