[mythtv-users] High mysql cpu usage

[Michael T. Dean]

On 03/24/2012 01:55 PM, martin wrote:
> 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

Definitely worth trying optimize_mythdb.pl (or Repair Tables then 
Optimize Tables in MythWeb's setup area, under Database).  That will 
check for corrupt tables and then repair them for you and optimize data 
layout within the tables.

If that does help, you can set up optimize_mythdb.pl to run in a cron 
job (daily or weekly or ...) at some time when you're unlikely to be 
recording (it's best to run when the system is mostly idle).

If not, there's a good chance the issue is related to a recent change in 
the scheduler, but I'll have to do some more digging to find the info on 
it and come up with a test for you.

Mike