[mythtv] Mythtv database indices

[faginbagin]

On 7/14/2020 5:07 PM, Peter Bennett wrote:
> 
> On 7/14/20 1:37 PM, Thomas Lunde wrote:
>> Hi Peter -
>>
>> I'm not a Mythtv dev, but check in on the list from time to time and
>> your recent message below on adding an index to recordedartwork and
>> the resulting peformance change was really interesting.
>>
>> I'm wondering if there are other places that indices might be useful.
>> When someone starts our front end, there's a multi-second pause before
>> the frontend software notices the back end and then a very notable
>> pause (might be 15-60 seconds) before it starts enumerating the
>> available recordings and making them available for playback.  This
>> isn't the regular front end, though.
>>
>> My system might be a bit unusual, but I don't think that it's that far
>> out in left field.  I've been using Myth since about 2002 and now my
>> family very much relies on it.  One of the few areas that it draws
>> complaints from my wife and kids is the startup time for interaction.
>> Our backend lives on a linux container running Ubuntu 18.04 which sits
>> on top of a Proxmox system.  There's plenty of CPU cycles on this six
>> core system and plenty of RAM free.  The root for the container sits
>> on an SSD and there's a pair of 6 TB mirrored disks dedicated to
>> storing recordings.  In normal use, the system is only running at
>> single digit CPU usage and iostat doesn't show of bottlenecks.
>>
>> The frontends are AppleTV boxes running MrMC ( https://mrmc.tv/about/
>> ) which, I think, started life as a fork of XBMC / Kodi on Android.
>> It has a plugin for MythTV.  Here's the page for the Kodi version:
>> https://www.kodi.tv/addon/pvr-client/mythtv-pvr-client
>>
>> So, we navigate to the MrMC app on the AppleTV and launch it.  There's
>> that multi-second pause before it and the MythTV backend "notice each
>> other" which isn't likely to be helped by database indices.  After
>> they start "talking" I can observe each show that we've recorded be
>> named as it builds the available list of recordings to pick from.
>> After15-60 seconds, it knows enough about the shows to make the
>> Default recording group appear and have items be selectable.
>>
>> Anyway, this has gotten long and I know that there are a number of
>> potential pieces of software / integration points that would make this
>> hard to troubleshoot.  But, I'm hoping that your database fix might
>> help us.
>>
>> Please let me know if there's any way that I can help this along other
>> than cheering on your work.  :-)
>>
>> Thanks
>> Thomas
>>
>> Original message follows:
>>
>> [mythtv] Database version update
>>
>> Peter Bennett pb.mythtv at gmail.com
>> Wed Jul 1 19:35:15 UTC 2020
>>
>> Next message (by thread): [mythtv] Database version update
>> Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
>>
>> ________________________________
>>
>> I plan to update the database version in master to 1363, to add an index
>> to recordedartwork.
>>
>> The /Dvr/GetRecordedList service takes 45 seconds on my prod system.
>> This change reduces it to 5 seconds. recordedartwork has no index and
>> the /Dvr/GetRecordedList searches recordedartwork multiple times during
>> execution.
>>
>> CREATE INDEX recordedartwork_ix1 ON recordedartwork (inetref);
>>
>> Anybody have any issues with that?
>>
>> Peter
> 
> I noticed that leanfront was taking a long time to get its list of recordings, so I ran "top" on the backend database machine while leanfront was loading. I found 100% CPU utilization for 40-odd seconds. If you suspect there is a similar issue you can try that. Another thing you can do is run "show full processlist" against the database while you are starting your frontend. This will show the queries that are running and how long they are taking. This helped me to find the cause of the problem.
> 
> I don't know how the kodi mythtv plugin works so I cannot comment on it specifically.
> 
> Peter

I have noticed that loading the "Watch Videos" window always takes longer on my remote frontends vs the frontend on the master backend. I think the behavior suggests the delay is not the database, rather the it's the time it takes to send information about ALL the videos across my LAN. It seems to me that implementing lazy loading would be the solution. I haven't looked at the code in this regard and don't know how difficult it would be to implement or if it would help either or both "Watch Videos" and "Watch Recordings".