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

Wed Jun 8 23:11:16 UTC 2016

Den 08. juni 2016 15:55, skrev Michael T. Dean:

[snip]
> 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".
Never noticed the "Remove all episodes for this title" . Handy , thanks :-)
[snip]
> 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
Yes, I have some rules for channels I no longer have and the like,
should probably go through them :-)
> and/or 3) reduce the number of program listings.
Program listings are very few. I've got two weeks worth from 16
channels. The "program" table has 11241 rows.
> 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).
My recordings are on xfs file-systems. I thought they should be OK with
"fast deletes" ?
>
> 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).
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%

>
> 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.
>
Hmm. yes ...
> 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.
>
Done, ages ago :-). I'm on DVB-T, and after the encrypted channels went
"only on set-tops approved by us", I removed those channels. I've got a
script to redo channel-numbers and the like for when I have to re-scan
for channels.
> 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.
>
The recordings are on a separate raid, that got me ~zero delay on
starting watching, and thumbnails generate pretty fast. Scrolling
through the list of recordings and other types of navigation was often
frustrating until I found "join buffer size".

Thank you very much for lots of helpful tips. Now I have some sort of
priority-list for my janitorial work here :-) .

-- Håkon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20160609/a817fed1/attachment.html>