[mythtv-users] High mysql cpu usage
Jarle Thorsen
jarlethorsen at gmail.com
Sun Dec 30 12:30:09 UTC 2012
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:11:36.381909 I [10117/10231] Scheduler scheduler.cpp:2129
(HandleReschedule) - Reschedule requested for MATCH 0 0 0 - EITScanner
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
2012-12-30 13:14:27.607802 I [10117/10232] HouseKeeping housekeeper.cpp:221
(RunHouseKeeping) - Running housekeeping thread
2012-12-30 13:15:52.708895 I [10117/10231] Scheduler scheduler.cpp:2129
(HandleReschedule) - Reschedule requested for MATCH 0 0 0 - EITScanner
2012-12-30 13:17:32.275981 I [10117/10231] Scheduler scheduler.cpp:2242
(HandleReschedule) - Scheduled 294 items in 99.4 = 97.44 match + 0.45 check
+ 1.51 place
2012-12-30 13:18:30.218244 I [10117/10231] Scheduler scheduler.cpp:2129
(HandleReschedule) - Reschedule requested for MATCH 0 0 0 - EITScanner
2012-12-30 13:19:29.609454 I [10117/10232] HouseKeeping housekeeper.cpp:221
(RunHouseKeeping) - Running housekeeping thread
2012-12-30 13:20:09.262982 I [10117/10231] Scheduler scheduler.cpp:2242
(HandleReschedule) - Scheduled 294 items in 98.9 = 96.28 match + 0.43 check
+ 2.21 place
Any hints?
2012/3/28 Terjesen Jens Peder <Jens.Peder.Terjesen at devoteam.com>
> -----Original Message-----
> On 26. mars 2012 10:49 martin wrote:
>
> On 26/03/12 08:29, Terjesen Jens Peder wrote:
> > -----Original Message-----
> > On 24. mars 2012 6:56 martin wrote:
> >
> > On 24/03/12 05:02, Michael T. Dean wrote:
> >> On 03/23/2012 06:43 PM, martin wrote:
> >>> I have been running the master branch for many months but over the
> >>> last few weeks I have seen the system become very unresponsive.
> >>> Unfortunately I can't say exactly when the problem started. When I
> >>> looked at cpu usage it shows mysql at 100% usage for long periods of
> time.
> >>>
> >>> The log below was produced with -v schedule.
> >>>
> >
> >>>
> >>> As you can see from the above logs the scheduling run took approx 39
> >>> secs and mysqld was showing 100% cpu usage for the whole time.
> >>>
> >>> I have done all the mysql tuning suggested in previous messages and
> >>> also run mysqltuner.pl but it hasn't helped. I have also "repaired"
> >>> and "optimised" the database. The backend uses a AMD Athlon(tm) 64
> >>> X2 Dual Core Processor 5200+ with 2Gb of RAM so is not short of power.
> >>>
> >>> Any ideas would be appreciated.
> >>>
> >>
> >> I'm guessing mysql data on a file system that's using barriers (i.e.
> >> ext4 or something). Note that I'm not suggesting you disable
> >> barriers nor that you change to a different file system (as another
> >> file systems without barriers isn't necessarily safer than ext4 without
> barriers).
> >> There have been several threads on this list where people discussed
> >> them. Search for: ext4 barrier
> >>
> >> I'm not providing links because I'm not suggesting any course of
> >> action nor am I trying to imply that you can or can not make MySQL
> >> server perform sufficiently well with its data on a file system with
> >> barriers enabled. I'm simply pointing to the most likely cause of
> >> the performance difference you're noticing from "before" and leaving
> >> it to you to do the research and decide how important you feel
> >> barriers--and then decide what approach to take to fix the performance
> issues.
> >>
> >> Mike
> >
> > Mike,
> >
> > Thank you for the detailed response. I have seen some of those threads
> but assumed they didn't apply as I had not changed any kernel software for
> the past 6 months and the change in mysql performance happened during that
> time.
> >
> > To check if it is file system related I have installed a new SSD disk
> and created a 5 gb ext4 partition just for the database files. This allows
> me to change the mount options. There is nothing else on the SSD disk.
> >
> > With the kernel defaults the scheduler run is taking approx 45 sec at
> 100% cpu. Mounting with noatime the runtime and cpu usage stay the same.
> > I tried data=writeback but that sends the scheduler a bit crazy so I
> took that back off. With noatime and barrier=0 the scheduler run takes
> 40secs at 100% cpu.
> >
> > In case it was the tmp files I put the tmp files into /dev/shm but that
> didn't change the times either.
> >
> > So there is a small effect with nobarriers but it is not the real
> problem. I wonder if my database is corrupt somewhere. It was first created
> in 2003 and been upgraded many times since then so there has been lots of
> chances for something to go wrong.
> >
> > Anything else I can check?
> >
> > Thanks for all the help
> >
> > Martin
> >
> > -----Original Message-----
> >
> > I am in the same situation.
> > How are you updating the EPG?
> > I am using EIT and have about 700 channels from two satellite positions,
> and EPG for seven to eight days.
> >
> > Using SHOW PROCESSLIST; in mysql showed that insertions and
> modifications of the program table had a status of LOCKED for long periods
> of time while the mysqld CPU load was at near 100%.
> >
> > In an attempt to verify this to be the problem I deleted all channels,
> inputs and cards. This deleted all rows in the eit_cache and program
> tables. I then set up cards and inputs again and did a scan for channels.
> > Immediately after this the mysqld CPU load was down at a more normal
> level of 10-20%. After about 24 hours the CPU load was back up at near 100%
> again.
> >
> > Since my combined BE/FE is connected to an UPS I have disabled barriers
> on the EXT4 partition containing the OS and the database. Disabling
> barriers have reduced the rescheduling time from about 25-30 seconds to
> about 15 seconds but not had any impact on the CPU load.
> >
> > Jens
> >
> Jens,
>
> My master backend has 2 cards on UK Freeview and my slave backend has a
> card on Freesat. I update the schedule for both using EIT. I have 455
> channels in the database and 129,000 entries in the program table.
>
> I am still using all the changes I described above and it has certainly
> made mythweb respond faster except for the ~40 secs when the scheduler is
> running.
>
> Martin
> -----Original Message-----
>
> Running SHOW FULL PROCESSLIST; while mysqld CPU load is high shows many
> connections sleeping but some running for tens of seconds.
>
> mysql> SHOW FULL PROCESSLIST;
>
> <snip>
>
> | 3238 | mythtv | localhost | mythconverg | Sleep | 25 |
> | NULL
>
>
>
>
>
>
>
>
>
>
>
>
> |
> | 3239 | mythtv | localhost | mythconverg | Execute | 80 | Sending
> data | REPLACE INTO recordmatch (recordid, chanid, starttime, manualid)
> SELECT record.recordid, program.chanid, program.starttime, IF(search = 5,
> record.recordid, 0) FROM (record, program INNER JOIN channel ON
> channel.chanid = program.chanid) WHERE record.search = 0 AND
> program.manualid = 0 AND program.seriesid <> '' AND program.seriesid =
> record.seriesid AND channel.visible = 1 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(program.starttime) >= 19 AND
> HOUR(program.starttime) < 23)) 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_TO_SEC(record.starttime) = TIME_TO_SEC(program.starttime)) AND
> ((record.type = 2) OR ((DAYOFWEEK(record.startdate) =
> DAYOFWEEK(program.starttime) AND ((record.type = 5) OR
> ((TO_DAYS(record.startdate) = TO_DAYS(program.starttime)) AND
> (record.type <> 0) ) ) ) ) ) ) ) )) |
> | 3240 | mythtv | localhost | mythconverg | Sleep | 21 |
> | NULL
>
> <snip>
>
> | 3244 | mythtv | localhost | mythconverg | Sleep | 12 |
> | NULL
>
>
>
>
>
>
>
>
>
>
>
>
> |
> | 3245 | mythtv | localhost | mythconverg | Execute | 35 | Locked
> | UPDATE program SET title = 'Noitatyttö Bibi', subtitle
> = 'Bibi ja vampyyrit. Bibin isä on innostunut vanhoista
> vampyyrileffoista.', description = 'Amanda antaa Bibin perheelle
> lahjakortin transsilvanialaiseen linnahotelliin.', category =
> 'Barn', category_type = 'tvshow', starttime = '2012-03-28
> 07:30:00', endtime = '2012-03-28 08:00:00', closecaptioned = 0,
> subtitled = 0, stereo = 0, hdtv = 0,
> subtitletypes = 0, audioprop = 0, videoprop = 0,
> partnumber = 0, parttotal = 0, syndicatedepisodenumber = '',
> airdate = '0000', originalairdate=NULL, listingsource =
> 1, seriesid = '', programid = '', previouslyshown = 0
> WHERE chanid = 5907 AND starttime = '2012-03-28 07:30:00'
>
>
> |
> | 3247 | mythtv | localhost | mythconverg | Sleep | 39 |
> | NULL
>
>
>
>
>
>
>
>
>
>
>
>
> |
> | 3249 | mythtv | localhost | mythconverg | Execute | 24 | Locked
> | SELECT title, subtitle, description, category,
> category_type, starttime, endtime,
> subtitletypes+0,audioprop+0, videoprop+0, seriesid,
> programid, partnumber, parttotal,
> syndicatedepisodenumber, airdate, originalairdate,
> previouslyshown,listingsource, stars+0 FROM program WHERE chanid
> = 5811 AND manualid = 0 AND ( ( starttime >= '2012-03-28
> 07:39:00' AND starttime < '2012-03-28 08:07:00' ) OR ( endtime >
> '2012-03-28 07:39:00' AND endtime <= '2012-03-28 08:07:00' ) )
>
>
>
>
>
> |
> | 3257 | mythtv | localhost | mythconverg | Sleep | 1155 |
> | NULL
>
> <snip>
>
> Jens
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users
>
--
Jarle Thorsen
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20121230/89c8a335/attachment.html>
More information about the mythtv-users
mailing list