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

[Michael T. Dean]

On 02/19/2013 02:48 PM, Dan Wilga wrote:
> 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.

Yes, but there's more than one possible cause (though it's almost 
certain that the "other" cause isn't the problem for Dan B).  Please 
provide the information I requested, Dan B., so I run through all the 
details and provide a complete answer that will help anyone with the 
same issue--allowing them to diagnose their own problem and fix it.

>
> 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.
>

optimize_mythdb.pl in contrib, and it should be packaged with a MythTV 
installation (often in /usr/share or /usr/share/docs or ...).  But 
please do not run it until you've provided the above-requested info.  :)

Thanks,
Mike