[mythtv-users] Bad mysql performance -- huge oldrecorded -- joins without index

Michael T. Dean mtdean at thirdcontact.com
Thu Jun 9 17:27:29 UTC 2016


On 06/08/2016 07:33 PM, Håkon Alstadheim wrote:
> Den 09. juni 2016 01:11, skrev Håkon Alstadheim:
>> Can't seem to get mythweb to speak english at the moment, below are 
>> the stats you are talking about in Norwegian I believe, after I 
>> deleted oldrecorded from 2001 until june 2013. I also deleted 6 * 365 
>> entries of two daily news-shows, and some other low-hanging-fruit. 
>> phpMyAdmin now says 27 377 rows in oldrecorded. I know I have some 
>> recorded files missing, I usually try running a "find orphans" at 
>> least once a year.
>>
>>
>>     Opptaksstatistikk
>>
>> Antall sendinger: (= number of broadcasts)
>>     1678
>> Antall episoder: (= number of episodes )
>>     26827
>> Første opptak: (= first recording)
>>     Saturday June 1st, 2013
>> Siste opptak: (= last recording )
>>     Wednesday June 8th, 2016
>> Total kjøretid: (= total running time )
>>     3 år 7 dager 22 t 11 minutter
>> Totalt tatt opp: (= total recorded )
>>     2 år 23 dager 2 t 36 minutter
>> Prosent av tiden brukt til å ta opp:
>>     68%
>>
>>
> I just realized you mentioned this report as well:
...
> Only news-shows on the top 8, and that is after I removed a couple. 
> Then Shaun the Sheep at number 9 :-)

So the changes you made to your data completely changed your data--you 
lost the record of the first 12-13 years of use, but what remains seems 
to indicate that your system was actually working properly.  The 
performance problem just came down to your having a ton of recordings 
over the years (and, yes, starting in 2001 is a lot of years--beats me 
by 3 years).

Your channels count (and, therefore, listings count) is almost small 
compared to many users--to the point that even old recording rules won't 
make a huge difference in scheduling times (though still worth cleaning 
up--I like to use MythWeb to get the Recording Schedules page and then 
sort by "Last Recorded" and work my way through the ones that haven't 
recorded in a long time to see which ones to delete).

Therefore, the right solution for you was to clean up your recording 
history.  Likely "Remove all episodes for this title" for just those 
daily news shows would have made a pretty major impact on your 
performance.  If your system is still too sluggish for your liking 
(after cleaning up your recording rules), you'll probably have to 
venture into some of the "outside of MythTV" approaches to improving it 
(i.e. working on improving MySQL server performance and/or upgrading the 
system).  That said, it sounds like things are working well for you now 
and you were mainly looking for information.  I hope we've provided 
sufficient--and sufficiently helpful--information.  At least you'll know 
what to look at in the future if things slow again. :)

Mike


More information about the mythtv-users mailing list