[mythtv-users] REPAIR/OPTIMIZE in HouseKeeper (was Re: Running optimize_mythdb.pl before mythfilldatabase)

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

    > Date: Thu, 22 Feb 2007 11:52:23 -0500
    > From: "Michael T. Dean" <mtdean at thirdcontact.com>

    > On 02/19/2007 10:27 PM, Michael T. Dean wrote:
    > > I'm also considering asking Chris Pinkham if he'd be interested in a 
    > > patch that puts the REPAIR and OPTIMIZE in the housekeeper.

    > Looking deeper in this, I'm starting to think this may not be a good idea...

I agree with you that it's not a good idea, and for an entirely
different set of reasons as well.  Here's what I wrote a couple
of days ago, then held off to see if anyone else had some opinions:

No!  Please no!

Unless you can guarantee that the housekeeper will -never- be running
while a recording is in progress (or several minutes beforehand!),
please do NOT add things that lock tables for extended periods of time
and run when the user can't control them.

Otherwise, you WILL trash recordings in progress from anything that
must write to recordedmarkup/recordedseek, at least until the current
issue of interaction between seek inserts & buffer-reading is resolved.
[And remember that, by default in recent releases, mfdb runs at
-random- times chosen by DataDirect, not by the user, hence the user
never knows when a repair/optimize will lock a table and glitch a
recording.]

(I'd argue that Myth should instead use a decent database.  When I
started using Myth, I had no direct experience with MySQL, and figured
that all databases were roughly equal---but that's because I know DB
theory, have used other non-MySQL DB's, and it never occurred to me
that MySQL would fall so flat on a variety of things the theorists had
solved decades ago.  Unfortunately, between MySQL's whole-table-locking
approach [which is half of the problem wrt the current issue with
locking out DB inserts & thus locking out buffer-emptying from IVTV,
as per the discussion of a few weeks ago], and MySQL's need for
constant maintenance (*), I really wish it was possible to use any
other database that isn't so fragile.  I'm quite pleased that usleep
has been trying to add the necessarily hooks to actually enable, e.g.,
postgres or something else to be used.)

(*) E.g., because pretty much every time somebody says anything at all
DB-related, you (mtdean) come back with "have you repaired & optimized?"
It'd be nice to use a database that didn't require -daily- optimization
just to perform well, and I've certainly seen enough mail in the last
year and a half on this list from people saying "Myth is acting weird"
only to discover that their tables are broken that I no longer have
much faith in MySQL actually working well without constant attempts
at repair.  I mean, just imagine how much less traffic there'd be on
the list if the database Myth was using wasn't a continual source of
buggy behavior.