[mythtv-users] Slow program listings and deleting

[Rob Willett (Mythtv)]

Hi,

I'm trying to analyse why my MythTV system occassionally works slowly on 
program listings and deleting recorded programs.

My system is Mythtv V0.19 running on an AMD 2800XP box. It has 512MB RAM 
and is dedicated to MythTV. I have two DVB cards in it. It has worked 
pretty well since I built it Feb last year (2006). I have not upgraded 
as it runs well, V.20 of MythTV doesn't really have a lot extra that I 
need and it works. If it ain't broken, don't change it!

I run a MiniMythtv front end against this server. Again that works very 
well. Playback is fairly flawless and the whole system is partner 
friendly. Very important to me.

I have noticed over the last few months that actions involving listing 
programs, e.g. web access, listing all the recorded programs, deleting a 
program from a list can often take a long time (e.g. 30 secs to 70 secs) 
to complete. This seems to have got worse over the last few months.

I checked on my system and I notice that when I do these listings, 
mysqld jumps to the top of TOP and stays there for a while. I looked 
through a series of Google pages on tuning mysql and added the following 
to my.cnf and restarted.


key_buffer = 48M
max_allowed_packet = 8M
table_cache = 128
sort_buffer_size = 48M
net_buffer_length = 8M
thread_cache_size = 4
query_cache_type = 1
query_cache_size = 4M


Performance inproved a little but nothing that got the response down to 
the fast response I used to remember from months ago.

I then turned on the slow_queries option in /etc/my.cnf using and restarted.

log_slow_queries
log_long_format

and started looking in the file it created. My intention was to look at 
the offending SQL and see if I could modify the indexes to speed things 
up. Ha, the SQL statements it goes slow on is a monster and way, way 
beyond my understanding of what to do!

This is one of the lines from the slow_queries file.  There are a few of 
these now. This is actually the fastest one.

---------------------------

