[mythtv-users] Mythfilldatabase taking FOREVER
Larry Kennedy
lunchtimelarry at gmail.com
Tue Jun 14 01:40:16 UTC 2022
On Mon, Jun 13, 2022 at 9:21 PM Bill Meek <keemllib at gmail.com> wrote:
> On 6/13/22 19:54, Larry Kennedy wrote:
> >
> > On Tue, Jun 7, 2022 at 9:52 PM Larry Kennedy <lunchtimelarry at gmail.com
> <mailto:lunchtimelarry at gmail.com>> wrote:
> >
> >
> >
> > On Sun, Jun 5, 2022 at 2:06 PM Gary Buhrmaster <
> gary.buhrmaster at gmail.com <mailto:gary.buhrmaster at gmail.com>> wrote:
> >
> > On Sun, Jun 5, 2022 at 3:06 AM Gary Buhrmaster
> > <gary.buhrmaster at gmail.com <mailto:gary.buhrmaster at gmail.com>>
> wrote:
> >
> > > There are numerous checks to determine if the
> > > existing program is *really* the same (with all the
> > > details). Sometimes it is, sometimes it is not
> > > ("generic" programs such as news often get
> > > updates as the time gets closer to the "now"
> > > value).
> >
> > And, as I recall, the generic (daily) news programs
> > can sometimes be marked as repeats, and
> > sometimes not, due to the logic that mythfilldatabase
> > and the upstream scheduling source use.
> >
> > > And mythfilldatabase does some additional
> > > adjustments of the data that can (next run) result
> > > in detections of changes. Some of which was
> > > improved with recent (supported) releases of
> > > MythTV.
> >
> > At the end of the run, there is a line of the form:
> > Updated programs: nnnnn Unchanged programs: nnnnnn
> > which indicates how many programs were updated.
> >
> > If you run the same mythfilldatabase load immediately
> > again (which implies the same source guide data) you
> > will likely see a small (but persistent) number of updates
> > due to how MythTV manages the program data and
> > detection of changes (which require updates). For
> > a daily run, ~10% changes for Schedules Direct is
> > not atypical as generic placeholder show data get
> > replaced with actual episode data for the particular
> > showings and new data for an additional day.
> >
> > Running a recent mythfilldatabase and a recent
> > version of the grabber, which together support
> > (mostly) accurate new (previouslyshown) marking,
> > I tended to use --no-mark-repeats to prevent
> > mythfilldatabase from "improving" the program
> > data (you can also modify the database setting
> > NewEpisodeWindow to accomplish something
> > equivalent, both mostly only matter because
> > Schedules Direct actually often has more than
> > 14 days of program data, and using/changing
> > either may cause different issues depending
> > on the details (and --no-mark-repeats had
> > a specific bug when used in older versions)).
> >
> > Lastly, sometimes database tuning may be
> > appropriate. From time to time there are
> > discussions on the MythTV email lists, or
> > forum, regarding database tuning, Running
> > mysqltuner.pl <http://mysqltuner.pl> can point out specific
> cases
> > which may be causing issues (just be aware
> > that not everything mentioned by that script
> > is actually going to be an issue for MythTV).
> >
> >
> > OK, Gary, I bit the bullet and upgraded to Ubuntu 20 LTS and Mythtv
> 31. I'm sure that will help long term, but it sure created some
> > additional drama :)
> >
> > I found some old tuning parameters in mysql.cnf that I think were
> problematic (thanks Bill from another post of yours I stumbled across). I
> > removed those and it seems to help. MFDB logging shows that
> inserts/deletes are now taking 1 second or less, as seen here:
> >
> > 2022-06-07 21:47:44.317605 I Removing existing program:
> 2022-06-13T14:00:00Z - 2022-06-13T15:00:00Z
> I57391.json.schedulesdirect.org
> > <http://I57391.json.schedulesdirect.org> Little People, Big World
> > 2022-06-07 21:47:45.123297 I Inserting new program :
> 2022-06-13T14:00:00Z - 2022-06-13T15:00:00Z
> I57391.json.schedulesdirect.org
> > <http://I57391.json.schedulesdirect.org> Little People, Big World
> > 2022-06-07 21:47:47.038876 I Removing existing program:
> 2022-06-13T15:00:00Z - 2022-06-13T16:00:00Z
> I57391.json.schedulesdirect.org
> > <http://I57391.json.schedulesdirect.org> Little People, Big World
> > 2022-06-07 21:47:47.927278 I Inserting new program :
> 2022-06-13T15:00:00Z - 2022-06-13T16:00:00Z
> I57391.json.schedulesdirect.org
> > <http://I57391.json.schedulesdirect.org> Little People, Big World
> > 2022-06-07 21:47:49.853728 I Removing existing program:
> 2022-06-13T18:00:00Z - 2022-06-13T19:00:00Z
> I57391.json.schedulesdirect.org
> > <http://I57391.json.schedulesdirect.org> 90 Day Diaries
> > 2022-06-07 21:47:50.308645 I Inserting new program :
> 2022-06-13T18:00:00Z - 2022-06-13T19:00:00Z
> I57391.json.schedulesdirect.org
> > <http://I57391.json.schedulesdirect.org> 90 Day Diaries
> >
> > Still, this is going to take longer than one would think necessary.
> Especially, given that this is not the first run following the upgrade.
> >
> > I have a few questions about why MFDB is slow:
> >
> > 1. Does the number of channels in the Sqlite database matter? I
> have 1,000 channels of which only 90 are marked as "selected." Should
> > I/can I delete the unused channels?
> > 2. Does the number of channels in the mythtv database matter? I
> have 1,300 channels, of which 100 or so are marked as "visible." Should
> > I/can I delete the unused channels?
> > 3. I noticed that the XMLTV download to /tmp had over 1 million
> lines, per "cat /tmp/xxx | wc". That seems large.
> >
> > Which of these is possibly slowing me down? I assume #1, so I tried
> to delete "unselected" channels from the sqlite database, but they got
> > reloaded later. I can't recall what command I ran that caused the
> reload, or if this was automatic and unavoidable. Thoughts?
> >
> >
> > Larry
> >
> > _______________________________________________
> > mythtv-users mailing list
> > mythtv-users at mythtv.org <mailto:mythtv-users at mythtv.org>
> > http://lists.mythtv.org/mailman/listinfo/mythtv-users <
> http://lists.mythtv.org/mailman/listinfo/mythtv-users>
> > http://wiki.mythtv.org/Mailing_List_etiquette <
> http://wiki.mythtv.org/Mailing_List_etiquette>
> > MythTV Forums: https://forum.mythtv.org <
> https://forum.mythtv.org>
> >
> >
> > As recommended, I've upgraded: the O/S is LTS 20.04.4, MariaDB is
> 10.3.34, and Mythtv is V31 w/fixes. XMLTV module is version 0.6.1 and
> > tv_grab_zz_sdjson_sqlite is version 1.66.
> >
> > However, mythfilldatabase still takes over 6 hours daily. Can anyone
> say if this is normal? Top shows a low level of CPU for all related
> > processes.
> >
> > One side effect of all this is the database wait timeout issue that
> nukes the upcoming recordings. I think I've addressed that with a global
> > change to bump up the default 8hour timeout. Time will tell.
> >
> > Here is a snippet of the log showing where mythfilldatabase spent time:
> >
> > 2022-06-13 01:01:15.313333 I Cardutil: HDHomeRun Cablecard Present.
> > 2022-06-13 01:01:15.333400 I Cardutil: HDHomeRun Cablecard Present.
> > 2022-06-13 01:01:15.353206 I Cardutil: HDHomeRun Cablecard Present.
> > 2022-06-13 07:33:51.007237 I Updated programs: 5891 Unchanged programs:
> 32563
> > 2022-06-13 07:33:51.865934 N Data fetching complete.
> > 2022-06-13 07:33:51.866108 I Adjusting program database end times.
> > 2022-06-13 07:33:53.149755 I 0 replacements made
> >
> > I can add more logging if anyone has suggestions on what to look for. I
> just can't see why this process should take more than 15 minutes to
> > perform a daily incremental change.
>
> I recall your saying you have 100ish channels marked as selected in the
> SQLite DB.
>
> Is there exactly one SchedulesDirect.DB file? locate SchedulesDirect.DB
> finds all files of that name in *buntu at least.
>
> --
> Bill
>
Bill, there is one sqlite database.
I'll note that running the grabber takes less than a minute. I just ran it
manually like so:
tv_grab_zz_sdjson_sqlite --config-file
~/.xmltv/tv_grab_zz_sdjson_sqlite.conf --output /tmp/lmk2.xml
Then, I ran mythfilldatbase manually:
mythfilldatabase -v xmltv,database --file --only-update-guide --sourceid 2
--xmlfile /tmp/lmk2.xml
Queries are fast, but I'm seeing inserts/replace commands that take over
800ms, so that doesn't bode well for potentially thousands of inserts:
2022-06-13 21:33:36.706803 I MSqlQuery::exec(DBManager1) REPLACE INTO
credits ( person, chanid, starttime, role) VALUES ('6708',
'20892', '2022-06-30T10:00:00.000Z', 'producer') <<<< Took 866ms
That suggests a mysql/mariadb database issue, no?
Larry
_______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20220613/0a852f1e/attachment-0001.htm>
More information about the mythtv-users
mailing list