Plugin to test database tweaks

[PasTim]

Hi i dont want to go into the deeeeeeeep but a few lines should be enough.

If you drop a table or database and create a new one there is no "trash" or "old" data that belongs to the trash (means no "fragmentation")

If you change some (may in your library) and do a rescan, there are some old datas, thats now trash or "fragmentation".
if you dont change nothing where should the "trash" / Old Data come from?

This is not a black hokuspokus, its pure logic.

If you dont believe - check the source from both plugins and "check" what it does.

I'm not suggesting hocus pocus, just puzzled.

As I understand it custom scan deletes entries and recreates them, even though the data has not changed. In the process fragmentation occurs, even though the content is the same. Deleting and inserting does not necessarily re-use the same blocks. Vacuuming persist.db brought the size down from around 440MB to 411MB, even though no music tags have changed, and database tweak hard reported only around 3% 'waste'.

[mherger]

Plugin to test database tweaks

Ok, I tried to implement some of the two major findings of this thread
and plugin into 7.9:

- VACUUM is being run on library.db and persist.db during a wipe &
rescan. persist.db only is VACUUMed if its fragmentation has reached a
certain level though (20%).

- I've added a third "highmem" option: Default, High, Maximum. The third
option would use _lots_ of memory if needed (up to 500MB per database),
but only as much as needed, of course. It might be the best option for
most users who are running LMS on PC class hardware.

Most likely none of this does speed up the scanning (I haven't seen any
advantage using more than the 20MB of cache_size in the scanner), but
should help with the browsing. I sure hope it helps the users with
largish libraries (50k+ tracks).

Please uninstall the "Tweak DB" plugin and give 7.9 another try. Let me
know about any changes compared to 7.8 or earlier 7.9 _without_ the DB
tweaks. Thanks!

Builds should be available shortly.

--

Michael

[PasTim]

Builds should be available shortly.

Fine. Can you post here when the build including the changes is available?

[mherger]

Plugin to test database tweaks

> Fine. Can you post here when the build including the changes is
> available?

http://downloads.slimdevices.com/nightly/?ver=7.9 - now! :-)

--

Michael

[PasTim]

New 7.9 scanning as I write. The base LMS scan was the quickest yet (17:09 as against around 19). Custom Scan will take its time....

Will any missing indices (possibly removed using the tweak plugin, now uninstalled) get rebuilt automatically?

[erland]

As I understand it custom scan deletes entries and recreates them, even though the data has not changed. In the process fragmentation occurs, even though the content is the same. Deleting and inserting does not necessarily re-use the same blocks. Vacuuming persist.db brought the size down from around 440MB to 411MB, even though no music tags have changed, and database tweak hard reported only around 3% 'waste'.

In your case, based on the logs you posted, the delete of Custom Tag and Mixed Tag data, which should be the biggest chunk, is deleted using drop + create + insert.
In a smaller library, with less than 40 000 rows for a scanning module, delete will be used and this is also the case in your library for scanning modules like Rating Tag which produce less rows.

I'm not saying that this explains everything, I'm just saying that if fragmentation is only caused by delete but not by drop+create+insert, this could be part of the explanation.

I plan to do some further profiling myself, just have to be able to find the necessary time...

[PasTim]

Following on from the fast LMS scan on the most recent 7.9 release my Custom Scan took just under 1.5 hours, which is fine. I have set the buffer size to the max, although the number of tracks isn't large.

As to custom scan menu performance (I don't use the standard ones much at all I'm afraid), they were good, but not the best. I did a manual vacuum, and the menus were back to their sharpest. The two main databases sizes before/after manual vacuum were:

library.db - 53,472,256 down to 50,793,471
persist.db - 448,744,448 down to 416, 257,024

Unless I am imagining things library.db does seem a bit smaller than it was (it used to be around 60MB).

So if this becomes the new standard, that's good, but I'll probably carry on executing a manual vacuum after each scan. An extra 3 or 4 minutes after 2 hours (total) is not an issue for me.

