Opened 11 years ago

Closed 11 years ago

Last modified 11 years ago

#7604 closed enhancement (invalid)

Very slow query when delete recording

Reported by: anonymous Owned by: Isaac Richards
Priority: minor Milestone: unknown
Component: MythTV - General Version: 0.22
Severity: low Keywords: delete recordings slow query
Cc: Ticket locked: no

Description (last modified by sphery)

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 	

Change History (5)

comment:1 Changed 11 years ago by sphery

Description: modified (diff)
Status: newinfoneeded_new

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.

comment:2 Changed 11 years ago by anonymous

Yes it is on a ext3 fs but "delete files slowly" is enabled. Although i dont know the exact Logic behind that option, i always thought it was used for the actual deletion of the file. In this case its the select query that takes a long time. It shows up in my slow query log. Also when I run the query above directly it takes 13 secs.

Please let me know if it is a confuguration issue or if you need more info.

comment:3 Changed 11 years ago by sphery

Does the slow query do anything? Are there any problems you notice--other than a mention in the slow query log? The scheduler queries are big, complex, and slow queries, but they should always be run in such a way that the slowness isn't really a problem.

Please do send an e-mail to the -users list and we can debug configuration there and recommend things like optimize_mythdb.pl and ...

comment:4 Changed 11 years ago by robertm

Resolution: invalid
Status: infoneeded_newclosed

No response in eight weeks, and no specifics about how this is actually inhibiting proper operation in any way.

comment:5 Changed 11 years ago by sphery

And, FWIW, the efficiency of the Watch Recordings screen has been improved with Daniel K's recent work and changes to the DB connection pool may also improve responsiveness.

Note: See TracTickets for help on using tickets.