info at gxl
Nov 19, 2009, 1:59 PM
Post #6 of 14
(955 views)
Permalink
|
|
Re: Very slow mythfrontend, mysql at 100%
[In reply to]
|
|
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)
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
|
