NSLU performance

[Anonymous]

It seems to work fine with the full 44k songs on the Mac.

When connected to the Mac it reports that it retrieves 45,710 titles when browsing by title. Not sure I would really want to browse like that. Pressing Play just reboots the Soundbridge 🙂

Normally we listen to Smart Playlists, but do like to fine a particular artist or album/genre

On the NSLU with logging at 5, the log reports
2008-01-21 21:08:16 (00309404): timeout in select
2008-01-21 21:08:17 (00309404): timeout in select
2008-01-21 21:08:17 (00309404): timeout in select
2008-01-21 21:08:18 (00309404): timeout in select
2008-01-21 21:08:19 (00309404): timeout in select
2008-01-21 21:08:20 (00309404): timeout in select

While at idle

With 33k songs loaded on the Slug I can access playlists and search for individual albums/artists etc!!! That may be all the functionality that I need..

Off to test it some more, would be sweet ot get all 45k songs on it….
On a slug!!

[Anonymous]

mmm

The Slug is doing really well, I have it driving 4 Soundbridge units around the house!!

That is with 33k songs

I am only using Smart Playlists and Search, browse isn’t that great when you have scroll through 7,000 artist names anyway.

5This is perfect, I am setting up the Presets for playlists that are appropriate to the room, each room has a predefined set of Presets, and you can always choose form the Playlists.

Simple, power efficient and oh such a small little package.

Now to ramp up to 45k songs

Sweet

[Anonymous]

@crosbie wrote:

I created a new ‘album’ table, using the same schema as ‘songs’. I then did ‘insert into album select * from songs where track = 1’. I now had a table with just one entry per album.

Next I took a hex editor to the mt-daapd binary. I searched for the string ‘distinct album from songs’ and replaced it with ‘select distinct album from album’. There were two occurrences to change.

If there is only one entry per album, couldn’t you remove the “distinct” from your “select distinct album from album” and get even faster results?

Another way you might solve your problem is to open songs.db in sqlite and create an index on album. The syntax would be:
create index idx_album on songs(album); and then undo your hack to permit the select distinct album from songs to run much faster against an index. It probably wouldn’t be quite as fast, but it would always be in sync.

[Anonymous]

@pmorris wrote:

If there is only one entry per album, couldn’t you remove the “distinct” from your “select distinct album from album” and get even faster results?

Another way you might solve your problem is to open songs.db in sqlite and create an index on album. The syntax would be:
create index idx_album on songs(album); and then undo your hack to permit the select distinct album from songs to run much faster against an index. It probably wouldn’t be quite as fast, but it would always be in sync.

Yes probably. On a de-underclocked slug, browse albums is now more or less instant so I left it at that.

Regarding the index on album, I expected that to work, but it didn’t. As discussed earlier in the thread, this eliminates the delay in finding songs by album, but does not seem to help at all on the album list query. I think it should; this is probably a shortcoming of sqlite.

[fizze]

Well, yes and no.

For an index to work you need to re-write the query.
I wasn’t aware that sqlite supports indices until now. (Is that a new thing in sqlite3 – anyone?) I did some comparisions and figured that the SELECT DISTINCT is a lot faster than a SELECT … GROUP BY.

On a proper SQL database, the GROUP BY is the way to go, but the difference is slim to none. On sqlite the performance hit of the GROUP BY is almost 100%. Maybe the index changes that. I’ll try that on the slug later today. 😉

edit:
Ok, here come the sqlite3 impressions:

Verdict N°1:
I think the browse by artist, album etc queries should look like this:

SELECT DISTINCT MIN(ALBUM) FROM songs GROUP BY UPPER(REPLACE (ALBUM,' ',''));

That way, albums like “hello kitty”, “Hello Kitty” and “Hellokitty” would appear as a single entry. The min of those would be displayed, in this case it would be “hello kitty”. It has always nerved me when I had slightly untidy metadata to get multiple artist / album entries.

Unfortunately the performance hit seems to be hideous. Now from this point I cant tell wether the screen output of the sqlite3 client on the slug is actually that slow, or wether its just the ssh terminal.
Either way, the performance seems to be 2-3x worse than compared to the traditional

SELECT DISTINCT artist FROM songs ORDER BY artist;

I used a shell script and date to measure the execution time. Its not rocket science, but it should do the trick.
My slug was far from being idle when I crunched those numbers. So it would be nice if someone (perhaps with a bigger library) could give some numbers as well?
Here’s the script:

#!/bin/bash

echo "This is the old way:"

echo "SELECT DISTINCT $1 FROM songs ORDER BY $1"

time1=`date +%s%N`

sqlite3 /opt/var/mt-daapd/songs3.db "SELECT DISTINCT $1 FROM songs ORDER BY $1" >/dev/null

time2=`date +%s%N`

runtime1=`calc a=$time2 - $time1, a / 10^9`

echo "Execution time: $runtime1 seconds"





echo ""

echo "This is the new way:"

echo "SELECT DISTINCT MIN($1) FROM songs GROUP BY UPPER(REPLACE $1,' ','');"

time3=`date +%s%N`

sqlite3 /opt/var/mt-daapd/songs3.db "SELECT DISTINCT MIN($1) FROM songs GROUP BY UPPER(REPLACE($1,' ',''));" >/

time4=`date +%s%N`

runtime2=`calc a=$time4 - $time3, a / 10^9`

echo "Execution time: $runtime2 seconds"

echo ""

echo "Difference is `calc $runtime2-$runtime1` seconds."

And here’s what I get on songs:

./sqlite_performance.sh album
This is the old way:
SELECT DISTINCT album FROM songs ORDER BY album
Execution time: 1.110705 seconds

This is the new way:
SELECT DISTINCT MIN(album) FROM songs GROUP BY UPPER(REPLACE album,’ ‘,”);
Execution time: 4.777444 seconds

Difference is 3.666739 seconds.

Please note that I did create an index on album and artist, but as this query does a full table scan anyway, only the aggregate (MIN) actually would gain. An index certainly speeds up browsing albums by a single artist though.

PS:
For this to run on the slug you need calc, date and bash, next to sqlite3. Duh. 😉

[Anonymous]

@fizze wrote:

For an index to work you need to re-write the query.

AFAIK you don’t need to re-write a query to gain benefit from an added index. I’ve witnessed this on new Firefly installations. I normally add an index idx_artist on songs(artist,album). If I browse my database (18,000 songs) on my Soundbridge (Browse Artist, then select an artist to see their albums) before adding the above index , it is very slow. After I add the index and browse again, the Albums for an Artist are returned much more quickly.

[fizze]

Yes, that is the nature of an index.
You will see, however that a query like “select artist from songs” will never profit from an index on artist. 😉

Anyway, what just popped into my mind: instead of rewriting all the queries that would be hard on the slug maybe the same effect can be acheived by cleaning up the DB.
This would make sense to be run as a cron-job, presumably after a rescan cronjob.
🙄

[Author]

Posts