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

    Plugin to test database tweaks

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


    The auto_increment is not about performance, but about disk space
    recovery only. There's even a chance that it would harm performance, due
    to increased fragmentation. Only the full vacuum does re-organize the
    file internally to clean up fragmentation. The plugin's UI is probably a
    bit misleading, as what is reported as "fragmentation" actually is the
    percentage of unused disk space occupied by the db file.

    I guess that the performance improvement you've seen wouldn't become
    reality with enabling auto_vacuum only. But I still consider it an
    option for the db files which change a lot (caches).

    --

    Michael

  2. #22
    Senior Member Mnyb's Avatar
    Join Date
    Feb 2006
    Location
    Vństerňs Sweden
    Posts
    16,525
    I did the vacuum thing shrank some files considerably .

    See the pictures . I'll report other findings if i notice anything (mid size collection 42k files not very fast server anyway.. )
    --------------------------------------------------------------------
    Main hifi: Rasbery PI digi+ MeridianG68J MeridianHD621 MeridianG98DH 2 x MeridianDSP5200 MeridianDSP5200HC 2 xMeridianDSP3100 +Rel Stadium 3 sub.
    Bedroom/Office: Boom
    Loggia: Raspi hifiberry dac + Adams
    Bathroom : Radio (with battery)
    iPad with iPengHD & SqueezePad
    (spares Touch, SB3, reciever ,controller )
    server Intel NUC Esxi VM Linux mint 18 LMS 7.9.2

    http://people.xiph.org/~xiphmont/demo/neil-young.html

  3. #23
    Senior Member
    Join Date
    Nov 2010
    Location
    Hertfordshire, UK
    Posts
    2,969
    I notice that my persist.db is much larger than many others reported, at around 411MB, even with a smallish collection of < 25,000 tracks. This is because I have several custom tags, this being the only way I have found to properly index classical music. I believe the fragmentation got severe for every rescan.
    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.

  4. #24
    Senior Member
    Join Date
    Sep 2005
    Posts
    2,845
    Quote Originally Posted by mherger View Post
    I guess that the performance improvement you've seen wouldn't become
    reality with enabling auto_vacuum only. But I still consider it an option for the db files which change a lot (caches).
    Michael
    i know it has nothing to do with that, cause i vacuum (like i wrote earlier in this thread) the db after each clear and scan so i had "no other" feeling of the time consumption on a not vacuumed db - cause i never ever had one.....

    The Big performance improvement was after i screw the buffer size to 100 mb (cause of my library.db was 93.7 MB).
    [Hab ich doch extra zwischen eckige klammer auf B eckige klammer zu gemalt]? *lol*

  5. #25
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,032
    Quote Originally Posted by PasTim View Post
    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.
    Just to confirm, Custom Scan will delete all data and re-create it every time you rescan.
    If there are more than 40000 rows for the Custom Tag/Mixed Tag module in the customscan_track_attributes table, it will instead drop the table and recreate it, since DELETE is too slow when the table gets large.

    Quote Originally Posted by PasTim View Post
    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.
    Custom Tag module should for each track do a:
    Code:
    SELECT id,url from tracks where audio=1 limit 1 offset ?
    Mixed Tag module will do INSERT based on SELECT, like for example:
    Code:
    INSERT INTO customscan_track_attributes 
        (
           track,
           url,
           musicbrainz_id,
           module,
           attr,
           value,
           valuesort,
           extravalue,
           valuetype
        ) 
        SELECT 
            tracks.id,
            tracks.url,
            case when tracks.musicbrainz_id like '%-%' then tracks.musicbrainz_id else null end,
            'mixedtag',
            ?,
            contributors.name,
            contributors.namesort,
            contributors.id,
            'artist' 
        from tracks,contributor_track,contributors 
        where 
            tracks.audio=1 and 
            tracks.id=contributor_track.track and 
            contributor_track.role=1 and 
            contributor_track.contributor=contributors.id
    There will be a SQL like this for each tag you have configured in the Mixed Tag scanning module.

    I suspect the buffer might help with Mixed Tag scanning module but it feels like it will have less impact on the Custom Tag scanning module. However, I haven't had the time to try this myself and I suspect my library is probably too small to notice any difference. If auto vacuum works, I suspect it will probably be the biggest improvement related to Custom Scan related performance.
    Erland Isaksson (My homepage)
    Lead platform developer of ickStream Music Platform - A world of music at your fingertips

    (Also developer of many plugins/applets)

  6. #26
    Senior Member
    Join Date
    Jan 2006
    Location
    South Coast, NSW, Australia
    Posts
    1,255
    Back from the sticks for a day.

    First off, I have moved my LMS servers from Windows to VortexBox 2.3, but my workstations are still on Win 7 with SSD and RAMDisk, so I changed CACHE from RAMDisk to a fragmented WD 640Gb work disk for this testing.

    Logitech Media Server Version: 7.8.0 - 1395409907 @ Thu Mar 27 20:19:17 CUT 2014
    Hostname: Gladstone, Intel Q9450 2.7GHz Quad Core 8GB RAM
    Total Tracks: 144,947, Total Albums: 11,302, Total Artists: 3,146, Total Genres: 104

    Tweak Buffer Size - 20
    Starting LMS took more than 10 minutes after moving the CACHE from R:\ to E:\ , Windows Task Manager showed LMS was reading Persist.db and Library.db .

    Browse New Music - alsmost instant (used to stop server and music playback for a minute or two - the reason I went to RAMDisk !)
    Search Albums for "the girl" - first time took 54 seconds, player stopped with re-buffering. Second search almost instant.
    New Albums 2 seconds.

    This is frustrating, most searches are returned in 1~2 seconds where a year ago they would lock up the server and stop the players.

    Memory :Hard Faults/sec - Commit (KB) - Working Set (KB) - Shareable (KB) - Private (KB)
    SQUEEZ~3.EXE 0 - 314,692 - 332,528 - 23,044 - 309,484

    Tweak Buffer Size - 500
    Searches are returned in 1~2 seconds, New Music almost instant, New Albums 2 seconds.
    Memory Usage
    SQUEEZ~3.EXE 0 - 435,664 - 450,940 - 23,044 - 427,896

    Restarted LMS with Tweak Buffer set to 500, took almost 3 minutes, however search albums for "the girl" almost instant. Other searches, New Music, New Albums and memory useage are as above.

    Back to the farm for a few days then down the coast for more testing.

    These results show the great work already done in optimising LMS 7.8 , and I think that a cut down version of this plugin with just the memory slider should be included in LMS as a Settings / Performance function. It also shows me that RAMDisk is no longer needed for Windows.
    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

  7. #27
    Senior Member
    Join Date
    Nov 2010
    Location
    Hertfordshire, UK
    Posts
    2,969
    Quote Originally Posted by Wirrunna View Post
    These results show the great work already done in optimising LMS 7.8 , and I think that a cut down version of this plugin with just the memory slider should be included in LMS as a Settings / Performance function. It also shows me that RAMDisk is no longer needed for Windows.
    But please also keep the vacuum options for those of us for whom it also makes a really big difference
    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.

  8. #28
    Senior Member
    Join Date
    Jan 2006
    Location
    South Coast, NSW, Australia
    Posts
    1,255
    Quote Originally Posted by PasTim View Post
    But please also keep the vacuum options for those of us for whom it also makes a really big difference
    I stand corrected.

    OK, I have removed the RAMDisk and the system has returned the 2GB to the memory pool. Also moved cache back to C:\.....cache on the SSD.
    Have run lots of searches, browses and lookups, nothing hangs like it used to a year or so ago. Checked memory with the Win 7 Resource Monitor -

    SQUEEZ~3.EXE 0 630,704 643,820 23,052 620,768

    So compared to memory used when Tweak Buffer is 20, 500 will allow the process to expand its memory use, but with 8GB there is still 5GB available.

    Edit: Starting LMS still takes about 2mins 30secs.
    Last edited by Wirrunna; 2014-05-06 at 02:00. Reason: Added start up timing.
    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

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

    Plugin to test database tweaks

    > Just to confirm, Custom Scan will delete all data and re-create it every
    > time you rescan.
    > If there are more than 40000 rows for the Custom Tag/Mixed Tag module in
    > the customscan_track_attributes table, it will instead drop the table
    > and recreate it, since DELETE is too slow when the table gets large.


    Are you storing your tables in library.db, too?

    I was wondering about the dropping of tables (or truncate), too. But
    Andy left that note:

    -- Use DELETE instead of TRUNCATE, as TRUNCATE seems to need unlocked
    tables.

    I'd expect a drop table to be as restrictive at least. Did you never
    encounter any problems? Because yes, delete is slow.

    > Custom Tag module should for each track do a:


    Just a few comments (and keep in mind I'm no SQL expert at all, only
    just read quite a few performance FAQs :-)):

    > SELECT id,url from tracks where audio=1 limit 1 offset ?


    Wouldn't it be faster to create a statement handler with this query
    (without the limit & offset) and iterate it?

    > SELECT
    > tracks.id,
    > tracks.url,
    > case when tracks.musicbrainz_id like '%-%' then tracks.musicbrainz_id else null end,
    > 'mixedtag',


    (See - I didn't even know such a thing as a case within a select was
    available!..)

    > ...
    > from tracks,contributor_track,contributors
    > where
    > tracks.audio=1 and
    > tracks.id=contributor_track.track and
    > contributor_track.role=1 and
    > contributor_track.contributor=contributors.id


    Could you change the order of the where statements? If I understood the
    FAQs correctly (http://www.sqlite.org/optoverview.html), only the first
    statement's index would be used to narrow down the selection of records.
    In your case that's the "audio=1", which would probably still return
    >>90% of all tracks. If you used the tracks.id first, that might

    probable speed up the processing?

    But that's rather OT.

    > If auto vacuum
    > works, I suspect it will probably be the biggest improvement related to
    > Custom Scan related performance.


    TBH: I still don't understand why vacuum would have such a large impact,
    as I'd expect the DB to be smart enough to only read those parts of the
    data which it requires. Thus the file _size_ shouldn't matter, should
    it? A full vacuum might help re-arrange things to keep them together.
    But audo_vacumm does not.

    --

    Michael

  10. #30
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,032
    Quote Originally Posted by Wirrunna View Post
    Edit: Starting LMS still takes about 2mins 30secs.
    Are you using any of my plugins ?

    I'm just asking since I know that some of them run heavy database queries at server startup.
    Erland Isaksson (My homepage)
    Lead platform developer of ickStream Music Platform - A world of music at your fingertips

    (Also developer of many plugins/applets)

Posting Permissions

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