[mythtv-users] High mysql cpu usage
Terjesen Jens Peder
Jens.Peder.Terjesen at devoteam.com
Mon Mar 26 07:29:08 UTC 2012
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.
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
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.
More information about the mythtv-users