View Issue Details

IDProjectCategoryView StatusLast Update
0005110MMW v4DB/FileMonitorpublic2008-12-31 18:44
Reporterjiri Assigned To 
PriorityimmediateSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version3.1 
Fixed in Version3.1 
Summary0005110: DB Maintenance is very slow for large DBs
DescriptionWhen DB Maintenance is executed for large DBs, it can take many minutes, or even hours. We can't do much about it at this moment, because it's how SQLite works, but I'd suggest to split maintenance to two steps:

[x] Quick maintenance
[ ] Complete maintenance (can take very long)

Teh first item would make check for DB integrity and some basic optimization, while the second item would completely rebuild DB, as MM currently does.
TagsNo tags attached.
Fixed in build1206

Activities

jiri

2008-12-30 22:58

administrator   ~0015884

Assigning to Rusty for a review and possible inclusion of this and its strings in MM 3.1.

Teknojunky

2008-12-30 23:08

updater   ~0015887

Last edited: 2008-12-30 23:19

I have a huge library (~ 650 megs for 3.1.x) and db maintenance varies greatly based on cpu processor...

My laptop (core duo 2ghz) takes about 1 to 2 hours to maintain library, where my home system (intel quad core) takes 10 to 15 minutes.

In either case, the mm app window is stuck frozen and unable to abort or see any progress.

Some Suggestions/enhancements:
- allow for abort to cancel process and return to pre-vacuumed db
- stop playback (and any other background processes) to prevent audio stuttering at end of currently played track
- show progress bar in the normal MM status/progress area (or perhaps the database conversion progress dialog)
- option to shutdown MM on maintence completion (so that user can do over-night, perhaps use the sleep timer options)

rusty

2008-12-30 23:18

administrator   ~0015888

I'd suggest:

Optimize database (Quick)
Optimize database (Complete)

Tooltips:
1) Optimizes the database to improve performance. (this string already exists)
2) Optimizes the database to improve performance. It yields some additional performance, but may take several hours.

Also, since it's going to take a long time, the user needs some indicator that progress is being made (e.g. Optimizing record 1/100,000). Otherwise, they'll almost certainly think that the process is frozen.

reminder: include logic to prevent both options from being selected.

peke

2008-12-30 23:45

developer   ~0015890

I can confirm Teknojunky findings and I agree with Rusty About Suggestion. With Only Addition that Minimize to tray While Optimizing Library would be usefull

jiri

2008-12-31 00:10

administrator   ~0015892

Fixed in build 1206.
 - Added both items.
 - No progress indicator was added, since I can't get anything useful from SQLite. That said, the first action is very fast and the second is as slow as it has always been.

Owyn

2008-12-31 02:01

updater   ~0015898

Is there any linkable documentation about what is included in the maintenance processes?

jiri

2008-12-31 09:59

administrator   ~0015917

You can check the exact SQLs that are executed in the debug log, but there's not much interesting - just some consistency checks and SQLite's VACUUM.

Owyn

2008-12-31 11:54

updater   ~0015919

Thanks. I will do just that. Database integrity is a keen issue for me at the moment. :)

rusty

2008-12-31 18:44

administrator   ~0015929

Verified 1207.