[mythtv-users] High mysql cpu usage

[Michael T. Dean]

On 01/06/2013 02:01 PM, Jarle Thorsen wrote:
> I have now gone through all my channels manually and removed any 
> channel that I was not able to tune in to. I now have about 400 
> channels, all with EIT enabled.
>
> Every time I see the following in my backend log:
>
>  2013-01-06 19:44:36.559637 I [19447/19514] Scheduler 
> scheduler.cpp:2129 (HandleReschedule) - Reschedule requested for MATCH 
> 0 0 0 - EITScanner
> <snip>
> 2013-01-06 19:46:19.206036 I [19447/19514] Scheduler 
> scheduler.cpp:2242 (HandleReschedule) - Scheduled 304 items in 102.0 = 
> 100.30 match + 0.35 check + 1.39 place

That's terribly bad performance.  Your scheduler query should run in a 
few seconds.  Yours is taking > 100s.

Scheduler performance depends on (in addition to hardware resources--CPU 
and RAM--and file system--not blocking when MySQL is trying to write 
(due to I/O wait or things like barriers blocking until data is on the 
platter, versus the disk cache)) number of programs in the listings 
(which, itself, is generally affected by the number of channels), number 
of recording rules (which may match some of those programs in the 
listings), and number of shows in recording history.

That said, this performance (unless you're running MySQL on an Atom or a 
PogoPlug or similar--which just isn't enough for a large, cable-sized 
system, especially if you're using EIT) is bad enough it's almost 
definitely not your rules/history.  I have 17831 shows in my recorded 
history, 119 recording rules, but only 35 channels, and my system gives:

Scheduled 517 items in 1.3 = 0.00 match + 1.25 place

(2 orders of magnitude faster than your system).  Therefore, I'd suggest 
you should look at other things.  What CPU and how much RAM do you 
have?  What file system are you using?  What mount options?  Do you have 
MySQL on the same file system (really bad) or disk (not ideal) as your 
recordings?

> the mysqld process goes through the roof and any other program trying 
> to access the db will just hang until the scheduling is finished 
> (which takes more than 1 1/2 minute!).
>
>  mysql> show full processlist;
> <snip>
> | 1209 | mythtv | localhost                          | mythconverg | 
> Execute |     8 | Sending data | REPLACE INTO recordmatch (recordid, 
> chanid, starttime, manualid,                           
> oldrecduplicate, findid) SELECT record.recordid, program.chanid, 
> program.starttime,  IF(search = 5, record.recordid, 0), (CASE   WHEN 
> record.type IN (1, 7, 8) THEN  0   WHEN record.type IN (6, 9, 10) 
> THEN  -1   ELSE (program.generic - 1)  END) , (CASE record.type   WHEN 
> 6    THEN record.findid   WHEN 9    THEN 
> to_days(date_sub(convert_tz(program.starttime, 'UTC', 
> 'SYSTEM'),             interval time_format(record.findtime, '%H:%i') 
> hour_minute))   WHEN 10    THEN 
> floor((to_days(date_sub(convert_tz(program.starttime, 
> 'UTC',             'SYSTEM'), interval time_format(record.findtime, 
> '%H:%i')             hour_minute)) - record.findday)/7) * 7 + 
> record.findday   WHEN 7    THEN record.findid   ELSE 0  END) FROM 
> (record, program INNER JOIN channel       ON channel.chanid = 
> program.chanid)  WHERE record.type <> ? AND record.search = ? AND 
> program.manualid = 0 AND program.seriesid <> '' AND program.seriesid = 
> record.seriesid  AND channel.visible = 1  AND program.endtime > (NOW() 
> - INTERVAL 480 MINUTE) AND (((record.filter & 1) = 0) OR 
> (program.previouslyshown = 0)) AND (((record.filter & 2) = 0) OR 
> (program.generic = 0)) AND (((record.filter & 4) = 0) OR 
> (program.first > 0)) AND (((record.filter & 8) = 0) OR 
> (HOUR(CONVERT_TZ(program.starttime, 'UTC', 'SYSTEM')) >= 19 AND 
> HOUR(CONVERT_TZ(program.starttime, 'UTC', 'SYSTEM')) < 22)) AND 
> (((record.filter & 16) = 0) OR (channel.commmethod = -2)) AND 
> (((record.filter & 32) = 0) OR (program.hdtv > 0)) AND 
> (((record.filter & 64) = 0) OR ((record.programid <> '' AND 
> program.programid = record.programid) OR (record.programid = '' AND 
> program.subtitle = record.subtitle AND program.description = 
> record.description))) AND (((record.filter & 128) = 0) OR 
> ((record.seriesid <> '' AND program.seriesid = record.seriesid))) AND 
> ((record.type = 4 OR record.type = 6 OR record.type = 9 OR record.type 
> = 10)  OR  ((record.station = channel.callsign)   AND   ((record.type 
> = 3)    OR   (( TIME(CONVERT_TZ(ADDTIME(record.startdate, 
> record.starttime), 'UTC', 'SYSTEM')) = 
> TIME(CONVERT_TZ(program.starttime, 'UTC', 'SYSTEM')))     AND     
> ((record.type = 2)      OR     
> ((DAYOFWEEK(CONVERT_TZ(ADDTIME(record.startdate, record.starttime), 
> 'UTC', 'SYSTEM')) = DAYOFWEEK(CONVERT_TZ(program.starttime, 'UTC', 
> 'SYSTEM'))       AND       ((record.type = 5)        OR       
> ((ADDTIME(record.startdate, record.starttime) = 
> program.starttime)         AND (record.type <> 0)        )       
> )      )     )    )   )  ) )) |

That's the scheduler query--a big one.

>
> <snip>
> | 1221 | mythtv | localhost                          | mythconverg | 
> Execute |     6 | Locked       | UPDATE program SET title          = 
> ?,     subtitle      = ?,     description    = ?,     category       = 
> ?,  category_type = ?,     starttime      = ?, endtime       = ?,     
> closecaptioned = ?,        subtitled     = ?,     stereo         = 
> ?,    hdtv          = ?,     subtitletypes  = ?,     audioprop      = 
> ?, videoprop     = ?,     partnumber     = ?,    parttotal     = 
> ?,     syndicatedepisodenumber = ?,     airdate        = ?,   
> originalairdate=?,     listingsource  = ?,     seriesid       = ?,  
> programid     = ?,     previouslyshown = ? WHERE chanid    = ? 
> AND       starttime = ?
>
> <snip>
>
> This last process seems very strange, doesn't it?

And that one is EIT inserting data.

Mike