[mythtvnz] Database management

[Stephen Worthington]

On Fri, 23 Jul 2021 20:46:59 +1200, you wrote:

>On Fri, 23 Jul 2021 16:39:23 +1200, you wrote:
>
>>
>>On 23/07/21 3:43 pm, Paul wrote:
>>> Hi All
>>>
>>> What is the current set up for database optimisation?
>>>
>>> In Mythbuntu 16.04 I selected "optimise database" in mythbuntu control 
>>> panel and a cron job was set up to run  optimize_mythdb.pl
>>>
>>> I'm using the forked  Mythbuntu control panel in Xubuntu 20.04 ,but it 
>>> has no database set up functions.
>>>
>>> Prior to creating a cron job to run optimize_mythdb.pl, I tried to run 
>>> optimize_mythdb.pl from the command line and I get:
>>>
>>> Can't locate MythTV.pm in @INC (you may need to install the MythTV 
>>> module) (@INC contains: /etc/perl 
>>> /usr/local/lib/x86_64-linux-gnu/perl/5.30.0 
>>> /usr/local/share/perl/5.30.0 /usr/lib/x86_64-linux-gnu/perl5/5.30 
>>> /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.30 
>>> /usr/share/perl/5.30 /usr/local/lib/site_perl 
>>> /usr/lib/x86_64-linux-gnu/perl-base) at 
>>> /usr/share/doc/mythtv-backend/contrib/maintenance/optimize_mythdb.pl 
>>> line 15.
>>>
>>> Perl 5.30 is installed on my system
>>>
>>> I found this thread: 
>>> https://lists.archive.carbon60.com/mythtv/users/628894?search_string=MythTV.pm;#628894 
>>> <https://lists.archive.carbon60.com/mythtv/users/628894?search_string=MythTV.pm;#628894> 
>>>
>>>
>>> Is the solution suggested advisable ,or are  there  other suggestions?
>>>
>>> Thanks
>>>
>>> Paul
>>>
>>Further searching on the web prompted me to check if libmythtv-perl was 
>>installed - which it wasn't.
>>
>>And in spite of things I read but didn't quite understand regarding @INC 
>>being set at compilation , after installing libmythtv-perl ,to my 
>>surprise, optimize_mythdb.pl  now seems to function - at least running 
>>from the command  reports no errors.
>>
>>Cheers
>>
>>Paul
>
>Mythbuntu Control Center has been forked to Mythbuntu Control Panel
>and is now maintained again:
>
>https://www.mythtv.org/wiki/Mythbuntu_Control_Panel
>
>You normally want optimize_mythdb to be run daily - it should be put
>in /etc/cron.daily.  On Ubuntu, you can find the latest version from
>the packages here:
>
>/usr/share/doc/mythtv-backend/contrib/maintenance/optimize_mythdb.pl
>
>I prefer to backup my database daily also, so I copy
>/etc/cron.weekly/mythtv-database to /etc/cron.daily and modify it to
>backup to my backups directory on my big drive on my Windows PC, so I
>get local backups weekly on the MythTV box and daily backups to the
>Windows box.  I use my modified version of mythconverg_backup.pl that
>has an option to put the temporary .sql file in a different location
>from the backup file.  I put the temp file on my local NVMe SSD before
>compressing it over the much slower network connection to my Windows
>box to the mythconverg-*.sql.gz file, which makes the backup of my
>massive database much faster.
>
>http://www.jsw.gen.nz/mythtv/mythconverg_backup_jsw.pl

And presuming that your new system has an SSD, then you probably want
to TRIM the SSD more than once a week, as the MythTV database activity
can use up quite a bit of the erased SSD space.  The default settings
for Ubuntu's fstrim.service and fstrim.timer are to only run fstrim
once a week.  So you should do:

sudo edit fstrim.timer

and put something like this in the systemd override file:

[Unit]
Description=Discard unused blocks once a day

[Timer]
OnCalendar=
OnCalendar=daily
AccuracySec=10m

Or you could add the "discard" option to your SSD partitions in fstab.
Or both, as I am now doing.