Plugin to test database tweaks

[mherger]

> as i hav e a highly tagged large library, is it helpful if i'm testing
> something for new releases? I'm happy to be a guinea pig!
> I'm in home office :-) i can fiddle around a lot.

Just get whatever are the dev branch builds. Currently this would be LMS
8.2.

[mherger]

Plugin to test database tweaks

I'd like to better understand how we could tune the LMS database to
improve performance. Please continue to read this posting if

- you have a decent collection of 20k+ tracks
- you run LMS 7.8/7.9
- you are willing to take the risk of applying some little tested
changes to your music server
- you have a server machine with a couple of gigabytes of RAM
- you think LMS usage (NOT the scanner!) is slow


This simple plugin allows you to:

- set the buffer size for the database
- drop indices which I strongly believe are not used in LMS
- VACUUM individual DB files and enable/disable auto_vaccum

Many of the actions will keep your server busy for a while, blocking
playback etc. while changes are applied. Be warned! There's some red
print on the settings page. Read it :-).

If you still want to give this a try: please add the following
repository to the list in Settings/Plugins:

http://www.herger.net/slim-plugins/test.xml

Then install the "Tweak Database" ("Datenbank optimieren" if you're
server is set to German). Go to Settings/Advanced/Tweak Database and
have some fun :-).

What I'm most interested in is the buffer size setting. We currently use
2MB or 20MB (if you set the preference for high memory usage). With this
plugin you can throw lots more at it. I've seen massive improvements in
eg. search in the web UI when lifting this value. Eg. using a 100k
collection a track search might take a couple of seconds with the
default LMS settings. Using a buffer size of 50MB brought this down to
less than a second.

If you have plenty of memory, go to the max and do some searches. The
database will never use more memory than it really needs. Then reduce
the size to half the previous value, until you think searches are
getting slower again (no need to restart LMS between changes). Then
please tell me what your library size, library.db file size and the
buffer size are.

Please note that the standalone scanner does not yet take profit of any
of this. It's server only for now.

--

Michael

[Wirrunna]

Michael, I'm out in the sticks for a few days, reading this via tethered phone. Will test extensively when back.
Using a plugin to alter SQLite memory usage is a clever idea. Look forward to trying it.

[DJanGo]

Hi Michael,

i soon report my feddback, til the scanner is done.
Since i logged all my prev. scans i knew my (better my familys Lib ) with 4242 Albums should be scanned in ~1 hour.

I Just stopped the server drop the db files set the Buffersize to 100 MB and edit whats going on.
My Server (7.8.0 - 1395409907) runs under:

High config > 1GB Ram
Serverprio -6
Searchprio -16

Since i always (via cronjob - if there are new files to scan) do a

ls -l *.db>/tmp/maintenance.log
sudo sqlite3 persist.db "vacuum";
sudo sqlite3 artwork.db "vacuum";
sudo sqlite3 imgproxy.db "vacuum";
sudo sqlite3 library.db "vacuum";
sudo sqlite3 persist.db "vacuum";
ls -l *.db>>/tmp/maintenance.log

I also knew that vacuum is a good thing and can tell is realy worth to do (even if a dont rescan always drop db and do a freshscan).
The "new" feature to look @ the db filesizes via webgui is something "some" not so experienced Endusers will kiss your feets ;-)

[edit]
id changed the buffer to 100 MB and a full scan needs 7808 seconds for 60.733 tracks (idv2.3 only all with embedded artwork) - thats 20 mins more than without the settings.
Searching was bit faster so i drop the unneeded indizes - fragmentation for lib.db goes up to 5% db size was 92.9 MB
and searching was like normal.

But after i press fragment lib.db the buffersize was [as shown in the webgui] set to "" - just set to 100.
The lib.db after that was 87.4 MB.

[/edit]
what kind of information you need?

[DJanGo]

Hi Michael,

i played with the Server and thats what i found out - see settings above:

