Opened 14 years ago
Closed 14 years ago
Last modified 14 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 )
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:2 Changed 14 years ago by
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 14 years ago by
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 14 years ago by
Resolution: | → invalid |
---|---|
Status: | infoneeded_new → closed |
No response in eight weeks, and no specifics about how this is actually inhibiting proper operation in any way.
comment:5 Changed 14 years ago by
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.
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.