indexes, scanning and the NSLU

[roku2006]

So I’ve got a medium sized music library (~40gigs) and I’m running on an NSLU. As mt-daapd scans this library to build the DB, it trucks along decently for a while. Eventually however the db select statements in the scan start taking forever. This is as the database itself begins to exceed the memory size. So not only is the DB search linear, but we’re swapping to do it as well. A single select stretches out into 30 seconds or more. And we’re doing one select per song! UGLY.

In looking at the code I noticed that the index on the “path” column is not added until the end of all the scanning. I realize there is some “insert” pain for an index, but in this case I can’t imagine that it would exceed the pain of a non-indexed select. Perhaps making this some sort of startup option, such that people with small memory can take the index hit to prevent swapping on the search.

Thoughts ?

[rpedde]

@roku2006 wrote:

In looking at the code I noticed that the index on the “path” column is not added until the end of all the scanning. I realize there is some “insert” pain for an index, but in this case I can’t imagine that it would exceed the pain of a non-indexed select. Perhaps making this some sort of startup option, such that people with small memory can take the index hit to prevent swapping on the search.

There are two code paths, one for a “from scratch” scan, when the db is empty, and the other for when there are already songs in the db. When there are no songs in the db, I don’t build the index until it is scanned, but it shouldn’t matter, as selects should return not found immediately, without querying the db.

Actually, it sounds like you lost your indexes, because if you are doing an incremental scan it should already have its indexes.

Do a mt-daapd -r to force a new full scan, and it should rebuild indexes fine. I’m going to add defensive code to make sure that the indexes already exist when doing an incremental scan, but that will probably help your startup time.

— Ron

[roku2006]

Hi Ron:
Thanks for the quick reply. _I_ think this is a new scan, since I deleted the songs.db file before starting. Watching -d 9, the code appears to do a select on the “path” column before doing an insert. Perhaps the code thinks it’s doing an incremental scan, and that is why the select is there? ( I’m guessing that a new scan would assume that a particular path is not already stored, and therefore not need a select ). Anyway I’m pretty certain I’m in a weird state where it’s doing selects, but the index is NOT in place.

I’ll let it run for another day or so, and, if my patience runs out, I’ll try the -r option.

Thanks again!

[fizze]

watching with -d9 on a NSLU2 does lead to various side-effects.
If you also (like me) do have a swap-file on an NTFS mounted drive (slow), where the songs and .db are as well (slooow), and digest the -d9 output via ssh (did I mention its slow ?) then you start to get weird, very weird log output.

This is the Heisenberg principle applied to debugging 😉

[Author]

Posts