[mythtv-users] Slow MySQL query after delete

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

    Date: Thu, 29 Nov 2007 21:38:39 -0500
    From: "Michael T. Dean" <mtdean at thirdcontact.com>

    > On 11/29/2007 07:55 PM, David Rees wrote:
    > > On Nov 29, 2007 4:41 PM, Michael T. Dean <mtdean at thirdcontact.com> wrote:
    > >   
    > >>>   It is very frustrating to
    > >>> sit and wait up to 10 seconds after every delete (from either the frontend
    > >>> or mythweb).
    > >>>
    > >>> I am open to suggestions as to how I can improve this situation.
    > >>>       
    > >> Do you have your MySQL database on the same drive as your recording
    > >> disk?  If so, that's the first thing you should consider fixing.
    > >>     
    > > It is completely unreasonable to expect someone to have a multiple
    > > disk system to get decent usability from MythTV.

    > IMHO, it is completely unreasonable to expect Myth to be able to make
    > your kernel/filesystem driver/hardware be able to do something that your
    > kernel/filesystem driver/hardware is unable to do.

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.

(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.)

    > >  I would venture to
    > > guess that the number of single disk systems far outnumber the number
    > > of multi-disk systems

    > You do realize that Myth uses a /lot/ of storage space, right?

You do realize that an increasing number of people are starting to
call you on your condescending attitude, right?

(You're not always that way, and you're often helpful.  But you're
also quite often a determined Pollyanna and/or apologist for the
status quo, insisting that -all- user complaints -must- be because the
-user- is somehow confused and that Myth itself must be perfect.  Just
because many users -are- confused is no reason to assume (or insist)
that they -all- are.  I've gritted my teeth, grumbled to myself, and
kept quiet when you've done this, but recent traffic in other threads
shows that others are starting to come out and say it.)

    >                                                                   Do you
    > really think someone buys a new 750GB hard drive, then throws away the
    > 300GB hard drive she was using for Myth?  Or buys a 300GB hard drive and
    > throws away the old 80GB hard drive?  You do realize that most (all?)
    > motherboards come with more than one disk connector, right?

Every additional disk---even if the disk itself is "free"---adds heat,
noise, and power cost; reduces reliability and the ability to add any
-more- disks (due to using up both space and bus connectors); and
complicates the machine's configuration.

Many people are unwilling to make those sorts of sacrifices for what
looks, at the heart of it, to be poor implementation; many others
-cannot- make that sacrifice if their backend is limited in, e.g.,
physical size.

And besides, as my evidence has shown, putting the DB on a separate
spindle doesn't help significantly.  It is NOT, repeat NOT, any sort
of contention for where the disk head is hanging out, unless you count
the contention that MySQL is imposing on ITSELF by executing its queries.

    > Also, you do realize that MySQL is a /network-capable/ database
    > management system, right?  You don't even have to put MySQL on either
    > backend.  It could be on the dedicated frontend.  It could be on another
    > computer somewhere in the house. ...

...adding even -more- heat, power, space, and unreliability, unless
the machine was already in use---in which case it just adds unreliability
and makes it more complicated to take that machine---or any intervening
piece of network hardware--- down for any reason (lest you break Myth's
ability to do -anything- while it's down).

    > Fine.  I really couldn't care less what he does first.  But my point is
    > that he should be trying to fix the MySQL configuration,

And how -exactly- do you recommend that he do that, besides putting it
on a second spindle that he may not have?  And how would you recommend
I fix -my- configuration, given all the info I've given you in previous
threads about all the things I've tried with marginal or zero benefit?
Replies of the form "don't keep so much history", "have fewer channels",
"you can't use even one any-channel rule", and so forth are not going
to get a good reception, since all of those are things Myth is supposed 
to handle well.

There are three fundamental problems here.  Solving any of them would
solve the whole mess:
(a) The BUSQ has very poor performance for some people.
(b) Large portions of Myth hang completely waiting for the BUSQ.
(c) Common UI tasks, such as deletion or scheduling, run the BUSQ.

I frankly think that (b) might be the most tractable, because it
-might- be easier to separate out the BUSQ into another thread and/or
refactor the schema to avoid weird crosslocking of tables, than it
might be to change either (a) or (c).  But that's just a guess.

[If it were up to me, I'd have solved (a) by implementing a
truth-maintenance system to do scheduling, rather than by building
unwieldy DB queries, but since the chances of -that- making it into
Myth are zero I'm not going to waste any time on it.  The current DB
approach is something of a travesty because it has to redo -all- of
its work on -every- reschedule, as if it's coming into the world
brand-new with no prior knowledge; a TMS has the great advantage that
reasonable changes cause small propagations and are very fast; they
even have the advantage that explaining -why- something was or wasn't
scheduled in some way can be derived by traversing the structure and
generating the explanation, which is something that can only be
crudely approximated with the current DB-based approach.]

And of course there's also:
(d) The BUSQ seems to increase some sort of window of vulnerability
    to timing races that can crash the backend if it's accessed by
    something else
...which isn't a performance problem so much as evidence that there's
something screwy going on in a mutex somewhere.

    >                                                          not blaming the
    > BUSQ/arguing with me that the scheduler query is a problem when his

But others, like myself, are arguing that the BUSQ is a big problem
all by itself, and EVEN IF everything else about deletion was instantaneous,
we'd still be seeing UI lockups and generally bad performance, because we
-also- see them in contexts where no deletion is happening, e.g,. when
scheduling new shows via the UI, or when the BUSQ is running autonomously
because some recording has just finished on its own.

    > system executes the query in the same amount of time as mine and my
    > system does /not/ have the "entire system locks up when I try to delete
    > a recording" issue his has.  Also, it doesn't really help that he's
    > refusing to try slow deletes because he has a super-fast XFS filesystem
    > and he's smart enough to know that a slow delete (that will cause the
    > removal of recordedseek/recordedmarkup entries to be delayed about 2min
    > 10sec per GiB of recording) will have no effect on performance of his
    > deletes.

...or maybe he just can't believe that deleting a few thousand items
from a DB can take tens of seconds.  I can't.  If you're saying that
he has to defer that to some random time by using code designed to
work around filesystems that have their own problems, just to cause
the DB update to happen at some indeterminate time in the future, I
can see why he might view this dubiously as a kluge (and, for that
matter, as something that could bite him when he least expects it,
depending on when that actual DB update finally -does- happen).

    > 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.

Various devs commented that they, too, were seeing reschedule times
upwards of 30 seconds; there was a long thread with stats about that
back when the DB-vs-recording-glitches thing was playing out.  I guess
the devs' systems are misconfigured, too.  Maybe you should explain to
them the error of their ways, and perhaps it will enlighten us unwashed
masses, too.