If i am searching "putumayo" its very (kinda faster than it was) fast and shows me all Albums that starts with "Putumayo presents".
But if i search after e.g. "the girl" it needs the same amount of time than without the plugin and shows me all Tracks from "Everything but the Girl"

Means it seems there is a improvement if i search after the first word(s) from a Album/Artist but searching the last words - its "slow" than before.


edit
grmblfix
i just looked after the "/settings/index.html" and changed the setting from "Innerhalb eines Wortes suchen" to "Innerhalb eines Wortes suchen
" - it was "nur am Wortanfang suchen" - now its the same speed everywhere ;-)


I'll soon check with not removed "unused indizes"

[PasTim]

I'm a heavy user of Erland's custom scan plugin (for classical tags). Some of the menus were extremely slow the first time they were used after a restart (e.g. 2 minutes to get any reaction at all), and I tend to shutdown at least once a day so this happened quite a lot. After this delay, the menus were reasonably quick (a few seconds at worst).

A few months ago I discovered that vacuuming persist.db after a rescan (whether clean or just additional) made an enormous difference. All custom browse/scan menus benefited.

So I'll give this a try and let you know.

[PasTim]

I've just run some not very scientific tests to see whether this helps at all. My library is a little under 25,000 tracks. The stats are below after a full rebuild and vacuum.

The vacuuming makes a big difference to custom browse using custom scan tags, and I'd be very happy if this became a standard plugin for doing this. As I said before, it turns waits of a couple of minutes into almost instant response (for the 1st use after restart). All menus then give a sharp response.

I've been running with a buffer of 100MB. It seems that even custom scan times are considerably improved, from around 3 hours for a full scan to about 2 hours. Can this be my imagination?

I tried with and without the indexes and can't say I've notice any difference (and I assume they get rebuilt anyway after a full rescan).

One question: Does the auto-vacuum setting 'stick' or not? I haven't done enough trials to quite understand how this is expected to work. If I tick it and Apply, the tick disappears.

Vacuum 	auto_vacuum 	 	File Length 	Fragmentation
		/var/lib/squeezeboxserver/cache/imgproxy.db	4.3 MB	0%
		/var/lib/squeezeboxserver/cache/PluginCache-CustomBrowse.db	2.0 MB	0%
		/var/lib/squeezeboxserver/cache/persist.db	409.1 MB	0%
		/var/lib/squeezeboxserver/cache/PluginCache-SQLPlayList.db	0.3 MB	0%
		/var/lib/squeezeboxserver/cache/library.db	71.1 MB	0%
		/var/lib/squeezeboxserver/cache/cache.db	3.4 MB	0%
		/var/lib/squeezeboxserver/cache/artwork.db	83.7 MB	0%
		/var/lib/squeezeboxserver/cache/PluginCache-DatabaseQuery.db	0.2 MB	0%
		/var/lib/squeezeboxserver/cache/PluginCache-MultiLibrary.db	0.1 MB	0%

[DJanGo]

[edit]uuuuppssss...

after i stopped and start LMS (*ux) the settings are gone....
May its cause its only a testbaloon and michael wants us to have a "known good setting if there is something wrong after a wrong setting is set?"
[/edit]

One question: Does the auto-vacuum setting 'stick' or not? I haven't done enough trials to quite understand how this is expected to work. If I tick it and Apply, the tick disappears.

Hi please check this "workaround"

tick "all" boxes click the "gotit - doit" button and then "apply".
On mine the vacuum settings are sticky (only) when i did it this way.
(But i dont know exactly what this feature does - ill prefer just to vacuum if the db files are changed - but iam not an ordinary user)

@ michael:
everything runs pretty well i dont know if its bit better but its not worser ;-)
(but i dont use any custom plugins other than this, "Just Covers" & pluginplayer even no radiostreaming )

[PasTim]

tick "all" boxes click the "gotit - doit" button and then "apply".
On mine the vacuum settings are sticky (only) when i did it this way.

