[mythtv-users] Slow MySQL query after delete

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

    > Date: Wed, 05 Sep 2007 13:00:03 -0400
    > From: "Michael T. Dean" <mtdean at thirdcontact.com>

    > On 09/05/2007 12:46 PM, David Brodbeck wrote:
    > > So it's still there, it's just hidden.  I guess if MySQL (or MythTV)  
    > > quietly ran an OPTIMIZE TABLE once a week, instead of my scheduling  
    > > it in cron,

    > MythTV should be doing the OPTIMIZE, ANALYZE, and REPAIR--as well as 

Doesn't OPTIMIZE subsume ANALYZE?  (And presumably the output from your
patch gets logged somewhere---if I have a damaged table that needed
repair, I wanna know about it pronto, because otherwise I might miss
whatever's damaging it---or have some "wierd bug" and always wonder,
"Could it have been a damaged table that was repaired behind my back?")

    > doing automated backups--by 0.21.  (If I can just get some time to
    > finish up the patches.)

I'm not sure if this ever got resolved some months ago when it was
initially discussed, and I can't find anything in the archives, so just
to confirm---it will take care to never do these while any streams are
recording on -any- backend, or due to record "soon", right?  (Where
"soon" is configurable in some way by the user, who is the one in the
best position to know how long these actions should take with the
local configuration.)

Otherwise it will certainly corrupt streams, at least in my setup,
even with the DB on a separate spindle.  This is of course due to the
locking involved and the disk I/O.  (The locking will also tend to
hang the UI, though that's somewhat easier to tolerate, but ideally
of course it'd also be configurable to run at times when the UI is
unlikely to be in use.)

I think I also mentioned that it'd be very nice for scriptable hooks
in this both -before- and -after- the run, e.g., so the freshly-made
backup could be put somewhere else as well, etc, especially if users
disagree about how often they're made, how many get preserved, and
where they go.  I think this is likely to be a large point of
variability and you have to account for people's own local knowledge
about what's where and how much redundancy and history they want.

Lacking noninterference & hooks, then I assume there'll be a way to
turn this off so users who don't like the default can run things the
way they'd like to instead.

P.S.  Ditto for when mfdb runs, and there the "soon" must be
configurable to a much larger value (potentially 10-15 minutes
or more, including time for the download), since mfdb will also
acquire table locks and lead to general disk-thrashing that will
corrupt recordings.  This might include ignoring the preferred
download time if it's the only way to avoid stepping on recordings
without potentially waiting forever.

[Obviously, the "noninterference" logic would be useful for a large
variety of background tasks and it would be nice if there was an easy
way to hook into it---especially from shell scripts and the like, so
quick maintenance operations could be thrown together without
requiring recompiling Myth itself, etc.  Right now I kluge this
by doing what MythWeb would do to get status and then looking for
any tuners marked "in use" or recording "soon" by my own definition,
but that's clumsy.]