Load average around 4.0 – normal is between .18 and .33.

Does the mysqld included with SC have this issue? It's 5.0.22.

Actually it's 5.0.21 on Linux.

Not sure about your question Andy - does that apply to all Linux installs? Running on Ubuntu it seems to have pulled a mysql version from the Ubuntu repos as part of the SC deps? I certainly never installed it manually on this VM (it only exists to run SC7.4) and if I try to uninstall it I'm told it'll also remove SC.

Anyhoo - it's version 5.0.67 and it certainly shows the CPU issue on SC 7.4 r28002. What I notice is that it's not taking CPU all the time, but does whenever I touch the web UI (even changing tabs in settings, and I can't imagine what the DB is needed for there), blocking the server for many seconds.

snarlydwarf reported the following in the forums (http://forums.slimdevices.com/showpost.php?p=445241&postcount=50):

This query runs once a minute or so, and, well it sucks, creating huge
temporary tables all for what seems like a worthless statistic:
 
 
Code:
--------------------
 
  SELECT COUNT( * ) FROM (SELECT me.id FROM tracks me WHERE ( me.audio = '1' ) GROUP BY me.id, me.url, me.content_type, me.title, me.titlesort, me.titlesearch, me.album, me.primary_artist, me.tracknum, me.timestamp, me.filesize, me.disc, me.remote, me.audio, me.audio_size, me.audio_offset, me.year, me.secs, me.cover, me.vbr_scale, me.bitrate, me.samplerate, me.samplesize, me.channels, me.block_alignment, me.endian, me.bpm, me.tagversion, me.drm, me.musicmagic_mixable, me.musicbrainz_id, me.lossless, me.lyrics, me.replay_gain, me.replay_peak, me.extid) count_subq;
 
--------------------
 
 
Is there a reason for this?  Almost all of those 'group by' items
seem useless for whatever this is trying to do...

Ouch!  That looks bad.  There's no point for the group by - it's grouping by the primary key, so it's never going to group any rows up.  So you'll get the same result with:

	SELECT COUNT(*) FROM tracks WHERE audio = '1'

Which I think is a little bit more efficient ;-)

I wonder why it's doing it every minute though?

Created attachment 5555 [details]
log snippet

Here's a 3 minute log snippet with SQL logging enabled.  The server was sitting mostly idle (no streaming), with no web interface open at the time.  That same query is executed sometimes twice within a second.

Yes - run with --perfwarn=0.5 and you see this is the culprit of the server stalling and hogging cpu.

It comes from some worthless stats which are sent to squeezeplay clients on every server status query:

   frame 0: Slim::Utils::PerfMon::check (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Schema/Debug.pm line 31)
   frame 1: Slim::Schema::Debug::query_end (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI.pm line 945)
   frame 2: DBIx::Class::Storage::DBI::_query_end (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI.pm line 982)
   frame 3: DBIx::Class::Storage::DBI::_dbh_execute (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI.pm line 400)
   frame 4: DBIx::Class::Storage::DBI::dbh_do (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI.pm line 989)
   frame 5: DBIx::Class::Storage::DBI::_execute (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI.pm line 1201)
   frame 6: DBIx::Class::Storage::DBI::_select (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI/Cursor.pm line 78)
   frame 7: DBIx::Class::Storage::DBI::Cursor::_dbh_next (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI.pm line 415)
   frame 8: (eval) (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI.pm line 408)
   frame 9: DBIx::Class::Storage::DBI::dbh_do (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI/Cursor.pm line 97)
   frame 10: DBIx::Class::Storage::DBI::Cursor::next (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI.pm line 1317)
   frame 11: DBIx::Class::Storage::DBI::count (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/Storage/DBI.pm line 1354)
   frame 12: DBIx::Class::Storage::DBI::count_grouped (/home/adrian/squeezecenter/7.4/trunk/server/CPAN/DBIx/Class/ResultSet.pm line 1155)
   frame 13: DBIx::Class::ResultSet::count (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Control/Queries.pm line 3145)
   frame 14: Slim::Control::Queries::serverstatusQuery (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Control/Request.pm line 1907)
   frame 15: (eval) (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Control/Request.pm line 1907)
   frame 16: Slim::Control::Request::execute (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Web/JSONRPC.pm line 354)
   frame 17: Slim::Web::JSONRPC::requestMethod (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Web/JSONRPC.pm line 201)
   frame 18: (eval) (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Web/JSONRPC.pm line 201)
   frame 19: Slim::Web::JSONRPC::handleURI (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Web/HTTP.pm line 484)
   frame 20: Slim::Web::HTTP::processHTTP (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Networking/IO/Select.pm line 133)
   frame 21: (eval) (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Networking/IO/Select.pm line 119)
   frame 22: Slim::Networking::IO::Select::__ANON__ (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Networking/IO/Select.pm line 177)
   frame 23: (eval) (/home/adrian/squeezecenter/7.4/trunk/server/Slim/Networking/IO/Select.pm line 177)
   frame 24: Slim::Networking::IO::Select::loop (./slimserver.pl line 605)
   frame 25: main::idle (./slimserver.pl line 574)
   frame 26: main::main (./slimserver.pl line 1084)

Surely the app only needs to fetch the stats from the database once at startup, and whenever a scan completes.  It shouldn't need to run the query every time.

That query has always been there I think, why is it just now becoming a problem?

Change 28016, I commented the following out.  We don't use these values, does anything else?

	if (Slim::Schema::hasLibrary()) {
		# add totals
		$request->addResult("info total albums", Slim::Schema->count('Album'));
		$request->addResult("info total artists", Slim::Schema->rs('Contributor')->browse->count);
		$request->addResult("info total genres", Slim::Schema->count('Genre'));
		$request->addResult("info total songs", Slim::Schema->rs('Track')->browse->count);
	}

If we really need these queries they should be rewritten using native DBI and custom SQL.

This bug has been marked as fixed in the 7.4.0 release version of SqueezeBox Server!
    * SqueezeCenter: 28672
    * Squeezebox 2 and 3: 130
    * Transporter: 80
    * Receiver: 65
    * Boom: 50
    * Controller: 7790
    * Radio: 7790  

Please see the Release Notes for all the details: http://wiki.slimdevices.com/index.php/Release_Notes

If you haven't already, please download and install the new version from http://www.logitechsqueezebox.com/support/download-squeezebox-server.html

If you are still experiencing this problem, feel free to reopen the bug with your new comments and we'll have another look.