[mythtv-users] Slow MySQL query after delete

Michael T. Dean mtdean at thirdcontact.com
Fri Sep 7 04:45:55 UTC 2007


On 09/06/2007 10:10 PM, crs23 wrote:
> Michael T. Dean wrote:
>   
>> I don't think much testing has been done on systems having identical
>> recording rules.  Though it shouldn't cause issues, cleaning out the
>> duplicates (triplicates?  whatever) would significantly simplify the
>> query's processing, so doing so would be very much to your advantage.
> Done and the improvement was substantial.  There were several multiple
> entries.  One program had about a dozen duplicate entries and that just
> happened to be the program with the most previously recorded episodes.  I
> also changed the recordings to be on a particular channel instead of any
> channel which I think helped to.
>
> I'm now down to ~150,000 rows examined in 5.5 seconds, 696 rows returned.  5
> or 6 seconds is hugely better but still a bit of an annoyance.  But I'm very
> pleased with the results so far and the incredible support I've received on
> this forum.  Thank you to everyone!

That's more like it.  From my logs:

2007-09-01 22:39:20.102 Scheduled 267 items in 3.1 = 1.59 match + 1.49 place

down to ones like

2007-09-07 00:11:23.008 Scheduled 255 items in 1.9 = 0.02 match + 1.86 place

on my Athlon XP 2400+ master backend.  During "the season", it typically
places more like 500 items and takes about 4-6 seconds (rather than the
~250 items in 2-3 seconds, now).  So, your results seem about right for
a Via 600MHz processor.

Keeping your recording schedule clean of unwanted items is A Good
Thing.  And, duplicates of actually in-use rules (as opposed to rules
for canceled shows) doubles the positives for that rule (plus the
previously-recorded), making query processing significantly harder.  So
in your case, you had a double whammy of unnecessary rules and
duplicates (or would that be a 12-tuple, or a double and a decuple, or
maybe a dodecuple(?) whammy with your 12 copies of the one rule?).

Using "this channel" recordings can be useful for those with
underpowered systems--as long as you remember to deal with the
consequences of using your brain power to save on CPU requirements
(brain power to remember that if your channels change or if the show is
moved to another channel or ... you need to change the rule so you don't
miss episodes).  My approach is to have a powerful enough system--my
5-year-old, 2002-vintage Athlon XP 2400+, in this case--and let Myth
deal with the issues because its memory is a lot better than mine.

I'm also a big fan of the don't optimize before profiling approach, so
that's why I /always/ recommend users use "any channel" rules.  /If/
they see a problem, they can then choose to "optimize," but premature
optimization typically causes more problems than designing (recording
rules, in this case) for maintenance.  (So now those of you who have, in
the last few days, mentioned my recommending "any channel" rules know
why I do so.)

Mike


More information about the mythtv-users mailing list