Announcement

Collapse
No announcement yet.

Plugin to test database tweaks

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    #16
    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.

    Comment


      #17
      "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

      "It doesn't work - what shall I do?" - "Please check your server.log and/or scanner.log file!"
      (LMS: Settings/Information)

      Comment


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

        Comment


          #19
          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:
          [email protected]:# sqlite3 /var/lib/squeezeboxserver/cache/library.db "PRAGMA auto_vacuum;"
          1

          Comment


            #20
            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 ;-)
            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?

            Comment


              #21
              Plugin to test database tweaks

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

              Full.

              --

              Michael
              Michael

              "It doesn't work - what shall I do?" - "Please check your server.log and/or scanner.log file!"
              (LMS: Settings/Information)

              Comment


                #22
                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
                Michael

                "It doesn't work - what shall I do?" - "Please check your server.log and/or scanner.log file!"
                (LMS: Settings/Information)

                Comment


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

                  Comment


                    #24
                    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 8.1 on PC, Xubuntu 20.04, FLACs 16->24 bit, 44.1->192kbps. 2 Touches & 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 20.04 laptop firefox/upplay or Android mobile with Squeeze-Ctrl/BubbleUPnP controls LMS/Minimserver.

                    Comment


                      #25
                      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*

                      Comment


                        #26
                        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.

                        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 IGNORE 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 Lindmark (My homepage)
                        Developer of many plugins/applets
                        Starting with LMS 8.0 I no longer support my plugins/applets (see here for more information )

                        Comment


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

                          Comment


                            #28
                            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 8.1 on PC, Xubuntu 20.04, FLACs 16->24 bit, 44.1->192kbps. 2 Touches & 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 20.04 laptop firefox/upplay or Android mobile with Squeeze-Ctrl/BubbleUPnP controls LMS/Minimserver.

                            Comment


                              #29
                              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, 11: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

                              Comment


                                #30
                                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
                                Michael

                                "It doesn't work - what shall I do?" - "Please check your server.log and/or scanner.log file!"
                                (LMS: Settings/Information)

                                Comment

                                Working...
                                X