Plugin to test database tweaks

[erland]

> 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.

We still have a long way to go :-).

Just keep in mind that there might be more important to improve browsing functionality than improve scanning speed :-)
Especially since scanning speed already is acceptable for at least 95% of all LMS users, IMHO.

Currently only persist and library use that maximum setting. Caches
don't. I probably have to tweak this further, as caches are accessed
quite often, while persist is rather simple (unless you're using one of
Erland's plugins). But I'd like to get some data from you first.

I plan to experiment a bit with reordering the WHERE criterias in my plugins in case SQLite works as you indicated earlier in the thread and its optimiser prefers if the most limiting criteria comes first in the WHERE statement. Just have to be able to find some time to experiment with it...
Not sure if this affects anything regarding the buffer size choices you offer, I'm just mentioning it in case it does.

[mherger]

Plugin to test database tweaks

> 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?

No, it does not re-create them. The only way to do so without the plugin
is to delete the library.db file, which would cause LMS to start from
scratch.

--

Michael

[mherger]

Plugin to test database tweaks

> 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?

auto_vacuum is off for these two files. The auto_vacuum is mostly about
not wasting disk space, but it doesn't help the fragmentation. If you
want best performance, then you'll have to run a full VACUUM. As these
two files don't see that many changes the waste of disk space should be
negligible.

It's a different story for the cache files which change every minute if
you listen to some online source. This is the reason why I did not
enable auto_vacuum on library and persist, but on the cache files.

Initially I set vacuum to be run right after the scanner had wiped all
tables, as I expected this to be the quickest. Unfortunately the DB is
slightly slower if it has to constantly grow the file. Plus at the end
of the scan there would be a certain amount of fragmentation already.
Therefore I decided to run it in the optimize step.

> 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.

We still have a long way to go :-).

> 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.

Another change I just committed: the VACUUM is read during a new &
changed scan, too, if no player is active. The problem is that VACUUM
can break playback. If you use the rescan plugin to run scans at night,
you should be on the save side.

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

It's per database connection, to be accurate. Eg. the scanner can use a
different buffer size than the server (which makes sense, as the scanner
doesn't profit as much from the larger buffer to its mostly writing nature).

Currently only persist and library use that maximum setting. Caches
don't. I probably have to tweak this further, as caches are accessed
quite often, while persist is rather simple (unless you're using one of
Erland's plugins). But I'd like to get some data from you first.

Keep in mind that in most cases LMS would be using a fraction of that
maximum cache_size only anyway. Eg. my server process is currently
running at around 200MB memory usage. Before the buffer change it was
around 140MB. It's far from using 500MB times two cache_size.
Unfortunately I haven't figured out a way yet to determine how much
sqlite is actually using.

--

Michael

[PasTim]

Schedule the scans when not listening , You can live without you latest tag change a day or two

I do I kick them off when I go to bed, and then Server Power Control shuts the system down later on.

[Mnyb]

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

Schedule the scans when not listening , You can live without you latest tag change a day or two

[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

[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]

...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.

[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?

[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?

[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]

> 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

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

No, the indices are not touched currently.

--

Michael

[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 .

[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...