[mythtv-users] Very large logs seen in mythconverg database

[Dan Wilga]

On 2/19/13 1:25 PM, Michael T. Dean wrote:
> On 02/19/2013 01:12 PM, Dan Bernard wrote:
>> I am running mythtv 0.25.2, and have had it running for maybe 6 
>> months with up to 15 recordings per week, and not using any schedules 
>> (all manual), on a single tuner (hdhomerun).  I only keep recordings 
>> for 10 days, so around 30 show up in the list normally.
>>
>> I noticed that my available hard drive space had dropped somewhat, so 
>> I checked the mysql/mythconverg database to see how much space that 
>> was taking up.  Here are the largest 4 files:
>> -rw-rw---- 1 mysql mysql 3.1M Feb 18 23:35 recordedseek.MYD
>> -rw-rw---- 1 mysql mysql 3.1M Feb 18 23:35 recordedseek.MYI
>> -rw-rw---- 1 mysql mysql 621M Feb 19 11:53 logging.MYI
>> -rw-rw---- 1 mysql mysql 1.7G Feb 19 11:53 logging.MYD
>>
>> Why are those two logging files taking up over 2GB?  I'm not 
>> recording a lot of shows, so I'm surprised at the large size.  I have 
>> a very vanilla  install on Linux Mint 13.  Maybe that's normal, but I 
>> wanted to ask if anybody considers that strange behavior..  I can't 
>> imagine that keeping >2GB of logs is necessary.
>>
>> Thanks for your help!
>
> Please post the output of the following commands (which you should be 
> able to paste into the system shell--not directly into a mysql terminal):
>
> mysql -umythtv -p mythconverg -e 'SHOW CREATE TABLE logging\G'
> mysql -umythtv -p mythconverg -e 'SHOW TABLE STATUS LIKE 'logging'\G'
> mysql -umythtv -p mythconverg -e 'SELECT COUNT(*) FROM logging\G'
> mysql -umythtv -p mythconverg -e 'SELECT MAX(id) FROM logging\G'
>
> I'm pretty sure I know what's going on, but this will allow me to 
> confirm the theory.
Mike and I are probably thinking the same thing: The table files are 
likely to be mostly empty space. Basically, Myth has deleted a bunch of 
data from them during the normal housekeeping task, but the remaining 
rows are in a much later part of the file on disk, so the space on disk 
leading up to the current data is still in use.

The way around this problem would be to run something like this once a week:

   mysql -umythtv -p mythconverg -e 'OPTIMIZE TABLE logging'

There's actually a script somewhere in the wiki (or maybe contrib) that 
will do this to all of your tables, which is not a bad idea.

-- 
Dan Wilga                                                        "Ook."