Home of the Squeezebox™ & Transporter® network music players.
Page 1 of 3 123 LastLast
Results 1 to 10 of 29
  1. #1
    Member
    Join Date
    Oct 2009
    Location
    Switzerland
    Posts
    33

    Cool Performance of SQLite and large databases

    Hi all

    I've been a user of the Squeezebox for 8 years now, Iím a silent reader of the forum. I have all models except the Slimp and Squeezebox 1 in operation. My database is very large and includes over 300,000 tracks. It runs stable and ok on a dedicated dual Pentium D with 3.0 GHz and 1 Giga memory .
    I am always cautious with updates. I am running 7.5.4, use the scanner.exe from 7.5.2 due to crashes and I'll only update in a few weeks when 7.6.x goes stable.

    I worry about SQLite. Does anyone have experience with such large databases with SQLite? In the beta forum Iíve read somewhere that with very large databases SQLite may run unexpectedly slow and possibly even slower than MySQL? Thank you for your answers.

    Sincerely
    Daniel

  2. #2
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,307
    Quote Originally Posted by Bronx View Post
    Hi all

    I've been a user of the Squeezebox for 8 years now, Iím a silent reader of the forum. I have all models except the Slimp and Squeezebox 1 in operation. My database is very large and includes over 300,000 tracks. It runs stable and ok on a dedicated dual Pentium D with 3.0 GHz and 1 Giga memory .
    I am always cautious with updates. I am running 7.5.4, use the scanner.exe from 7.5.2 due to crashes and I'll only update in a few weeks when 7.6.x goes stable.
    l
    The scanner crashes are probably those that have been fixed in 7.5.6 nightly.

    Quote Originally Posted by Bronx View Post
    I worry about SQLite. Does anyone have experience with such large databases with SQLite? In the beta forum Iíve read somewhere that with very large databases SQLite may run unexpectedly slow and possibly even slower than MySQL? Thank you for your answers.
    Nobody has actually been able to verify it's slower with real testing as far as I know, except from the issue some people currently have with the 7.6 scanner the 7.6 release seems to be generally faster than 7.5.
    So I wouldn't worry to much about this, SQLite is a lot better today than it was five years ago.
    Erland Isaksson (My homepage)
    Developer of many plugins/applets
    Starting with LMS 8.0 I no longer support my plugins/applets (see here for more information )

  3. #3
    Senior Member
    Join Date
    May 2009
    Location
    Denmark (Struer)
    Posts
    854
    pallfreeman got 200k http://forums.slimdevices.com/showthread.php?t=89205

    For me the 7.6.x fullscan times are 4 times faster than 7.5.x.

    If you are cautious then wait for 7.6.1 to be released.

    Would you have problems if you tried 7.6.1 today?! you might:
    - if you use cue sheets
    - use unc paths
    - your tags are a mess
    - have plenty of playlists; some of which are not updated
    -

    anyway when you do try pleas get back here and give some information about your library, hardware and scan times before and after
    Main: Receiver (Audiocom) -> Beresford Caiman+ (Gatorized) -> Carver A-500x -> B&W 704
    Office: Receiver -> Luxman L-210 -> Stax SR84 Pro
    -> Beresford Bushmaster -> Superlux HD668B
    Server: A8-5500, 4 GB, SSD+ 2*1 TB, Win8 w. SBS 7.9.0 (SQLite w. High Mem)
    Tied together by D-Link DIR-655 + DGS-1008D

  4. #4
    Member
    Join Date
    Oct 2009
    Location
    Switzerland
    Posts
    33

    Cool Thank you

    Wow, Erland, Slate thanx for the quick response. I'll surely will report back when I dare to use 7.6.x in some days.
    Daniel

  5. #5
    Senior Member
    Join Date
    Jan 2006
    Location
    South Coast, NSW, Australia
    Posts
    1,298
    Bronx, add another Gb of RAM, it's very cheap. Then set the the new advanced performance option in 7.6.x to use extra memory.
    http://forums.slimdevices.com/showth...t=88904&page=3 shows scanning performance of one of my boxes, a mini-itx with a low power dual core AMD X2 235E @ 2.7Ghz, 4Gb RAM and the library on Samsung 5400RPM Eco-Green HD204 2Tb drives, the system on a 500Gb WD Blue 2 1/2 drive, Win XP.
    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

  6. #6
    Senior Member Philip Meyer's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    5,596

    Performance of SQLite and large databases

    >I worry about SQLite. Does anyone have experience with such large
    >databases with SQLite? In the beta forum Iíve read somewhere that with
    >very large databases SQLite may run unexpectedly slow and possibly
    >even slower than MySQL? Thank you for your answers.
    >

    I worried about the move to SQLite too. Not so much scanning time; I had heard initial reports that in general browsing may be slower for large libraries.

    For my library (~40000 tracks - titchy compared to yours!), 7.6.x is only marginally quicker at scanning (about 5 mins), but it's less reliable at the moment.

    But so far, it feels like 7.6.x is a bit faster for browsing, which I am much more interested in than scan time.

    It may not be specifically the transition from MySQL to SQLite that has been the deciding factor in performance gains, but rather a combination of other changes in the code. i.e. refactoring code, optimising the number of queries made, better file scanning, less scan phases to tidy up DB after files have been scanned.

    In my case, I had optimised by own MySQL instance, so it was already faster than the SBS built-in MySQL instance default settings.

    But it would be interesting to hear how performance scales over DB size - whether effect on performance is linear or exponential as the number of tracks increases.

    Please post your findings when you give it a try.

  7. #7
    Senior Member JJZolx's Avatar
    Join Date
    Apr 2005
    Location
    Colorado
    Posts
    11,588
    I'm generating a very large test library right now and will post the results after it's been scanned under both SQLite and MySQL. Maybe sometime tomorrow or Monday.

    It won't be possible to compare the overall scan times of SQLite/7.6 to MySQL/7.6, since a clear & rescan with MySQL in 7.6 hangs during the precaching of artwork. It's possible to have the artwork precached afterward, however, by running a new & changed scan.

  8. #8
    Senior Member JJZolx's Avatar
    Join Date
    Apr 2005
    Location
    Colorado
    Posts
    11,588
    Ok, the new library has been generated: 1,000,000 tracks (Why mess around, right?)

    I'm scanning it now with a 7.6.1 server running SQLite. I'm not certain, but already I may be seeing something. Discovery of the 1M files took just 9:28. Scanning the first 25k tracks took about 10 minutes. Now, at 50k tracks, the time for the scanning new files pass is over 42 minutes. Much slower per track than for my 33k track library, which takes less than 12 minutes total to scan.

    If it keeps degrading like this as the database grows larger, it's going to take a _very_ long time to scan the whole library. Maybe longer than I have the patience for.

    It will be interesting to see how well SBS with MySQL handles scanning the same library. I may also have to scale back the test to something like 250k tracks.

  9. #9
    Senior Member bluegaspode's Avatar
    Join Date
    Jul 2009
    Location
    Berlin, Germany
    Posts
    3,229
    Does anyone know if an analyze tables is done after inserting each 50000 tracks (or different count)
    Every major database needs this if there are bulk inserts and reads happening at the same time (which I expect the scanner to does?)
    Did you know: SqueezePlayer will stream all your music to your Android device. Take your music everywhere!
    Remote Control + Streaming to your iPad? Squeezebox + iPad = SqueezePad
    Want to see a Weather Forecast on your Radio/Touch/Controller ? => why not try my Weather Forecast Applet
    Want to use the Headphones with your Controller ? => why not try my Headphone Switcher Applet

  10. #10
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,307
    Quote Originally Posted by bluegaspode View Post
    Does anyone know if an analyze tables is done after inserting each 50000 tracks (or different count)
    Every major database needs this if there are bulk inserts and reads happening at the same time (which I expect the scanner to does?)
    As far as I can see it runs ANALYZE(SQLite) or OPTIMIZE TABLE(MySQL) at the end of a full rescan and after every 100 tracks in a new/changed scan.
    Erland Isaksson (My homepage)
    Developer of many plugins/applets
    Starting with LMS 8.0 I no longer support my plugins/applets (see here for more information )

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
  •