[mythtv-users] Slow program listings and deleting
Rob Willett (Mythtv)
rob.mythtv2 at robertwillett.com
Wed May 16 12:58:37 UTC 2007
Steve,
Already done it (as stated in the e-mail <grin>).
Rob.
Steve Daniels wrote:
> 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