PDA

View Full Version : Building Playlists



larrettp
2008-03-24, 13:25
A suggestion on the subject of Playlists. Will there EVER be a method of creating Playlists 'offline' - in other words, without affecting the list you are currently listening to? This would be an obvious function as you don't necessarily want to listen to the list you are creating (for a future party, for instance).

Building Playlists is also VERY time-consuming. I like to have large Playlists shuffled by song (currently about 7500 songs). This takes AGES. Since a Playlist is, to all intents and purposes just a list of filenames, types and locations, why isn't this information stored and indexed when the library is scanned (which takes ages in itself). What is the point of the library scan if the locations are validated again when the songs/albums are added to the Playlist.

Saving a Playlist also takes ages.

In fact these delays are the single biggest complaint of the entire system. When it is working, it is very impressive but it takes far too long to set up.

Any answers?

aubuti
2008-03-24, 14:51
A suggestion on the subject of Playlists. Will there EVER be a method of creating Playlists 'offline' - in other words, without affecting the list you are currently listening to? This would be an obvious function as you don't necessarily want to listen to the list you are creating (for a future party, for instance).
Here's a workaround for you, although it is far from elegant. Open up a SoftSqueeze player, and build the playlist on that while the music you're listening to is playing on your real SB. You could build the playlist using the web ui or using the simulated remote interface for SoftSqueeze.


Building Playlists is also VERY time-consuming. I like to have large Playlists shuffled by song (currently about 7500 songs). This takes AGES. Since a Playlist is, to all intents and purposes just a list of filenames, types and locations, why isn't this information stored and indexed when the library is scanned (which takes ages in itself). What is the point of the library scan if the locations are validated again when the songs/albums are added to the Playlist.

Saving a Playlist also takes ages.

In fact these delays are the single biggest complaint of the entire system. When it is working, it is very impressive but it takes far too long to set up.

