Glad to read that it is going to be implemented this way. I assumed something like that might happen, but you know what they say about assumptions…
For the time being I made a script that fixes the scripts 
Ohhh… Share, plz?
Most preferably in
core optionswindow.
So would that be an auto-reset option… you click “clean up DB” in core options, nothing happens until you close and then it runs, and the tick-box is then cleared? Or something else…
Trully… just a button. you click on it and it does VACUUM; ANALYZE on db 
I’ll try to spend more time on something even less obtrusive than that and more user friendly (for those users who don’t want no stinkin “maintaince db” button in their settings) 
1 Like
Excellent 
I’ve send you a message.
1 Like
Hey everyone - there’s PR #4337 addressing all things mentioned.
After my PR’s merged VACUUM will be unconditionally done on all db structure upgrades.
Turns out - making this at db upgrade was easiest part 
Thought about it… Gave up. Instead went for options + nice dialog
Exactly… I mean partially. With my PR user will have following choices:
- freepage_ratio based, so if db has too many free pages (25% by default) then program MIGHT ask for maintenance
- user can set up when program will ask about maintenance: never, on startup, on close, on both. With never the program will obviously never ask about maintenance and will only perfrom one on db upgrade.
- even if program asks, then it actually asks whether to perform now or later (later being next program startup/close if freepage_ratio is still above threshold)
I think that’s good enough rather than some button somewhere
And I hope this is up to everybody’s expectations 
EDIT: and after @Pascal_Obry suggestion, the db maintenance options are in “misc” section in core options tab in setings window
3 Likes
@johnny-bit Today, when I closed darktable, a small window popped up asking me to aprove database maintenance.
However, I could only see that window, not darktable main window, like this:
Is darktable main window expected to be opened until the maintenance finishes? It was a bit weird after I clicked yes, because the small window also disappeared and I wasn’t sure when it finished. The point is that at that moment I was about to shut down the notebook and I was afraid of interrupting the maintenance.
Also, Is there some kind of progress bar showing the progress of the maintenance?
And is there a way to see some kind of statistics from the last maintenance? Or at least, if it succeeded or not.
1 Like
There’s 30.2 MB to be freed. Nothing to write home about imho 
my whole library.db is that size
Not at all. GUI closing is the 1st thing before any cleanup process happens (and darktable does a lot of clean up, db maintenance is small-ish part)
And that’s UX evolution for me and you click “yes” and poof, it’s done. Theoretical limit is your disk write speed so with 30MB of freepages and default settings I’d believe your db pre-maintenance was ~110MB, so the time to do maintenance would be “as long as it takes to write 80MB to disk”. In most cases it’s neglible. Would you like any kind of confirmation that it’s done? or would you rather have it by default asking at the start of darktable (so it finishes before you start working)?
Unless you brute-force closing, there’s no way any OS would do kill on that process (since it would send SIGTERM, and we’re already closing)
Plus - db maintenance is an atomic operation for sqlite (if i remember correctly), meaning that it’s done in separate temporary file and once it’s successful it changes temp name to old file (and that’s instanteous in todays filesystems)
Nope, and it’s impossible to “show progress” in any menaingfull way… Maybe except showing little “pliz vait” window and the process shouldn’t take more than ~2s in bad cases
Statistics? I mean - it shows you that it can free 30 MB and once you click yes it doesn’t lie
I can add another dialog with “Maintenance done good sir, 30 mb freed”, but I personally hate those
How would you like it to be?
I personally think that asking on startup by default would be better, since asking on close creates problems you’ve mentioned.
1 Like
“For how long have you been using darktable ?”
“Oh, something like 300 MB.”
Nifty. Thanks @johnny-bit !
1 Like
My pleasure! (TBH for cases like yours it SHOULD speed up db access a tiny bit). Especially since that whole set of patches forces code to VACUUM, ANALYZE on command + PRAGMA optimize on every close…
No, if it means popping up another window from nothing (at this point I won’t have any visual indication that DT is running, right?).
ANd also no if most of those maintenance processes run so fast - this confirmation window would pop up instantaneously after pressing yes, which would be kind of weird.
So, if shutting down the computer, as well as closing the lid (suspend), don’t present any issue, I’m ok: press yes and be happy.
Thanks for having worked on this @johnny-bit
1 Like
@johnny-bit another question.
Today I started darktable and then closed it, without doing anything. The maintenance windows popped up claiming 29.5 MB to be cleaned. I aswered yes.
Then I reopened darktable and closed it and, again, the same windows popped up. I repeated this a couple of times and the window always show up.
Is it normal?
No it isn’t. Which version do you have? I’ve fixed some stuff around maintenance and it should fix most of the problems.
In recent PR (#4379) I’ve added some debugging info that can be enabled via -d sql option to diagnose such cases.
However - If you have newest master and still sqlite still won’t vacuum it right then try changing the option to on startup. If that fixes it then we’ll have some moving around to do…
1 Like
I had git721, now upgraded to git-776.
I opened and closed it and the window didn’t show up.
It seems ok now.
I’ll keep an eye, though.
Thanks!
1 Like
Hey @gadolf @GBB29 @pehar @Jade_NL and @anon41087856
In order to get the newest changes in maintenance code merged I need better descriptions for options. Since English isn’t my first language I need a bit of help.
Here’s my current propositions:
maintenance_check :
shortdesc: “check for database maintenance”
longdesc: “when to check and ask to perform database maintenance. the 'don’t ask options will simply perform maintenance if needed.”
maintenance_freepage_ratio :
shortdesc: “database fragmentation for maintenance”
longdesc: “a minimum fragmentation ratio to perform maintenance”
I welcome all suggestions. Messages need to be clear and not scaring people away. for maintenance_freepage_ratio the values should be in range of [0-100] but sensible range is IMO [15-40] and I don’t know how much you’d need to grind db to get freepage ratio above 50%.
I couldn’t work out from your commits what the options were. But guessing, I’d put something like:
maintenance_check:
shortdes: “Permission for database maintenance”
longdesc: “is permission required to launch database management?. ‘Don’t ask’ options will run without asking”
maintenance_freepage_ratio:
shortdesc: “Fragmentation beyond this triggers automatic maintenance, or request”
longdesc: “Fragmentation above the selected ratio suggests database maintenance. In this case permission will be asked, or carried out automatically according to choice for maintenance_check”
English is my first language, but that might not be an advantage for international understanding…
2 Likes
I’m Dutch so English isn’t my first one either. That said:
I think @GBB29’s proposal is rather spot on. Only minor comment about it: all should be lower case and no question marks to conform to dt standards.
so… “database fragmentation threshold to trigger cleaning maintainance” ?
3 Likes