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

[David Campbell]

f-myth-users at media.mit.edu wrote:
>     > 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.

What do you suggest as an alternative - Postgres, SQL lite - Firebird?

I have never heard of daily optimisation to get MYSQL to perform and I 
look after a few 100 req/sec installs.

Dave