Thanks - that worked for me.

[JJZolx]

Vacuuming:
I don't see any appreciable difference, but I've been vacuuming the SQLite databases nightly for quite some time, so wouldn't expect to see any.

Buffer size:
No real difference. I've always had nearly instantaneous search results and very fast web page generation.

Removing unused indices:
No noticeable difference in browsing, searching, etc. The one place where I expected to see a difference has proven to be true: doing full rescans. However, they've gotten slower, not faster. I've run at least half a dozen scans today and the results I'm getting are consistent. Also, the scan times with indices are virtually identical to the times I see in 7.8.1.

(~41,000 tracks)

Without indices:

Discovering files/directories: E:\Flac (45585 of 45585) Complete 00:00:26
Scanning new music files: E:\Flac (40839 of 40839) Complete 00:11:59
Discovering playlists: C:\ProgramData\Squeeze\70\playlists (6 of 6) Complete 00:00:00
Scanning new playlists: C:\ProgramData\Squeeze\70\playlists (5 of 5) Complete 00:00:01
Pre-caching Artwork (3567 of 3567) Complete 00:01:54
The server has finished scanning your media library.
Total Time: 00:14:20 (Sunday, May 4, 2014 / 11:02 AM)

With indices:

Discovering files/directories: E:\Flac (45585 of 45585) Complete 00:00:26
Scanning new music files: E:\Flac (40839 of 40839) Complete 00:11:23
Discovering playlists: C:\ProgramData\Squeeze\70\playlists (6 of 6) Complete 00:00:00
Scanning new playlists: C:\ProgramData\Squeeze\70\playlists (5 of 5) Complete 00:00:01
Pre-caching Artwork (3567 of 3567) Complete 00:01:52
The server has finished scanning your media library.
Total Time: 00:13:42 (Sunday, May 4, 2014 / 11:38 AM)

Without indices:

Discovering files/directories: E:\Flac (45585 of 45585) Complete 00:00:26
Scanning new music files: E:\Flac (40839 of 40839) Complete 00:11:58
Discovering playlists: C:\ProgramData\Squeeze\70\playlists (6 of 6) Complete 00:00:00
Scanning new playlists: C:\ProgramData\Squeeze\70\playlists (5 of 5) Complete 00:00:01
Pre-caching Artwork (3567 of 3567) Complete 00:01:53
The server has finished scanning your media library.
Total Time: 00:14:18 (Sunday, May 4, 2014 / 11:58 AM)

[mherger]

Plugin to test database tweaks

> If i am searching "putumayo" its very (kinda faster than it was) fast
> and shows me all Albums that starts with "Putumayo presents".
> But if i search after e.g. "the girl" it needs the same amount of time
> than without the plugin and shows me all Tracks from "Everything but the
> Girl"

You don't mention what parameter you changed. Installing the plugin
won't do anything.

--

Michael

[mherger]

Plugin to test database tweaks

> The vacuuming makes a big difference to custom browse using custom scan

Did you check the file sizes before and after vacuum?

> I've been running with a buffer of 100MB. It seems that even custom
> scan times are considerably improved, from around 3 hours for a full
> scan to about 2 hours. Can this be my imagination?

Did you only change the buffer size? I've seen reports of both, changing
it to to worse and the better... but I thought it wouldn't have any
impact on the scan at all, as the buffer size is not applied in the
scanner :-).

As I did see some slower scanning with larger buffers, I'll have to
investigate this further.

> I tried with and without the indexes and can't say I've notice any
> difference (and I assume they get rebuilt anyway after a full rescan).

No, they are not rebuilt on a rescan. You'd have to delete the
library.db or use the button in this plugin.

> One question: Does the auto-vacuum setting 'stick' or not? I haven't
> done enough trials to quite understand how this is expected to work. If
> I tick it and Apply, the tick disappears.

