[mythtv-users] High mysql cpu usage

Juhani Rautiainen jrauti at iki.fi
Sat Jan 12 11:55:35 UTC 2013


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.



> Mike
>
>
Juhani
-- 
Juhani Rautiainen                                   jrauti at iki.fi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20130112/25d938b3/attachment.html>


More information about the mythtv-users mailing list