[mythtv-users] Slow MySQL query after delete
Michael T. Dean
mtdean at thirdcontact.com
Thu Sep 6 22:16:35 UTC 2007
On 09/06/2007 05:36 PM, f-myth-users at media.mit.edu wrote:
> > Date: Thu, 06 Sep 2007 17:04:32 -0400
> > From: "Michael T. Dean" <mtdean at thirdcontact.com>
> > On 09/06/2007 04:14 PM, f-myth-users at media.mit.edu wrote:
> > > > 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?
> > Quite possible. Someone else mentioned the same. However, the patch
> > will be taking into account the different storage types.
> Oh, whoops. Hadn't considered that. (Does Myth have a "suggested" or
> (gulp) "supported" set of storage engines, or is it claimed that any
> storage engine MySQL supports will "work"? I'm assuming 99% of
> everybody leaves theirs at the default, but of course we don't know.)
I don't know the official policy, but mine is, "Unless you know that you
know better than the MySQL default, the default is the 'suggested'
engine." (I know that I don't know better, so guess what I'm using.)
> (My idea of "configurable" was "read something from the settings
> table", which allows people who really, really care to modify it,
> without having to gum up the UI with it. E.g., just put the number of
> minutes it must run in advance in the DB as a setting and not as some
> figure compiled into the binary. Yes, another of the "let users touch
> the DB directly" applications... :)
> > > 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,
> > Handled by storage groups. Create a "backup" storage group (name not
> > set in stone) to specify the location for backups. If there is no
> > backup storage group, the default storage group will be used.
> (And if there are two?
Depends on what I find in the SG code. :) Could ignore all but the
first writable dir, could alternate.
> When I back up my DB, I do a mysqldump through
> gzip --best into a file, and then copy that file to a different disk.
> I keep backups on the two disks for very different amounts of time
> because they have very different sizes. Other users might well want
> to rsync or scp the backup elsewhere, immediately [rather than poll
> for it, etc]. And the backups have standard yyyymmddhhmmss filenames
> (rather than .1, .2) so they won't rotate and bloat the process that
> backs up the whole machine... and also to make it trivial to find the
> "right" backup if I break something and need to reload the DB.)
These will probably be best considered after the initial work is done
and perhaps as we consider the "generic" housekeeping/jobs support or
whatever. I still need to get the support for job constraints in before
this should happen, anyway.
> > > 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.
> > Again, don't know about to start with, but...
> Just an "off" switch in the DB?
Probably some "just in case" kill switch, but it will be accompanied
(probably on the list) by my pleas for feedback.
> > > 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),
> > I'm thinking greater than that for even the repair/optimize (more like
> > 25min for repair/optimize). mfdb may be more like 55 minutes (though
> > mfdb will be allowed to run when the set aside would prevent a run for a
> > "period"--i.e. if the set aside would mean missing the window to run
> > mfdb that day).
> By "set aside", do you mean "'cause we're recording" or "'cause we're
> not in our preferred runtime window"?
Meaning "time that must exist before the next recording or else we
prefer to wait until after the recording".
> (I'd be a little concerned if, for example, the preferred times kept
> landing on the afternoon and evening---my prime recording times---and
> would thus damage streams as Myth made a last-ditch effort to run mfdb
> at all lest it miss a day. Whereas I typically force runs in the
> morning (but not the wee hours, 'cause we knew that to be bad in the
> DD days) since I know it won't trash recordings then (and I know
> exactly when it'll run and hope to spot the rare recording during
> that time period in advance).)
> > > 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.
> > I don't plan to allow mfdb to ignore preferred download time as some
> > users may have to pay extra tariffs or whatever for downloading outside
> > the specified times. Instead, if they're having recording issues due to
> > the times they've specified, and they want to modify the allowed run
> > times, they'll need to modify those times.
> Ah, we're using different terminology. You're talking about "the
> times they set using mythtv-setup" and I'm talking about the times
> that DD used to (and SD still does?) send during a retrieval that say
> "do your next run no earlier than -this- time".
> So by "preferred times" in my paragraph above, etc, I mean the times
> supplied by the data source, not the windows specified by the user
> (who presumably knows what he's doing and whose preferences should be
Oops. Yeah. I see what you mean, and yes, it will run some things
early if necessary to prevent missing the period (though it will prefer
running them late if possible and without negative consequences).
More information about the mythtv-users