PDA

View Full Version : Big dropouts on searches still.



CouchPotatoe
2005-06-02, 16:29
I'm sure V6 is still settling but just wanted to let those
involved in the performance/DB issues that I still have dropouts when
searching for songs, big dropouts that I can't help feel reflect a major
issue with the architecture and make the search features unusable for
me. I have a huge collection (100K tracks) and am running 4 players on
Windows (Perl version not exe) and using latest nightly (although this
doesn't change things much) . P4 3.4Ghz 2GB RAM and Slimserver only app
running. Now I know that's not a typical ( or perhaps even reasonable)
size but I had kinda hoped for a better response than the times I'm
experiencing, maybe that is unrealistic. From a user perspective losing
control of the system for minutes at a time seems very unsatisfactory.
Is it not possible to thread the playback and display handling separate
from the searches in some way ?

As an example I enter a value in 'track' in advanced search on the
web interface and 4 seconds later all players stop playing, after 25
seconds all displays blank andtypically around 3 minutes later I get a
result, players and displays resume. The length of the stall directly
relates to the number of matches, a non existant search term like
"asfdjhkg" will not stall the players, an unusual term like 'unusual'
will not blank the players but will stall the playback, something like
'sometimes' will return in 30 seconds and something like 'love' hits the
3 mins with over 2 1/2 mins of blank screens. I just tried 'a' and
that's at around 14 minutes. During all this time the cpu utilisation
barely hits 5% and memory usage slowly creeps upto 150MB or so with that
last 'a' search. Is the bottleneck here the DB access or the building
of the results in RAM - perhaps for display in the web page ? If I
execute things from the player or CLI with no active web session the
times are similar. If it's the number of results is it not possible
to return this in some smaller chunk size or is that not done because of
sorting that is applied to the results.

I can search the same music library with an application like
TelCanto running on the same PC and get near immediate results and no
skips in the playback from SlimServer. So I feel there must
architecturally be a better way of managing this. Maybe these things are
in the works but I know that most developers work with much smaller
libraries and on the Linux builds so I just wanted to keep people
informed of the issues I'm seeing on Windows.

If I moved to Linux might I solve this problem do you think ? I have
a large investment in Slim (8 players total) and so I would like to get
it all working smoothly if possible. If the library size is just too
large to ever be manageable in SlimServer then I would appreciate
knowing that and I can explore other options. If there's still things in
the works that might resolve this then great, I'll hang on in there...

K

JJZolx
2005-06-03, 09:22
I'm sure V6 is still settling but just wanted to let those
involved in the performance/DB issues that I still have dropouts when
searching for songs, big dropouts that I can't help feel reflect a major
issue with the architecture and make the search features unusable for
me. I have a huge collection (100K tracks) and am running 4 players on
Windows (Perl version not exe) and using latest nightly (although this
doesn't change things much) . P4 3.4Ghz 2GB RAM and Slimserver only app
running.

Have you tried using MySQL instead of the built-in SQLite database engine? I have a feeling that while SQLite may be adequate for most collections, it may not scale terribly well. I can't say that for sure - but then again, I don't think I've seen anyone saying that SQLite has been tested much with collections over 100k tracks.

Do a search here to see the changes you need to make to slimserver.pref to run with MySQL.

Of course you'll first need to download MySQL 4 and get it running as a service on your machine. Not too difficult.

Robin Bowes
2005-06-03, 12:58
JJZolx wrote:
> CouchPotatoe Wrote:
>
>>I'm sure V6 is still settling but just wanted to let those
>>involved in the performance/DB issues that I still have dropouts when
>>
>>searching for songs, big dropouts that I can't help feel reflect a
>>major
>>issue with the architecture and make the search features unusable for
>>me. I have a huge collection (100K tracks) and am running 4 players on
>>
>>Windows (Perl version not exe) and using latest nightly (although this
>>
>>doesn't change things much) . P4 3.4Ghz 2GB RAM and Slimserver only app
>>
>>running.
>
>
> Have you tried using MySQL instead of the built-in SQLite database
> engine? I have a feeling that while SQLite may be adequate for most
> collections, it may not scale terribly well. I can't say that for sure
> - but then again, I don't think I've seen anyone saying that SQLite has
> been tested much with collections over 100k tracks.
>
> Do a search here to see the changes you need to make to slimserver.pref
> to run with MySQL.
>
> Of course you'll first need to download MySQL 4 and get it running as a
> service on your machine. Not too difficult.

I wouldn't waste your time - the problems you're having are a function
of the monolithic architecture. When slimserver is busy doing something
else, it doesn't always keep up with the streaming.

R.
--
http://robinbowes.com

Michaelwagner
2005-06-03, 13:30
You might consider fiddling with this setting, under server settings/performance.

I set mine to 1. It seems to work better and seldom drop out now. Of course, this is a hack, but it might help for now:


Build Items Per Pass
To avoid interrupting the music, SlimServer builds long lists in chunks. This setting determines how many items to build in a single pass.

Robert Moser
2005-06-03, 13:56
It would be nice to be able to pin down exactly where the problem is
coming from. I'd start by looking at the find() to see if it is the
database that is causeing the slowness.

Unfortunately, there aren't enough debug statements in
Slim::DataStores::DBI::DataModel::find to be able to tell, so you'll
need to add some.

Around line 643 (after the if ($@) {} block) add this:
$::d_sql && Slim::Utils::Misc::msg("SQL query finished, building
objects\n");

Then before the first "return $objects;" line add this:

$::d_sql && Slim::Utils::Misc::msg("Returning objects (sth_to_objects)\n");

And before the second "return $objects;" add this:

$::d_sql && Slim::Utils::Misc::msg("Returning objects
(fetchall_arrayref)\n");



After adding those lines, start slimserver with the --d_sql flag. Then
we can see if the delay is in find() and if it is, whether it is in the
execution of the sql, or the object building.

Robert Moser
2005-06-03, 14:08
Michaelwagner wrote:
> You might consider fiddling with this setting, under server
> settings/performance.
>
> I set mine to 1. It seems to work better and seldom drop out now. Of
> course, this is a hack, but it might help for now:
>
>
>>Build Items Per Pass
>>To avoid interrupting the music, SlimServer builds long lists in
>>chunks. This setting determines how many items to build in a single
>>pass.
>
>
>

Unfortunately this won't help him because the advanced search build
doesn't make use of this pref.

CouchPotatoe
2005-06-04, 06:07
First of all thanks for the help here Robert, I have absolutely no Perl
experience although I have used a couple of other languages so hope the
o/p is as you wanted.

....comments inline..

....Robert Moser wrote:

> It would be nice to be able to pin down exactly where the problem is
> coming from. I'd start by looking at the find() to see if it is the
> database that is causeing the slowness.
>
> Unfortunately, there aren't enough debug statements in
> Slim::DataStores::DBI::DataModel::find to be able to tell, so you'll
> need to add some.
>
> Around line 643 (after the if ($@) {} block) add this:
> $::d_sql && Slim::Utils::Misc::msg("SQL query finished, building
> objects\n");

yep - bang on vacant line 643

>
> Then before the first "return $objects;" line add this:
>
> $::d_sql && Slim::Utils::Misc::msg("Returning objects
> (sth_to_objects)\n");

added at line now 659
my $objects = [ $c->sth_to_objects($sth) ];

$sth->finish();
$::d_sql && Slim::Utils::Misc::msg("Returning objects (sth_to_objects)\n");
return $objects;

>
> And before the second "return $objects;" add this:

>
> $::d_sql && Slim::Utils::Misc::msg("Returning objects
> (fetchall_arrayref)\n");
>
>
added at line now 668

my $objects = [ grep((defined($_) && $_ ne ''), (map $_->[0], @$ref)) ];

$sth->finish();
$::d_sql && Slim::Utils::Misc::msg("Returning objects
(fetchall_arrayref)\n");
return $objects;

>
> After adding those lines, start slimserver with the --d_sql flag.
> Then we can see if the delay is in find() and if it is, whether it is
> in the execution of the sql, or the object building.

Heres's the op.

2005-06-04 13:47:49.8147 Running SQL query: [SELECT DISTINCT tracks.id
AS id,tra

cks.multialbumsortkey AS multialbumsortkey,tracks.thumb AS
thumb,tracks.age AS a

ge,tracks.ct AS ct,tracks.titlesort AS titlesort,tracks.album AS
album,tracks.tr

acknum AS tracknum,tracks.url AS url,tracks.tag AS tag,tracks.title AS
title,tra

cks.disc AS disc,tracks.fs AS fs FROM tracks WHERE ( ( (
tracks.titlesort like

? ) OR ( tracks.titlesort like ? ) ) ) ORDER BY tracks.titlesort]

2005-06-04 13:47:49.8149 Bind arguments: [LOVE%, % LOVE%]



2005-06-04 13:47:49.8250 SQL query finished, building objects

2005-06-04 13:47:56.5625 Returning objects (sth_to_objects)

So there is about 7 secs to that last line and then still about 3 mins
after this last debug statement to the browser refresh and the Slim
displays returning and playback resuming. The second line you asked me
to insert doesn't get displayed - is that as intended ?? The SQL query
looks very fast...

Thanks K

Robert Moser
2005-06-04, 11:44
Kevin Hawkins blurted out:
> So there is about 7 secs to that last line and then still about 3 mins
> after this last debug statement to the browser refresh and the Slim
> displays returning and playback resuming. The second line you asked me
> to insert doesn't get displayed - is that as intended ?? The SQL query
> looks very fast...
>
> Thanks K

There were two paths to the object building phase, so only two of the
three debug statements were going to be used. As I wasn't sure which of
the paths were going to be used, I had you stick a debug in each path.

This basically tells us that the database isn't the main culprit here,
and that using MySQL won't be of any help. Instead we'll need to focus
on the page/list building.

What is your Items Per Page set to? (Server Settings -> Interface)

In your original e-mail it wasn't so clear to me, do you also get huge
wait times for searches done from te remote?

CouchPotatoe
2005-06-04, 17:42
Hi Robert,

Robert Moser wrote:

> Kevin Hawkins blurted out:
>
>> So there is about 7 secs to that last line and then still about 3
>> mins after this last debug statement to the browser refresh and the
>> Slim displays returning and playback resuming. The second line you
>> asked me to insert doesn't get displayed - is that as intended ??
>> The SQL query looks very fast...
>>
>> Thanks K
>
>
> There were two paths to the object building phase, so only two of the
> three debug statements were going to be used. As I wasn't sure which
> of the paths were going to be used, I had you stick a debug in each path.

Ahhh ok

>
> This basically tells us that the database isn't the main culprit here,
> and that using MySQL won't be of any help. Instead we'll need to
> focus on the page/list building.
>
> What is your Items Per Page set to? (Server Settings -> Interface)

50

>
> In your original e-mail it wasn't so clear to me, do you also get huge
> wait times for searches done from te remote?

Yes , I get exactly the same delays from the remote.. 'Searching...'
followed by the interrupted playback / blank display for 3 mins and
then back to the 'now playing' screen so in actual fact I don't ever see
the results . I think this is poss due to screensaver timeout.

K

Fred
2005-06-09, 15:12
Guys,

The culprit seems to me to be in DBIStore. The following trace is
generated by the CLI titles command. It basically tries to get ALL tracks:


2005-06-09 23:49:43.7792 Slim::Control::Command::execute(titles) - find()
2005-06-09 23:49:43.7809 Slim::DataStores::DBI::DBIStore::find() -
Generated findKey: [track::title:::]
2005-06-09 23:49:43.7895 Running SQL query: [SELECT DISTINCT tracks.id
AS id,tracks.thumb AS thumb,tracks.tracknum AS tracknum,tracks.title AS
title,tracks.fs AS fs,tracks.titlesort AS titlesort,tracks.ct AS
ct,tracks.disc AS disc,tracks.url AS url,tracks.age AS age,tracks.tag AS
tag,tracks.multialbumsortkey AS multialbumsortkey,tracks.album AS album
FROM tracks ORDER BY tracks.titlesort]
2005-06-09 23:49:43.8006 Slim::DataStores::DBI::DataModel::find() - SQL
query finished, building objects
2005-06-09 23:49:55.1714 Slim::DataStores::DBI::DataModel::find() -
Returning objects (sth_to_objects)
2005-06-09 23:49:55.1753 Slim::DataStores::DBI::DBIStore::find() -
Checked cache
2005-06-09 23:50:52.4698 Slim::DataStores::DBI::DBIStore::find() -
Returning...
2005-06-09 23:50:52.4721 Slim::Control::Command::execute(titles) -
find() done
2005-06-09 23:50:55.1193 Slim::Control::Command::execute(titles) - done

The query is uber fast. Returning the objects take 12 seconds. And then
the last if() in DBIStore::find (between Checked cache and Returning...
above) takes a cool 57 seconds... Not sure what happens in there,
_includeInTrackCount & _checkValidity...

Also while this is running there is no stopping it so even if the search
is cancelled (or I kill my CLI client during the minute), nothing
happens until it is finished.

Also strangely the query is only on tracks (no joints) which means that
the album/artists/genre relationships are done in memory, but that maybe
a design objective.

Fred




Kevin Hawkins wrote:
> Hi Robert,
>
> Robert Moser wrote:
>
>> Kevin Hawkins blurted out:
>>
>>> So there is about 7 secs to that last line and then still about 3
>>> mins after this last debug statement to the browser refresh and the
>>> Slim displays returning and playback resuming. The second line you
>>> asked me to insert doesn't get displayed - is that as intended ??
>>> The SQL query looks very fast...
>>>
>>> Thanks K
>>
>>
>>
>> There were two paths to the object building phase, so only two of the
>> three debug statements were going to be used. As I wasn't sure which
>> of the paths were going to be used, I had you stick a debug in each path.
>
>
> Ahhh ok
>
>>
>> This basically tells us that the database isn't the main culprit here,
>> and that using MySQL won't be of any help. Instead we'll need to
>> focus on the page/list building.
>>
>> What is your Items Per Page set to? (Server Settings -> Interface)
>
>
> 50
>
>>
>> In your original e-mail it wasn't so clear to me, do you also get huge
>> wait times for searches done from te remote?
>
>
> Yes , I get exactly the same delays from the remote.. 'Searching...'
> followed by the interrupted playback / blank display for 3 mins and
> then back to the 'now playing' screen so in actual fact I don't ever see
> the results . I think this is poss due to screensaver timeout.
>
> K

JJZolx
2005-06-10, 09:25
Also strangely the query is only on tracks (no joints) which means that
the album/artists/genre relationships are done in memory, but that maybe
a design objective.

No joins??? I doubt that it's a "design objective" - more likely a quick and dirty translation of the old code to get 6.0 out the door. Obviously a lot of room for improvement, so at least there's a positive spin.

CouchPotatoe
2005-06-15, 18:48
Not sure if this is being looked at or is it best I re-open the bug again ??

Kevin

JJZolx wrote:

>>Also strangely the query is only on tracks (no joints) which means that
>>
>>the album/artists/genre relationships are done in memory, but that
>>maybe
>>a design objective.
>>
>>
>
>No joins??? I doubt that it's a "design objective" - more likely a
>quick and dirty translation of the old code to get 6.0 out the door.
>Obviously a lot of room for improvement, so at least there's a positive
>spin.
>
>
>
>