[mythtv-users] [mythtv-commits] Ticket #7604: Very slow query when delete recording (was Re: Very slow mythfrontend, mysql at 100%)

[Michael T. Dean]

On 11/19/2009 03:58 PM, GXL.nl wrote:
> Michael T. Dean said the following on 19/11/09 21:38:
>> On 11/19/2009 03:27 PM, GXL.nl wrote:
>>> Yes, I run mythfilldatabase although not daily and since 0.22 it may 
>>> have run only 2-3 times.
>>>
>>> The log also shows:
>>> Rows_sent: 608  Rows_examined: 2574883
>>> Which seems like a lot of rows examined and might indicate a bad 
>>> join/index?
>> mysql -umythtv -p mythconverg -e
>>
>> cat << "EOF" | mysql -umythtv -p mythconverg
>> SELECT COUNT(*) FROM oldrecorded;
>> SELECT NOW();
>> SELECT MIN(starttime) FROM oldrecorded WHERE recstatus NOT IN (-3, 11);
>> EOF 
> $ cat << "EOF" | mysql -umythtv -p mythconverg
> > SELECT COUNT(*) FROM oldrecorded;
> > SELECT NOW();
> > SELECT MIN(starttime) FROM oldrecorded WHERE recstatus NOT IN (-3, 11);
> > EOF
> Enter password:
> COUNT(*)
> 4226
> NOW()
> 2009-11-19 21:57:21
> MIN(starttime)
> 2009-11-08 19:40:00 

That and the reply with the indices shows that your oldrecorded table 
(the one aliased as oldrecstatus in the query that you saw was slow) is 
in good shape.  You have all the indices you should have and it's being 
cleaned up properly by the backend (so I'll assume the other tables get 
cleaned up just as well).

Therefore, I'm guessing you either just have a) a huge number of 
programs in your listings, b) a huge number of recording 
rules/schedules, c) a huge number of matches on your rules, d) an 
underpowered and/or frequency-scaled CPU, or e) a poor-performing MySQL 
configuration.

cat << "EOF" | mysql -umythtv -p mythconverg
SELECT COUNT(*) FROM program;
SELECT COUNT(*) FROM record;
SELECT COUNT(*) FROM recordmatch;
EOF

Mike