Home of the Squeezebox™ & Transporter® network music players.
Page 1 of 11 123 ... LastLast
Results 1 to 10 of 102
  1. #1
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,314

    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

  2. #2
    Senior Member
    Join Date
    Jan 2006
    Location
    South Coast, NSW, Australia
    Posts
    1,256
    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.
    A camel is a racehorse designed by a committee.

    Seen sprayed on the outside wall of the local library -
    Three things I hate in life :
    1. Vandalism
    2. Irony
    3. Lists

  3. #3
    Senior Member
    Join Date
    Sep 2005
    Posts
    2,845
    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:
    Code:
    High config > 1GB Ram
    Serverprio -6
    Searchprio -16
    Since i always (via cronjob - if there are new files to scan) do a
    Code:
    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?
    Last edited by DJanGo; 2014-05-02 at 23:56.

  4. #4
    Senior Member
    Join Date
    Sep 2005
    Posts
    2,845
    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"
    Last edited by DJanGo; 2014-05-04 at 04:03.

  5. #5
    Senior Member
    Join Date
    Nov 2010
    Location
    Hertfordshire, UK
    Posts
    2,976
    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.
    LMS 7.9.2 on PC, Xubuntu 18.04, FLACs 16->24 bit, 44.1->192kbps. 2 Touchs & EDO.
    LMS plugin UPnP/DLNA Bridge to MF M1 CLiC (A308CR amp & ESLs) & Marantz CR603 UPnP renderers.
    Also Minimserver & Upplay to same & to upmpdcli/mpd PC renderers.
    Squeezelite to Meridian USB Explorer DAC to PC speakers/headphones.
    Wireless Xubuntu 18.04 laptop firefox/upplay or Android 'phone with Squeeze-Commander/BubbleUPnP controls LMS/Minimserver.

  6. #6
    Senior Member
    Join Date
    Nov 2010
    Location
    Hertfordshire, UK
    Posts
    2,976
    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.

    Code:
    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%
    LMS 7.9.2 on PC, Xubuntu 18.04, FLACs 16->24 bit, 44.1->192kbps. 2 Touchs & EDO.
    LMS plugin UPnP/DLNA Bridge to MF M1 CLiC (A308CR amp & ESLs) & Marantz CR603 UPnP renderers.
    Also Minimserver & Upplay to same & to upmpdcli/mpd PC renderers.
    Squeezelite to Meridian USB Explorer DAC to PC speakers/headphones.
    Wireless Xubuntu 18.04 laptop firefox/upplay or Android 'phone with Squeeze-Commander/BubbleUPnP controls LMS/Minimserver.

  7. #7
    Senior Member
    Join Date
    Sep 2005
    Posts
    2,845
    [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]

    Quote Originally Posted by PasTim View Post
    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 )
    Last edited by DJanGo; 2014-05-04 at 03:51.

  8. #8
    Senior Member
    Join Date
    Nov 2010
    Location
    Hertfordshire, UK
    Posts
    2,976
    Quote Originally Posted by DJanGo View Post
    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.
    LMS 7.9.2 on PC, Xubuntu 18.04, FLACs 16->24 bit, 44.1->192kbps. 2 Touchs & EDO.
    LMS plugin UPnP/DLNA Bridge to MF M1 CLiC (A308CR amp & ESLs) & Marantz CR603 UPnP renderers.
    Also Minimserver & Upplay to same & to upmpdcli/mpd PC renderers.
    Squeezelite to Meridian USB Explorer DAC to PC speakers/headphones.
    Wireless Xubuntu 18.04 laptop firefox/upplay or Android 'phone with Squeeze-Commander/BubbleUPnP controls LMS/Minimserver.

  9. #9
    Senior Member JJZolx's Avatar
    Join Date
    Apr 2005
    Location
    Colorado
    Posts
    11,531
    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)

  10. #10
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,314

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •