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

[GXL.nl]

Dan Wilga said the following on 19/11/09 22:12:
> At 8:09 PM +0100 11/19/09, GXL.nl wrote:
>> 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.
> 
> In my experience, there should be no speed difference if all you did was 
> remove two fields from the SELECT part of the statement. It's usually 
> necessary to remove a JOIN or sub-select.
> 
> The other two things that can have a huge effect on speed are missing 
> indices on JOINed fields and different character collations (latin1 vs. 
> UTF-8, for instance) on fields being compared or JOINed.
> 
> I just ran this on my machine, with the parts you removed, and it took 
> 746 mS to return 9400 rows.
> 
> Perhaps if you give me the output of EXPLAIN for this statement, 
> something will jump out. Search and replace "sched_temp_record" with 
> "record" for this to work. A few indices I found that may help are:
> 
> ALTER TABLE  `cardinput` ADD INDEX (  `sourceid` );
> ALTER TABLE  `cardinput` ADD INDEX (  `cardid` );
> ALTER TABLE  `channel` ADD INDEX (  `sourceid` );
> 
> One other thing: Where is your "tmpdir", as set in /etc/my.cnf? This 
> query operates on a temporary table which can be quite large, and uses 
> another temporary table for sorting. If your tmp_table_size is too low 
> and you haven't set a tmpdir, then mysql may be writing to a slow (USB, 
> perhaps?) disk.


My tmpdir is on the same disk as the tables (tmpdir		= /tmp)

The explain is below:
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 	



Below are the two queries I used, it might be that I'm overseeing something, to run them I copied the record table to 
sched_tmp_record. I though maybe the two missing fields made the query to big for memory but removing other fields don't have 
the same result. Am I missing something here, can you see another difference? I ran both queries multiple times, yesterday 
and today and always the same result?


SELECT SQL_NO_CACHE 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,  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
608 rows in set (2.89 sec)

SELECT SQL_NO_CACHE 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
608 rows in set (16.19 sec)