[mythtv-users] High mysql cpu usage

[martin]

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