[mythtv] Mythtv database indices

[Peter Bennett]

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
>
>
>
> ________________________________
>
> Next message (by thread): [mythtv] Database version update
> Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
>
> ________________________________
> More information about the mythtv-dev mailing list

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