[mythtv-users] High mysql cpu usage

[Michael T. Dean]

On 01/11/2013 12:18 AM, Juhani Rautiainen wrote:
> On Thu, Jan 10, 2013 at 10:47 PM, Jarle Thorsen<jarlethorsen at gmail.com>wrote:
>
>> To sum up the information in this thread so far:
>>
>> Several people report the same problem with high CPU usage for mysql (and
>> unusable system) while the EIT rescheduling is runnnig.
>>
>> None of us have seen any real improvement by removing barriers from our
>> filesystem and we all have capable hardware.
>>
>> Maybe it is just the fact that we have 100+ channels with EIT turned on
>> that makes us affected by this "bug"? Maybe somebody with more mysql skills
>> than me could have a look at the mysql-fu to see if the EIT rescheduling is
>> as optimized as it can be?
>>
> Hi!
>
> 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 . 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.

Jarle, please disable EIT (completely for a test) and see how the 
scheduler runs.  If it runs faster, you can try enabling passive 
EIT/disabling active EIT (so it's only collecting for the watched 
channels) and see if it still runs fast.  If so, I think Stuart Morgan 
was looking into problems with EIT writes getting in the way of other 
more-time-sensitive MySQL use.

If that doesn't change performance, it is possible it has to do with the 
"series link" functionality that UK users asked for.  We can look into 
that after we find out if it's just EIT getting in the way.

Mike