[mythtv-users] Bad mysql performance -- huge oldrecorded -- joins without index
Michael T. Dean
mtdean at thirdcontact.com
Wed Jun 8 15:01:17 UTC 2016
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:
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.
More information about the mythtv-users