[mythtv-users] Slow MySQL query after delete

[Michael Rice]

On Dec 1, 2007 2:13 PM, Michael Rice <mikerice1969 at gmail.com> wrote:
> On Dec 1, 2007 12:52 PM, David Brodbeck <gull at gull.us> wrote:
> >
> > On Nov 30, 2007, at 9:17 PM, Michael T. Dean wrote:
> > > How many times do I have to say, "His system takes the same amount of
> > > time for a scheduling run as my system and my system does /not/ lock
> > > up--or show /any/ delay at all--after a delete,"...
> >
> > It will if you immediately try a second delete.  That will force a
> > second scheduler query, which will block because the first one still
> > has the tables locked.  Same if you go in Mythweb and try to edit two
> > schedules in quick succession.
>
> Right.  So from looking at my logs carefully I think I see what
> is happening.  When the front end deletes it does a:
>
> UPDATE record SET last_delete = <date>.
>
> That's ok since it is the first delete and the BUSQ has not
> started yet.  Now if you time it correctly and start another
> delete after the BUSQ starts, the UPDATE record will be
> blocked and the frontend hangs until the BUSQ completes
> (or unlocks the table).

Well this is probably not the problem.  Even after removing the
UPDATE I still am blocked by the BUSQ.  In this case it appears
in the the backend that doesn't respond to the frontend that causes
the blocking.

BE 2007-12-01 16:38:52.550 Reschedule requested for id 0.
BE 2007-12-01 16:38:52.553 BuildWorkList...
BE 2007-12-01 16:38:52.555 AddNewRecords...
BE 2007-12-01 16:38:52.584  |-- Start DB Query...

<FE makes a delete request>
FE 2007-12-01 16:38:54.977 write -> 17 641
DELETE_RECORDING[]:[]The Fairly OddParents[]:[]

<BE receives request>
BE 2007-12-01 16:38:54.994 read  <- 15 641
DELETE_RECORDING[]:[]The Fairly OddParents[]:[]

<BE responds to the request but after ~15 seconds?>
BE 2007-12-01 16:39:09.616 write -> 15 2       -1
BE 2007-12-01 16:39:09.720  |-- 6221 results in 17.1347 sec. Processing...

<FE receives response and continues?>
FE 2007-12-01 16:39:09.731 read  <- 17 2       -1

I have 240 rows in my channel table, 309 rows in record many are
probably any time on any channel
rules, 12,721 rows in oldrecorded, and up to 6 capture cards.  I
suppose that is more than the average
Myth user and I suppose those with just OTA channels and a different
usage pattern wouldn't see much
of a problem.

The devs probably know what the problem is.  I hope there is a way to
fix this that isn't
just making the query run faster since there will always be people with slower
machines or larger input who will end up with nonresponsive frontends.
 Anyone know if
there is a ticket for this problem?  Or if it would be worth it to create one?