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 "deleted" 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 "duplicate" column in the oldrecorded<br>table. Evidently by setting them to autoexpire instead of delete, the "duplicate" setting<br>
was being left at "0" when the system deleted the files out of the "deleted" group.<br>After updating the table with "update oldrecorded set duplicate='1';",<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>"oldrecorded_test"<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>"oldrecorded" to "oldrecorded_back", and renamed "oldrecorded_test" to "oldrecorded".<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 "any channel" to "this channel", 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 "optimize_mythdb.pl"<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'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) = '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<br>
9 THEN to_days(date_sub(program.starttime, interval<br> time_format(record.findtime, '%H:%i') hour_minute))<br> 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<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 > 1 AND oldrecorded.duplicate <> 0 AND program.title = oldrecorded.title<br>
AND ( (program.programid <> '' AND program.generic<br>= 0 AND program.programid = oldrecorded.programid)<br> OR (oldrecorded.findid <> 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, '%H:%i') hour_minute))<br> 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) )<br>
OR ( program.generic = 0 AND (program.programid = '' OR oldrecorded.programid = '') AND<br> (((record.dupmethod & 0x02) = 0) OR (program.subtitle <><br>'' AND program.subtitle = oldrecorded.subtitle))<br>
AND (((record.dupmethod & 0x04) = 0) OR (program.description <> '' AND program.description = oldrecorded.description)) ) ) ) LEFT JOIN recorded ON (<br> record.dupmethod > 1 AND recorded.duplicate <> 0 AND<br>
program.title = recorded.title AND recorded.recgroup <><br> '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<br>
WHEN 9 THEN to_days(date_sub(program.starttime, interval<br> 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) )<br>
OR ( program.generic = 0 AND<br>(program.programid = '' OR recorded.programid = '') AND<br> (((record.dupmethod & 0x02) = 0) OR (program.subtitle<br><> '' AND program.subtitle = recorded.subtitle))<br>
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<br>
to_days(date_sub(program.starttime, interval<br>time_format(record.findtime, '%H:%i') hour_minute)) WHEN 10<br> THEN floor((to_days(date_sub(program.starttime, interval<br> time_format(record.findtime, '%H:%i') 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