[mythtv] Housekeeping function thrashing mysql

Buechler, Mark R Fri, 13 Jan 2006 15:23:32 -0800

I've logged a select sql which is causing mysql to jump to 100% for upwards of 40 seconds which kills anything else needing data. I believe the sql is part of the housekeeping thread. Is there anyway of putting a limit on the return and putting it in a loop instead of getting the whole thing at once?

This bit of sql returns 1500 rows in my setup and takes generally 35 seconds. I've optimized the table and rebuilt the index:

# Time: 060113 18:07:06

# [EMAIL PROTECTED]: mythtv[mythtv] @ localhost []

# Query_time: 35 Lock_time: 0 Rows_sent: 1500 Rows_examined: 4307708

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 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;

EMAIL DISCLAIMER

Please Note: The information contained in this message may be privileged and
confidential, protected from disclosure, and/or intended only for the use of
the individual or entity named above. If the reader of this message is not
the intended recipient, or an employee or agent responsible for delivering
this message to the intended recipient, you are hereby notified that any
disclosure, distribution, copying or other dissemination of this
communication is strictly prohibited. If you received this communication in
error, please immediately reply to the sender, delete the message and
destroy all copies of it.

Thank You

_______________________________________________
mythtv-dev mailing list
[email protected]
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev

[mythtv] Housekeeping function thrashing mysql

Reply via email to