[mythtv-users] How to clean up database?

[R. G. Newbury]

Jean-Yves Avenard wrote:
> Hi
> 
> 2009/3/12 Nick Morrott <knowledgejunkie at gmail.com>
> 
>> 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.
>>
> 
> 1.2TB worth ... I keep the original MPEG2 (from DVB-T) file.
> 
> Though I could use transcode, but transcode always gave me errors and never
> ever completed.
> 
> 
>> 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.
>>
>>
> Well, I don't care much about the size of the database ... More like I would
> like to start as if it was a brand new mythtv installation, without any of
> the old settings that came from 0.18 and got dragged through.

Two major space wasters are the credits and people tables. They grow as 
more programs are recorded. Those tables do not seem to be compressed or 
  have records deleted.

The program table is reasonably static in size as it just holds 2 weeks 
info. But if you were doing a new install, you could reasonably drop 
that table, before transferring the backup to the new machine.

If you just want to remove the old settings, then use mysqldump to dump 
your database, then edit out the table data for the settings table. and 
restore the database. You can delete the card and source data in 
mythtv-setup.

Geoff



-- 
         Please let me know if anything I say offends you.
          I may wish to offend you again in the future.

          Tux says: "Be regular. Eat cron flakes."