mythtv at cvs
Nov 18, 2009, 12:05 PM
Post #2 of 6
(609 views)
Permalink
|
|
Re: Ticket #7604: Very slow query when delete recording
[In reply to]
|
|
#7604: Very slow query when delete recording
------------------------------+---------------------------------------------
Reporter: anonymous | Owner: ijr
Type: enhancement | Status: infoneeded_new
Priority: minor | Milestone: unknown
Component: MythTV - General | Version: 0.22
Severity: low | Mlocked: 0
------------------------------+---------------------------------------------
Changes (by mdean):
* status: new => infoneeded_new
Old description:
> When I delete a recording through the frontend it takes over 15 seconds
> until the record is deleted. There is one very slow query that takes over
> 13 seconds:
>
> 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
>
> Here is the explain of this query:
> id select_type table type possible_keys key key_len
> ref rows Extra
> 1 SIMPLE capturecard ALL PRIMARY NULL NULL
> NULL 3 Using temporary; Using filesort
> 1 SIMPLE cardinput ALL NULL NULL NULL NULL 4
> Using where; Using join buffer
> 1 SIMPLE recordmatch ALL recordid NULL NULL
> NULL 456 Using join buffer
> 1 SIMPLE sched_temp_record eq_ref PRIMARY PRIMARY
> 4 myth-test.recordmatch.recordid 1
> 1 SIMPLE channel eq_ref PRIMARY,sourceid PRIMARY
> 4 myth-test.recordmatch.chanid 1 Using where
> 1 SIMPLE program eq_ref
> PRIMARY,id_start_end,program_manualid,starttime PRIMARY
> 16 myth-test.recordmatch.chanid,myth-test.recordmatch.starttime
> ,myth-test.recordmatch.manualid 1
> 1 SIMPLE oldrecstatus ALL NULL NULL NULL NULL
> 4224
New description:
When I delete a recording through the frontend it takes over 15 seconds
until the record is deleted. There is one very slow query that takes over
13 seconds:
{{{
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
}}}
Here is the explain of this query:
{{{
id select_type table type possible_keys key key_len
ref rows Extra
1 SIMPLE capturecard ALL PRIMARY NULL NULL
NULL 3 Using temporary; Using filesort
1 SIMPLE cardinput ALL NULL NULL NULL NULL 4
Using where; Using join buffer
1 SIMPLE recordmatch ALL recordid NULL NULL
NULL 456 Using join buffer
1 SIMPLE sched_temp_record eq_ref PRIMARY PRIMARY
4 myth-test.recordmatch.recordid 1
1 SIMPLE channel eq_ref PRIMARY,sourceid PRIMARY
4 myth-test.recordmatch.chanid 1 Using where
1 SIMPLE program eq_ref
PRIMARY,id_start_end,program_manualid,starttime PRIMARY 16
myth-test.recordmatch.chanid,myth-test.recordmatch.starttime,myth-
test.recordmatch.manualid 1
1 SIMPLE oldrecstatus ALL NULL NULL NULL NULL
4224
}}}
--
Comment:
That's part of the scheduler, which is not tied to the UI updates.
Are you using an ext3 file system (or other file system with very slow
delete times) without enabling the "Delete files slowly" setting in
mythtv-setup? If so, please let us know and ask for help configuring Myth
on the mythtv-users mailing list.
--
Ticket URL: <http://svn.mythtv.org/trac/ticket/7604#comment:1>
MythTV <http://www.mythtv.org/>
MythTV
_______________________________________________
mythtv-commits mailing list
mythtv-commits [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-commits
|
