[mythtv-users] High mysql cpu usage
Terjesen Jens Peder
Jens.Peder.Terjesen at devoteam.com
Wed Mar 28 10:01:27 UTC 2012
-----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
More information about the mythtv-users
mailing list