[mythtv-users] Very slow mythfrontend, mysql at 100%

[GXL.nl]

Ronald Pijnacker said the following on 19/11/09 19:46:
> Hi all,
> 
> My primary frontend is very slow since a while.
> Deleting recordings etc. is taking seconds, which is very annoying.
> I noticed that mysql is at 100% cpu on the backend.
> Could it be that over the years mythconverg has become so big that
> this is the cause of the issue?
> 
> If so, which tables can I safely purge?
> 
> Thanks,
> 
> Ronald
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
> 
Hi,

I have the same thing, also made a bug report (#7604) for it since it only happens since I upgraded to 0.22 (and from Ubuntu 
8.04 to 9.1).

I have enabled the slow-query-log from MySQL and it shows the following query as being slow (13 secs):

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

I dug in a little deeper and when I run this query direclt from mysql (phpmyadmin) it takes 13 secs but if I remove the two 
fields from oldrecstatus it only takes 3 secs. For this test I have disabled the cache with SQL_NO_CACHE and I have only 
removed the two fields (oldrecstatus.recstatus, oldrecstatus.reactivate) but have kept the JOIN on that table.

Anyone have an idea?