Home of the Squeezebox™ & Transporter® network music players.
Results 1 to 7 of 7
  1. #1
    Senior Member cparker's Avatar
    Join Date
    Oct 2005
    Location
    Hampshire, United Kingdom
    Posts
    594

    Why is LMS using such an old SQLite version?

    Hi

    Is there any reason why LMS is using such an old SQLite version, 3.7.7.1 which dates back to 2011.

    Looking through the SQLite release log there seem to be pretty impressive performance gains made over the years so just wondering why this hasn't been rolled into LMS. (DBD-SQLite-1.58 released March 2018)

    Cheers

    PS Can we have the old forum software back.. thanks (scrub that I found the link at the bottom.. phew )
    www.spicefly.com - ** Spicefly SugarCube ** - A hassle free acoustic journey through your music library using MusicIP. Plus the finest MusicIP installation guides, enhanced MIP Interface and SpyGlass MIP the Windows Automated MusicIP Headless Installer.

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

    Why is LMS using such an old SQLite version?

    > Is there any reason why LMS is using such an old SQLite version, 3.7.7.1
    > which dates back to 2011.


    There are many potential reasons. Call it laziness, or "never touch a
    running system", or lack of priorities, premature optimization, lack of
    time...

    Updating the library for all platforms is virtually impossible. Dealing
    with the potential issues of running different versions on different
    platforms might lead to some unwanted decisions (eg. rollback, drop
    support for some platforms etc.) and certainly would consume
    considerable amounts of time. Which probably would better be spent
    elsewhere.

    Maybe there just isn't any compelling reason to update.

    > Looking through the SQLite release log there seem to be pretty
    > impressive performance gains made over the years so just wondering why
    > this hasn't been rolled into LMS. (DBD-SQLite-1.58 released March
    > 2018)


    Did you carefully read where those improvements would apply? Does it
    apply to LMS? Did you confirm it's any faster at all? And where do you
    see the database as the bottleneck, btw?

    I'd be happy to see a thorough comparison of current LMS and LMS on
    latest SQLite, scanning libraries of various sizes (5k, 30k, 100k) on
    various platforms (Pi, Mac, Windows, desktop Linux). Or at least one of
    them showing that "impressive performance gain". What would you measure?
    Scanning time? Browsing performance? Time to playback?

    > PS Can we have the old forum software back.. thanks


    Can it get any older?... it's vbulletin 4, announced in 2009. Its
    successor v5 was announced in 2012. We never updated beyond security
    updates.

    --

    Michael

  3. #3
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,032
    Ok, I thought I'd give it the quickest try I could, running a test on my dev machine (2017 MBP, SSD, 16GB RAM). Here are the results:

    DBD::SQLite 1.58 (sqlite 3.22.0)

    Discovering files/directories: /Users/mh/Documents/SqueezeZeugs/flac100000 (107993 of 107993) Complete 00:00:19
    Scanning new music files: /Users/mh/Documents/SqueezeZeugs/flac100000 (99663 of 99663) Complete 00:06:01
    Building full text index (7 of 7) Complete 00:05:30
    Create library views (2 of 2) Complete 00:00:00
    Database Optimize (2 of 2) Complete 00:00:08

    The server has finished scanning your media library.
    Total Time: 00:11:58 (Thursday, August 9, 2018 / 2:57 PM)

    Discovering files/directories: /Users/mh/Documents/SqueezeZeugs/flac100000 (107993 of 107993) Complete 00:00:17
    Scanning new music files: /Users/mh/Documents/SqueezeZeugs/flac100000 (99663 of 99663) Complete 00:06:11
    Building full text index (7 of 7) Complete 00:05:35
    Create library views (2 of 2) Complete 00:00:00
    Database Optimize (2 of 2) Complete 00:00:08

    The server has finished scanning your media library.
    Total Time: 00:12:11 (Thursday, August 9, 2018 / 3:34 PM)


    DBD::SQLite 1.34_01 (sqlite 3.7.7.1)

    Discovering files/directories: /Users/mh/Documents/SqueezeZeugs/flac100000 (107993 of 107993) Complete 00:00:20
    Scanning new music files: /Users/mh/Documents/SqueezeZeugs/flac100000 (99663 of 99663) Complete 00:08:43
    Building full text index (7 of 7) Complete 00:00:21
    Create library views (2 of 2) Complete 00:00:00
    Database Optimize (2 of 2) Complete 00:00:15

    The server has finished scanning your media library.
    Total Time: 00:09:39 (Thursday, August 9, 2018 / 3:08 PM)

    Discovering files/directories: /Users/mh/Documents/SqueezeZeugs/flac100000 (107993 of 107993) Complete 00:00:19
    Scanning new music files: /Users/mh/Documents/SqueezeZeugs/flac100000 (99663 of 99663) Complete 00:08:55
    Building full text index (7 of 7) Complete 00:00:30
    Create library views (2 of 2) Complete 00:00:01
    Database Optimize (2 of 2) Complete 00:00:17

    The server has finished scanning your media library.
    Total Time: 00:10:02 (Thursday, August 9, 2018 / 3:20 PM)


    There clearly is an improvement in some parts of the newer code, but unfortunately a massive loss in another part. Now you could say I don't use FTS. But I do.

    That might show you that a decision pro or con can't easily be taken. Things are a bit more complicated than they might seem.
    Michael

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

  4. #4
    Senior Member cparker's Avatar
    Join Date
    Oct 2005
    Location
    Hampshire, United Kingdom
    Posts
    594
    Thanks for the research. Long story, short.. my interest is due to a low powered wintel system grinding to a stop when doing a full rescan due to Trackstat. So I was looking at the db and noticed the old version and hence the interest. Is there anyway of decompressing/patching a wintel system to play around with this further?

    Cheers
    www.spicefly.com - ** Spicefly SugarCube ** - A hassle free acoustic journey through your music library using MusicIP. Plus the finest MusicIP installation guides, enhanced MIP Interface and SpyGlass MIP the Windows Automated MusicIP Headless Installer.

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

    Why is LMS using such an old SQLite version?

    > Thanks for the research. Long story, short.. my interest is due to a
    > low powered wintel system grinding to a stop when doing a full rescan
    > due to Trackstat. So I was looking at the db and noticed the old
    > version and hence the interest. Is there anyway of
    > decompressing/patching a wintel system to play around with this further?


    You can run LMS from the source if you're willing to install Activestate
    Perl 5.14 (which I could provide you a copy if needed).

    I guess you've already upped the memory parameter for the database? It
    can make a huge difference (depending on library size, storage type etc.).

    How large is your library?
    What is the bottleneck? Memory? CPU? Disk I/O? If you run out of memory,
    then any other optimization might be irrelevant.
    Do you know what kind of query it is that plugin is chewing so badly on?

    --

    Michael

  6. #6
    Senior Member cparker's Avatar
    Join Date
    Oct 2005
    Location
    Hampshire, United Kingdom
    Posts
    594
    Quote Originally Posted by mherger View Post
    > Thanks for the research. Long story, short.. my interest is due to a
    > low powered wintel system grinding to a stop when doing a full rescan
    > due to Trackstat. So I was looking at the db and noticed the old
    > version and hence the interest. Is there anyway of
    > decompressing/patching a wintel system to play around with this further?


    You can run LMS from the source if you're willing to install Activestate
    Perl 5.14 (which I could provide you a copy if needed).

    I guess you've already upped the memory parameter for the database? It
    can make a huge difference (depending on library size, storage type etc.).

    How large is your library?
    What is the bottleneck? Memory? CPU? Disk I/O? If you run out of memory,
    then any other optimization might be irrelevant.
    Do you know what kind of query it is that plugin is chewing so badly on?

    --

    Michael
    hi Michael

    That would be great if you could supply me the source.

    My library is 30k tracks (on a NAS) and the bottleneck is I/O for sure, the SATA died and wiped out the disks, so I rebuilt it with Win7 booting from a USB drive. It has 8x (i7) CPU cores and 16Gb RAM so is strong enough. I can't change the OS due to other software, it works fine day to day but definitely chewing on the db rebuild when Trackstat does a massive update query after a full rescan. So I would like to see if I can play around with the database side of things and happy to share anything useful I find.

    Thanks in advance
    www.spicefly.com - ** Spicefly SugarCube ** - A hassle free acoustic journey through your music library using MusicIP. Plus the finest MusicIP installation guides, enhanced MIP Interface and SpyGlass MIP the Windows Automated MusicIP Headless Installer.

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

    Why is LMS using such an old SQLite version?

    > My library is 30k tracks (on a NAS) and the bottleneck is I/O for sure,
    > the SATA died and wiped out the disks, so I rebuilt it with Win7 booting
    > from a USB drive.


    You're running LMS from a different computer than where the files are
    saved? This kind of I/O might be the bottleneck during the scan. But I
    could imagine that when TrackStat is doing its job, it's LMS server side
    only, no access to the files. I might be wrong. But please double check,
    because your machine seems more than powerful enough to process 30k
    tracks. Check the resource monitor while TrackStat is running. Monitor
    disk, network, cpu and memory usage.

    > It has 8x (i7) CPU cores and 16Gb RAM so is strong


    Did you check LMS' memory settings for the database?

    --

    Michael

Tags for this Thread

Posting Permissions

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