[mythtv-users] High mysql cpu usage

[David Scammell]

I upgraded from 0.24 to 0.26 not too long ago and found exactly the
same situation. I have nearly 4000 recording rules, I've been running
myth for a long time (thanks!). Mostly any time/any channel rulesand
had approximately 120 channels setup (uk freeview/freesat). EIT+RT
grabbers.

I tried a lot of things to resolve the situation, with the exception
of checking the indexes of tables responsible for the re-scheduling
code were created as they should be, I should still do this. My mysql
slow query log was showing up to 20 minutes for the update into
recordmatch sql to run which made myth unusable for any purpose.
Interestingly converting tables (channel, recordmatch, program and
record) to innodb (unsupported!) with tuning halved the slowquery
times to 10 minutes.

I did have barriers and ext4 running but switched them off at an early
stage, it made some difference.

I looked at my housekeeping jobs after seeing this today but i'm
unsure if some of the items aren't from old versions of myth:

| DailyCleanup                 | 2012-12-30 07:41:36 |
| JobQueueRecover-bradford     | 2012-12-30 13:55:41 |
| BackupDB                     | 2012-08-27 21:02:06 |
| DBCleanup                    | 2009-11-05 22:04:05 |
| JobQueueRecover-leeds        | 2012-12-29 05:34:41 |
| MythFillDB                   | 2012-12-30 10:48:44 |
| LogClean                     | 2012-12-29 12:36:04 |
| ThemeChooserInfoCacheUpdate  | 2012-12-30 00:01:07 |

At the end of the day I'm unsure if there's a problem with indexes but
my situation was fixed by setting the visible=0 setting on a number of
'useless' channels that i'm unlikely to record from, bringing the
number of visible channels to about 90. My longest slow query is now
13 seconds, once or twice a day.

cheers
dave






On 30/12/2012, Karl Dietz <dekarl at spaetfruehstuecken.org> wrote:
> On 30.12.2012 13:30, Jarle Thorsen wrote:
>> Having had problems with a very unresponsive system for a long time I
>> have now finally found time to look into it. I've had this problem on
>> 0.25 and now on 0.26 (and probably on 0.24 too) It seems like what I am
>> experiencing is the same problem described in this thread.
>> (http://www.mythtv.org/pipermail/mythtv-users/2012-March/330451.html)
>>
>> Basically mysqld is running at almost 100% CPU for minutes at a time,
>> and no other tasks are able to get any data from the db and just hangs
>> until mysqld has finished it's task. Looking at the backend log it seems
>> to always happen in conjunction with "Reschedule requested for MATCH 0 0
>> 0 - EITScanner". (and it seems to take a very long time to finish):
>>
>> 2012-12-30 13:13:17.530239 I [10117/10231] Scheduler scheduler.cpp:2242
>> (HandleReschedule) - Scheduled 294 items in 101.0 = 97.08 match + 0.42
>> check + 3.51 place
>
> 97 seconds just for finding all programs that MATCH your recording
> rules seems to be a bit much.
>
> Can you check that programs in the past get cleaned out?
>    select * from housekeeping;
> Look if you have sane values for "lastrun".
>
> And verify that your database indices are intact. Just run the
> repair/optimize script for that.
> http://www.mythtv.org/wiki/User_Manual:Periodic_Maintenance#Optimize_the_Database
>
> Regards,
> Karl
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users
>