[mythtv-users] Database sizes

[Michael T. Dean]

On 08/31/2007 02:19 PM, Gareth Glaccum wrote:
> Have searched through the threads, nothing really jumps out to answer....
> How large would you expect the database to be on an 8 month old system?

Really, database size doesn't change much with usage.  The one factor
that does have a large impact on database size is the total duration of
all (especially MPEG-2) recordings.  So, if you have 10 hours of
recordings, you'll have a small DB, if you have 400 hours of recordings,
it will be significantly larger.

>  I have been running a system since around the turn of the year, FC5 I think (maybe 6, not really of consequence) I am currently in the process of moving the database and the capture devices to a new box. Access to the database has been generally getting slower recently, as has deleting files.
> I thought that dumping the database, renaming everyting that was computer name specific (via sed) and then importing on the new machine would be a good way of doing things. 
> When the database was dumped (only mythconverg at the moment) the size is 168Mb. This seems quite excessive. Not determined exactly where the data is yet (EPG?) but is this normal? Is there a good spring clean routine to improve matters (a 168Mb database has got to cause a little bit of lag, my new data store can only do around 300Mb R/W /s non-sequential)

Yes, and based on your e-mail address, I'm guessing you need to use it.  :)

In MythTV 0.20-fixes and below, running mythfilldatabase is mandatory
because it cleans up the database.  Because of the great work Stuart A
and Janne and others have done on the EIT handling in Myth, there are
actually a lot of users who use EIT data only for MythTV.

These users should still run mythfilldatabase "occasionally".  If you
haven't been running mythfilldatabase, you'll need to verify your
configuration to ensure it doesn't break your listings data.  Make sure
all your video sources are configured with the "EIT only" or "No
grabber" grabbers and then run mythfilldatabase.  It will run very
quickly, outputting several messages, but appearing to do very little. 
However, it is actually cleaning a lot of stuff up.

So, how often is "occasionally"?  Well, whenever you feel it would be
helpful.  Some run it in a once/month cron job (which is probably
enough), though once/week wouldn't hurt (once/day wouldn't hurt, but is
probably overkill).

Note that with 0.21, this will no longer be required as the cleanup has
been moved to the backend and will occur daily.

And, once you've deleted a large amount of information from the DB, it
always makes sense to "optimize" the DB.  So, once you've run
mythfilldatabase, run the optimize_mythdb.pl script (from the contrib
directory) and you'll be running with optimum (like new) performance. 
You may also want to put the optimize_mythdb.pl script in a cron job (I
run it daily, though doing so could potentially cause issues if your
system--specifically mysqld--crashes frequently, so you'll have to
decide whether to do this automatically or manually).

Mike