SQLite WAL in 7.6.1 - please test

[erland]

I've only run a quick test yet on my Linux setup but so far I seem to get the right number of tracks, albums and artists.

However, it had a big negative impact on the performance in my 50 000 track test library when doing an initial scan with empty Cache directory:
r33019: Scanning new files phase: 12 minutes
r33020: Scanning new files phase: 22 minutes

I'll run more tests tomorrow and later in the week to make sure this is repeatable and wasn't just caused by something temporary in my virtual test environment.

Of course, even at 22 minutes it's still faster than MySQL which took about 34 minutes when I tried it earlier today, so if it does the right thing it might be acceptable to loose a bit of performance of full rescans.

[dsdreamer]

Operating system: Linux - EN - utf8
Platform Architecture: armv5tel-linux
Perl Version: 5.10.0 - armv5tel-linux-thread-multi
Database Version: DBD::SQLite 1.34_01 (sqlite 3.7.7.1)
Total Players Recognized: 1

On an old and slow ARM Linux NAS box, I have 7,898 tracks which were first-time scanned in 27 minutes instead of the previous 24 minutes.

Look for new and changed music ran much as before, and I was able to browse the music library as usual.

I verified the presence of squeezebox-persistent.db-wal, squeezebox.db-wal and ArtworkCache.db-wal in my Cache directory.

[erland]

I've only run a quick test yet on my Linux setup but so far I seem to get the right number of tracks, albums and artists.

However, it had a big negative impact on the performance in my 50 000 track test library when doing an initial scan with empty Cache directory:
r33019: Scanning new files phase: 12 minutes
r33020: Scanning new files phase: 22 minutes

I'll run more tests tomorrow and later in the week to make sure this is repeatable and wasn't just caused by something temporary in my virtual test environment.

Just for information, I made another test run with the 50 000 tracks test library and r33020 with WAL activated is consistently a lot slower than the old solution during a full rescan in my setup, still faster than MySQL though.

[DrLovegrove]

On 8 August 2011 23:04, JJZolx
<JJZolx.4y693z1312841161 (AT) no-mx (DOT) forums.slimdevices.com> wrote:
>
> Is it working for anyone on Windows?

I cant get it working on windows either..

The initially produced 71k squeezebox.db loads fine into SQlite
Manager and does indeed have the WAL set as the journal mode.

But... A few seconds later, when it starts getting written to, SQlite
Manager reckons the db file's corrupt and no matter what I do I cant
open it.

The ArtworkCache.db and squeezebox-persistent.db are all fine..

After stopping, clearing, starting, scanning and waiting:

Directory of C:\ProgramData\Squeezebox\Cache
09/08/2011 11:29 986,112 ArtworkCache.db
09/08/2011 11:29 1,166 extrastrings.json
09/08/2011 11:29 26,858 plugin-data.yaml
09/08/2011 11:29 290,816 squeezebox-persistent.db
09/08/2011 11:29 91,136 squeezebox.db
09/08/2011 11:33 32,768 squeezebox.db-shm
09/08/2011 11:29 0 squeezebox.db-wal
09/08/2011 11:29 32,768 SQUEEZ~1.DB-shm
09/08/2011 11:29 353,208 SQUEEZ~1.DB-wal
09/08/2011 11:29 96,694 strings.586.bin

Hope that helps,

--
- Dr Lovegrove
http://www.rusticrhino.com/drlovegrove
http://groups.google.com/group/moosenews

[slate]

Slower here as well, 0:45 now 1:16

And it appears that I lost half my library

New Library Statistics

Total Tracks: 4,518
Total Albums: 514
Total Artists: 265
Total Genres: 50
Total Playing Time: 347:45:14

It used to be (33011):

Total Tracks: 9,018
Total Albums: 814
Total Artists: 397
Total Genres: 59
Total Playing Time: 679:04:10

and scanning reports the right numbers

Discovering files: E:\music (10542 of 10542) Complete 00:00:09
Scanning new files: E:\music (9018 of 9018) Complete 00:00:57
Discovering playlists: C:\Users\jfm\Music\Playlists (1 of 1) Complete 00:00:00
Pre-caching Artwork (453 of 453) Complete 00:00:10

The server has finished scanning your music collection.
Total Time: 00:01:16 (Tuesday, August 9, 2011 / 12:56 PM)

Nothing in the logfiles

Edit: using windows

[mario21]

Unfortunately, the scanner (7.6.1 v.33022 for readyNas Pro) sometimes scanned twice the same song. I have 25 451 songs in my musical library (iTunes), but with this scan, 32 632 is appearing on my Squeezebox server...

[pallfreeman]

r33022+perl 5.12+FreeBSD 8.2 is running fine for me. I zapped the old db files before starting a full scan, and I can browse and play stuff even though it's scanned less than 10% of my collection so far.

No point in posting a timing comparison; I'm scanning an old backup drive.

I'm still not seeing any "the server is still scanning your collection" notification. Should I put in a bug report for that?

[andyg]

It's OK if performance is a bit less than before, I'm not so worried about that as long as it scans correctly. Turning off exclusive locking is probably the main reason for this, I may see if it can be re-enabled later.

Some people are reporting missing around half their files. This was a bug I also ran into while developing but I thought I had fixed it. If you have this problem, can you try moving your existing squeezebox-persistent.* files out of the way and see if the problem goes away with a full scan?

I don't think it matters on Windows if the files show up as 8.3 filenames or not. SQLite has internal Win32 code that should take care of this sort of thing.

Note that if you're using a SQLite viewer app that it will need to be new enough to support WAL mode, make sure you have the latest version.

[verypsb]

Well, after I deleted the squeezebox-persistent.* files, it doesn't rebuild the database at all... 0 tracks 0 albums 0 artists. The scanner detects all files and then quits...

[DrLovegrove]

On 9 August 2011 16:31, andyg
<andyg.4y7ljb1312903921 (AT) no-mx (DOT) forums.slimdevices.com> wrote:
>
> I don't think it matters on Windows if the files show up as 8.3
> filenames or not. SQLite has internal Win32 code that should take care
> of this sort of thing.
>
> Note that if you're using a SQLite viewer app that it will need to be
> new enough to support WAL mode, make sure you have the latest version.

There is something up on windows though and it consistently creates a
duff .db file on my safe-mode setup with just 1 album..
The server reckons no library's been set once it's done it too.

I use this guy in Firefox for browsing db's:
http://code.google.com/p/sqlite-manager/
It's based on 3.7.5, so should be fine with WAL.
The artwork and persistent db's show up with WAL mode ticked, as does
squeezebox.db for the 1st couple of seconds.. After that it corrupts..

I've gone back to yesterday's nightly and all is well again - the
journal mode's back at 'delete' etc, so it's definitely this new stuff
causing the problem..

Be great if you could get WAL mode working.. Moose's new SQLite
scanner should (in theory) already support it too. :-)

--
- Dr Lovegrove
http://www.rusticrhino.com/drlovegrove
http://groups.google.com/group/moosenews