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

[Dan Wilga]

At 10:59 PM +0100 11/19/09, GXL.nl wrote:
>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

You've got a few NULLs in the "key" column which suggest possible 
inefficiencies due to missing indices. For starters, it definitely 
looks like you are missing indices on the oldrecorded (aliased as 
oldrecstatus) table. Here's what the schema says there should be:

ALTER TABLE oldrecorded ADD  PRIMARY KEY  (station,starttime,title);
ALTER TABLE oldrecorded ADD  KEY endtime (endtime);
ALTER TABLE oldrecorded ADD  KEY title (title);
ALTER TABLE oldrecorded ADD  KEY seriesid (seriesid);
ALTER TABLE oldrecorded ADD  KEY programid (programid);
ALTER TABLE oldrecorded ADD  KEY recordid (recordid);
ALTER TABLE oldrecorded ADD  KEY recstatus (recstatus,programid,seriesid);
ALTER TABLE oldrecorded ADD  KEY recstatus_2 (recstatus,title,subtitle);

You should be able to apply these to the table, and any that already 
exist will just produce an error message (duplicate key). I suggest 
you make a backup of the table first, though.

Assuming there were some missing indices, try re-running the explain. 
You can just replace everything between DISTINCT and FROM with 
"DISTINCT channel.chanid FROM", to make the query easier to 
understand; explain doesn't care about the other columns, in this 
case.

Here's what the same explain looks like on my DB (hope the wrapping 
is preserved):

+----+-------------+--------------+--------+-------------------------------------------------+----------+---------+---------------------------------------------------------------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table        | type   | possible_keys 
| key      | key_len | ref 
| rows | Extra                                        |
+----+-------------+--------------+--------+-------------------------------------------------+----------+---------+---------------------------------------------------------------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | record       | index  | PRIMARY 
| PRIMARY  | 4       | NULL 
|  106 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | recordmatch  | ref    | recordid 
| recordid | 5       | mythconverg_022.record.recordid 
|    7 | Using where                                  |
|  1 | SIMPLE      | channel      | eq_ref | 
PRIMARY,sourceid,sourceid_2                     | PRIMARY  | 4 
| mythconverg_022.recordmatch.chanid 
|    1 |                                              |
|  1 | SIMPLE      | cardinput    | ALL    | sourceid,cardid 
| NULL     | NULL    | NULL 
|    9 | Using where; Distinct; Using join buffer     |
|  1 | SIMPLE      | capturecard  | eq_ref | PRIMARY 
| PRIMARY  | 4       | mythconverg_022.cardinput.cardid 
|    1 | Using index; Distinct                        |
|  1 | SIMPLE      | program      | eq_ref | 
PRIMARY,id_start_end,program_manualid,starttime | PRIMARY  | 16 
| 
mythconverg_022.recordmatch.chanid,mythconverg_022.recordmatch.starttime,mythconverg_022.recordmatch.manualid 
|    1 | Distinct                                     |
|  1 | SIMPLE      | oldrecstatus | eq_ref | PRIMARY,title 
| PRIMARY  | 456     | 
mythconverg_022.channel.callsign,mythconverg_022.recordmatch.starttime,mythconverg_022.program.title 
|    1 | Using index; Distinct                        |
+----+-------------+--------------+--------+-------------------------------------------------+----------+---------+---------------------------------------------------------------------------------------------------------------+------+----------------------------------------------+
-- 
Dan Wilga                                                        "Ook."