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

[Michael T. Dean]

On 06/08/2016 08:38 AM, Håkon Alstadheim wrote:
> Hi all, I just noticed I had more than 100 000 entries in oldrecorded.

MythTV automatically deletes anything you don't actually record (and 
anything you didn't explicitly mark to Never Record) from the recording 
history after about 10 days (to give you some time to go back and find 
out why some episode you thought would record didn't record).

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/...).  The only other non-recording entries in 
recording history are those episodes in the current listings or from up 
to 10 days ago matching one of your existing recording rules.  It's 
theoretically possible that you have a huge number of these, but it 
would require a huge number of channels and a huge number of matches 
(i.e. recording rules or at least very "loose" recording rules--like 
custom recording rules with very liberal WHERE clauses).  If this is the 
case *and* your system can't handle it, you need to fix something--see 
below.

> Bludgeoned it down to ~30 000, and mysqltuner seems to indicate that
> fewer temporary tables are now being written to disk. Down from 12% to
> 2% , but I need to run longer to be sure. Setting join buffer size even
> higher got temp. tables on disk down to 8%, but that meant going
> dangerously high on ram-setting. Anyway, this is not just my
> ricer-personality showing, there is actually a noticeable difference on
> front-end responsiveness.
>
> This seeming success caused me to think that it would be nice if stuff
> like daily news-shows which I only ever keep a couple of editions of,
> and which never are repeated, should maybe be deleted after a few days
> from oldrecorded,

Anything not automatically removed from history that you want to remove 
from /your/ history must be done manually (because other people have 
other ideas about what should be kept).  This is actually quite simple 
to do, however.  In mythfrontend, go to Manage Recordings|Previously 
Recorded, then find an episode of a show (like your daily news show) and 
hit SELECT (by default Enter)*** and then select either "Remove this 
episode from the list" or "Remove all episodes for this title".  Note 
that you can change the ordering of the list of episodes in Previously 
Recording using the MENU (to sort by time in ascending or descending 
order or to sort by title in ascending or descending order).

*** I'm pretty sure it's SELECT and not MENU, but if you don't get the 
right options, try MENU.

>   and it should be possible to set a limit for how far
> back myth should remember old recordings.

There isn't currently a way to do this in MythTV.  And, really, it 
probably doesn't make sense to do this for everyone (even if it's just 
an option) since different shows have different run lengths--for 
example, if you delete someone's history of Doctor Who just because they 
recorded it 10 years ago, they're likely to start re-recording old 
episodes though they may only want the new episodes.  Therefore, 
manually trimming your recording history is, IMHO, a better approach.  
When you delete a recording rule, feel free to also remove your 
recording history for that series as described above (this isn't done 
automatically because the history is actually still relevant when a rule 
is deleted and, in many cases, rules are deleted and re-created during 
the run of a series).

Alternatively, you could create a script to do trim all your old 
episodes from the history using the Python bindings rather easily. You 
could even do it such that it limits the trimming to specific series 
(i.e. remove all episodes of your daily news shows, etc., and remove any 
episodes of some list of series that you recorded over a year ago).

That said, let's talk about the root of the problem.  Your system was 
unable to handle the scheduler query (often called the BUSQ--the "Big, 
Ugly Scheduler Query") runs with the system resources provided and the 
data you have.  There exist 3 ways to improve scheduler performance in 
MythTV:  1) reduce the number of episodes in the recording history (as 
discussed above) and/or 2) reduce the number of recording rules and/or 
3) reduce the number of program listings. There is one way to improve 
scheduler performance outside of MythTV--4) throw more/better resources 
at it:  get better/more CPU and/or RAM and/or file system performance 
(which can have a huge impact if using things like barriers or file 
systems that take huge amounts of I/O resources when doing things like 
deleting large files and when the user didn't enable slow deletes in 
MythTV, like he should have).

