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

[GXL.nl]

Michael T. Dean said the following on 19/11/09 21:30:
> On 11/19/2009 02:49 PM, GXL.nl wrote:
>> Michael T. Dean said the following on 19/11/09 20:33:
>>> On 11/19/2009 02:09 PM, GXL.nl wrote:
>>>> I have the same thing, also made a bug report (#7604) for it since 
>>>> it only happens since I upgraded to 0.22 (and from Ubuntu 8.04 to 
>>>> 9.1).
>>>>
>>>> I have enabled the slow-query-log from MySQL and it shows the 
>>>> following query as being slow (13 secs):
>>>>
>>>>
>>> <scheduler query>
>>>> 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.
>>>>
>>>> Anyone have an idea? 
>>>
>>> GXL, I'm assuming you're the person who submitted ticket #7604, 
>>> right?  Are you seeing any issues or negative effects or UI slowness 
>>> or anything?
>>>
>>> That query does take time.  13s could be normal or may indicate a 
>>> problem on your system--depending on a lot of different factors.  
>>> But, the UI should be completely disconnected from the scheduler 
>>> query's execution, so it shouldn't affect anything even if it is 
>>> running slow.
>>>
>>> Also, can you please provide the (complete) output of:
>>>
>>> mythbackend --version
>>>
>>> Please describe symptions--what do you see besides just a query 
>>> being reported in the slow-query log?
>> Yes it was me who entered that ticket and I was about to write a mail 
>> to the list as (you?) suggested in that ticket when I saw Ronald 
>> having the same issue.
>>
>> The frontend hangs on the confirmation message. So when I select a 
>> recording to delete and I press `Yes Delete` that window will be 
>> shown for about 15-20 secs. It might not sound as a long time but 20 
>> sec waiting is a long time :) That is the only thing noticable (but I 
>> havent really used this version yet).
>
> Can you run and paste the output of:
>
> mysql -umythtv -p mythconverg -e 'SHOW INDEXES FROM oldrecorded;'
>
> Mike
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
mysql -umythtv -p mythconverg -e 'SHOW INDEXES FROM oldrecorded;'
Enter password:
> +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table       | Non_unique | Key_name    | Seq_in_index | Column_name 
> | Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
> Comment |
> +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | oldrecorded |          0 | PRIMARY     |            1 | station     
> | A         |          25 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          0 | PRIMARY     |            2 | starttime   
> | A         |        4226 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          0 | PRIMARY     |            3 | title       
> | A         |        4226 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | endtime     |            1 | endtime     
> | A         |        4226 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | title       |            1 | title       
> | A         |         183 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | seriesid    |            1 | seriesid    
> | A         |         201 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | programid   |            1 | programid   
> | A         |          37 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | recordid    |            1 | recordid    
> | A         |         211 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | recstatus   |            1 | recstatus   
> | A         |           8 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | recstatus   |            2 | programid   
> | A         |          44 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | recstatus   |            3 | seriesid    
> | A         |         211 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | recstatus_2 |            1 | recstatus   
> | A         |           8 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | recstatus_2 |            2 | title       
> | A         |         201 |     NULL | NULL   |      | BTREE      
> |         |
> | oldrecorded |          1 | recstatus_2 |            3 | subtitle    
> | A         |        1408 |     NULL | NULL   |      | BTREE      
> |         |
> +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>
Nijn