[mythtv-users] Slow MySQL query after delete

Larry K lunchtimelarry at gmail.com
Fri Nov 30 15:51:11 UTC 2007


I agree that a condescending tone is unnecessary in this forum.  It also
appears that some participants attempt to obfuscate the situation rather
than offer clear, direct advice.  All I (we) need is a little guidance from
those who understand how this thing is designed (and therefore understand
its flaws).

Assuming the BUSQ is executed by the BE and not the FE, I don't understand
why a FE action (i.e., my attempt to navigate through the recordings menu
following a delete) would hang if a BE action (i.e., rescheduler) is
triggered to perform a task (i.e., BUSQ).  Aren't these things running
independently and asynchronously?   Unless they are somehow locking each
other inside the database, I wonder why they are affecting each other.   I
come from a Sybase/Oracle point of view on this, but I suppose it is
possible that the BUSQ could be holding share locks that prevent another
database transaction from acquiring exclusive locks necessary for an
update/delete.  But I am not sure what this could be given the use-case in
question here.  Is it possible that something else is blocking the FE action
apart from the BUSQ?  Could the BUSQ be a red herring?

FYI, I enabled slow deletes and so far I am not convinced it has helped
any.  This was a useful suggestion that perhaps I dismissed prematurely.

Several people have suggested we convert the recordedseek table to innoDB,
but that table does not participate in the BUSQ.  Further, this table has
never shown up in my slow-sql log, nor have I seen any evidence that the
BUSQ (or any other query) is blocked by another database activity using the
recordedseek table.  The BUSQ consistently runs in 7 seconds, even if I run
it ad-hoc when nothing else is going on.  I remain puzzled as to how the
recordedseek table could be a factor.  Additionally, I know that making
changes to the schema *can* break future upgrades, so I am reluctant to do
this unless it is absolutely necessary.  Is this innoDB change one that
could break the next upgrade?  I suppose if it is, I could just convert it
back and then do the upgrade....

Larry

On 11/30/07, f-myth-users at media.mit.edu <f-myth-users at media.mit.edu> wrote:
>
>     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.
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20071130/12f102ff/attachment.htm 


More information about the mythtv-users mailing list