[mythtv-users] Slow MySQL query after delete

[Robert Johnston]

On Nov 29, 2007 9:39 PM, Michael Rice <mikerice1969 at gmail.com> wrote:
> On Nov 29, 2007 6:38 PM, Michael T. Dean <mtdean at thirdcontact.com> wrote:
> > In other words, my system seems--to me--to be proof that a system that
> > takes 6-7 seconds to execute the scheduling run does not lock up when a
> > recording is deleted.  Perhaps I'm just naive, though, in thinking my
> > system acts like a properly configured Myth system.  Maybe I've
> > misconfigured my system and gotten unreasonably good performance because
> > of it.
>
> Mike,
>
> It's good to know that it is possible to delete without lock up... but
> whatever it
> takes isn't obvious to me.  I am using slow deletes and do not have my database
> on my recordings disks.  I have a newish Core 2 Duo backend and frontend.  I
> optimize my database in a cronjob regularly.  I read the list and try to
> follow the advise you and others give but I've always had this issue.
>
> Tonight I turned on "all" logging in the frontend and the backend is running
> "most" logging right now.  In the front end I deleted a show.. the UI came back
> immediately.  I then chose another show and selected "Delete".  The
> delete dialog came up then the frontend hung.  After some seconds
> it returned and I was able to delete the second show.  Below I've added log
> snippets...  Can you see the problem?  I am sure those of us that suffer from
> this would be forever in your debt if you could identify what the
> misconfiguration
> is.

<snip />

There's your problem.

BE 2007-11-29 18:05:51.780 MythEvent: RESCHEDULE_RECORDINGS 0

The backend is rescheduling recordings because of the change you have
just made (Deleting a recorded show). I would be willing to bet you
have at least one of:

Lots of channels
Lots of recording schedules
A large number of "At any time on any channel" rules.
Lots of previously watched shows

All of these things can slow down rescheduling for Myth

Rescheduling is done on delete so that (For instance) an "Only keep x
recordings" rule is accurate, repeats (And watched episodes) can be
marked  as "Do not record".

IIIRC, rescheduling is a blocking task for Myth (Though I'm not
certain why - could be a Database locking issue), but perhaps one of
the devs can look into using more optimistic locking (And/or spooling
the reschedule into a child thread so it becomes non-blocking). I
would say "Clean up the rescheduling query", but I've seen a glimpse
at the query that's generated for scheduling, and I ain't touching
that thing!
-- 
Robert "Anaerin" Johnston