[mythtv-users] Slow MySQL query after delete

[f-myth-users at media.mit.edu]

    > Date: Sat, 01 Dec 2007 00:17:43 -0500
    > From: "Michael T. Dean" <mtdean at thirdcontact.com>

    > On 11/30/2007 05:12 AM, f-myth-users at media.mit.edu wrote:
    > > It is also completely unreasonable for you to ignore and dismiss all
    > > the evidence people are handing you that RESCHEDULES ARE SLOW even
    > > if one has ARLEADY DONE all of the recommended screwing around.

    > 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," before it's apparent
    > that my system can somehow run a "SLOW" reschedule without affecting UI
    > responsiveness?  Am I the only one to whom this implies that the "SLOW"
    > reschedule is /not/ the issue?

There are two points here.  One is that reschedules are slow no matter
what you're doing, for some users.  (E.g., just ignore that this
thread started out talking about deletions and still mentions them in
the subject line.)  This slowness is the cause of numerous problems.
For example, note my comments that I cannot ask my FE to skip, pause,
or stop or start playing during a reschedule, including reschedules
caused because some program being recorded has just finished.  Since
skipping around in playback should have -nothing- to do with what's
being scheduled to record in the future, the fact that there's -any-
interaction here is symptomatic of some sort of misdesign.

The second is that deletions -will- cause a reschedule, so the above
scheduling problems are going to bite on a deletion, no matter how
fast a deletion otherwise runs.  (Even if it -wasn't- locking the
UI---but my UI is certainly locked---it would lock other things, such
as making any changes to the current recording rules, etc.)

Now, as for your own performance, can you please run a couple of tests
and let us know how -your- system performs?

(1) Pick five recordings you don't care about.  Make sure they are not
manual recordings.  (I don't remember whether the scheduler runs after
these & haven't tried retesting.)  Using the frontend (not mythweb),
delete them as fast as you can, individually (e.g., don't cheat and
put them into a single recording group and then delete that).  Do you
see the UI pause at any point and prevent you from continuing?

(2) If the answer to this is "no", turn off slow deletes and try
again with five more.  If the answer is -still- no, then something
very odd indeed is going on.

If the answer at any point becomes "yes", try renaming the actual
video files, touch new ones into their place, and repeat the test.
(This eliminates your ext3fs filesystem from the benchmark; it should
also allow you to turn slow deletes on & off to eliminate it from the
discussion.)

If you cannot ever get the UI to pause while doing this, I'm sure many
of us would be interested to know the size of your oldrecorded tables
and the number and makeup of your recording rules, as mentioned in
that old thread about scheduler performance from a few months ago.
Also interesting would be the match/place lines from your sched logs,
and the contents of your MySQL conf (e.g., memory allocations, etc).

    > > (Ignore the red herring of "deletions" for the moment, just to
    > > simplify the discussion---the only relevant point there is that
    > > deletions force a reschedule.)

    > Never optimize before profiling.  IMHO, picking something--no matter how
    > "likely" to be the problem--and changing it without actually proving it
    > is the source of the problem is just a waste of time.

You seem to be completely ignoring the fact that you've been told
repeatedly that simple reschedule -is- a big problem, whether or not
a deletion is happening.  Sure, the OP was talking about deletion
originally, but I and others have been talking about scheduling
-in general-.  Do we have to put this in a separate thread just
to make it clear to you what we're talking about?

(For example:  If I create a recording rule for something via mythweb,
no further scheduling-related actions in mythweb will complete without
waiting 30-60 seconds for the scheduler run to completion.  If I try, my
browser will either hang, or report incomplete information (e.g., trying
"Find other showings of this program" will show -none- of them scheduled 
to record until the scheduler catches up; clicking on the program I just
scheduled will present the mythweb screen one gets when it is -not-
being scheduled [no "record never/record this one in particular"], etc).
This means that doing schedule maintenance is quite slow, because every 
change involves waiting 30-60 seconds before the next one is allowed.
This is also true if I've just deleted something, or if a program has
just finished recording, because both of those run the scheduler as well.)

    > Oh, and--just in case--saying that a 0.18.1 system is slow is adding
    > nothing to the argument of whether Myth needs fixing in
    > November/December, 2007.

The devs who reported slow scheduling behavior in the threads around
March certainly weren't running 0.18.1, and as far as I'm aware there
haven't been spectacular improvements in the scheduler since then,
though there have been some optimizations.  Users running 0.20.x and
SVN who've been reporting slow scheduler performance would also seem
to agree with that, no matter how fast -your- queries are running.

P.S.  One way of verifying your contention that it's the actual
removal of the recordedseek info that's causing deletions to
be slow would be to have the OP manually delete those entries
(via the appropriate SQL command) and wait (say) 30 seconds
-before- trying a deletion.  If the delete is still slow, then
the recordedseek hypothesis is busted.  (A better test would be
to do this for several programs and then delete them all as fast
as possible; an even better test would (a) zero out the video
files first and (b) include backing up the DB beforehand... :)