[mythtv-users] Slow program listings and deleting

Steve Daniels steve.p.daniels at googlemail.com
Wed May 16 12:08:10 UTC 2007


Rob Willett (Mythtv) wrote:
> 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.


Just skim read your post, but have you heard of "optimize_mythdb.pl" 
ever used it?

Google around it let me know if it helps, or if I'm completely off base :-)

HTH

Steve Daniels


More information about the mythtv-users mailing list