Mark_R_Buechler at bausch
Jan 13, 2006, 3:23 PM
Post #1 of 7
(779 views)
Permalink
|
|
Housekeeping function thrashing mysql
|
|
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
# User[at]Host: 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
|
