[mythtv-users] High mysql cpu usage

Jarle Thorsen jarlethorsen at gmail.com
Sun Jan 6 19:01:54 UTC 2013


2012/12/30 David Scammell <davescammell at gmail.com>

> Thanks for the replies, i'm wondering about the duplicate freesat
> channels of CH4 etc also. I've got lots of ram to cache table lookups
> and am writing over iscsi which I would guess would not honour barrier
> requests but then again they might. ;( ugh.
>
> On 30/12/2012, Michael T. Dean <mtdean at thirdcontact.com> wrote:
> > On 12/30/2012 12:25 PM, Jarle Thorsen wrote:
> >> 2012/12/30 David Scammell
> >>
> >>> 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.
> >>>
> >> I have currently about 700 channels, and I am in the process of deleting
> >> channels that I can not tune in to. I'm thinking that my number of
> >> (possibly) bad channels may contribute to my problem.
> >>
> >> I have seen some reports of similar problems for ext4 users, but I'm not
> >> running ext4 on my box.
> >
> > It's not just ext4.  It's most any file system with barriers
> > enabled--because that slows write speeds to platter write speeds (it
> > completely disables disk cache (in software /and/ hardware) for writes).
>

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

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)        )       )      )     )    )   )  ) )) |

<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?

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


More information about the mythtv-users mailing list