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

[Michael T. Dean]

On 06/08/2016 10:18 AM, John Veness wrote:
> On 08/06/2016 14:55, Michael T. Dean wrote:
>> So, let's think about the number you had. If you record 27 shows 
>> every day of every year, it would still take you more than 10 years 
>> to get 100k entries in your recording history.  Most people don't 
>> record anywhere near 27 shows per day, let alone every day 
>> (especially in mid-season/summer season/...).
>
> I'm not the OP, but I was interested in the "how many shows per day" 
> people record. Before I had kids I probably recorded only a couple of 
> shows per day, but more recently I probably record 20 or so, the 
> majority of which never get watched.

I'm probably recording around 10/day or so now--even though my average 
over 12 years appears closer to 5.  Most of the stuff we record, we 
don't watch, either--we have the system set to record every episode of 
any new series that comes on.  After a while if we decide not to watch a 
show, I remove the recording rule (set to "Do not record") and MENU 
(once or twice)|Add this group to playlist, MENU (once or 
twice)|Playlist Options|Delete and allow re-record. The "allow 
re-record" part is specifically to remove the already-recorded episodes 
that we didn't and won't ever watch from the recording history.  (This 
is why my previous message said, "I have a complete list of every TV 
show I've recorded for watching"--because I've recorded much more for 
"just in case" and decided it wasn't worth watching.)

>
> Specifically, I have dozens of recording rules to record the series 
> that they are interested in. Each rule is set to keep maximum 10 
> episodes, deleting the oldest when the maximum is reached, and I set 
> it to only match duplicates in current recordings (unlike most of my 
> recording rules for the grown-ups, which look for duplicates in 
> current and previous recordings and have no limit).
>
> That way, at any one time, they have the 10 most recent episodes 
> broadcast for a whole load of different shows, from which they watch 
> freely. They don't need to bother to delete and the selection will 
> freshen over time. The selection they get is similar to what can be 
> streamed on demand, without taking up my Internet bandwidth. These are 
> shows with no continuing storyline, so order doesn't matter, and they 
> don't mind watching episodes they've watched before, in fact they love 
> to do so!
>
> I haven't looked in my oldrecorded table recently, but I expect the 
> above behaviour is causing it to grow considerably since adding all 
> these rules. For episodes that have been recorded multiple times, will 
> it be storing multiple rows in oldrecorded?

For every single recording MythTV does, a record is stored in recording 
history.  However, as mentioned above, deleting with "allow re-record" 
removes (all) records of that episode from the recording history.  When 
you set a rule to "Record new and expire old", your system will expire 
the episodes over your max (you'll see in your backend logs "Deleting 
... Too many episodes, we only want to keep <max>").  When recordings 
are expired (as opposed to deleted), they are automatically allowed to 
re-record (all records of that episode are removed from the recording 
history).  However, there is a global option (set it once from any 
machine) in mythfrontend settings that is disabled by default:

Re-record watched
If enabled, programs that have been marked as watched and are 
Auto-Expired will be re-recorded if they are shown again.

If that option is disabled on your system (as was the default) and the 
episodes that are being expired to make room for new episodes are marked 
as watched, then the history for those episodes will not be removed.  
Note that episodes can be manually marked as watched in Watch Recordings 
with MENU (once or twice)|Mark as Watched (or via the Playlist Options 
menu, for a whole playlist at a time), but also will be marked as 
watched if you've enabled:

Automatically mark a recording as watched
If enabled, when you exit near the end of a recording it will be marked 
as watched. The automatic detection is not foolproof, so do not enable 
this setting if you don't want an unwatched recording marked as watched.

in mythfrontend settings (under Playback Settings--by default it is 
disabled) and the kids exit playback near the end of the recording. So, 
with that setting enabled, you may well be keeping records for each 
recording (and re-recording) of each episode.

Unfortunately, there's no way to mark a recording rule to enable "always 
delete and allow re-record" (or record but don't remember in history) or 
to "never mark as watched" or whatever, so if you want to use the 
auto-mark-as-watched option for other recordings and/or you don't want 
to use the re-record-watched option for other recordings, you'll have to 
deal with the unnecessary history manually.  If that's the case, you 
should probably go in and "Remove all episodes for this title" for those 
every once in a while.  Or, you could create a Python-bindings based 
script to remove episodes from recording history.

Note, too, that there is (and has been for years) a plan to rework the 
Previously Recorded screen in mythfrontend (if nothing else, to use a 
2-level list, so you have titles to scroll through, then when you find a 
title, you can move to the other list to see the episodes).  This would 
make it much easier to use, but the work is rather high compared to the 
benefit, so no one has made the time to get it done, yet.

Mike