[mythtv-users] Slow MySQL query after delete

f-myth-users at media.mit.edu f-myth-users at media.mit.edu
Thu Nov 29 04:41:50 UTC 2007


    > Date: Wed, 28 Nov 2007 22:10:07 -0500
    > From: "Larry K" <lunchtimelarry at gmail.com>

    > > >(Though there's a good chance you /should/ enable slow deletes.)
    > >
    > > Since the issue here is a slow SQL statement and not a slow file system
    > > (mine is xfs and deletes have never been slow), I wonder how this will
    > > help?  I thought this myth-setup option was to implemented to help overcome
    > > slow file system deletes.

It won't help you.

    > > >Yep.  Without changing your MySQL configuration, that's as good as it
    > > >will get.  I'll leave figuring out how to change your MySQL
    > > >configuration--and the consequences of doing so--to you (as really, the
    > > >fact that it takes 6-7 seconds to schedule recordings should make no
    > > >difference to you).

    > If the scheduler is blocking the frontend when it does this 7 second
    > operation, it clearly makes a difference to me.  It is very frustrating to
    > sit and wait up to 10 seconds after every delete (from either the frontend
    > or mythweb).

It's even worse than that for those of us for which -any- reschedule
takes 20-40 seconds or more (*)---and remember, a reschedule happens
-every- time something finishes recording!

My experience is that my frontend can't get a word in edgewise during
such a reschedule, so if I'm unfortunate enough to want to, say, skip
forward while rescheduling is happening, the request gets buffered for
up to most of a minute (!) and -then- executes.  This means that
skipping an ad if I happen to want to do this near the top or bottom
of the hour is problematic---since reschedules often happen then
because that's when shows tend to end, yet of course part of the point
is to free me from having to know what the -actual- time is while
watching something recorded earlier.  This isn't exactly good for
showing off the machine to people when occasionally one has to
explain, "Well, it's busy thinking and can't process the UI for the
next minute or so," while the ad plays on...  [This is particularly
weird because it's clearly not a disk-contention issue---video
playback proceeds just fine---but because the backend simply acts deaf
for a minute and then processes the queued stuff from the frontend.
Why should -anything- the scheduler is doing affect processing
commands?  I'm guessing because the UI and the scheduler are sharing a
thread somehow, which seems bizarre, but I haven't looked into it.]

Deletes are also slow, of course, because deletes provoke reschedules.
It's absolutely not the time required to delete the video from the
filesystem, because I'm running JFS, where single-file deletions of
any size are in the milliseconds.

[By the way, WHY do deletions provoke rescheduling?  I've been meaning
to ask this forever, and maybe I will in -dev.  It seems to me that
unless what you're deleting is (a) a keep-only-n-of-these sort of
item, or (b) a delete-and-allow-rerecord, the scheduler DOESNT NEED TO
KNOW that you've just deleted it, because it's not going to cause the
scheduler to do anything differently!  I've never used either one of
these in my entire history of using Myth, and yet the scheduler runs
after -every- deletion...  (Okay, I guess if you have some hand-made
Power Search rule that implements either of the above bits of
functionality, there's no way for the scheduler to know it's there
and thus it has to be maximally pessimistic about what a deletion
might do.  Surely there must be a way to optimize this for the common
case, though.)]

Yes, this makes deleting things by hand from the frontend an exercise
in aggravation---the first one returns to the UI almost immediately,
but the second one can take a while, and by the time we get to the
third one, the UI is just hung for a minute or so until all the
reschedules are processed.  (And lord help you if you pick the Delete
option for something that's in the process of being recorded---that
seems to hang the UI for at least -twice- as long, as if it's running
the scheduler once when the recording gets stopped, then immediately
again when the recording gets deleted, and even if it's the -only-
deletion you're doing, the popup won't pop down and you won't get your
UI back until it's completely done, a couple of minutes later...)

But queuing them up and using mythweb to delete them (via a script
that calls wget---cheesy but easy to write, and I do this because of
some custom automation we use here) is fraught as well, because not
only will the wgets jam up in the same way, waiting for the scheduler,
but mythweb is apparently able to provoke some sort of race that will
CRASH THE BACKEND if they're allowed to come in too quickly.  And even
with a careful script that (a) waits 30 seconds between deletions and
(b) retries deletions if the relevant video file is still in the
backend filesystem (e.g., hasn't been deleted yet because mythweb
timed out 'cause the scheduler was still chewing on the -last-
deletion and the requested current deletion never happened), it's also
possible to crash the backend if I do anything on the -frontend- like
skipping forward while watching something.

[In fact, the only backend crashes I've -ever- had were during scheduling.]

I'm still running an old release (0.18.1), but I'm -assuming- that at
least this mass-deletion issue might be addressable once I move to
0.20.x, because in theory I should be able to put everything I'd like
to delete into a single program group and then delete the entire group
as a unit---I'm presuming that moving things between groups does -not-
cause a scheduler run and that deleting a group does all the deletions
before trying to reschedule, but I don't have firsthand experience
yet.  And of course it doesn't help at all the issue of the UI
becoming unresponsive for a minute at the top and bottom of many
hours...

[I was hoping that this race/crash issue was solved in 0.20.x, but
recent threads about simply querying status crashing the backend make
me believe that it hasn't been solved.  If someone thinks they'll be
spending the time to track this down, I can provide some procedures
that are extremely likely to crash your backend, so perhaps this can
be debugged---again, probably best on -dev.]

(*) And yes, I've documented in other threads that I've done all the
"recommended practices" re where the DB is, how it's optimized, MySQL
configuration, etc etc---but I'm still using MyISAM for all the tables.


More information about the mythtv-users mailing list