sgunther
November 4th, 2009, 01:22 AM
I have recently updated to 9.10
Whenever I start mythtv-backend the mysqld in top goes to 99% and the whole systems lags until I stop mythbackend and restart the sql database.
It seems to be choking on the following; (From mysql-slow.log)
SELECT DISTINCT channel.chanid, channel.sourceid, program.starttime, program.endtime, program.title, program.subtitle, program.description, channel.channum, channel.callsign, channel.name, oldrecduplicate, program.category, sched_temp_record.recpriority, sched_temp_record.dupin, recduplicate, findduplicate, sched_temp_record.type, sched_temp_record.recordid, program.starttime - INTERVAL sched_temp_record.startoffset minute AS recstartts, program.endtime + INTERVAL sched_temp_record.endoffset minute AS recendts, program.previouslyshown, sched_temp_record.recgroup, sched_temp_record.dupmethod, channel.commmethod, capturecard.cardid, cardinput.cardinputid, UPPER(cardinput.shareable) = 'Y' AS shareable, program.seriesid, program.programid, program.category_type, program.airdate, program.stars, program.originalairdate, sched_temp_record.inactive, sched_temp_record.parentid, (CASE sched_temp_record.type WHEN 6 THEN sched_temp_record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) - sched_temp_record.findday)/7) * 7 + sched_temp_record.findday WHEN 7 THEN sched_temp_record.findid ELSE 0 END) , sched_temp_record.playgroup, oldrecstatus.recstatus, oldrecstatus.reactivate, program.videoprop+0, program.subtitletypes+0, program.audioprop+0, sched_temp_record.storagegroup, capturecard.hostname, recordmatch.oldrecstatus, sched_temp_record.avg_delay, channel.recpriority + cardinput.recpriority + (cardinput.cardinputid = sched_temp_record.prefinput) * 2 AS powerpriority FROM recordmatch INNER JOIN sched_temp_record ON (recordmatch.recordid = sched_temp_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 ) ORDER BY sched_temp_record.recordid DESC;
# Time: 091103 21:23:12
# User@Host: mythtv[mythtv] @ MythTV-MBE [192.168.0.205]
# Query_time: 150.482581 Lock_time: 0.000117 Rows_sent: 0 Rows_examined: 16940170
SET timestamp=1257304992;
UPDATE recordmatch INNER JOIN sched_temp_record ON (recordmatch.recordid = sched_temp_record.recordid) INNER JOIN program ON (recordmatch.chanid = program.chanid AND recordmatch.starttime = program.starttime AND recordmatch.manualid = program.manualid) LEFT JOIN oldrecorded ON ( sched_temp_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 sched_temp_record.type WHEN 6 THEN sched_temp_record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) - sched_temp_record.findday)/7) * 7 + sched_temp_record.findday WHEN 7 THEN sched_temp_record.findid ELSE 0 END) ) OR ( program.generic = 0 AND (program.programid = '' OR oldrecorded.programid = '') AND (((sched_temp_record.dupmethod & 0x02) = 0) OR (program.subtitle <> '' AND program.subtitle = oldrecorded.subtitle)) AND (((sched_temp_record.dupmethod & 0x04) = 0) OR (program.description <> '' AND program.description = oldrecorded.description)) AND (((sched_temp_record.dupmethod & 0x08) = 0) OR (program.subtitle <> '' AND program.subtitle = oldrecorded.subtitle) OR (program.subtitle = '' AND oldrecorded.subtitle = '' AND program.description <> '' AND program.description = oldrecorded.description)) ) ) ) LEFT JOIN sched_temp_recorded recorded ON ( sched_temp_record.dupmethod > 1 AND recorded.duplicate <> 0 AND program.title = recorded.title AND recorded.recgroup NOT IN ('LiveTV','Deleted') AND ( (program.programid <> '' AND program.generic = 0 AND program.programid = recorded.programid) OR (recorded.findid <> 0 AND recorded.findid = (CASE sched_temp_record.type WHEN 6 THEN sched_temp_record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) - sched_temp_record.findday)/7) * 7 + sched_temp_record.findday WHEN 7 THEN sched_temp_record.findid ELSE 0 END) ) OR ( program.generic = 0 AND (program.programid = '' OR recorded.programid = '') AND (((sched_temp_record.dupmethod & 0x02) = 0) OR (program.subtitle <> '' AND program.subtitle = recorded.subtitle)) AND (((sched_temp_record.dupmethod & 0x04) = 0) OR (program.description <> '' AND program.description = recorded.description)) AND (((sched_temp_record.dupmethod & 0x08) = 0) OR (program.subtitle <> '' AND program.subtitle = recorded.subtitle) OR (program.subtitle = '' AND recorded.subtitle = '' AND program.description <> '' AND program.description = recorded.description)) ) ) ) LEFT JOIN oldfind ON (oldfind.recordid = recordmatch.recordid AND oldfind.findid = (CASE sched_temp_record.type WHEN 6 THEN sched_temp_record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) - sched_temp_record.findday)/7) * 7 + sched_temp_record.findday WHEN 7 THEN sched_temp_record.findid ELSE 0 END) ) SET oldrecduplicate = (oldrecorded.endtime IS NOT NULL), recduplicate = (recorded.endtime IS NOT NULL), findduplicate = (oldfind.findid IS NOT NULL), oldrecstatus = oldrecorded.recstatus;
SO I assume this is the command to do the scheduling. Back on 9.04 I never saw the database hang the machine before. I let it run and it stayed between 88% to 99% for an entire day.
Question: Is there an issue with the default config of 9.10? Anyone else seeing similar performance hits?
-Steve
Whenever I start mythtv-backend the mysqld in top goes to 99% and the whole systems lags until I stop mythbackend and restart the sql database.
It seems to be choking on the following; (From mysql-slow.log)
SELECT DISTINCT channel.chanid, channel.sourceid, program.starttime, program.endtime, program.title, program.subtitle, program.description, channel.channum, channel.callsign, channel.name, oldrecduplicate, program.category, sched_temp_record.recpriority, sched_temp_record.dupin, recduplicate, findduplicate, sched_temp_record.type, sched_temp_record.recordid, program.starttime - INTERVAL sched_temp_record.startoffset minute AS recstartts, program.endtime + INTERVAL sched_temp_record.endoffset minute AS recendts, program.previouslyshown, sched_temp_record.recgroup, sched_temp_record.dupmethod, channel.commmethod, capturecard.cardid, cardinput.cardinputid, UPPER(cardinput.shareable) = 'Y' AS shareable, program.seriesid, program.programid, program.category_type, program.airdate, program.stars, program.originalairdate, sched_temp_record.inactive, sched_temp_record.parentid, (CASE sched_temp_record.type WHEN 6 THEN sched_temp_record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) - sched_temp_record.findday)/7) * 7 + sched_temp_record.findday WHEN 7 THEN sched_temp_record.findid ELSE 0 END) , sched_temp_record.playgroup, oldrecstatus.recstatus, oldrecstatus.reactivate, program.videoprop+0, program.subtitletypes+0, program.audioprop+0, sched_temp_record.storagegroup, capturecard.hostname, recordmatch.oldrecstatus, sched_temp_record.avg_delay, channel.recpriority + cardinput.recpriority + (cardinput.cardinputid = sched_temp_record.prefinput) * 2 AS powerpriority FROM recordmatch INNER JOIN sched_temp_record ON (recordmatch.recordid = sched_temp_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 ) ORDER BY sched_temp_record.recordid DESC;
# Time: 091103 21:23:12
# User@Host: mythtv[mythtv] @ MythTV-MBE [192.168.0.205]
# Query_time: 150.482581 Lock_time: 0.000117 Rows_sent: 0 Rows_examined: 16940170
SET timestamp=1257304992;
UPDATE recordmatch INNER JOIN sched_temp_record ON (recordmatch.recordid = sched_temp_record.recordid) INNER JOIN program ON (recordmatch.chanid = program.chanid AND recordmatch.starttime = program.starttime AND recordmatch.manualid = program.manualid) LEFT JOIN oldrecorded ON ( sched_temp_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 sched_temp_record.type WHEN 6 THEN sched_temp_record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) - sched_temp_record.findday)/7) * 7 + sched_temp_record.findday WHEN 7 THEN sched_temp_record.findid ELSE 0 END) ) OR ( program.generic = 0 AND (program.programid = '' OR oldrecorded.programid = '') AND (((sched_temp_record.dupmethod & 0x02) = 0) OR (program.subtitle <> '' AND program.subtitle = oldrecorded.subtitle)) AND (((sched_temp_record.dupmethod & 0x04) = 0) OR (program.description <> '' AND program.description = oldrecorded.description)) AND (((sched_temp_record.dupmethod & 0x08) = 0) OR (program.subtitle <> '' AND program.subtitle = oldrecorded.subtitle) OR (program.subtitle = '' AND oldrecorded.subtitle = '' AND program.description <> '' AND program.description = oldrecorded.description)) ) ) ) LEFT JOIN sched_temp_recorded recorded ON ( sched_temp_record.dupmethod > 1 AND recorded.duplicate <> 0 AND program.title = recorded.title AND recorded.recgroup NOT IN ('LiveTV','Deleted') AND ( (program.programid <> '' AND program.generic = 0 AND program.programid = recorded.programid) OR (recorded.findid <> 0 AND recorded.findid = (CASE sched_temp_record.type WHEN 6 THEN sched_temp_record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) - sched_temp_record.findday)/7) * 7 + sched_temp_record.findday WHEN 7 THEN sched_temp_record.findid ELSE 0 END) ) OR ( program.generic = 0 AND (program.programid = '' OR recorded.programid = '') AND (((sched_temp_record.dupmethod & 0x02) = 0) OR (program.subtitle <> '' AND program.subtitle = recorded.subtitle)) AND (((sched_temp_record.dupmethod & 0x04) = 0) OR (program.description <> '' AND program.description = recorded.description)) AND (((sched_temp_record.dupmethod & 0x08) = 0) OR (program.subtitle <> '' AND program.subtitle = recorded.subtitle) OR (program.subtitle = '' AND recorded.subtitle = '' AND program.description <> '' AND program.description = recorded.description)) ) ) ) LEFT JOIN oldfind ON (oldfind.recordid = recordmatch.recordid AND oldfind.findid = (CASE sched_temp_record.type WHEN 6 THEN sched_temp_record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(sched_temp_record.findtime, '%H:%i') hour_minute)) - sched_temp_record.findday)/7) * 7 + sched_temp_record.findday WHEN 7 THEN sched_temp_record.findid ELSE 0 END) ) SET oldrecduplicate = (oldrecorded.endtime IS NOT NULL), recduplicate = (recorded.endtime IS NOT NULL), findduplicate = (oldfind.findid IS NOT NULL), oldrecstatus = oldrecorded.recstatus;
SO I assume this is the command to do the scheduling. Back on 9.04 I never saw the database hang the machine before. I let it run and it stayed between 88% to 99% for an entire day.
Question: Is there an issue with the default config of 9.10? Anyone else seeing similar performance hits?
-Steve