Plugin to test database tweaks

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

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

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

[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

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