[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