[mythtv-users] High mysql cpu usage

[Jarle Thorsen]

2013/1/12 Juhani Rautiainen <jrauti at iki.fi>

>
>
>
> On Fri, Jan 11, 2013 at 5:13 PM, Michael T. Dean <mtdean at thirdcontact.com>wrote:
>
>> On 01/11/2013 12:18 AM, Juhani Rautiainen wrote:
>>
>>>
>>>
>>> Has anyone with the problems tried to log slow queries? This should
>>> pinpoint where the time is spent. Instructions:
>>> http://dev.mysql.com/doc/**refman/5.5/en/slow-query-log.**html<http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html>. From there
>>> some EXPLAIN and SHOW PROFILE for those queries should give concrete
>>> information instead of speculation. I haven't analyzed mysql queries but
>>> from Oracle side I've got over lots of experience. Principles should be
>>> similar.
>>>
>>
>> Slowness is almost definitely not a problem with the SQL--that EIT SQL
>> hasn't changed for years.  It's most likely just that the EIT scanning
>> thread is writing data to and, therefore, locking tables that are required
>> for the scheduler to run.  So, the scheduler thread is waiting on the EIT
>> writes to complete/yield so it can finish.  I wouldn't expect high MySQL
>> CPU use due to a locking/wait issue, though, so I don't know what's causing
>> that.
>>
>
> As I said, I'm not that experienced with MySQL, put at least with Oracle
> this happens time to time. There the problem is changing size of data. That
> changes query execution plans. Sometimes queries that used to take under
> second can take minutes or hours (very rare). Usually this is related to
> table/index statistics and usually the change happen literally over night.
> And checking MySQL docs there are similar heuristics here. How the
> statistics are gotten in MySQL seem to vary between MyISAM and InnoDB.
> That's why I was suggesting checking for slow queries. If there are any, we
> could compare execution plans between systems. I don't see how logging slow
> queries hurts anything. At least you could easily rule this problem out.
>
>

I have now tried to have EIT completely off for the last 24H and it was
like giving my mythbox a new life. No lagging at all. I have now turned on
logging for slow mysql queries (longer than 20 seconds), and also turned on
passive EIT scanning.

And here we go again:

2013-01-14 20:36:10.697272 I [16203/16230] Scheduler scheduler.cpp:2129
(HandleReschedule) - Reschedule requested for MATCH 0 0 0 - EITScanner
2013-01-14 20:36:57.013252 I [16203/16230] Scheduler scheduler.cpp:2242
(HandleReschedule) - Scheduled 140 items in 46.2 = 45.42 match + 0.18 check
+ 0.60 place

mysqld is again at 100% CPU in this time window, but the query is not
logged by mysql as a slow query for some reason?

I'm guessing that the passive EIT scan gives it less data and that is the
reason why it uses apx 40 seconds, compared to the 80+ seconds with active
EIT scan enabled.

-- 
Jarle Thorsen
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20130114/5b595ac0/attachment.html>