Any answers?
Not sure I have answers for you here, except that Random Mix (or Erland's plugins for SQL Playlist and Random Playlist) are *much* more efficient than shuffle play for randomizing your tracks.

I don't get your point about scanning. Playlists are done as part of the scan, and they have to be redone when doing a _complete_ clear and rescan because sometimes people move or rename files, so the scan has to check that info.

I've never found saving playlists to take long, but I've never had playlists of more than ~200 tracks. Are you doing 1000s of tracks? If not, maybe something else isn't quite right with your system.

Finally, consider setting up your playlists in something other than SqueezeCenter, because SC really doesn't have the greatest interface for it. There is a lot of other software that can create SC-readable playlists, like MusicIP, Winamp, etc.

larrettp
2008-03-24, 22:42
Nice idea about using softSqueezze instead but I am creating Playlists of about 7500 tracks. Performance REALLY drops off here.

My point about scanning is that the music library is scanned at the time for names, locations etc. and any new or changed Playlists. Why, then is this information not instantly available (via indexes, possibly) when you come to use them later.

SqueezeCenter is no improvement at all here, in fact it seems worse on the ReadyNas platform - I know it's a beta release at the moment but it isn't offering any solutions to the performance problems.

aubuti
2008-03-25, 06:03
Do you have shuffle mode on? In other words, are you reshuffling those 7500 track playlists every time you load them? If so, then that (i.e., the reshuffling) is what is taking so long -- not re-indexing the tracks. Shuffle is pretty inefficient, and the inefficiency is amplified tremendously when you shuffle huge lists, especially on an underpowered server like the ReadyNAS.

If you haven't checked out Erland's relevant plugins (SQL Playlist and Dynamic Playlist) then you really should. You can get very specific about what tracks/artists/genres/etc to include or exclude.

And if you want to stick with shuffle, you do realize that you can save the shuffled list? Loading the playlist without reshuffling should be very quick, even on a ReadyNAS.

larrettp
2008-03-25, 06:57
Yes, I do have shuffle on and, to my mind, the system should be able to cope with this. I want all my favourite songs available at any time in a random order.

I run my SB3's wired as well but it doesn't make a lot of difference. The fact is that the web interface takes SOOO long to load and that is running on the ReadyNas. This seems to be a universal problem and NOBODY is addressing it. Obviously, the database structure is inefficient (I am a DB2 Database Administrator professionally so I can recognise where the problems lies). If that is coupled with inefficient SQL, the results are bound to be slow. The size of the database should be irrelevant if proper indexing is used.

Why hasn't somebody resolved these problem sooner - they existed in v6. Just making the interface prettier doesn't disguise bad programming.

Thanks for the tip about the plug-ins. Where do I find them and how do I install them. Also, why aren't they supplied as standard?

radish
2008-03-25, 07:19
Yes, I do have shuffle on and, to my mind, the system should be able to cope with this. I want all my favourite songs available at any time in a random order.

You're running on a very underpowered piece of hardware, there are limits.



This seems to be a universal problem and NOBODY is addressing it.

How do you know nobody is addressing it? AFAIK quite a bit of work has been done on the DB and general data layer in the last couple of versions.



Obviously, the database structure is inefficient (I am a DB2 Database Administrator professionally so I can recognise where the problems lies). If that is coupled with inefficient SQL, the results are bound to be slow. The size of the database should be irrelevant if proper indexing is used.

I'm sure everyone would benefit from your expertise if you fancied doing some optimization!



Thanks for the tip about the plug-ins. Where do I find them and how do I install them. Also, why aren't they supplied as standard?

http://wiki.slimdevices.com/index.cgi?SlimServerPlugins

They're not supplied as standard because they're not part of the default supported package. That's what plugin means.

aubuti
2008-03-25, 07:53
I want all my favourite songs available at any time in a random order.
Even using the Random Mix option that comes standard with SC7 (and came standard with SS6.x) would be a better way of achieving what you are trying to do than using shuffle. Just create a "Favorites" genre, include that in the Genre tag of your favorite tracks, and let Random Mix do its thing on that genre. I understand your frustration that it's not working the way you have in mind, but have you tried the alternative?

The plugins do essentially the same thing, but allow more fine tuning of what tracks are considered for selection.

larrettp
2008-03-25, 08:02
Thanks for that. I am running the ReadyNas with 1Gb of RAM. Why is it still underpowered and do you have any suggestions for how I can get more out of it, please?

The plugins only describe installation instructions for Windows, Mac or Linux. Can they be installed on ReadyNas and, if so, do you know how?

aubuti
2008-03-25, 08:46
I honestly don't know whether shuffling is more CPU intensive or memory intensive, but 1GB of RAM should be plenty. What processor does your ReadyNAS have?

Re plugins, do you have telnet or ssh access to the ReadyNAS? If so, you just need to copy the plugins to the correct directory (preserving the directory structure) and re-start SC. Then you control the plugin settings via the web ui. I don't know where the "correct place" is on a ReadyNAS. For my LinkStation it's /usr/local/slimserver/Plugins/ and for my x86 debian box it's something different (maybe /usr/share/squeezecenter/Plugins/ ?).

If you don't have telnet or ssh access, you should check with whatever vendor provides the SC installation module to see if they support plugins.

In the meantime, try the stock Random Mix, which you already have available.

radish
2008-03-25, 09:38
There's a lot more info about running SC on the ReadyNas in the third party hardware forum.

larrettp
2008-03-26, 05:47
There is, indeed, a lot of stuff out there about performance of Slimserver/Squeezecenter on the ReadyNas.

Unfortunately, although customers themselves try to give tips on improvement (some work, some don't) there is little, if any, evidence of the vendors (i.e.Slidevices and Netgear) showing any rapid progress towards finding a lasting, ready to implement solution.

I really feel conned out of a lot of time and money for both the ReadyNas and the Squeezeboxes which have NEVER lived up to the promise I was given at the time of purchase.

When are we going to see true collaboration and willingness to resolve this issue?

iPhone
2008-04-14, 10:16
There is, indeed, a lot of stuff out there about performance of Slimserver/Squeezecenter on the ReadyNas.

Unfortunately, although customers themselves try to give tips on improvement (some work, some don't) there is little, if any, evidence of the vendors (i.e.Slidevices and Netgear) showing any rapid progress towards finding a lasting, ready to implement solution.

I really feel conned out of a lot of time and money for both the ReadyNas and the Squeezeboxes which have NEVER lived up to the promise I was given at the time of purchase.

When are we going to see true collaboration and willingness to resolve this issue?

Why do you feel conned? If you do not like the SB3 or Duet, send it back. I believe they both come with a 30-day Satisfaction Guarantee. As for the ReadyNAS, I had one and it did what it said it would do. Was it up to the task of my huge CD collection, NO it was not. The ReadyNAS is also really only meant for a small music collection and not other tasks. It is NOT a PC, it is an intellegent Network Storage Device. The Thecus N5200 Pro is in my opinion the minumum equipment as far as an NAS goes for any small to medium CD collection. And if one has over 5000 CDs, I strongly suggest that one builds their own NAS based off a minimum of an Intel P4 machine with 2 GB RAM running some form of Linux (and before everybody goes crazy, yes you can use older slower PCs, but with P4 prices where they are why would you). The biggest advantage of building your own NAS is that you can fix it yourself from regular PC parts as well as upgrade it anytime.

Finally, if one has a wireless network, why not try keeping the music files stored on the ReadyNAS and running SqueezeCenter on an old PC. That way the NAS does not have to deal with SqueezeCenter or all GUI overhead.

larrettp
2008-04-14, 11:22
There are 2 issues here:

1. I have spent countless hours trying to get this setup to perform as promised. WAY beyond any 30 day guarantee. This is mostly because any technical support offered (for any IT product) automatically makes you assume it is YOUR fault! I have tried every new version of Slimserver, SqueezeCentre and RAIDiator issued and they have ALL performed like dogs. My library is well ordered and I spent a long time converting all tracks to the same format (AAC). I put 1gB of RAM in the ReadyNAS and that DID lift the performance from appalling to just dreadful. Since the ReadyNAS, Squeezeboxes and Slimserver were sold as a package, I would have expected them (naively) to work out of the box. Which part of this statement is unreasonable?

2. I would expect the vendors (Netgear and Logitech) to work TOGETHER to solve this problem, not in seeming isolation and denial of responsibility for a product combination which obviously is not fit for purpose. If you are selling a 1Tb server with a promise of music streaming to a specifically named product, it should be capable of working as well with 1Tb of stored music as with 1Gb. Size should not be an issue. Is there anything wrong with that assumption?

What you are suggesting is that the adverts recommending this combination were misleading and, although I can store music on the ReadyNAS and use the Squeezeboxes to stream it, I need to use an interim computer to do the work that SlimServer/SqueezeCenter reckons it should be able to do on the original combination.

aubuti
2008-04-14, 11:42
My library is well ordered and I spent a long time converting all tracks to the same format (AAC).
I don't recall you mentioning before that your tracks are AAC. So in addition to the demands of 7500 track shuffle plays, you are also spending lots of the ReadyNAS's scarce CPU power in transcoding. I don't expect to change your mind about what you think you were promised, but imo you are way overtaxing that machine. You could get much better performance with a codec that the SB handles natively (Ogg, MP3, FLAC, etc.) and using Random Mix instead of shuffle.

larrettp
2008-04-14, 12:01
But it is advertised to be capable of handling AAC!!! Why should I not believe it? There is a basic dishonesty here. How am I to know that, although it is capable of playing AAC files, it isn't capable of playing them efficiently?

sebp
2008-04-14, 12:27
There is a basic dishonesty here. How am I to know that, although it is capable of playing AAC files, it isn't capable of playing them efficiently?
You should not blindly trust ads and reviews.
There are tons of threads in both SlimDevices and Infrant forums where people complain about SlimServer's / SqueezeCenter's poor performance with the ReadyNAS and large music collections.
Now, if you're so upset with your ReadyNAS, sell it and buy a Thecus or whatever, but please stop bugging Slim/Infrant forums with your complaints.

larrettp
2008-04-14, 12:35
So...

Are the forums a service provided for customer feedback and to provide solutions for problems found during use...

or are they a nerdfest for those who just want to tinker with the hardware and software but aren't interested in a product that is readily usable by the general public?

radish
2008-04-14, 12:45
Forums are like life. They're whatever you make of them.

bpa
2008-04-14, 12:59
What processor does your ReadyNAS have?

The processor in the ReadyNAS NV is a IT3107 Network Storage Processor. This is a custom processor tuned for networked storage operations and does not have floating point which means the processor is not good at handling some Perl operations and also many decoding applications.

AFAICT the SC port is the responsibility of ReadyNAS engineering and it is up to them to assess what is acceptable performance from the ReadyNAS platform and decide which functionality (e.g. plugins) can be supported.

aubuti
2008-04-14, 13:58
But it is advertised to be capable of handling AAC!!! Why should I not believe it? There is a basic dishonesty here. How am I to know that, although it is capable of playing AAC files, it isn't capable of playing them efficiently?
And it is capable of playing AAC. You have just chosen to torture this little CPU with the way you choose to use it.

You have been given advice to reduce your problems, eg, use Random Mix instead of trying to use shuffle play on 7500 tracks, use a separate program for building your playlists, run SC on a separate PC (doesn't solve your ReadyNAS problem, but at least you cut the losses from buying hardware that isn't up to your expectations), etc. Have you tried any of these, or do you just think these "nerdfest" forums are supposed to be a place to complain and ignore advice?

larrettp
2008-04-14, 21:16
OK, I haven't ignored advice and I really want this to work but let's take these one at a time..

Converting everything from AAC is going to be a hell of a job. I haven't found a program yet that will preserve the folder structure - any ideas?

Random mix won't work for me, I'm afraid. The library contains my kids music as well as my own and I don't even want to hear some of my OWN music on a regular basis. Let alone hip-hop and death metal.

A lot of my time problems have stemmed from the fact that I work abroad during the week and have always had the foolish notion that I might be able to walk into the house on a Friday evening and switch on the music I want to hear over the weekend. In practice, I have spent most weekends over the past year trying the tips and tricks (with some success but mostly frustration). What I haven't had time to do is to set up another PC for this.

I would say that the AAC thing doesn't seem to be an issue once the playlist is running. The quality is good and it doesn't demonstrate a problem at that time at all. The main problem is the amount of time it takes to BUILD the playlists and I still don't understand why this is. As I said before, a playlist is (as far as I can see) simply a text file with a list of file addresses in it. These have, presumably, been verified during the scan process and should, therefore, be almost instantly available to the program. What else does it do when I add a song or album to the list that takes so long?

mherger
2008-04-14, 22:20
> Random mix won't work for me, I'm afraid. The library contains my kids
> music as well as my own and I don't even want to hear some of my OWN
> music on a regular basis. Let alone hip-hop and death metal.

That's why RandomMix lets you exclude genres from the mix. No classical
for me, nor spoken stuff, but mixes all the time.

> I would say that the AAC thing doesn't seem to be an issue once the
> playlist is running. The quality is good and it doesn't demonstrate a
> problem at that time at all. The main problem is the amount of time it
> takes to BUILD the playlists and I still don't understand why this is.

Well, as your box has to transcode AAC, it's busy doing more important
stuff (playing music) than creating your playlist. I'd guess the CPU is
close to 100% loaded due to transcoding.

Michael

larrettp
2008-04-14, 22:52
Thanks, at last a reply that seems to make sense of the way the machine is being slowed down. I'll have a look at the random mix options to see if I can use it.

From a professional point of view though (I am a mainframe DB2 Database Administrator), I'd love to know why the access paths to the data are so slow - is it the database deign (more indexes required, maybe) or the SQL used to access the data or the program design? Any or all of these could be having an adverse effect on performance and some of the solutions could be quite simple and very effective.

I'm not just having a go at Slim Devices here, I think Infrant/Netgear and Logitech could all play their part to make the performance more efficient.

For now though, I'll be satisfied with the replies I've had in this forum.

Thanks to all.

MuckleEck
2008-04-15, 10:28
Converting everything from AAC is going to be a hell of a job. I haven't found a program yet that will preserve the folder structure - any ideas?



If you are using a PC try dBpoweramp reference version there is a small cost ($30 I think) but it will convert and keep in the same folder.

erland
2008-04-15, 10:37
From a professional point of view though (I am a mainframe DB2 Database Administrator), I'd love to know why the access paths to the data are so slow - is it the database deign (more indexes required, maybe) or the SQL used to access the data or the program design? Any or all of these could be having an adverse effect on performance and some of the solutions could be quite simple and very effective.

If someone knew I supposed it would already have been corrected. The database backend is a MySQL database, since it sounds like you have experience with databases I'm sure the developers would appreciate if you take a look and suggest improvements in the indexes if you see some.

The database is normally accessible at port 9092 (not the default 3306 port normally used by MySQL), I'm not sure if the ReadyNAS setup uses 3306 or 9092. I think the database by default is configured to restrict access to local users, so you might need to login to the ReadyNAS first and access it locally.

A brief documentation of the database structure can be found here:
http://wiki.slimdevices.com/index.cgi?SlimServerDatabaseStructure

ModelCitizen
2008-04-15, 11:43
I'm just staggered how politely helpful the people on these forums continue to be despite the unreasonable and misguided expectations and the less than grateful or thankful attitude of the original poster.

What an amazing place this is.

MC

larrettp
2008-04-15, 12:24
Thanks for the invitation to look at the database and some of the commonly used SQL. I couldn't see which indexes are currently set up but I would strongly recommend all of the 'Number 1' and 'Number 2' indexes listed below and as many of the others as you can afford. Don't forget that there is a performance overhead when a new item is added to the database but, at the moment, the scanning process takes so long as to make this negligible. The real benefit comes when retrieving data and these indexes will definitely speed up most, if not all of the SELECT statements in the document I saw.

Good luck

Table name with proposed indexes underneath
-------------------------------------------
contributors
IX1 id,namesort
IX2 namesort

contributor_album
IX1 contributor,role
IX2 role

albums
IX1 id,contributor,titlesort,compilation,year,disc,des c
IX2 compilation
IX3 titlesort,disc
IX4 Year,desc,titlesort,disc

tracks
IX1 album,audio,titlesort,disc,tracknum
IX2 audio

genre_track
IX1 genre,track

genres
IX1 id,name
IX2 name

larrettp
2008-04-15, 23:03
I've also spotted in the documentation that several 'many-to-many' tables are created and maintained. These are an enormous I/O overhead and, if they are not indexed, will give exactly the performance log-jam we are experiencing.

Basically, the bigger the library, the longer each operation will take as the program has to do a table-scan every time.

I would strongly suggest removing the 'many-to-many' tables and all the associated creation and maintenance and applying indexes to the main tables. Then MySQL can take care of I/O in a far more efficient and much faster way.

Try running an EXPLAIN on some of the SQL statements before and after Indexing. You should see a HUGE difference.

larrettp
2008-04-29, 02:07
I have worked very hard to configure the whole ReadyNas/SqueezeCenter combination based on the latest versions available and I have now achieved a workable combination of options. It is now performing well although I still maintain that the database structure and SQL are the root cause of the slowness of the scans and playlist creation (see my earlier post),

There was a further problem I experienced where a track would play for about 2-3 seconds and then stop for a couple of seconds before restarting – very annoying. I experimented with different options one at a time until I decided to reinstall using the latest versions of Raidiator and SqueezeCenter available on 28/04/08. I installed these plus the Plugin Pack and the Multiplayer Plugin Pack. Then I uninstalled all Plugins through the Settings option except:

Favorites - I would like to disable this but the ‘tick box’ will not respond
Grab Playlist
Random Mix
Save Playlist
The Synchronizer

The key to the problem of tracks Start-Stop-Starting seemed to be this one under Settings/Advanced/Network:

Synchronized Players Startup Delay – 500

On the ReadyNas FrontView, I have disabled everything and uninstalled all Add-ons except:

Services/Standard File Protocols
CIFS
NFS
AFP
Advertise AFP service over Bonjour
Advertise AFP service over AppleTalk
HTTP
HTTPS

Services/Streaming Services
SlimServer

UPnP AV - I want to remove this but I can’t
Home Media Streaming - I want to remove this but I can’t

Services/Discovery Services
Bonjour service

UPnP - I want to remove this but I can’t

Services/Installed Add-ons

I have uninstalled all Add-ons

System/Performance Options
Disable full data journaling
Disable journaling
Optimize for OS X

bobkoure
2008-05-01, 06:08
A brief documentation of the database structure can be found here:
http://wiki.slimdevices.com/index.cgi?SlimServerDatabaseStructure
BTW, that link didn't work for me.

I've often wondered why the DB isn't indexed for all the "predictable" web-interface queries.
I could have a totally incorrect conception of what indexes do, of course - just about all my DB experience is with hierarchical DBs (and 25+ years ago), with a bit of dabbling into network DBs ' round about the same time.
I did have the privilege(?) of witnessing some screaming matches between Ted Codd and Ernie Bachman, (mostly, it was Ted raising his voice) that didn't persuade any of us that SQL was the way to go (performance issues). Who knew?

Now I'm wondering if it makes sense to be able to somehow build "index descriptors" and associate them with web UI elements...(?)
Might be time for me to get a more-than-passing acquaintance with SQL...

erland
2008-05-01, 06:29
BTW, that link didn't work for me.

The wiki urls has changed when they changed the wiki software, new link:
http://wiki.slimdevices.com/index.php/SlimServerDatabaseStructure


I've often wondered why the DB isn't indexed for all the "predictable" web-interface queries.

I'm not sure I understand what you are talking about, the database is already today indexed based on the queries standard SqueezeCenter/SlimServer uses. I don't know if they have been updated due to the latest query changes but I'm guessing they are up to date. However, this still doesn't say that the indexes can't be improved even more.

If anyone have specific suggestions of any index that can be improved, please let the developers know by posting the suggestion either in the Developers section of the forum or as an enhancment request at http://bugs.slimdevices.com

larrettp
2008-05-07, 01:15
Well, I've had a look at that Wiki as well and I still see the 'Many-to-Many' tables in there. The problem with these is that they have to be created and maintained, which takes time and slows down the scanning process. Also, when they are being read, they are very inefficient as they will be doing table scans and the larger the database, the longer it takes - exponentially!

By removing these and replacing them with indexes on the main tables, the creation I-O will, almost certainly, be reduced during scanning and the access paths will be vastly improved and oblivious to the size of the database.

Again, I would strongly suggest creating a clone of the database, remove the 'Many-to-Many' tables. Create the Indexes and run an EXPLAIN of the SQL against the old and new databases. The difference should be obvious.

erland
2008-05-07, 10:01
Well, I've had a look at that Wiki as well and I still see the 'Many-to-Many' tables in there. The problem with these is that they have to be created and maintained, which takes time and slows down the scanning process. Also, when they are being read, they are very inefficient as they will be doing table scans and the larger the database, the longer it takes - exponentially!

By removing these and replacing them with indexes on the main tables, the creation I-O will, almost certainly, be reduced during scanning and the access paths will be vastly improved and oblivious to the size of the database.

Do I understand you correctly that you are suggesting a single "tracks" table that contains all information in the current database ?

Or are you just saying that the information that is in "contributor_track" and "genre_track" should be moved into the "tracks" table, but still keep the "albums", "genres" tables as separate tables ?

In my library this would result in increasing the number of rows in the "tracks" table by three times, but I have a rather small library and quite few tags, so in other users you might see an even larger increase.
It would also result in that the information in the "tracks" table for a single music file would be represented by several rows, since the many to many, still has to be represented. The SqueezeCenter perl code would need to handle this, which creates another complexity.

I understand that a "tracks" table which is 3 times larger might still be faster than joining through the many to many tables that exists today, assuming you have the correct indexes of course.

The big problem however is that this will also require a big change of the SqueezeCenter code since it relies on the current data structure in the database.

Just a stupid question, is relation databases really this bad at representing relations ? I thought this was something they were optimized for ?
After all, if we aren't going to use the features of the relation database, why use it at all ?

larrettp
2008-05-07, 10:17
No, that's not what I'm saying.

Basically, without access to the full database column definitions, I'm proposing this:

Instead of the many=to-many tables (ALWAYS something to send shivers down a DBAs spine!), put Indexes on the columns used in the SQL and rewrite the statements. The resulting statements (based on the examples provided in the Wiki) will be more streamlined and, when run through an EXPLAIN, will show much improved access paths.

This has 2 major impacts:

1. The Many-to-many tables don't have to be created and maintained at the time of a scan or rescan.

2. The SQL can speedily access the rows required at run time instead of constantly scanning at least 2 tables tables for each row required.

This, to me, explains a lot of the sluggish performance and exponential sluggishness as the database grows.

erland
2008-05-07, 10:48
It might be my knowledge that isn't deep enough regarding this, but I really can't understand what you mean.

The many to many tables contains information that isn't available anywhere else in the database, so they can't just be removed without moving the information anywhere else.

I will give an example how the current database layout is filled to make it a bit more clear.

Let's say we have a two tracks:
Track 1: Belongs to Genre "Pop" and genre "Easy Listening", performed by the artist "John Doe" and composed by "Mozart"
Track 2: Belongs to Genre "Pop", performed by the artist "John Doe"

In the database this will be represented as:

tracks
======
Row 1: Id=1, Title="Track 1"
Row 2: Id=2, Title="Track 2"

genre_track
===========
Row 1: Genre=10, Track=1
Row 2: Genre=20, Track=1
Row 3: Genre=10, Track=2

genres
======
Row 1: Id=10, Name="Pop"
Row 2: Id=20, Name="Easy Listening"

contributor_track
=================
Row 1: Contributor=100, Track=1, Role="artist"
Row 2: Contributor=200, Track=1, Role="composer"
Row 3: Contributor=100, Track=2, Role="artist"

contributors
============
Row 1: Id=100, Name="John Doe"
Row 2: Id=200, Name="Mozart"

(I made the id's in the examples within different ranges, just to make it easier to see which id is related to which id)

If I understand you correctly you are suggesting that the information in genre_track and contributor_track is just removed from the database ?

How would it in this case be possible to find the composer for Track 1 ?
How would it in this case be possible to find all the genres for Track 1 ?

larrettp
2008-05-07, 14:17
OK, you have now shown me a little more about the database structure which I wasn’t aware of but it doesn’t mean all is lost. It does mean a bit more work on the existing system but the benefits could solve many problems. Taking your example, lets remove the genre_track and contributor_track tables and create the remaining tables as:


In the database this will be represented as:

tracks
======
Row 1: Id=1, Title="Track 1", genre=10,artist=100,composed=200,performed=100
Row 1: Id=1, Title="Track 1", genre=20,artist=100,composed=200,performed=100
Row 2: Id=2, Title="Track 2",genre=10,,composed=100(default),performed=100

genres
======
Row 1: Id=10, Name="Pop"
Row 2: Id=20, Name="Easy Listening"

contributors
============
Row 1: Id=100, Name="John Doe"
Row 2: Id=200, Name="Mozart"

The indexes would be on:

tracks
======
Id
Title
Genre
Artist
Composed
Performed

genres
======
Id
Name

contributors
============
Id
Name

The only piece of extra coding I can see from this example is that, if either the composer of performer are not supplied, default them both to be the same as whichever IS supplied. If not, have a default of (perhaps) ‘0’.

In answer to your questions:

How would it in this case be possible to find the composer for Track 1 ?

Select contributors.name
track.title
from contributors,
track
where track.composed = contributors.id

How would it in this case be possible to find all the genres for Track 1 ?

Select genres.name
track.title
from genres,
track
where track.genre = genre.id

In each case, the columns are indexed and, therefore, access is direct. Arguably, you have cut down on the overall size of the database while making it much faster. The only significan code changes to programs is in the scan – where you build the database – and the SQL changes for retrieval are pretty simple.

larrettp
2008-05-07, 14:21
By the way, I followed the link in your signature and I want to try out some of your plugins. They look as though they might be interesting - especially with regard to this problem.

I'll let you know how I get on.

Cheers, it's nice to be having a conversation with someone who understands what this is all about.

larrettp
2008-05-07, 23:09
A couple of supplementary points arising from my reply above:

1. Why would a song have 2 genres - as shown in your example? Surely, a song is either 'Opera' or 'Death Metal'!

2. It seems to me that there are 2 distinct functions to the SqueezeCenter application. One is to gather the information abou all the tracks in the library and the other is to access that information to play the track (i.e. access and run the file).

In fact, the application seems to be trying to do everything at the same time EVERY time it runs. That is where the processing wastage is arising. Let's treat these seperately:

SCAN
----

In this part of the exercise the application should scan every track, playlist, favourite etc. in the library and build the database. Obviously, this is going to be quite a long process but once the tables have been built they should be easily accessible if they are well designed and indexed.

PLAYLIST
--------

When we want to play track, albums, playlists etc., the information is all now available and simply needs to be copied to the 'Playlist' area of memory. It doesn't need to be checked again until each track is due to be played. If that track doesn't exist, skip it until you find one that does. When the playlist needs to be saved, the only information needed is ID, Sequence number, Title, Album, Artist and file location.

ERROR REPORTING
---------------

During the scan process, Playlists could be checked for any tracks which no longer exist. These lines could easily be removed from the list and an error report could be written to the log.

In all of the above, I am assuming the recommendations I have made regarding Many-to-Many tables and Indexes have been applied as I can't see any of these methods working efficiently without them.

Seriously, if you want to discuss this further offline and you are a Slim Devices employee, please get in touch through my e-mail address in the forum database. I'd be delighted to help.

MuckleEck
2008-05-08, 00:05
A couple of supplementary points arising from my reply above:

1. Why would a song have 2 genres - as shown in your example? Surely, a song is either 'Opera' or 'Death Metal'!


I don't want to get involved in the minutae of database structures as that is not my speciality, however if you want to discuss underseas telecommunication I shall chip in.

Getting to the point about multiple genres, I have a large number (1000s) of tracks that have multiple genres as shown in the following example (not all tags included):-

J S Bach - Great Organ Works - Toccata & Fugue - BWV 538
Genres - Classical,Organ,Instrumental

In this way I can play random classical music and still get this piece and all other classical, alternatively I can specify instrumental and get this as well as other single instrument pieces or I can be very specific and just listen to organ music, it all depends on the mood I am in.

Perhaps I am in the minority!

just my 0.02....FWIW

larrettp
2008-05-08, 00:16
Thanks, sometimes it's difficult to see the usage of something if you are not using it in that way yourself. Obviously, people do use multiple genres and that's not a problem in the structure I'm proposing.

Anytime I need any help with Undersea Communications, I know who to come to! ;)

ModelCitizen
2008-05-08, 00:19
Perhaps I am in the minority!
I'll double the size of your minority.

MC

erland
2008-05-08, 10:02
2. It seems to me that there are 2 distinct functions to the SqueezeCenter application. One is to gather the information abou all the tracks in the library and the other is to access that information to play the track (i.e. access and run the file).
It's already partly separated.

The scanner code runs in a separate process but it also uses some common code, for example data access code to access the database. The design of the SqueezeCenter application is going towards object orientation, but some old parts hasn't reached this state.

The the main part of the application handles:
- User interaction through web interface, SqueezeBox Controller interface and IR Remote interface. For example to browse your music library in various ways and create/manage your playlists.
- Playing music (streaming the music data over the network).
- Adding new tracks found when browsing your library to avoid having to perform a full scanning which take a bit of time.

It has been discussed that the main art of the application would be appropriate to modularize a bit more than it is today, for example so you can only run the "Playing music" part on a small computuer(for example a NAS), while running the web interface on a real computer. This kind of modularization would make it easier to run SqueezeCenter on a low resource device such as a NAS.



During the scan process, Playlists could be checked for any tracks which no longer exist. These lines could easily be removed from the list and an error report could be written to the log.
This is done already today.



In all of the above, I am assuming the recommendations I have made regarding Many-to-Many tables and Indexes have been applied as I can't see any of these methods working efficiently without them.

While I can understand that your recommendation will result in better performance in some situations, I also think it might be quite a big job to change the SqueezeCenter perl code so it works towards this new structure.

I've done some work in the Custom Scan plugin that is in the same line as your suggestion, basically a single "tracks" table that contains multiple rows per track. Actually it contains one row for each track/attribute combination where an attribute for example can be a genre or artist. The problem I got was that this table obviously got quite large and contained 30 rows per track if a track had 30 attributes. The result was a table size with about 3 000 000 rows for some users with a large library, which resulted in poor performance. I experimented with indexing but I did never find a solution to the problem.

We had a discussion regarding possible solutions in this thread:
http://forums.slimdevices.com/showthread.php?t=38714

One problem with the Custom Scan plugin is that it needs to join with the SqueezeCenter standard tables in some situation and this is troublesome since there isn't any good keys to join with that survives a rescan. The result is that in some situation I need to join text/varchar columns which obviously is a bad idea regarding performance.



Seriously, if you want to discuss this further offline and you are a Slim Devices employee, please get in touch through my e-mail address in the forum database. I'd be delighted to help.
I'm not a Slim Devices employee.

For most people I don't think the current SqueezeCenter database is a problem regarding performance, it's only under some circumstances it causes problem, for example when running it on low resource hardware with a large library.

I think the major problem with your suggestions is that it will make the database less "object oriented" and due to this it will be a bit of work to adapt the object oriented code to it. So I suspect that before anyone at Logitech/Slim Devices are going to start changing the code according to your suggestion, they are probably going to want some third party developer to verify that the performance increase or resource usage decrease with this change results in a major difference. If it just get us 10-20% improvement I suspect no one is going to think it is worth the work. However, Logitech obviously needs to answer this themselves, this is just my suspicions.

Posting your ideas in the Developers section of the forum will make it visible for the correct people, so if you really think this change is going to make a big difference I would suggest that you post your ideas there and hope someone from Logitech likes to continue the discussion.

larrettp
2008-05-09, 01:27
This is all good detail. It's hard to analyse with only a sketchy outline of the database but I seriously believe the answers lie there.

I'm not proposing a single track record - just to eliminate the many-to many tables. If that is not currently possible, how about creating an index on both columns in each m-to-m table? Then create an index on the referenced column in each parent. It's a bit messy but it could eliminate a lot of table scans.

By the way, I downloaded a couple of your plugins but, as they are not *.bin files, I can't upload them to he ReadyNas. Any chance of bin files?

larrettp
2008-05-11, 22:40
Okay, since it would obviously mean a major rewrite to adodpt a different database structure, how about this for a suggestion:

The Playlist table doesn't really need to be coupled to the rest of the database as it is just a list of tracks to be played. The columns required are Playlist name, Sequence no., Artist, Album, Track and File Location (maybe Artwork location as well). Every column can have it's own Index but it doesn't need to be linked to the rest of the tables in any way as i's only function is to play the tracks.

Given that the rest of the tables take a long time to create at Scan time and that information is used for creating the Playlists, not other references to the parent tables should be required at run time.

Any thoughts?

erland
2008-05-11, 23:27
Playlists are currently stored in two tables:
- The playlist file together with the playlist title is stored in the "tracks" table
- The "tracks" table also contains all the music files
- The "playlist_track" table contains the relation between a playlist file and the music file, basically the playlist_track table describes which tracks that are part of a specific playlist.

SqueezeCenter uses a lot of tables when it should play a track, the basic data is in the "tracks" table but it read additional information from "albums" and "contributors" and all the many to many tables to be able to display extra information on the SqueezeBox display.

So changing the database structure won't be an easy task independent on how you do it. I would suggest that you continue this discussion in the "Developers" forum, because here in the "General" section it will probably not attract the correct people.

The SqueezeCenter source is open source and I suspect that the SqueezeCenter developer would like some real proof of improvement before they change the database structure, especially if the change result in less object oriented and less relational based approach than today. There are also other factors to think about than performance, for example the code has to be easily maintained and that is probably easier today where a specific information is stored in a single table/column in the database. Your suggestion is based on that the data should either be duplicated on multiple rows or even in multiple tables.

Before changing the database structure, I also think someone really ought to check what code and database queries that are executed today in the situation where you have performance problem. The solution might be as easy as just optimizing some code or changing a database query a bit.

But please continue this discussion in the "Developers" section of the forum.

larrettp
2008-05-12, 01:20
OK. do you have a link to the Developers forum. I think it's an issue worth raising. As you say, a lot of table activity takes place at run time which would not be necessary if the Playlist table was used as a 'Notepad' type table which had been built from the 'Catalog' tables. This goes back to my original point that we wait for hours for a scan while it builds the Catalog and then it uses the catalog to build the Playlist. After all that, at run time, it goes through all the same hoops again. It's simply not needed - the Playlist should simply play the tracks/files selected and not have to check back through the Catalog.

sebp
2008-05-12, 01:44
http://forums.slimdevices.com/forumdisplay.php?f=21

larrettp
2008-05-14, 02:16
I know there was some criticism of my use of AAC (*.m4a) file types earlier. I only used this because they all originate from iTunes and I couldn't find a sensible way to convert them while maintaining the file structure. anyway, I think I have found a piece of software which will do this for me and I wanted to know what the opinion is for the best quality file type I can convert to that can be handled easily by Squeezecenter?

Any suggestions? FLAC seems to be preferable to me but I could be wrong.

larrettp
2008-05-14, 05:50
Meanwhile, in the nightly updates folder - http://www.slimdevices.com/downloads/nightly/latest/trunk/ - I have been downloading the SqueezeCenter_7.0.1~nnnnn.bin files for testing. I also noticed in there, the jive_7.0.1_rnnnn.bin and u-boot.bin files which, on the face of it, would be usable on a ReadyNas.

Should I download these and use them? What do they do? Any answers appreciated.

Thanks.

aubuti
2008-05-14, 06:14
Meanwhile, in the nightly updates folder - http://www.slimdevices.com/downloads/nightly/latest/trunk/ - I have been downloading the SqueezeCenter_7.0.1~nnnnn.bin files for testing. I also noticed in there, the jive_7.0.1_rnnnn.bin and u-boot.bin files which, on the face of it, would be usable on a ReadyNas.

Should I download these and use them? What do they do? Any answers appreciated.
This seems to be straying off topic for this thread. Not a criticism, just an observation that your chances of a reply go up when the post is in the right thread.

The jive_7.0.1_*.bin file is the firmware for the Squeezebox Controller (Duet). It won't do anything for your ReadyNAS. I don't know what the u-boot.bin is.

erland
2008-05-14, 08:45
I know there was some criticism of my use of AAC (*.m4a) file types earlier. I only used this because they all originate from iTunes and I couldn't find a sensible way to convert them while maintaining the file structure. anyway, I think I have found a piece of software which will do this for me and I wanted to know what the opinion is for the best quality file type I can convert to that can be handled easily by Squeezecenter?

Any suggestions? FLAC seems to be preferable to me but I could be wrong.

If you convert the AAC files to something else you will lose quality. The only way to get better quality is to re-rip the music directly from CD into a loosless format (for example FLAC).

If you want to re-rip them from CD I would choose like:
- If you want to be able to use them from iTunes: Use Apple Lossless
- If you don't need to use them from iTunes: Use FLAC

If you rip them from CD into a lossless format you can always convert them to another lossless format without loosing quality, that's one of the advantages of using a lossless format compared to a lossy format such as AAC/MP3.

larrettp
2008-05-16, 02:26
I downloaded the 14th May nightly update and it seemed to be extremely well behaved. I noticed today that a 'final' release has been posted on Slimdevices for the ReadyNas and I've just installed that, along with the plugin pack and the Multi-player plugin pack (both of which have been impressive so far but I would advise turning off the ones you don't need).

For the first time in ages (based on the 14th May version) I'm quite optimistic here. It feels like a lot has been done and I'll report my findings back.

The only downer is that I now have to wait several hours for the initial library scan to complete before I can really try it out but the radio stations are working fine and the synchronization seems good.

More later....

aubuti
2008-05-16, 06:08
The only downer is that I now have to wait several hours for the initial library scan to complete before I can really try it out but the radio stations are working fine and the synchronization seems good.
If you want to listen while it scans you should be able to get at your full library via Browse Music Folders. Whether scanning and streaming to sync'd players is too much of a strain for the ReadyNAS is, as they say, an empirical question.

larrettp
2008-05-16, 06:28
I'm afraid the ReadyNas still gets a bit of a nosebleed when asked to do too much at the same time. I'm just trying what you suggested and it's rebuffering like crazy. The radio stations work OK but they seem to suddenly be way out of synch. That could be due to the scanning as well. It is a huge process.

aubuti
2008-05-16, 06:34
Sorry to hear that, but thanks for trying. Just curious: can you set scanning to a lower priority on the ReadyNAS? I'm not suggesting that you necessarily *want* to do that, as it would turn a long scanning process into a very long scanning process. I'm just wondering how much control users have with one of the few (the only?) NASs that ships with SC installed.

larrettp
2008-05-16, 06:44
You can set the server and scanner priority via the SC Settings in the Advanced - Performance tab. I've messed about with this in the past but not on this release. There is a danger of 'over-tweaking' sometimes, of course.

I'll just sit back and watch the grass grow while I'm waiting, I guess.

larrettp
2008-05-18, 15:07
The latest version on SqueezeCenter for ReadyNAS has now been running all weekend and is a significant improvement on all previous verions in many respects. There are still problems but I'll summarize them here:

Stuttering - I've not had any stuttering/rebuffering problems with this at all. A BIG bonus and very welcome.
Network disconnection - One of my Squeezeboxes DID suddenly disconnect and I had to connect it via wireless instead of ethernet. This really needs to be addressed.
Playlist build - this really hasn't changed at all. There is no sign of any improvement. Just make sure you have some wet paint nearby to watch dry.
Sound quality - no problems with that. It doesn't seem to have any problems with my AAC-based library, either

Overall, well done but still not perfect. PLEASE get this performance issue sorted for Release 7.1 (or even 8).

erland
2008-05-18, 21:24
Playlist build - this really hasn't changed at all. There is no sign of any improvement. Just make sure you have some wet paint nearby to watch dry.
Exactly what do you mean with "Playlist build" ?

Are we still talking about the time it takes when you hit the "Save" button or is there something else related to playlists that take a lot of time ?

Approximately how many songs does these playlists contain ?

larrettp
2008-05-18, 21:34
I mean that the whole creation, loading and saving process takes an unacceptable amount of time. My personal favourite playlist has 7750-ish songs but size should not be a factor (it doesn't affect iTunes).

JJZolx
2008-05-18, 23:10
I mean that the whole creation, loading and saving process takes an unacceptable amount of time. My personal favourite playlist has 7750-ish songs but size should not be a factor (it doesn't affect iTunes).

Have you tried installing and running SqueezeCenter on a typical, modern day PC instead of the ReadyNAS? I think you might be surprised at the performance difference.

As for _building_ very large playlists, SqueezeCenter just isn't a very good tool for doing this and there's not a lot you can do about it while using a web interface. Programs like Winamp or Foobar with nice GUIs are both easier and faster.

erland
2008-05-18, 23:16
I mean that the whole creation, loading and saving process takes an unacceptable amount of time. My personal favourite playlist has 7750-ish songs but size should not be a factor (it doesn't affect iTunes).
Does it work fast enough when you hit play or is that also a problem ?

larrettp
2008-05-19, 00:52
I'm afraid I only use Macs in my house - they work, PCs don't! Winamp and Foobar only seem to be available for PC which is a bit shortsighted.

The reason I use the ReadyNas is that I am often out of the country and keep all my music at home on it.

If a GUI can be run faster, why isn't one developed for Slim devices? It seems a bit shortsighted.

JJZolx
2008-05-19, 01:13
I'm afraid I only use Macs in my house - they work, PCs don't! Winamp and Foobar only seem to be available for PC which is a bit shortsighted.

Mac, PC, whatever. Surely there are music library managers for the Mac that can be used to build playlists. Even iTunes.


The reason I use the ReadyNas is that I am often out of the country and keep all my music at home on it.

I'm not sure what being out of the country has to do with it. You could easily set up a Mac with SlimServer running on it that would be a lot faster than the ReadyNAS.

larrettp
2008-05-19, 01:17
Having everyone's music on the nas means we are all backed up and it is in one central location. The ReadyNas is advertised as the machine for the job - it should be able to do it.

JJZolx
2008-05-19, 01:21
Having everyone's music on the nas means we are all backed up and it is in one central location. The ReadyNas is advertised as the machine for the job - it should be able to do it.

Ok. I stop talking to walls at 2:20AM.

larrettp
2008-05-19, 01:34
That's a shame. The thing is, I have all my music on my Mac Powerbook. My son and daughter each have a mac with their own music on it. Therefore, for all of us to be able to access a single music library, it needs to be centrally stored. That is why I bought the ReadyNas (as well as a backup location).

Therefore, the Squeezeboxes need to point at the ReadyNas. I can't see anything wrong with this in principle.

If you can suggest another way round it, I'd be happy to hear it. The main problem is the web interface being so slow.

egd
2008-05-19, 04:32
Bottom line is the ReadyNAS is NEVER going to make a decent Slimserver host, no matter what you do, it simply doesn't have the grunt for the job. If you're after a fast NAS that can comfortably handle SC have a look at the Thecus N5200PRO - my library is now >90k tracks and SC remains pretty responsive.

I'm interested in your suggestions re improving the underlying database design to increase performance. Are you willing to install SC on your MAC and to put say four tracks on it from two different albums (best include a compilation album in that mix) and then to examine the underlying DB to see how it hangs together? If you find gross inefficiencies and addressing them doesn't mean too many changes to underlying code (other than SQL statements) then the developers may well be willing to make the necessary changes. I for one would be delighted, because overall I do agree, the DB's performance just isn't what it should be given what's involved. If you're willing to do the analysis and post your findings in the developers forum, you may just get traction.

larrettp
2008-05-19, 04:50
I'll give it a try while I'm in the Netherlands this week. It should be interesting.

Pete

egd
2008-05-19, 06:12
Look forward to seeing your findings.

aubuti
2008-05-19, 06:47
That's a shame. The thing is, I have all my music on my Mac Powerbook. My son and daughter each have a mac with their own music on it. Therefore, for all of us to be able to access a single music library, it needs to be centrally stored. That is why I bought the ReadyNas (as well as a backup location).

Therefore, the Squeezeboxes need to point at the ReadyNas. I can't see anything wrong with this in principle.

If you can suggest another way round it, I'd be happy to hear it. The main problem is the web interface being so slow.
(a) I think the first point is that you could easily accomplish all of that with a regular pc/Mac/linux box instead of a ReadyNAS, at comparable cost. The difference is that the pc/Mac/linux box will run SC much faster.

(b) Re building playlists, are you still doing shuffle with 7500 tracks? Have you tried the built-in Random Mix feature, selecting only those genres you want to include in the mix (ie, not your kids' music)?

(c) But since you already have the ReadyNAS, you probably don't want to shell out more for a pc/Mac/etc. In that case, stop torturing yourself by using the web ui, and get used to using the remote. Both the IR remote and the SB Controller are much snappier than the web ui, especially on underpowered NAS boxes. If you must go with the web ui, see if you can install the flash-based slimfx skin on your ReadyNAS -- it is considerably quicker than other skins, and has better drag-and-drop support for building playlists.

larrettp
2008-05-19, 13:58
Thanks for that. I haven't come across the slimfx skin. Where is that available or is it already installed? I'll try it when I get back to the UK at the end of the week.

Cheers,

Pete

aubuti
2008-05-19, 14:15
Thanks for that. I haven't come across the slimfx skin. Where is that available or is it already installed?
You'll need to install. Put it with the other skins under the HTML/ folder. Details about slimfx are in this thread: http://forums.slimdevices.com/showthread.php?t=32422

erland
2008-05-19, 14:40
That's a shame. The thing is, I have all my music on my Mac Powerbook. My son and daughter each have a mac with their own music on it. Therefore, for all of us to be able to access a single music library, it needs to be centrally stored. That is why I bought the ReadyNas (as well as a backup location).

Therefore, the Squeezeboxes need to point at the ReadyNas. I can't see anything wrong with this in principle.

If you can suggest another way round it, I'd be happy to hear it. The main problem is the web interface being so slow.
What we are suggesting is that you should keep the music files on the ReadyNAS but run SqueezeCenter on a separate machine (mac or PC).

I'm still interested to hear exactly which parts you have performance problems with. Are we just talking about the performance when editing playlists or do you also have performance problems when you hit the play button and music should start to play ?

Does the performance problem only show up when you are accessing playlists from the web interface or do you have the same problem when accessing them from the player interface ?

larrettp
2008-05-19, 14:46
The latest version of Squeezecenter plays track fine. The performance problems are the painful slowness of the web browser when trying to add albums to a playlist - it REALLY is SOOOO slow! Also, loading a playlist to be played is very slow.

Maybe I'm impatient but response times of 3-5 minutes are not acceptable to me in other apps and I find it hard to believe anyone thinks they are here.

erland
2008-05-19, 15:07
The latest version of Squeezecenter plays track fine. The performance problems are the painful slowness of the web browser when trying to add albums to a playlist - it REALLY is SOOOO slow! Also, loading a playlist to be played is very slow.

Maybe I'm impatient but response times of 3-5 minutes are not acceptable to me in other apps and I find it hard to believe anyone thinks they are here.
Is it this slow also when loading a short playlist to be played, or are we only talking about playlists that contains at least 200 tracks ?

larrettp
2008-05-19, 15:18
No, it's directly proportional to the size of the playlist. My point always has been that size, in a well designed database should not be an issue. Indexes and the removal of many-to-many tables would help greatly. However, removing the many-to-many tables would mean code changes. A quick way round i would be to index both columns in the 'm-to-m's and also to index the corresponding columns in the parent tables. This would increase the scanning time as the indexes would nee to be populated but it would speed up retrieval.

erland
2008-05-19, 15:52
No, it's directly proportional to the size of the playlist. My point always has been that size, in a well designed database should not be an issue. Indexes and the removal of many-to-many tables would help greatly. However, removing the many-to-many tables would mean code changes. A quick way round i would be to index both columns in the 'm-to-m's and also to index the corresponding columns in the parent tables. This would increase the scanning time as the indexes would nee to be populated but it would speed up retrieval.
The attached file contains the SqueezeCenter 7.0 table definitions, as you can see there are already a lot of indexes setup for both the main tables and the many-to-many tables. So I'm not sure if you just think that no indexes exist or if you have really verified that there are indexes missing that ought to be there.

I haven't verified this 100%, but I think all the columns which are included in a join directive should be indexed and probably also most (if not all) of the columns which are part of where directives.

If you enable the "database.sql" logging parameter in SqueezeCenter settings, you should be able to see exactly how the SQL statements that are executed looks like.

Mitch Harding
2008-05-19, 15:52
A point about playlist creation and shuffling.

For a long time I did what it sounds like you are trying to do -- maintain a
large playlist of songs that I like, and play it in shuffle mode. I think
my list got up to around 3k songs, but I forget exactly.

Anyway, I also noticed that adding tracks to the playlist became slower as
the list became larger. But this was exacerbated by having shuffle enabled
*while creating the playlist*. What I observed was that as I added each
track to the list, it also re-shuffled the entire list. So naturally it
took much longer for it to re-shuffle a list of 3k tracks than it would for
it to re-shuffle a list of 10 tracks.

What I ended up doing was turning off shuffle while I was adding tracks to
the list. This shouldn't be a problem unless you want to be playing that
same list while you are adding to it. But it sounds like that's what you
want to be doing. If you had an extra SB (or softsqueeze) you could use,
you could always copy your playlist and then add to the new copy
(unshuffled) on your extra SB or softsqueeze, while the original copy
(shuffled) plays on your other players. Periodically you could delete the
original copy, begin playing the playlist, make another copy, and then begin
editing that.

Obviously this is somewhat kludgey. But there are going to be compromises
when you have a large library, a large playlist which is constantly being
reshuffled, playing music in a format which requires transcoding, and a low
powered server.

For what it's worth, I now use the random mix plugin, and am loving it.

On Mon, May 19, 2008 at 5:18 PM, larrettp <
larrettp.39ohrb1211235601 (AT) no-mx (DOT) forums.slimdevices.com> wrote:

>
> No, it's directly proportional to the size of the playlist. My point
> always has been that size, in a well designed database should not be an
> issue. Indexes and the removal of many-to-many tables would help
> greatly. However, removing the many-to-many tables would mean code
> changes. A quick way round i would be to index both columns in the
> 'm-to-m's and also to index the corresponding columns in the parent
> tables. This would increase the scanning time as the indexes would nee
> to be populated but it would speed up retrieval.
>
>
> --
> larrettp
> ------------------------------------------------------------------------
> larrettp's Profile: http://forums.slimdevices.com/member.php?userid=10191
> View this thread: http://forums.slimdevices.com/showthread.php?t=45261
>
>

JJZolx
2008-05-19, 16:07
No, it's directly proportional to the size of the playlist. My point always has been that size, in a well designed database should not be an issue.

Current playlists are stored in the database. So when you add a playlist of 7500 tracks then you're doing 7500 table INSERTs. The time taken is always going to be directly proportional to the size of the playlist.

Not to mention that if you're shuffling the playlist then it's all being done in memory on a server platform that can barely get out of its own way. There was a post a while back from someone at Infrant (I may actually have read it in the Infrant forums) explaining why. When SlimServer 6.0 came out, with its move to MySQL for its dbms, it was faster on many systems with large libraries, slower on some systems with small libraries, but very much slower on the ReadyNAS. It was explained by Infrant that the ReadyNAS CPU architecture was uniquely unsuited to running MySQL.

erland
2008-05-19, 16:19
When SlimServer 6.0 came out, with its move to MySQL for its dbms, it was faster on many systems with large libraries, slower on some systems with small libraries, but very much slower on the ReadyNAS. It was explained by Infrant that the ReadyNAS CPU architecture was uniquely unsuited to running MySQL.
This is something to bear in mind when optimizing.

I've also noticed that the MySQL performance on some more complex queries can differ a lot between Windows and Linux. I've also seen that it can differ a lot between different 5.0.x versions of MySQL. SqueezeCenter currently uses 5.0.21 while there are a lot newer 5.0.x versions available.

So when checking optimizations, be really sure that you use the MySQL version bundled with SqueezeCenter and run it on the platform where you are going to use it later. Optimizing the performance when running SqueezeCenter on a Mac/PC doesn't have to result in better performance when you later are running it on ReadyNAS.

larrettp
2008-05-19, 23:30
The attached file contains the SqueezeCenter 7.0 table definitions, as you can see there are already a lot of indexes setup for both the main tables and the many-to-many tables. So I'm not sure if you just think that no indexes exist or if you have really verified that there are indexes missing that ought to be there.

I haven't verified this 100%, but I think all the columns which are included in a join directive should be indexed and probably also most (if not all) of the columns which are part of where directives.

If you enable the "database.sql" logging parameter in SqueezeCenter settings, you should be able to see exactly how the SQL statements that are executed looks like.

Thanks for this. I'll have a look at it in detail over the next couple of days. From the first look at it, I can see a lot of my suspicions confirmed but I want to check my facts and try to offer a contribution to a solution before I say any more.

Philip Meyer
2008-05-20, 00:39
You could try my plugin "PlaylistMan", which allows a song in the now playing playlist to be added to the end of another playlist. The way this is done is much quicker than loading the playlist, adding a song to it and then saving it again.

http://www.hergest.demon.co.uk/Music/music.shtml

Phil Leigh
2008-05-20, 11:50
Current playlists are stored in the database. So when you add a playlist of 7500 tracks then you're doing 7500 table INSERTs. The time taken is always going to be directly proportional to the size of the playlist.

Not to mention that if you're shuffling the playlist then it's all being done in memory on a server platform that can barely get out of its own way. There was a post a while back from someone at Infrant (I may actually have read it in the Infrant forums) explaining why. When SlimServer 6.0 came out, with its move to MySQL for its dbms, it was faster on many systems with large libraries, slower on some systems with small libraries, but very much slower on the ReadyNAS. It was explained by Infrant that the ReadyNAS CPU architecture was uniquely unsuited to running MySQL.

7,500 inserts on an indexed table on a PC might take a while...

On the other hand, all main queries need to be index-covered so you only read the index, not the data, wherever possible.

There is a read/write trade-off here, but since a database is (usually) write once, read many times, its best to invest the performance in the read rather than the write (at least in this application - unlike say a shopping cart, or airline system...)

I get annoyed by the length of time a scan takes (even with 7.x which is much faster than before)...but actually its the read performance that drives the user experience.

I don't get why it takes 60 mins to do 30k updates. I can't help wondering if the old trick of dropping all indexes, rescanning and then re-adding the indexes back at the end (obviously only for a clear & rescan!) would improve matters.

Doing updates to a table with a physically-clustered index is always slow...

Also, is it just me (I don't use playlists at all) but isn't 7.5k a bit BIG for a playlist?

larrettp
2008-05-20, 12:08
7,500 inserts on an indexed table on a PC might take a while...

On the other hand, all main queries need to be index-covered so you only read the index, not the data, wherever possible.

There is a read/write trade-off here, but since a database is (usually) write once, read many times, its best to invest the performance in the read rather than the write (at least in this application - unlike say a shopping cart, or airline system...)

I get annoyed by the length of time a scan takes (even with 7.x which is much faster than before)...but actually its the read performance that drives the user experience.

I don't get why it takes 60 mins to do 30k updates. I can't help wondering if the old trick of dropping all indexes, rescanning and then re-adding the indexes back at the end (obviously only for a clear & rescan!) would improve matters.

Doing updates to a table with a physically-clustered index is always slow...

Also, is it just me (I don't use playlists at all) but isn't 7.5k a bit BIG for a playlist?

I totally agree with all that you say about the Read/Write tradeoff. I've downloaded MySQL to my Mac and I'm currently experimenting with a few interesting combinations. I also found that the many-to-many records have Foreign Keys (with CASCADE ON DELETE) on them, which could be affecting things. I realise that this is a case of making the database fit the code - which is the wrong way round - but I don't want to instigate any code changes at the moment until I've proved the principle.

As for the size of my playlists, there shouldn't be a limit on such things (iTunes doesn't have one and it handles them instantly). I just have very eclectic taste and I enjoy the element of surprise!

More news later when I've finished the tests I'm currently running.

Phil Leigh
2008-05-20, 12:16
I totally agree with all that you say about the Read/Write tradeoff. I've downloaded MySQL to my Mac and I'm currently experimenting with a few interesting combinations. I also found that the many-to-many records have Foreign Keys (with CASCADE ON DELETE) on them, which could be affecting things. I realise that this is a case of making the database fit the code - which is the wrong way round - but I don't want to instigate any code changes at the moment until I've proved the principle.

As for the size of my playlists, there shouldn't be a limit on such things (iTunes doesn't have one and it handles them instantly). I just have very eclectic taste and I enjoy the element of surprise!

More news later when I've finished the tests I'm currently running.

Hi there - please ignore my comment about playlists as I don't use them...but I do design very large (>10Tb) commercial databases for a living!

(OK so they don't use MySQL...but the general principles tend to apply!)

That "cascade on delete" looks worrying :o) - also, what are these m:m relationships? - they tend to not work at all! (I'm a third-normal form type of guy :o) )

larrettp
2008-05-20, 12:26
Hi there - please ignore my comment about playlists as I don't use them...but I do design very large (>10Tb) commercial databases for a living!

(OK so they don't use MySQL...but the general principles tend to apply!)

That "cascade on delete" looks worrying :o) - also, what are these m:m relationships? - they tend to not work at all! (I'm a third-normal form type of guy :o) )

I'm a Mainframe DBA, so that's how my interest in this came about. I must admit, I'd never touched MySQL until today but, as you say, the same principles apply.

The thing about this whole design that gets me is the use of the many-to-many's - especially with the RI turned on so aggressively. I'll keep you posted about any results. Currently, in the absence of any programme information but based solely on the SQL logs, I've removed the RI and Indexed the affected tables up to the eyeballs! The initial results are encouraging, despite the slightly less han scientific methodology.

Phil Leigh
2008-05-20, 12:40
I'm a Mainframe DBA, so that's how my interest in this came about. I must admit, I'd never touched MySQL until today but, as you say, the same principles apply.

The thing about this whole design that gets me is the use of the many-to-many's - especially with the RI turned on so aggressively. I'll keep you posted about any results. Currently, in the absence of any programme information but based solely on the SQL logs, I've removed the RI and Indexed the affected tables up to the eyeballs! The initial results are encouraging, despite the slightly less han scientific methodology.

Hmmm m:m+RI... does not compute, Captain!

Surely those m:m's have got to go (I know that means redesigning the tables/indexes/keys but even so...)

Also - as I'm sure you know - RI only makes sense for 3rd+ normal form.

Anyway, I'm sure you know exactly what you are doing!

RI is really there (at least in this application) to cascade the deletes...but on a clear/re-scan you are in a database truncate situation...
RI won't help you there. Also (unless I'm missing something) RI is used to enforce parent-child (1:m) integrity for inserts...I'm thinking manual data entry here). In a "batch" situation, you can force all the parents in first (sorry - starting to ramble now - it's been a long day at the office)

larrettp
2008-05-20, 12:48
Hmmm m:m+RI... does not compute, Captain!

Surely those m:m's have got to go (I know that means redesigning the tables/indexes/keys but even so...)

Also - as I'm sure you know - RI only makes sense for 3rd+ normal form.

Anyway, I'm sure you know exactly what you are doing!

RI is really there (at least in this application) to cascade the deletes...but on a clear/re-scan you are in a database truncate situation...
RI won't help you there. Also (unless I'm missing something) RI is used to enforce parent-child (1:m) integrity for inserts...I'm thinking manual data entry here). In a "batch" situation, you can force all the parents in first (sorry - starting to ramble now - it's been a long day at the office)

Exactly! The m:m's plus the RI is not at all sensible. However, as I said, without access to the code, I'm in a position of trying to make the DB fit the code (tail wagging the dog?). The use of RI is ridiculous here because, as you said earlier, the only time the DB is written to is when scanning or compiling a playlist. Any checks on parentage would be done naturally in the code there. In fact, now I've removed it, it doesn't seem to have affected functionality at all (no surprise there).

I've been ranting on about the feeling I've had about the database for some time now and it's nice to have some confirmation that this is an avenue worth pursuing.

It would be much more worthwhile if the code could be changed to fit a more sensible design, though.

egd
2008-05-20, 13:03
However, as I said, without access to the code, I'm in a position of trying to make the DB fit the code (tail wagging the dog?) The code is freely available for download if that will help you decipher.

larrettp
2008-05-20, 13:08
Can you let me know where it is, please?

egd
2008-05-20, 13:17
This should be it http://www.slimdevices.com/downloads/nightly/latest/7.1/squeezecenter-7.1-19909.tgz

sebp
2008-05-20, 14:16
the only time the DB is written to is when scanning or compiling a playlist
Don't forget that SC will update its DB when you browse the music folder and access a not yet referenced directory.
No deletion there, but inserts and updates that may require integrity checks.

erland
2008-05-20, 14:53
Can you let me know where it is, please?

If you install svn you can get the latest version by checking out:
http://svn.slimdevices.com/repos/slim/7.0/trunk/server/

In a standard SqueezeCenter installation, you will also find all the code in the server/Slim directory.

On Windows installation, the code is compiled into .exe files, so to run changed code you will need to install ActiveState perl and run the slimserver.pl file instead of Squeeze.exe (or whatever the executable is called). On Linux/Mac installations, you can just change the code and run the new version by starting SqueezeCenter by running the slimserver.pl file.

A database abstraction layer is implemented throught the DBIx library in Slim/Schema*, so you won't see the exact SQL statements executed directly in the code.

All the database relevant stuff happens in server/Slim/Schema*.

I think the playlist editing is initiated from Slim/Web/Pages/Playlist.pm, but I'm not completely sure.

A recommendation to make it a bit easier is that you enable logging in SqueezeCenter Settings according to my previous post. This will let you see the SQL executed and you should also be able to see which ones that take most time. You can then make a copy of the table data with your new structure/indexes and run your version of the queries directly from a SQL client to check the difference. You can also run the SqueezeCenter queries directly in a SQL client with the EXPLAIN directive to check if the indexes available today are used correctly.

A bit of warning when you do this is that indexes used can differ between different platforms, so don't trust completely that MySQL is using the same indexes on ReadyNAS as it does on a Mac/PC. I've personally seen difference between the Windows and Linux platforms earlier when I've had similar problems with MySQL.

Finally, as I've already indicated earlier, doing a big change of the database structure will affect the SqueezeCenter code a lot, so to be able to convince the developers that this is worth the work I think that you are going to have to have proof that it improves the performance a lot. I also think that it needs to improve the performance for a normal user, if it's only a problem for users having 7500 tracks in their playlist I have a feeling that the developers will just ignore it as a special case not worth doing a lot of work to solve.

Also, as I said earlier, any suggestion or questions to the developers need to happen in the Developers section in this forum. So please, post your findings in the developers section when you have done the work, if you are just posing it here there is a risk that the right people won't see it.

Finally, as a side note, if the major problem is the time when you hit play on a playlist, it might help to play the playlist through the Dynamic Playlist plugin. If you play it through the Dynamic Playlist plugin, the playlist will be loaded in small steps, it starts by loading the first 10 tracks and after the first track has been played it loads the 11'th track and so on. Pretty much the same algorithm as the built-in Random Mix plugin but based on a saved ordered playlist. However, this won't help with the performance problems during editing of playlists.

larrettp
2008-05-22, 16:23
OK, I've had some REALLY good results from the database changes I've made to the SqueezeCenter database on my Mac. so good, in fact, that I can't wait to apply them to the ReadyNAS. The only problem is, I can't connect to it. Does anybody know the host name, port number, user-id and password I should use to connect MySQL administrator to it, please? Once I've tested it there, I'll publish the results. They are very good indeed.

larrettp
2008-05-22, 16:26
...also, the socket name, if possible, please?

erland
2008-05-22, 21:26
Does anybody know the host name, port number, user-id and password I should use to connect MySQL administrator to it, please?

By default remote connections are disabled, so you will probably only be able to connect to it on the ReadyNAS from a terminal prompt logged into the ReadyNAS. If you comment out the "bind-adress" row in the MySQL/my.tt file in the SqueezeCenter installationm and restart SqueezeCenter, you will be able to connect to it from another machine.

The port to use is:9092 (Instead of 3306 which is default for other MySQL installations)
The database name is: slimserver

Username/Password handling is disabled, so you don't need to specify those (or you can specify anything you want, it's ignored).

From a terminal prompt, you should be able to connect with the standard mysql client as:
mysql -h127.0.0.1 -P9092 slimserver

SqueezeCenter must be running, because MySQL is only started when SqueezeCenter is running.

This is on a standard PC/Linux installation, things may be different on the ReadyNAS.

larrettp
2008-05-23, 05:52
I can connect to the C drive on the ReadyNAS and I've removed the line from my.conf - I looked for my.tt but couldn't find it (any idea which directory?).

I restarted the NAS and I can still connect to 192.168.2.4 Port 22.

I am then using MySQL Administrator to try to connect to 192.168.2.4 Port 9092, user root, password, and socket /var/run/mysqld/mysqld.sock

I then get:

Connection error

Could not connect to MySQL instance at 192.168.2.4.
Error:Can't connect to MySQL Server on 192.168.2.4 (61)
Code 2003

I can Ping it OK but I'm currently out of ideas on this. If you have any, please let me know.

Thanks

sebp
2008-05-23, 08:42
I'm not sure whether the MySQL server port is different on the ReadyNAS from the one that's packaged with the standard SqueezeCenter installation, but what I'm sure about is that - once you can login as root through SSH on your ReadyNAS - connecting to the MySQL server is as simple as :

your_nas:~# mysql -u root slimserver
Have fun.

Seb

erland
2008-05-23, 09:19
I can connect to the C drive on the ReadyNAS and I've removed the line from my.conf - I looked for my.tt but couldn't find it (any idea which directory?).

The my.tt is found in the MySQL directory under the SqueezeCenter main installation directory, you are probably looking in the MySQL directory in the Cache dir.

The my.conf file will be overwritten with the my.tt contents at SqueezeCenter restart.

On my Ubuntu box, the my.tt file can be found in /usr/share/squeezecenter/MySQL

larrettp
2008-05-23, 11:22
Great googly-moogly! I'm in!!! On port 3036 with user slimserver and no password.

Thanks for this to all!

JJZolx
2008-05-23, 15:10
Great googly-moogly! I'm in!!! On port 3036 with user slimserver and no password.

Then there's probably no my.tt being used for MySQL on the ReadyNAS (even if you find one), as it appears to be using a standard MySQL install from the OS.

Let us know what you find. I'm betting that you won't be able to do much on that platform to provide a noticeable improvement in performance. On your Mac it may be a different story.

egd
2008-05-23, 20:58
If you need root access to your ReadyNAS via ssh have a look at the addins corresponding to your version of the Radiator firmware at readynas.com.

larrettp
2008-05-23, 23:29
Thanks but I managed to finally get in last night and I'm currently modifying and testing. I'll let you know how it goes.

egd
2008-05-25, 14:13
Thanks but I managed to finally get in last night and I'm currently modifying and testing. I'll let you know how it goes.Great, looking forward to your findings.

larrettp
2008-05-25, 15:05
Optimisation of the SqueezeCenter database
------------------------------------------

From the outset, I was not happy with the overall performance of the Squeezebox. Mine is used in conjunction with a ReadyNAS and I was constantly told that this had limitations, which meant it was always going to be ‘slower than on other platforms’. I downloaded the SqueezeCenter software to my Mac PowerBook G4 and found that I still wasn’t overwhelmed – the same problems were there.

My main issue was that, the larger the Playlist, the longer it took to add items to it. This meant that functionality was constrained by size, which should never be an issue in a well-designed database. I should state here that I am a professional Mainframe and mid-range Database Administrator (DB2, IDMS, SQL Server and a bit of Oracle).

I downloaded MySQL Administrator and Query Browser and also switched on SQL Logging in SqueezeCenter.

Firstly, with Administrator, I looked at the tables and their Indexes. This immediately rang alarm bells. Among the issues I found were:

• Several Many-to-Many tables – These are always going to cause problems in a database, as they are an I-O overhead to maintain and, without careful indexing, to reference.
• The Many-to-Many tables all had Foreign Keys (RI) to their parent tables. This is pointless in a database that is not heavily transactional. Worse still, they all had ‘Cascade on Delete’ parameters. The fact that RI exists plus the Cascade parameter creates an enormous I-O overhead on the system.
• Nearly all of the tables had inadequate Indexing for the Queries being run. This creates even more I-O and adds to the performance problems.
• Within the code itself, there is huge repetition of effort and checking of the Catalog tables at times when no such checking is required for normal functioning.

After this, I ran a full Scan. I saved the scan log from this and trimmed it down to retrieve all of the SQL used during the different parts of the process. I then created, saved, loaded and cleared a Playlist. I saved the server log and trimmed it in the same way as the scan log.

I saved a representative sample of 40 SQL statements that were run regularly and used the MySQL Query Browser to EXPLAIN them. This showed the access paths used when each was run.

My actions were limited in that I didn’t want to change any of the code (I’m a DBA, not a programmer) and, therefore, I could only provide fixes that made the database fit the programs as they are now. If any programming changes recommended later are applied, the database can be changed accordingly.

The first thing to remove was the RI (Foreign Keys). These served no practical purpose and were a performance overhead. I did this and ran various tests with no adverse effects.

Secondly, I applied Indexes to the Many-to-Many tables (these only have 2-3 columns) in EVERY possible permutation. For instance, where there are columns A, B and C, I created indexes for ABC, ACB, BAC, BCA, CAB and CBA. Obviously, this is well overdone but, without access to the code, the overhead of creating these at the time of the scan was far less than that of creating the RI and the potential benefits when the database is READ were huge. I also removed the existing, single column, Indexes.

Next, I looked at the 40 SQL statements I had saved and ensured each table had an appropriate index for optimum retrieval for each statement. The only other Index I added which had a big most impact was:

• CONTRIBUTOR on the Albums table

Having carried out all of the work on the tables, indexes and RI, I used MySQL Administrator to Optimise the tables. As so many new indexes had been created, this seemed like a good idea to make sure they were properly organised.

I then ran all of the sample statements through MySQL Query Browser to compare the results. All of the statements returned the same result sets except they were visibly faster and the access paths were greatly improved.

Following this, I ran a full library scan again and created, saved, cleared, loaded and played a Playlist. The difference was remarkable. The whole system was MUCH less clunky and had no loss of functionality or any errors.

Recommendations
---------------

1. I Strongly recommend the removal of all Foreign Keys in the database. These are an overhead providing a database facility that is inappropriate for this application.
2. I also recommend a complete overhaul of the table indexes – especially on the Many-to-Many tables. This can, obviously, be carried out more easily with full vision of the code (which I still don’t seem to have) but, in many cases, making the Index fit the SQL in the log is not a bad place to start.
3. Is it possible to add a database function to the Settings panels that would instigate a MySQL Optimisation? Alternatively, can this be done at the end of the scan process? Thinking about this, the scan process DOES talk of Optimisation as it’s last step but I’m not sure what it means by this.
4. Is it possible to switch off artwork? A lot of time is spent in the programs chasing artwork that may or may not be there and the only place you normally see it is in the web interface. Personally, I’m not bothered and if the system can work more efficiently without it, I’d rather have this option.

The application
---------------

As I said earlier, all of the above database changes were made without changing any of the code. I did notice, though, that the strategies of the programs could be modified to produce the same results much more efficiently.

1. Library scan – when a scan takes place, it creates a Catalog of tables that are used later when creating and running Playlists. The reason for scanning is to take into account and new or deleted music, Playlists etc. This takes a considerable time to run but, if it supports the system later, it has to be done. It does mean, however, that the music currently being played can be disrupted – almost to the point of being unlistenable. Is there any way that the scan can be run in it’s own memory address space to keep it separate from the part of the application which is playing the music (or vice versa)?
2. Creating Playlists - When creating a Playlist, the programs effectively carry out ‘mini-scans’ for EVERY track to make sure they still exist!! This is really where the performance goes downhill once the database access issues have been resolved. Why is this done again? We already have the Catalog tables from the last scan and all we need to create the Playlist is the URL and the artist/album/track etc. details. If the physical files have been deleted, we shouldn’t care at this stage. We are just building a ‘shopping list’ of tracks to play in the future based on our latest Catalog. Adopting this strategy will cut out a whole load of I-O activity.
3. Loading Playlists - When loading a Playlist that has already been created, the ‘mini-scan’ process described in ‘2’ is run again!!! Really, how often do you want to scan this database before you actually PLAY SOME MUSIC!!!!
4. Saving Playlists – Guess what? When we save a Playlist, we perform yet another ‘mini-scan’. Stop messing about. Just save what you have got.
5. Playing Playlists – at the time of playing, the application checks to see if the file exists (as if it hasn’t checked enough times before). If it exists, it plays it. If it doesn’t, it moves on to the next – although sometimes it hangs.

Let’s get this into perspective – taking the shopping list analogy. You need to go to the supermarket to get some food. You know the stuff that they sell so you make a list of what you want before you go. You go to the shelves for the items you want and, if they haven’t got one of them, you move on to the next item on the list. That is, in my opinion, exactly the strategy that SqueezeCenter should be adopting. With the current setup, you make your list, phone the store to see if they have everything in stock, go to the store, check with customer services to see if they still have it all and then go to the shelves to get it (or not, if you’ve spent too much time checking and, in the meantime, someone else has taken it!).

Conclusion
----------

This has taken a lot of work in the face of many on these forums taking the attitude that ‘If you’re running a ReadyNAS, what do you expect’! Maybe it’s not the fastest piece of kit on the market but I knew that when I used it for backup and retrieval purposes it showed no problems with response times. Therefore. The software had to be – at least – partly to blame. What I have presented here is only a workaround solution but it gives a lot of pointers to the kind of changes the developers should be focussing on in the future. Remember, the whole idea of this kit is to PLAY MUSIC – not to endlessly wait for an unresponsive machine to maintain an over-engineered database.

Caveat
------

If you feel confident to make these changes yourself, good luck. I have had nothing but good results from them, so far. However, I take no responsibility for any loss of data or service from your setup. Let’s hope the developers take notice of this and incorporate these, or similar, improvements into the released versions of the software.

JJZolx
2008-05-25, 15:25
Optimisation of the SqueezeCenter database

This really isn't a topic suited for General. You might want to start a new thread in the Developers forum.

Edit: I see you're crossposting. Maybe this thread should be locked in General?


I then ran all of the sample statements through MySQL Query Browser to compare the results. All of the statements returned the same result sets except they were visibly faster and the access paths were greatly improved.

Following this, I ran a full library scan again and created, saved, cleared, loaded and played a Playlist. The difference was remarkable. The whole system was MUCH less clunky and had no loss of functionality or any errors.

Is there no way to quantify the differences? These sounds like purely subjective conclusions.

larrettp
2008-05-25, 15:49
Apart from the fact that scanning and building palylists is considerably faster (20-25% in the case of scanning), I have run EXPLAINS on the SQL which shows far more efficient access paths to retrieve the same data. In the case of the 'contributor' Index, the query was taken to a completely different level!

I have posted these results/findings/recommendations on several forums (both Slimdevices and Netgear)and in a bug I have raised in the hope that somebody there will notice and try a few of these things out themselves.