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

GXL.nl info at gxl.nl
Thu Nov 19 22:04:56 UTC 2009


Michael T. Dean said the following on 19/11/09 22:45:
> 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
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
> 

I ran the query on two machines, one of them Quad Q9550 with 6GB and even then it takes 10 sec.


cat << "EOF" | mysql -umythtv -p mythconverg
 > SELECT COUNT(*) FROM program;
 > SELECT COUNT(*) FROM record;
 > SELECT COUNT(*) FROM recordmatch;
 > EOF
Enter password:
COUNT(*)
42261
COUNT(*)
149
COUNT(*)
606


More information about the mythtv-users mailing list