[mythtv-users] How to clean up database?

[Nick Morrott]

On 12/03/2009, Jean-Yves Avenard <jyavenard at gmail.com> wrote:
> Hi
>
> 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.

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.

Another sizeable chunk could be the program listings data for your
configured 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.

Nick

-- 
Nick Morrott

MythTV Official wiki:
http://mythtv.org/wiki/
MythTV users list archive:
http://www.gossamer-threads.com/lists/mythtv/users

"An investment in knowledge always pays the best interest." - Benjamin Franklin