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

[Michael T. Dean]

On 02/19/2013 07:04 PM, Dan Bernard wrote:
>>> * $ mysql -umythtv -p mythconverg -e 'SHOW TABLE STATUS LIKE 'logging'\G'*>>* Enter password:*>>* ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check*>>* the manual that corresponds to your MySQL server version for the right*>>* syntax to use near 'logging' at line 1*
>> Oops, that should have been:
>> mysql -umythtv -p mythconverg -e 'SHOW TABLE STATUS LIKE "logging"\G'
>> (quote issues).  I was hoping to get this output because it would
>> show--in addition to repeating the storage engine information--some size
>> information...  Something to the effect of:
...
>> where the Data_length shows the size of the data file (the .MYD) on disk
>> and the Index_length shows the size of the index file (the .MYI) on
>> disk.  Obviously, yours would show different information.
>> Note, also, that my output shows a Check_time of early this morning.
>> That corresponds to the time when my system runs a daily
>> optimize_mythdb.pl cron job--a time I chose because of the extreme
>> unlikelihood of a recording occurring at that time.
>
> Thanks for the very complete and informative response!
>
> Here is the output of the command that had the syntax error:
> $ mysql -umythtv -p mythconverg -e 'SHOW TABLE STATUS LIKE "logging"\G'
> Enter password:
> *************************** 1. row ***************************
>             Name: logging
>           Engine: MyISAM
>          Version: 10
>       Row_format: Dynamic
>             Rows: 27087
>   Avg_row_length: 174
>      Data_length: 1730321488
> Max_data_length: 281474976710655
>     Index_length: 650248192
>        Data_free: 1725601312
>   Auto_increment: 20519185
>      Create_time: 2012-11-19 09:05:36
>      Update_time: 2013-02-19 17:41:52
>       Check_time: 2013-02-18 08:02:34
>        Collation: utf8_general_ci
>         Checksum: NULL
>   Create_options:
>          Comment:
>
> Let me know if that changes your recommendation, otherwise I'll plan
> to setup a cron job as you suggested.
>

Still the same recommendation.  Though your Check_time is recent, it's 
likely that it was just a simple check.  optimize_mythdb.pl does a 
check, an optimize, and an analyze.  So, in my case, the check was 
performed at basically the same time as the optimize and analyze.  In 
your case, there probably was no optimize or analyze.

So, I'd recommend you run the script once, from the command line 
"manually", and then check the file sizes.  And please report back to 
let us know how it went.  Then, you can set up the cron job for future 
maintenance.

Note that the script will need to be run in an environment that includes 
a HOME that points to a directory with a valid 
${HOME}/.mythtv/config.xml.  It's likely you'll need to specify the HOME 
environment variable in your crontab (for at least this one command).

Mike