[mythtv-users] Mythfilldatabase taking FOREVER

Tue Jun 14 01:50:46 UTC 2022

On Mon, Jun 13, 2022 at 9:40 PM Larry Kennedy <lunchtimelarry at gmail.com>
wrote:

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

On second look, queries are slow too.  Select from Person can take upwards
of 400ms:

2022-06-13 21:33:10.412506 I  MSqlQuery::exec(DBManager1) SELECT person
FROM people WHERE name = 'Angel Parker' <<<< Took 323ms, Returned 1 row(s)

If I run these same queries from SQL Workbench on a windows desktop, they
are immediate, perhaps from caching. But I suspect something else might be
going on..

> 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/291ef2db/attachment.htm>