Home of the Squeezebox™ & Transporter® network music players.
Page 1 of 2 12 LastLast
Results 1 to 10 of 102

Hybrid View

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

  8. #8
    Senior Member
    Join Date
    Sep 2005
    Posts
    2,845
    Quote Originally Posted by mherger View Post
    > 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
    *lol* dont call me schnitzel please use "someone" or crazy professor ;-)
    Quote Originally Posted by DJanGo View Post
    I Just stopped the server drop the db files set the Buffersize to 100 MB and edit whats going on.
    [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?

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

    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

  10. #10
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,314
    "However, changing from "none" to "full" or "incremental" can only occur when the database is new (no tables have yet been created) or by running the VACUUM command."

    That's why somebody noted you'd have to run a VACUUM after enabling the flag to make it stick.
    Michael

    http://www.herger.net/slim-plugins - Spotty, MusicArtistInfo

Posting Permissions

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