No subject


Wed Oct 15 16:11:14 UTC 2008


so I renamed
"oldrecorded" to "oldrecorded_back", and renamed "oldrecorded_test" to
"oldrecorded".
In the next day, I started noticing the frontend hanging every time I
tried to delete
a file.

For the next couple of days, I read through this and a couple other threads and
tried all the suggestions:
I changed 90% of my recording rules from "any channel" to "this channel", and
ran the optimize_mythdb.pl script, changed my recordedseek and oldrecorded
tables to innodb, etc, with little improvement from any of it. I found a huge
sql query (the BUSQ?) in one of these threads (the query is listed below), and
running it returned about 2500 rows in about 45 seconds on average (sometimes
as low as 30, and a couple of times it took 5 minutes+).

Finally, I guessed it might be a problem with the oldrecorded table that I had
created, so I moved the backedup table back into place, and my query times
dropped down under 10 seconds, sometimes as low as 2 seconds.
My frontend is not hanging like it was, and everything seems better.

Some conclusions and questions:
I am not a database expert, so is there something wrong with the way I
copied the
table as I listed above?
Perhaps some of the other people having issues with slow deletes
hanging the frontend
have done as I did, and have something wrong with the table itself. Is
there a way to
check the oldrecorded table for correct structure, other that the
"optimize_mythdb.pl"
script? Is there a way to delete and recreate this table as it should
be, as a last
resort for those who have no backup and can't get their frontend to work?

Hope this helps someone. Mythtv rules!

Pat
=====================================
query I used to test for slow mysql (I got it from this mailing list):
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.recpriority, record.prefinput, program.hdtv,
program.closecaptioned, program.first, program.last, program.stereo
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;

------=_Part_25010_403626.1226594426435
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<pre>I started having issues with horribly slow sql queries after i messed around <br>with the oldrecorded table. I was able to fix it by restoring my backedup table.<br><br>Details of how I caused the problem:<br>I had set the frontend to move deleted shows into the &quot;deleted&quot; group rather than<br>
delete them outright, and I was having shows get re-recorded over and over. With<br>much digging in this list, I found out about the &quot;duplicate&quot; column in the oldrecorded<br>table. Evidently by setting them to autoexpire instead of delete, the &quot;duplicate&quot; setting<br>
was being left at &quot;0&quot; when the system deleted the files out of the &quot;deleted&quot; group.<br>After updating the table with &quot;update oldrecorded set duplicate=&#39;1&#39;;&quot;,<br>I decided to clean up all the duplicate entries in the table. I had several hundred<br>
lines which were duplicate title and subtitle, so I copied the table to<br>&quot;oldrecorded_test&quot;<br>  create table oldrecorded_test as select * from oldrecorded;<br>and made a delete statement as follows:<br>  delete from oldrecorded_test T1 where starttime not in <br>
  select max (starttime) from oldrecorded_test T2 where T2.programid=T1.programid);<br>From the contents of oldrecorded_test, this appeared to have worked, so I renamed<br>&quot;oldrecorded&quot; to &quot;oldrecorded_back&quot;, and renamed &quot;oldrecorded_test&quot; to &quot;oldrecorded&quot;.<br>
