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

    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

  2. #12
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,298

    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

  3. #13
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,298

    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

  4. #14
    Senior Member
    Join Date
    Nov 2010
    Location
    Hertfordshire, UK
    Posts
    2,970
    Quote Originally Posted by mherger View Post
    > 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.
    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.

  5. #15
    Senior Member JJZolx's Avatar
    Join Date
    Apr 2005
    Location
    Colorado
    Posts
    11,531
    Have you seen this? I'm not sure if this is version-specific, but it does seem to indicate that your plugin can't just turn auto_vacuum on and off on the fly.

    Auto-vacuuming is only possible if the database stores some additional information that allows each database page to be traced backwards to its referrer. Therefore, auto-vacuuming must be turned on before any tables are created. It is not possible to enable or disable auto-vacuum after a table has been created.
    More at: http://sqlite.org/pragma.html#pragma_auto_vacuum

    One thing I was thinking might be handy, since I'm doing it already in a roundabout way: Having the ability to schedule a nightly database maintenance operation from within LMS, much the same as we can do a nightly library rescan. In fact, ideally you'd be able to run it right after the scan has run, both to avoid running them simultaneously, and also to optimize the library database for that day's use.

  6. #16
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,298
    "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

  7. #17
    Senior Member JJZolx's Avatar
    Join Date
    Apr 2005
    Location
    Colorado
    Posts
    11,531
    Ah... I missed that. Are you using the full or incremental setting?

  8. #18
    Senior Member
    Join Date
    Sep 2005
    Posts
    2,845
    Quote Originally Posted by JJZolx View Post
    Have you seen this? I'm not sure if this is version-specific, but it does seem to indicate that your plugin can't just turn auto_vacuum on and off on the fly.



    More at: http://sqlite.org/pragma.html#pragma_auto_vacuum
    Hi,

    please check 1 means enable incremental vacuum see http://www.tutorialspoint.com/sqlite/sqlite_vacuum.htm
    Code:
    root@muzzigbox:# sqlite3 /var/lib/squeezeboxserver/cache/library.db "PRAGMA auto_vacuum;"
    1

  9. #19
    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?

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

    Plugin to test database tweaks

    > Ah... I missed that. Are you using the full or incremental setting?

    Full.

    --

    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
  •