Having spent quite a lot of time re-re-scanning..., I now need to go back to listening for a while, with a small glass of something .

[mherger]

Plugin to test database tweaks

> Will any missing indices (possibly removed using the tweak plugin, now
> uninstalled) get rebuilt automatically?

No, the indices are not touched currently.

--

Michael

[JJZolx]

> Will any missing indices (possibly removed using the tweak plugin, now
> uninstalled) get rebuilt automatically?

No, the indices are not touched currently.

Is your reluctance to alter the indices a matter of having to force a rescan when you redefine the database schema?

[mherger]

Plugin to test database tweaks

> Is your reluctance to alter the indices a matter of having to force a
> rescan when you redefine the database schema?

Well spotted! Yes, I want to make sure I have most everything together
before I force a rescan due to a schema change.

--

Michael

[JJZolx]

- VACUUM is being run on library.db and persist.db during a wipe &
rescan. persist.db only is VACUUMed if its fragmentation has reached a
certain level though (20%).

Do you turn on auto_vacuum during the scan, do a vacuum before scanning, or do a vacuum after scanning? Why not also do the remaining databases?

The thing I dislike about this approach is that if everything in LMS worked as it should (i.e. if there were no scanning bugs or bugs that cause the database to become inconsistent or corrupted) users should never have to run a full rescan. Having to do so to maximize performance is not the best strategy, IMO. For the users for whom this will do the most good, that full rescan may take hours due to very large libraries or very slow hardware.

- I've added a third "highmem" option: Default, High, Maximum. The third
option would use _lots_ of memory if needed (up to 500MB per database),
but only as much as needed, of course. It might be the best option for
most users who are running LMS on PC class hardware.

Is the max buffer size per SQLite database or is there just one buffer for the entire LMS application?

[PasTim]

> Will any missing indices (possibly removed using the tweak plugin, now
> uninstalled) get rebuilt automatically?

No, the indices are not touched currently.

--

Michael

Apologies if I wasn't clear and/or I'm being thick.

My question is as follows. I removed the tweak plugin whilst the indices had been cut back. I then installed the latest 7.9 and did a full scan. Will this recreate any missing indices or not?

[PasTim]

...if everything in LMS worked as it should (i.e. if there were no scanning bugs or bugs that cause the database to become inconsistent or corrupted) users should never have to run a full rescan. ....

I quite often change the tags on my FLACS, to ensure consistency across multiple performances of classical works.

I have to do a full rescan each time I do this because changes (rather than additions) to the library often result in errors in the menus. Further, because I make extensive use of custom scan, and this scan always takes quite a long time whether for a change scan or full scan, the shorter scan doesn't save much time over all.

[JJZolx]

I quite often change the tags on my FLACS, to ensure consistency across multiple performances of classical works.

I have to do a full rescan each time I do this because changes (rather than additions) to the library often result in errors in the menus. Further, because I make extensive use of custom scan, and this scan always takes quite a long time whether for a change scan or full scan, the shorter scan doesn't save much time over all.

Relatively few people using LMS use the custom scan plugin. If you're stuck having to do full rescans that take hours to correctly pick up minor tagging changes, that's your choice.

What I'm saying is that scanning has gotten and continues to get much better in 7.8.1 and 7.9, and requiring a full rescan is much less common these days. I very seldom have to do one when making tagging changes in my library now. Pretty much the only reason I run a full wipe and rescan these days is to test scanning speed.

[PasTim]

Relatively few people using LMS use the custom scan plugin. If you're stuck having to do full rescans that take hours to correctly pick up minor tagging changes, that's your choice.

What I'm saying is that scanning has gotten and continues to get much better in 7.8.1 and 7.9, and requiring a full rescan is much less common these days. I very seldom have to do one when making tagging changes in my library now. Pretty much the only reason I run a full wipe and rescan these days is to test scanning speed.

Fair enough.

However, without custom tags I really couldn't use LMS, so I don't feel I have much choice. The people (Microsoft?) who invented the standard tags seem to have had no idea of the various types of music and their requirements