Home of the Squeezebox™ & Transporter® network music players.
Page 1 of 2 12 LastLast
Results 1 to 10 of 11
  1. #1
    Senior Member gharris999's Avatar
    Join Date
    Apr 2005
    Location
    Santa Fe, NM
    Posts
    3,493

    Ver 7.9 Database Optimization Questions

    I'm noticing that when adding a single new album to my library, the whole "scan for new" process takes 1'19" on my server. Of that, 1'11" is taken up by the Database Optimize process, i.e. it's the most expensive process.

    What exactly happens during the database optimization phase of scanning?

    Does "Database Optimize" have to be performed at every scan? Would there be a way to limit the database optimize phase to just once per day and / or only after multiple albums (e.g. >20) have been added?

  2. #2
    Senior Member
    Join Date
    Apr 2013
    Location
    UK
    Posts
    1,100
    You can search for and start playing the new content before the optimise finishes, so personally I've never cared about this.


    Transcoded from Matt's brain by Tapatalk
    --
    Hardware: 3x Touch, 1x Radio, 2x Receivers, 1 HP Microserver NAS with Debian+LMS 7.9.0
    Music: ~1300 CDs, as 450 GB of 16/44k FLACs. No less than 3x 24/44k albums..

  3. #3
    Senior Member gharris999's Avatar
    Join Date
    Apr 2005
    Location
    Santa Fe, NM
    Posts
    3,493
    I run a custom SQL modification query after scans complete in order to to fix-up genre names. So, yes, I do care about this.

  4. #4
    Senior Member DJanGo's Avatar
    Join Date
    Sep 2005
    Location
    Germany
    Posts
    2,524
    Quote Originally Posted by gharris999 View Post
    I run a custom SQL modification query after scans complete in order to to fix-up genre names. So, yes, I do care about this.
    [OT]

    i have a debian /linux script that uses a commandline mp3tagger "eyed3" and the sqlite tables from lms that allows me to change genres eg from Synth-Pop to Synthpop.
    It only changes a single part eg. org Genre was "Synth-Pop;Top40" new Genre is "Synthpop;Top40". I am using ";" as delimiter...

  5. #5
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    19,872

    Ver 7.9 Database Optimization Questions

    > What exactly happens during the database optimization phase of scanning?

    It does run a "vacuum" on the database:
    https://sqlite.org/lang_vacuum.html

    > Does "Database Optimize" have to be performed at every scan? Would


    No. It's an optimization, not a requirement.

    > there be a way to limit the database optimize phase to just once per day
    > and / or only after multiple albums (e.g. >20) have been added?


    Most people don't run scans multiple times per day and just don't care.
    Or it doesn't take that long as for you (11s for me, 21k tracks, oldish
    Intel Atom 330).

    But feel free to add a few lines or disable the step in SQLiteHelper.pm:

    https://github.com/Logitech/slimserv...Helper.pm#L318

    If you look at sub vacuum, you'd see that it would accept an "optional"
    parameter:

    https://github.com/Logitech/slimserv...Helper.pm#L509

    By setting this the vacuum would only be run if there was a certain
    level of fragmentation. Setting that second parameter in the call to
    ->vacuum() around line 320 might be all you need to do.

    --

    Michael

  6. #6
    Senior Member DJanGo's Avatar
    Join Date
    Sep 2005
    Location
    Germany
    Posts
    2,524
    Quote Originally Posted by mherger View Post
    > What exactly happens during the database optimization phase of scanning?

    It does run a "vacuum" on the database:
    --

    Michael
    Hi Michael,

    my maintenance script (triggered by my rescan script) always vacuum the databasefiles and thats the output...

    start : Mo 11. Dez 18:18:44 CET 2017
    -rw-r--r-- 1 squeezeboxserver nogroup 177694720 Dez 11 18:18 artwork.db
    -rw-r--r-- 1 squeezeboxserver nogroup 259568640 Dez 11 18:17 cache.db
    -rw-r--r-- 1 squeezeboxserver nogroup 215212032 Dez 11 18:18 imgproxy.db
    -rw-r--r-- 1 squeezeboxserver nogroup 211119104 Dez 11 18:18 library.db
    -rw-r--r-- 1 squeezeboxserver nogroup 45911040 Dez 11 18:18 persist.db
    end :Mo 11. Dez 18:19:48 CET 2017
    -rw-r--r-- 1 squeezeboxserver nogroup 177580032 Dez 11 18:19 artwork.db
    -rw-r--r-- 1 squeezeboxserver nogroup 259453952 Dez 11 18:19 cache.db
    -rw-r--r-- 1 squeezeboxserver nogroup 215136256 Dez 11 18:19 imgproxy.db
    -rw-r--r-- 1 squeezeboxserver nogroup 206803968 Dez 11 18:19 library.db
    -rw-r--r-- 1 squeezeboxserver nogroup 45006848 Dez 11 18:19 persist.db
    Thats the part of the maintenance script:
    Code:
    service logitechmediaserver stop
    cd /var/lib/squeezeboxserver/cache
    ls -l *.db>>/tmp/maintenance.log
    for file in $(find -iname '*.db' | sort)
        do
        echo "processing $file"
        sudo sqlite3 $file "vacuum";
        sudo chown squeezeboxserver:nogroup $file
    done
    service logitechmediaserver start
    echo "end :$(date)">>/tmp/maintenance.log
    ls -l *.db>>/tmp/maintenance.log
    If the rescan also does a vacuum i wonder why there is such a difference in the filesize?

  7. #7
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    19,872

    Ver 7.9 Database Optimization Questions

    > If the rescan also does a vacuum i wonder why there is such a difference
    > in the filesize?


    I can't say. I can only guess. Eg. that the scanner (which is running
    the vacuum) is not the only application accessing the database, thus
    there might be some data written by the server after the vacuum
    happened. But then the differences you achieve with your additional
    vacuum run are neglectable, aren't they? Like in 0.0x%...

    --

    Michael

  8. #8
    Senior Member
    Join Date
    Aug 2012
    Location
    Austria
    Posts
    721
    Quote Originally Posted by mherger View Post
    > What exactly happens during the database optimization phase of scanning?

    It does run a "vacuum" on the database:
    If I understand the code correctly, a SQL ANALYZE is executed as well (every 100 tracks).
    Not sure how long this takes, but according to the docs, a full table scan will be done, so this may take some time if the DB is large,,,
    Setting logging to info should show the relevant entries.

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

    Ver 7.9 Database Optimization Questions

    >>> What exactly happens during the database optimization phase of
    >> scanning?
    >>
    >> It does run a "vacuum" on the database:
    >>

    > If I understand the code correctly, a SQL ANALYZE is executed as well


    Ah, correct. That's actually taking much longer than the vacuum, iirc.

    > (every 100 tracks).


    How so? No, the analyze is run all at the end, in one go.

    --

    Michael

  10. #10
    Senior Member
    Join Date
    Aug 2012
    Location
    Austria
    Posts
    721
    Quote Originally Posted by mherger View Post
    > (every 100 tracks)

    How so? No, the analyze is run all at the end, in one go.
    What I meant is that analyze is run at the end only if >100 changed tracks have accumulated since the last analyze.
    So one could increase OPTIMIZE_THRESHOLD to reduce the number of times analyze is run

Posting Permissions

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