Yes. I plan to add a bit of code to database.c
and other places to help with db overall.
Yes, I do plan on doing just that. Most preferably in core options
window.
Wonât be scripts, will be direct sqlite3 calls in C code
Yes. I plan to add a bit of code to database.c
and other places to help with db overall.
Yes, I do plan on doing just that. Most preferably in core options
window.
Wonât be scripts, will be direct sqlite3 calls in C code
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 options
window.
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)
Excellent
Iâve send you a message.
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:
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
@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.
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.
âFor how long have you been using darktable ?â
âOh, something like 300 MB.â
Nifty. Thanks @johnny-bit !
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
@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âŠ
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!
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âŠ
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.