Yeah, that UI isn't really clear. Quite obviously I didn't pay much
attention to it. The plugin started as a simple UI for myself to
enable/disable auto_vaccum. I didn't think it would evolve from there...

--

Michael

[mherger]

Plugin to test database tweaks

> No noticeable difference in browsing, searching, etc. The one place
> where I expected to see a difference has proven to be true: doing full
> rescans. However, they've gotten slower, not faster. I've run at least
> half a dozen scans today and the results I'm getting are consistent.

Thanks a lot for the extensive testing! I did indeed find one single
occurrence where the album title index would be used: during the scan...
I don't know yet why in this particular case the column created for this
task is not used as in all of the rest of the server code. Lack of that
index could be responsible for the slightly slower scan.

--

Michael

[mherger]

Plugin to test database tweaks

Some background information about this plugin. As I mentioned in another
post, it started out as a helper for myself to investigate the effect of
auto_vacuum. I did notice that under certain circumstances some database
files (artwork/image proxy caches) could grow to massive size - and
wouldn't free the disk space any more. I've been using them with
auto_vacuum enabled for months without the slightest issue. Deletion
queries might be slightly slower than without, but we rarely do them,
and when we do, then it's either during a wipe & rescan, or in idle
times (cache purging).

Last week, while offline on a train ride, I decided to investigate an
issue some users have been reporting: searching for tracks using the web
UI with large collections was said to be "atrocious". I had no reason to
complain: it was reasonably quick with my Atom based NAS and my <20k
collection. But indeed, searching 100k tracks took several seconds. I
figured out a few interesting facts:

- the index built on the title is never used during searches, as we do
"select 'name%'..." to match partial matches. SQLite doesn't use indices
in this case but does search the full table. Much to my surprise this
wasn't the reason for the slow search: 100k tracks would return four
matches within less than 500ms reliably.

- the performance hit came from sorting these four records by title.
This part took well over 5 seconds (yes, more than 10x the time the
search took to sort four records!). This just didn't make sense to me.
Until I monitored disk I/0: much of that time was spent reading lots of
data from the disk. FWICT SQLite would load the indices for the sorting.
It's my understanding that loading the indices into memory for the
sorting is what took the longest! I repeatedly reproduced this with a
simple SQL query on the tracks table alone using a SQLite client.

This observation lead me to play with the buffer size. I set it to be
about the size of the library.db. And voilà: after an initial first
search during which all the data needed to be read, all searches were
returned within a fraction of a second.

I then reduced the buffer size to see whether there was a clear
threshold where things slowed down again. 20MB buffer were obviously not
good enough. 30MB were (library.db being about 140MB). I therefore guess
that today's 20MB are good enough for the vast majority of users. But
users beyond 100k tracks might greatly profit from using more memory.
Which kind of confirms what users have been saying: there's a threshold
after which LMS with SQLite is much slower than when using MySQL. The
buffer size might eliminate (or shift :-)) that threshold.

About the indices: all SQLite optimization FAQs recommend to create as
little indices as possible, as maintaining them is costly, but add as
many as needed. We do have quite a few indices we don't seem to be
using. I don't expect wonders from deleting them. But if we really don't
use them, then it would be reasonable to get rid of them anyway. Maybe
it would at least reduce the data loaded into the buffers.

I'm still looking for some feedback from a Windows user with > 100k
tracks...

--

Michael

[PasTim]

> Did you check the file sizes before and after vacuum?

No, I didn't take a note of it, but what Custom Scan seems to do every time is a large number of deletes followed by the inserts. Once I discovered vacuuming I've been running it every time after a scan, and it's been a great improvement. Your tool makes it that much easier.

Did you only change the buffer size? I've seen reports of both, changing
it to to worse and the better... but I thought it wouldn't have any
impact on the scan at all, as the buffer size is not applied in the
scanner :-)

I wonder whether the scanning for Custom Scan reads a lot of data from library.db. I suspect it does. If so, a larger buffer might help.

I'd be happy if this plugin, or something like it, were to remain available.