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

[Michael T. Dean]

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...

REPAIR TABLE works only for MyISAM and ARCHIVE storage engines, so it 
would fail for any users who have switched to InnoDB, MERGE, BDB or 
whatever.

OPTIMIZE TABLE works only for MyISAM, BDB, and InnoDB.  For BDB, 
OPTIMIZE TABLE is mapped to ANALYZE TABLE.

ANALYZE TABLE works with MyISAM, BDB, and (as of MySQL 4.0.13) InnoDB 
tables.

Although it would be possible to ignore failures of these commands, it 
doesn't make any sense to run them daily on unsupported storage engines.

Also, if we were running a "hidden" REPAIR table and the server crashed, 
another REPAIR table should be executed immediately upon server startup 
(before reading from the table) or there could be data loss.  Because 
Myth uses tables in the database to get information it needs before 
housekeeping begins, Myth can't guarantee this will happen.  However, if 
the user /chooses/ to run REPAIR table on her own, she should understand 
the importance of "cleaning up the mess" if the server dies.

So, other options would include allowing the user to specify a script 
that can be run at particular times (i.e. one on server startup and 
another for daily cleanup) and providing sample scripts that do the 
REPAIR on startup and OPTIMIZE daily.

The server startup script is unnecessary because, generally, mythbackend 
itself is started from a startup script--which could be modified to 
execute any additional commands/scripts the user desires (i.e. 
mysqlcheck/optimize_mythdb.pl/whatever).

The daily cleanup script is "somewhat" useful, but because the user can 
specify a script name to run for mythfilldatabase, it's probably not 
necessary.  Granted, mythfilldatabase is only run on the master backend, 
but I really can't think of a reason to run a daily script on a slave 
backend.

Even though the help text for the "mythfilldatabase Program" field in 
frontend settings doesn't explicitly specify the possibility of doing a 
REPAIR/OPTIMIZE/ANALYZE, changing it to do so is probably not worthwhile 
as it makes the help text more complex.  A user just trying to get 
things working shouldn't have to worry about the "fine-tuning"--that can 
come later as he is reading the HOWTO/wiki/lists/...

So, although letting Myth worry about maintaining the database integrity 
sounds good in theory, due to differences in users' configurations, I 
think we need to leave things as they are--such that REPAIR's and 
OPTIMIZE's and ANALYZE's are the user's responsibility.

Mike