<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Jun 13, 2022 at 9:40 PM Larry Kennedy <<a href="mailto:lunchtimelarry@gmail.com">lunchtimelarry@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Jun 13, 2022 at 9:21 PM Bill Meek <<a href="mailto:keemllib@gmail.com" target="_blank">keemllib@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On 6/13/22 19:54, Larry Kennedy wrote:<br>
> <br>
> On Tue, Jun 7, 2022 at 9:52 PM Larry Kennedy <<a href="mailto:lunchtimelarry@gmail.com" target="_blank">lunchtimelarry@gmail.com</a> <mailto:<a href="mailto:lunchtimelarry@gmail.com" target="_blank">lunchtimelarry@gmail.com</a>>> wrote:<br>
> <br>
> <br>
> <br>
> On Sun, Jun 5, 2022 at 2:06 PM Gary Buhrmaster <<a href="mailto:gary.buhrmaster@gmail.com" target="_blank">gary.buhrmaster@gmail.com</a> <mailto:<a href="mailto:gary.buhrmaster@gmail.com" target="_blank">gary.buhrmaster@gmail.com</a>>> wrote:<br>
> <br>
> On Sun, Jun 5, 2022 at 3:06 AM Gary Buhrmaster<br>
> <<a href="mailto:gary.buhrmaster@gmail.com" target="_blank">gary.buhrmaster@gmail.com</a> <mailto:<a href="mailto:gary.buhrmaster@gmail.com" target="_blank">gary.buhrmaster@gmail.com</a>>> wrote:<br>
> <br>
> > There are numerous checks to determine if the<br>
> > existing program is *really* the same (with all the<br>
> > details). Sometimes it is, sometimes it is not<br>
> > ("generic" programs such as news often get<br>
> > updates as the time gets closer to the "now"<br>
> > value).<br>
> <br>
> And, as I recall, the generic (daily) news programs<br>
> can sometimes be marked as repeats, and<br>
> sometimes not, due to the logic that mythfilldatabase<br>
> and the upstream scheduling source use.<br>
> <br>
> > And mythfilldatabase does some additional<br>
> > adjustments of the data that can (next run) result<br>
> > in detections of changes. Some of which was<br>
> > improved with recent (supported) releases of<br>
> > MythTV.<br>
> <br>
> At the end of the run, there is a line of the form:<br>
> Updated programs: nnnnn Unchanged programs: nnnnnn<br>
> which indicates how many programs were updated.<br>
> <br>
> If you run the same mythfilldatabase load immediately<br>
> again (which implies the same source guide data) you<br>
> will likely see a small (but persistent) number of updates<br>
> due to how MythTV manages the program data and<br>
> detection of changes (which require updates). For<br>
> a daily run, ~10% changes for Schedules Direct is<br>
> not atypical as generic placeholder show data get<br>
> replaced with actual episode data for the particular<br>
> showings and new data for an additional day.<br>
> <br>
> Running a recent mythfilldatabase and a recent<br>
> version of the grabber, which together support<br>
> (mostly) accurate new (previouslyshown) marking,<br>
> I tended to use --no-mark-repeats to prevent<br>
> mythfilldatabase from "improving" the program<br>
> data (you can also modify the database setting<br>
> NewEpisodeWindow to accomplish something<br>
> equivalent, both mostly only matter because<br>
> Schedules Direct actually often has more than<br>
> 14 days of program data, and using/changing<br>
> either may cause different issues depending<br>
> on the details (and --no-mark-repeats had<br>
> a specific bug when used in older versions)).<br>
> <br>
> Lastly, sometimes database tuning may be<br>
> appropriate. From time to time there are<br>
> discussions on the MythTV email lists, or<br>
> forum, regarding database tuning, Running<br>
> <a href="http://mysqltuner.pl" rel="noreferrer" target="_blank">mysqltuner.pl</a> <<a href="http://mysqltuner.pl" rel="noreferrer" target="_blank">http://mysqltuner.pl</a>> can point out specific cases<br>
> which may be causing issues (just be aware<br>
> that not everything mentioned by that script<br>
> is actually going to be an issue for MythTV).<br>
> <br>
> <br>
> 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<br>
> additional drama :)<br>
> <br>
> 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<br>
> removed those and it seems to help. MFDB logging shows that inserts/deletes are now taking 1 second or less, as seen here:<br>
> <br>
> 2022-06-07 21:47:44.317605 I Removing existing program: 2022-06-13T14:00:00Z - 2022-06-13T15:00:00Z <a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">I57391.json.schedulesdirect.org</a><br>
> <<a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">http://I57391.json.schedulesdirect.org</a>> Little People, Big World<br>
> 2022-06-07 21:47:45.123297 I Inserting new program : 2022-06-13T14:00:00Z - 2022-06-13T15:00:00Z <a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">I57391.json.schedulesdirect.org</a><br>
> <<a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">http://I57391.json.schedulesdirect.org</a>> Little People, Big World<br>
> 2022-06-07 21:47:47.038876 I Removing existing program: 2022-06-13T15:00:00Z - 2022-06-13T16:00:00Z <a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">I57391.json.schedulesdirect.org</a><br>
> <<a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">http://I57391.json.schedulesdirect.org</a>> Little People, Big World<br>
> 2022-06-07 21:47:47.927278 I Inserting new program : 2022-06-13T15:00:00Z - 2022-06-13T16:00:00Z <a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">I57391.json.schedulesdirect.org</a><br>
> <<a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">http://I57391.json.schedulesdirect.org</a>> Little People, Big World<br>
> 2022-06-07 21:47:49.853728 I Removing existing program: 2022-06-13T18:00:00Z - 2022-06-13T19:00:00Z <a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">I57391.json.schedulesdirect.org</a><br>
> <<a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">http://I57391.json.schedulesdirect.org</a>> 90 Day Diaries<br>
> 2022-06-07 21:47:50.308645 I Inserting new program : 2022-06-13T18:00:00Z - 2022-06-13T19:00:00Z <a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">I57391.json.schedulesdirect.org</a><br>
> <<a href="http://I57391.json.schedulesdirect.org" rel="noreferrer" target="_blank">http://I57391.json.schedulesdirect.org</a>> 90 Day Diaries<br>
> <br>
> Still, this is going to take longer than one would think necessary. Especially, given that this is not the first run following the upgrade.<br>
> <br>
> I have a few questions about why MFDB is slow:<br>
> <br>
> 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<br>
> I/can I delete the unused channels?<br>
> 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<br>
> I/can I delete the unused channels?<br>
> 3. I noticed that the XMLTV download to /tmp had over 1 million lines, per "cat /tmp/xxx | wc". That seems large.<br>
> <br>
> 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<br>
> reloaded later. I can't recall what command I ran that caused the reload, or if this was automatic and unavoidable. Thoughts?<br>
> <br>
> <br>
> Larry<br>
> <br>
> _______________________________________________<br>
> mythtv-users mailing list<br>
> <a href="mailto:mythtv-users@mythtv.org" target="_blank">mythtv-users@mythtv.org</a> <mailto:<a href="mailto:mythtv-users@mythtv.org" target="_blank">mythtv-users@mythtv.org</a>><br>
> <a href="http://lists.mythtv.org/mailman/listinfo/mythtv-users" rel="noreferrer" target="_blank">http://lists.mythtv.org/mailman/listinfo/mythtv-users</a> <<a href="http://lists.mythtv.org/mailman/listinfo/mythtv-users" rel="noreferrer" target="_blank">http://lists.mythtv.org/mailman/listinfo/mythtv-users</a>><br>
> <a href="http://wiki.mythtv.org/Mailing_List_etiquette" rel="noreferrer" target="_blank">http://wiki.mythtv.org/Mailing_List_etiquette</a> <<a href="http://wiki.mythtv.org/Mailing_List_etiquette" rel="noreferrer" target="_blank">http://wiki.mythtv.org/Mailing_List_etiquette</a>><br>
> MythTV Forums: <a href="https://forum.mythtv.org" rel="noreferrer" target="_blank">https://forum.mythtv.org</a> <<a href="https://forum.mythtv.org" rel="noreferrer" target="_blank">https://forum.mythtv.org</a>><br>
> <br>
> <br>
> 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 <br>
> tv_grab_zz_sdjson_sqlite is version 1.66.<br>
> <br>
> 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 <br>
> processes.<br>
> <br>
> 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 <br>
> change to bump up the default 8hour timeout. Time will tell.<br>
> <br>
> Here is a snippet of the log showing where mythfilldatabase spent time:<br>
> <br>
> 2022-06-13 01:01:15.313333 I Cardutil: HDHomeRun Cablecard Present.<br>
> 2022-06-13 01:01:15.333400 I Cardutil: HDHomeRun Cablecard Present.<br>
> 2022-06-13 01:01:15.353206 I Cardutil: HDHomeRun Cablecard Present.<br>
> 2022-06-13 07:33:51.007237 I Updated programs: 5891 Unchanged programs: 32563<br>
> 2022-06-13 07:33:51.865934 N Data fetching complete.<br>
> 2022-06-13 07:33:51.866108 I Adjusting program database end times.<br>
> 2022-06-13 07:33:53.149755 I 0 replacements made<br>
> <br>
> 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 <br>
> perform a daily incremental change.<br>
<br>
I recall your saying you have 100ish channels marked as selected in the SQLite DB.<br>
<br>
Is there exactly one SchedulesDirect.DB file? locate SchedulesDirect.DB<br>
finds all files of that name in *buntu at least.<br>
<br>
-- <br>
Bill<br></blockquote><div><br></div><div>Bill, there is one sqlite database. <br></div><div><br></div><div>I'll note that running the grabber takes less than a minute. I just ran it manually like so: <br></div><div><br></div><div>tv_grab_zz_sdjson_sqlite --config-file ~/.xmltv/tv_grab_zz_sdjson_sqlite.conf --output /tmp/lmk2.xml</div><div><br></div><div>Then, I ran mythfilldatbase manually: <br></div><div><br></div><div>mythfilldatabase -v xmltv,database --file --only-update-guide --sourceid 2 --xmlfile /tmp/lmk2.xml</div><div><br></div><div>Queries are fast,</div></div></div></blockquote><div> </div><div> </div><div>On second look, queries are slow too. Select from Person can take upwards of 400ms: <br></div><div><br></div><div>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)</div><div><br></div><div>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..<br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div class="gmail_quote"><div> but I'm seeing inserts/replace commands that take over 800ms, so that doesn't bode well for potentially thousands of inserts:<br></div><div><br></div><div>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</div><div><br></div><div>That suggests a mysql/mariadb database issue, no?</div><div><br></div><div>Larry<br></div><div><br></div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
_______________________________________________<br>
mythtv-users mailing list<br>
<a href="mailto:mythtv-users@mythtv.org" target="_blank">mythtv-users@mythtv.org</a><br>
<a href="http://lists.mythtv.org/mailman/listinfo/mythtv-users" rel="noreferrer" target="_blank">http://lists.mythtv.org/mailman/listinfo/mythtv-users</a><br>
<a href="http://wiki.mythtv.org/Mailing_List_etiquette" rel="noreferrer" target="_blank">http://wiki.mythtv.org/Mailing_List_etiquette</a><br>
MythTV Forums: <a href="https://forum.mythtv.org" rel="noreferrer" target="_blank">https://forum.mythtv.org</a><br>
</blockquote></div></div>
</blockquote></div></div>