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

[Michael T. Dean]

On 11/19/2009 04:12 PM, Dan Wilga wrote:
> 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.

Excellent idea.

> 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` );

Please don't modify the schema.  Doing so only breaks upgrades and then 
results in invalid tickets that waste time (when we're trying to find 
out why your broken database schema doesn't upgrade).

If there's a need for those indices, please file a ticket on trac with 
information such as the reasoning behind the indices and examples of the 
improvement they show.

> 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.

Good point.  NFS/CIFS may also be bad.

Mike