In the next day, I started noticing the frontend hanging every time I tried to delete<br>a file.<br><br>For the next couple of days, I read through this and a couple other threads and<br>tried all the suggestions:<br>I changed 90% of my recording rules from &quot;any channel&quot; to &quot;this channel&quot;, and<br>
ran the optimize_mythdb.pl script, changed my recordedseek and oldrecorded<br>tables to innodb, etc, with little improvement from any of it. I found a huge<br>sql query (the BUSQ?) in one of these threads (the query is listed below), and<br>
running it returned about 2500 rows in about 45 seconds on average (sometimes<br>as low as 30, and a couple of times it took 5 minutes+).<br><br>Finally, I guessed it might be a problem with the oldrecorded table that I had<br>
created, so I moved the backedup table back into place, and my query times<br>dropped down under 10 seconds, sometimes as low as 2 seconds.<br>My frontend is not hanging like it was, and everything seems better.<br><br>Some conclusions and questions:<br>
I am not a database expert, so is there something wrong with the way I copied the<br>table as I listed above?<br>Perhaps some of the other people having issues with slow deletes hanging the frontend<br>have done as I did, and have something wrong with the table itself. Is there a way to<br>
check the oldrecorded table for correct structure, other that the &quot;optimize_mythdb.pl&quot;<br>script? Is there a way to delete and recreate this table as it should be, as a last<br>resort for those who have no backup and can&#39;t get their frontend to work?<br>
<br>Hope this helps someone. Mythtv rules!<br><br>Pat<br>=====================================<br>query I used to test for slow mysql (I got it from this mailing list):<br>SELECT DISTINCT channel.chanid, channel.sourceid, program.starttime, program.endtime, program.title, program.subtitle, program.description, channel.channum, channel.callsign, <a href="http://channel.name">channel.name</a>, oldrecorded.endtime IS NOT NULL AS oldrecduplicate,<br>
program.category, record.recpriority, record.dupin, recorded.endtime IS NOT NULL AS recduplicate, oldfind.findid IS NOT NULL<br> 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) = &#39;Y&#39; 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<br>
9    THEN to_days(date_sub(program.starttime, interval<br>         time_format(record.findtime, &#39;%H:%i&#39;) hour_minute))<br> WHEN 10    THEN floor((to_days(date_sub(program.starttime, interval                time_format(record.findtime, &#39;%H:%i&#39;) 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.recpriority, record.prefinput, program.hdtv, program.closecaptioned, program.first, program.last, program.stereo FROM recordmatch  INNER JOIN record ON (recordmatch.recordid = record.recordid)  INNER JOIN program ON (recordmatch.chanid = program.chanid AND<br>
 recordmatch.starttime = program.starttime AND<br>         recordmatch.manualid = program.manualid)  INNER JOIN<br>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 &gt; 1 AND     oldrecorded.duplicate &lt;&gt; 0 AND     program.title = oldrecorded.title<br>
AND      (       (program.programid &lt;&gt; &#39;&#39; AND program.generic<br>= 0        AND program.programid = oldrecorded.programid)<br>  OR       (oldrecorded.findid &lt;&gt; 0 AND         oldrecorded.findid = (CASE record.type   WHEN 6    THEN record.findid   WHEN<br>
 9    THEN to_days(date_sub(program.starttime, interval<br>          time_format(record.findtime, &#39;%H:%i&#39;) hour_minute))<br>  WHEN 10    THEN floor((to_days(date_sub(program.starttime, interval                time_format(record.findtime, &#39;%H:%i&#39;) hour_minute)) -                record.findday)/7) * 7 + record.findday   WHEN 7    THEN record.findid   ELSE 0  END) )<br>
OR       (        program.generic = 0        AND        (program.programid = &#39;&#39; OR oldrecorded.programid = &#39;&#39;)        AND<br>     (((record.dupmethod &amp; 0x02) = 0) OR (program.subtitle &lt;&gt;<br>&#39;&#39;           AND program.subtitle = oldrecorded.subtitle))<br>
    AND        (((record.dupmethod &amp; 0x04) = 0) OR (program.description &lt;&gt; &#39;&#39;           AND program.description = oldrecorded.description))       )      )   )  LEFT JOIN recorded ON   (<br>    record.dupmethod &gt; 1 AND     recorded.duplicate &lt;&gt; 0 AND<br>
   program.title = recorded.title AND     recorded.recgroup &lt;&gt;<br> &#39;LiveTV&#39;      AND      (       (program.programid &lt;&gt; &#39;&#39; AND program.generic = 0        AND program.programid = recorded.programid)       OR       (recorded.findid &lt;&gt; 0 AND         recorded.findid = (CASE record.type   WHEN 6    THEN record.findid<br>
  WHEN 9    THEN to_days(date_sub(program.starttime, interval<br>                time_format(record.findtime, &#39;%H:%i&#39;) hour_minute))   WHEN 10    THEN floor((to_days(date_sub(program.starttime, interval                time_format(record.findtime, &#39;%H:%i&#39;) hour_minute)) -                record.findday)/7) * 7 + record.findday   WHEN 7    THEN record.findid   ELSE 0  END) )<br>
      OR       (        program.generic = 0        AND<br>(program.programid = &#39;&#39; OR recorded.programid = &#39;&#39;)        AND<br>        (((record.dupmethod &amp; 0x02) = 0) OR (program.subtitle<br>&lt;&gt; &#39;&#39;           AND program.subtitle = recorded.subtitle))<br>
    AND        (((record.dupmethod &amp; 0x04) = 0) OR (program.description &lt;&gt; &#39;&#39;           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<br>
 to_days(date_sub(program.starttime, interval<br>time_format(record.findtime, &#39;%H:%i&#39;) hour_minute))   WHEN 10<br>   THEN floor((to_days(date_sub(program.starttime, interval<br>             time_format(record.findtime, &#39;%H:%i&#39;) hour_minute)) -                record.findday)/7) * 7 + record.findday<br>
WHEN 7    THEN record.findid   ELSE 0  END) )  ORDER BY record.recordid DESC;<br><br></pre>

------=_Part_25010_403626.1226594426435--


More information about the mythtv-users mailing list