We've already talked about 1) and you've seen its benefit.  You may 
choose to remove episodes from your recording history if you want. I 
never have--I have a complete list of every TV show I've recorded for 
watching since I started using MythTV--and in the 12 years 1 month 5 
days 13 hrs 22 mins since my first recording, I've amassed a total of 
26245 episode in my recording history with 25139 episodes ever 
recorded.  Therefore, either 2) or 3) are very likely the real problem 
in your situation.  To prove this, you could have looked at the 
Recording Statistics page of MythWeb (before your messing around in the 
database) to get a total "Number of episodes" recorded and compare that 
with the 100k to see how many were "current history"--i.e. the 10 days 
before today and the number of days into the future your listings go 
(usually maxing around 24 days).  I'd still be interested in seeing your 
current info--even though the database hacking you've done will have 
changed things--specifically the "Number of episodes" and "Total Running 
Time" from MythWeb Recording Statistics page and the count of your 
oldrecorded table. Again, note that I have never trimmed my recording 
history and I have no issues with my system's performance (and I'm using 
slow HDDs for my MySQL data).

So, 2) is something that's often forgotten.  Once you've recorded every 
single episode of a series, there's not a lot of reason to keep around 
the recording rule for that series.  When a series ends or is cancelled, 
I go through and remove the recording rule (set it to "Do not record").  
This way I don't get a huge number of useless matches when they show 
House and anything else I watched long ago in syndication with 2-4 
episodes per day every day.  You don't have to do this--some people like 
leaving the old rules in case they restart the series (saved someone a 
minute of making a new recording rule when they restarted Family Guy 3 
years later) or make a follow-up movie (though this didn't help those 
who loved Firefly since they called the movie Serenity).  However, if 
you have any "I'm done with that series" rules, deleting them is not a 
bad idea.

And 3) is something that people seem to have strong feelings about (even 
though they are nearly always strongly wrong about those feelings).  The 
number of program listings won't be a problem for anyone with a 
"reasonable" number of listings.  However, if you have a huge number of 
channels (like many "ultra premium package" cable or satellite TV users 
have), this could actually be a problem.  To reduce the number of 
program listings (i.e. episodes that are listed, any of which could be a 
match, many of which would be useless--read "already recorded or won't 
record"--matches), you need to either reduce the number of days of 
listings you have available or reduce the number of channels for which 
you have listings. Reducing the number of days of listings you have 
available is a bad idea.  The more future listings you have for a 
channel you watch/record, the better able MythTV is to make decisions 
about what to record when.  Therefore, the only proper way to reduce the 
number of program listings is to reduce the number of channels for which 
you have listings.  This is actually a good idea.  If you have channels 
you never watch, don't waste resources (your compuing resources when 
doing things like running the BUSQ, your bandwidth when downloading 
listings, your listings provider's bandwidth, your listings provider's 
CPU/processing, and your own time/ease of use when it comes to finding 
things in the guide/searches/... in MythTV) by including those channels 
in your MythTV configuration.  Ideally, delete those channels and remove 
them from your Schedules Direct or XMLTV configuration (I've done this 
for all channels I will never watch--SDTV versions of channels I have 
HDTV versions of and channels I'm not interested in (shopping channels, 
etc.)).  If you believe deleting them is wrong, at least remove them 
from your Schedules Direct/XMLTV/EIT configuration so their listings 
aren't populated.  After all, if you're not going to watch them, the 
listings aren't helping you--they're just preventing you from finding 
the listings you actually care about/the things you might actually 
watch/record.

If you don't like the above possible fixes for the issue, you'll have to 
go with 4)--throw more resources at the problem.  Note, though, that 
especially on systems that have file systems with barriers and 
especially on systems where MySQL is configured to use InnoDB tables, it 
may well make sense to enable "Delete files slowly" in mythtv-setup 
under General Settings.  It never hurts to enable that setting, and even 
on systems where it's not required, it actually helps.  I highly 
recommend enabling it and the *only* situation where it causes any 
problems is when you're recording to network file systems (NFS/CIFS/...) 
that don't support delete on last close--so if you're not recording to 
network file systems, you probably should enable it.

Mike