# Time: 070516 11:44:16
# User at Host: mythtv[mythtv] @ localhost []
# Query_time: 54  Lock_time: 0  Rows_sent: 8024  Rows_examined: 6065709
SELECT DISTINCT channel.chanid, channel.sourceid, program.starttime, 
program.endtime, program.title, program.subtitle, program.description, 
channel.channum, channel.callsign, channel.name, oldrecorded.endtime IS 
NOT NULL AS oldrecduplicate, program.category, record.recpriority, 
record.dupin, recorded.endtime IS NOT NULL AS recduplicate, 
oldfind.findid IS NOT NULL AS findduplicate, record.type, 
record.recordid, program.starttime - INTERVAL record.startoffset minute 
AS recstartts, program.endtime + INTERVAL record.endoffset minute AS 
recendts, program.previouslyshown, record.recgroup, record.dupmethod, 
channel.commfree, capturecard.cardid, cardinput.cardinputid, 
UPPER(cardinput.shareable) = 'Y' AS shareable, program.seriesid, 
program.programid, program.category_type, program.airdate, 
program.stars, program.originalairdate, record.inactive, 
record.parentid, (CASE record.type   WHEN 6    THEN record.findid   WHEN 
9    THEN to_days(date_sub(program.starttime, interval 
time_format(record.findtime, '%H:%i') hour_minute))   WHEN 10    THEN 
floor((to_days(date_sub(program.starttime, interval 
time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) 
* 7 + record.findday   WHEN 7    THEN record.findid  ELSE 0  END) , 
record.playgroup, oldrecstatus.recstatus, oldrecstatus.reactivate, 
channel.recpriority + cardinput.preference FROM recordmatch  INNER JOIN 
record ON (recordmatch.recordid = record.recordid)  INNER JOIN program 
ON (recordmatch.chanid = program.chanid AND 
recordmatch.starttime = program.starttime AND 
recordmatch.manualid = program.manualid)  INNER JOIN channel ON 
(channel.chanid = program.chanid)  INNER JOIN cardinput ON 
(channel.sourceid = cardinput.sourceid)  INNER JOIN capturecard ON 
(capturecard.cardid = cardinput.cardid)  LEFT JOIN oldrecorded as 
oldrecstatus ON   ( oldrecstatus.station = channel.callsign AND 
oldrecstatus.starttime = program.starttime AND     oldrecstatus.title = 
program.title )  LEFT JOIN oldrecorded ON   (     record.dupmethod > 1 
AND oldrecorded.duplicate <> 0 AND     program.title = oldrecorded.title 
   AND      (       (program.programid <> '' AND program.generic = 0 
  AND program.programid = oldrecorded.programid)       OR 
(oldrecorded.findid <> 0 AND         oldrecorded.findid = (CASE 
record.type   WHEN 6    THEN record.findid   WHEN 9    THEN 
to_days(date_sub(program.starttime, interval 
time_format(record.findtime, '%H:%i') hour_minute))   WHEN 10    THEN 
floor((to_days(date_sub(program.starttime, interval 
time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) 
* 7 + record.findday   WHEN 7    THEN record.findid  ELSE 0  END) ) 
   OR       (        program.generic = 0        AND 
(program.programid = '' OR oldrecorded.programid = '') AND 
(((record.dupmethod & 0x02) = 0) OR (program.subtitle <> ''          AND 
program.subtitle = oldrecorded.subtitle))        AND 
(((record.dupmethod & 0x04) = 0) OR (program.description <> ''     AND 
program.description = oldrecorded.description))       )      )   )  LEFT 
JOIN recorded ON   (     record.dupmethod > 1 AND recorded.duplicate <> 
0 AND     program.title = recorded.title AND recorded.recgroup <> 
'LiveTV'      AND      (       (program.programid <> '' AND 
program.generic = 0        AND program.programid = recorded.programid) 
      OR       (recorded.findid <> 0 AND recorded.findid = (CASE 
record.type   WHEN 6    THEN record.findid WHEN 9    THEN 
to_days(date_sub(program.starttime, interval 
time_format(record.findtime, '%H:%i') hour_minute))   WHEN 10 THEN 
floor((to_days(date_sub(program.starttime, interval 
time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) 
* 7 + record.findday   WHEN 7    THEN record.findid  ELSE 0  END) ) 
   OR       (        program.generic = 0        AND 
(program.programid = '' OR recorded.programid = '')        AND 
(((record.dupmethod & 0x02) = 0) OR (program.subtitle <> ''      AND 
program.subtitle = recorded.subtitle))        AND (((record.dupmethod & 
0x04) = 0) OR (program.description <> ''  AND program.description = 
recorded.description))       )      )   ) LEFT JOIN oldfind ON 
(oldfind.recordid = recordmatch.recordid AND oldfind.findid = (CASE 
record.type   WHEN 6    THEN record.findid   WHEN 9    THEN 
to_days(date_sub(program.starttime, interval 
time_format(record.findtime, '%H:%i') hour_minute))   WHEN 10    THEN 
floor((to_days(date_sub(program.starttime, interval 
time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) 
* 7 + record.findday   WHEN 7    THEN record.findid  ELSE 0  END) ) 
ORDER BY record.recordid DESC;

---------------------------

based on reading the mysql manual it appears that this query took 54 
seconds to complete. It also returns a lot of lines, 8024, which does 
appear to be rather a lot. When I ran the query manually and looked at 
the results, it returns programs that were recorded but have been 
deleted. This may be the right behavior, I have no idea and guidance 
here would be welcome.

I looked at some of the tables mentioned here and got a count of the size:

recordmatch - 4012 records. Which is interesting as it is half the 
number of rows returned. I also noticed that the above monster query 
seems to return duplicates.

Googling for recordmatch doesn't give me any more information on what 
recordmatch does. Looking at the top 10 entries seems to show the last 
10 things I recorded. I wonder if this table is growing and not being 
pruned? I looked through the installation again and can't see anything 
about clearing out old records from tables. No crontab entries. I also 
looked and ran the optimise_database script. No changes to the results.

So my questions are, since you've got this far,

1) Should the table recordmatch be so large? Is 4,000 entries a lot?
2) Should data be deleted from this table? if so how?
3) What sizes are other peoples recordmatch tables?
4) Does the monster SQL statement above look familiar? Does it have form 
as a slow query? Are there additional indexes that could be created? I 
did an EXPLAIN on it, but it doesn't give me any real help.
5) Any suggestions as to what to do?

My DB skills are limited, I'm a network engineer, so am wary of going 
into DB's and making changes willy-nilly.

Thanks.

Rob.