[mythtv-users] Mythtv 0.24 Acer Revo ALSA WriteAudio buffer underruns

[David Edwards]

On 5 March 2011 17:35, <pnetherwood at email.com> wrote:
> I still get the same problem in that mysqld goes to 100% CPU usage when a recording starts/stops.

I'm getting similar pauses on my frontend that appear to coincide with
the backend doing things (started when I upgraded from 0.23 to 0.24),
but I'd correlated it with autoexpire rather than recording
start/stop. I have defragmented my XFS drives and checked that slow
deletes are switched on, but I still get the pauses. I will try
monitoring MySQL next time and see if I can confirm if these queries
are causing the problem on my system.

> I'd be very interested how long the query takes to run on other peoples machines. Mine takes on average over 3 seconds.

Mine is consistently less than half a second... but my tables aren't
terribly big and I don't have many inputs.

> When I do an explain plan on it you can see its doing a full table scan 'cardinput' and 'recordmatch'. The query is joining
> on cardinput using sourceid which is not part of the primary key and also has no index. The query is also joining on
> 'chanid', 'starttime' and 'manualid' in 'recordmatch' which are not part of the primary key or on an index.

>From my basic understanding of the query (having just looked at the DB
and not the code), I'd say that adding cardid and sourceid indexes on
cardinput might help (and should be there anyway since they're
effectively foreign keys), but the effect would depend on how many
cards and sources you have. I only have two cards, and they both
provide the same channels so I don't think it would make much
difference for me. I think that adding an index for
(chanid,starttime,manualid) to recordmatch will have no effect unless
there are enough rows in the recordmatch table compared to the program
table to make the MySQL optimiser switch the way it does the query.

Having said all that, I've only quickly looked at the schema and the
query quoted here and have not had a chance to properly play with it.
Based on little evidence, I'd guess that the ORDER BY clause could be
the main culprit for slowing down the query, since it is probably
responsible for making MySQL use a temporary table and file sort.

David