[mythtv-users] How to clean up database?

Michael T. Dean mtdean at thirdcontact.com
Thu Mar 12 05:55:04 UTC 2009


On 03/12/2009 01:25 AM, Nick Morrott wrote:
> On 12/03/2009, Jean-Yves Avenard wrote:
>   
>> I've just installed a new mythtv backend from scratch ; and I noticed that
>> some of the configuration screens are different to my old one (which I just
>> upgraded along the years starting with 0.18)
>>
>> A backup of my database is now around 200MB !
>>
>> So I thought of starting from scratch, yet preserving all my recordings,
>> videos metadata and TV programs up to say last month.
>>
>> Not being a mysql wiz ... What would be a good way to do that ?
>>     
> Have you got a lot of current recordings? A good chunk of that 200MiB
> could be the recordedseek information for those recordings. It is
> possible (although not through normal operation) for recordedseek data
> to linger even after the recording has been deleted.
>   

Though in 0.21-fixes and above (at least, might have been in 0.20, 
also), the backend cleans up the orphans in recordedseek (and other 
recorded* tables) daily.

> On my current system, I have 100GB of recordings (~50hrs) in MPEG-2
> format, and the associated recordedseek data is taking up 36MiB with a
> 10MiB overhead. This represents about 45% of the total database size.
>   

On my system with 709 recordings with a total recorded length of 751 
hours 25 minutes:

-rw-rw---- 1 mysql mysql 137172325 2009-03-12 01:12 recordedseek.MYD
-rw-rw---- 1 mysql mysql 125993984 2009-03-12 01:12 recordedseek.MYI

Total size of the mythconverg data directory on my system is 282MiB, 
and--as shown above--250MiB of that is recordedseek.

Also, if you have MythVideo videos for which you've created a seektable, 
these will cause the database size to grow very quickly.

> Another sizeable chunk could be the program listings data for your
> configured channels.
>   

Though that shouldn't be too large...on the order of 1MiB for each 10 
channels.

> You can check your /var/lib/mysql/mythconverg directory and list by
> filesize to see which tables in the DB are taking up the most space.
> IMO it is unlikely (although not impossible) that you will have many
> MiB of associated cruft in other places in the database.

Agreed.  Myth does a very good job of cleaning up the garbage.

Really, Myth's database size reaches a fairly steady state within about 
the first year of use.  At that point nearly all growth is due to 
increasing the number of seconds of recordings in Myth.  So, if you're 
filesystem limited for recordings and/or delete as quickly as you 
record, the database won't really grow in size.

Mike


More information about the mythtv-users mailing list