[mythtv-users] High mysql cpu usage

[Michael T. Dean]

On 01/07/2013 02:36 PM, Jarle Thorsen wrote:
> 2013/1/6 Michael T. Dean
>> On 01/06/2013 02:01 PM, Jarle Thorsen wrote:
>>> I have now gone through all my channels manually and removed any channel
>>> that I was not able to tune in to. I now have about 400 channels, all with
>>> EIT enabled.
>>>
>>> Every time I see the following in my backend log:
>>>
>>>   2013-01-06 19:44:36.559637 I [19447/19514] Scheduler scheduler.cpp:2129
>>> (HandleReschedule) - Reschedule requested for MATCH 0 0 0 - EITScanner
>>> <snip>
>>> 2013-01-06 19:46:19.206036 I [19447/19514] Scheduler scheduler.cpp:2242
>>> (HandleReschedule) - Scheduled 304 items in 102.0 = 100.30 match + 0.35
>>> check + 1.39 place
>>>
>> That's terribly bad performance.  Your scheduler query should run in a few
>> seconds.  Yours is taking>  100s.
>>
>> Scheduler performance depends on (in addition to hardware resources--CPU
>> and RAM--and file system--not blocking when MySQL is trying to write (due
>> to I/O wait or things like barriers blocking until data is on the platter,
>> versus the disk cache)) number of programs in the listings (which, itself,
>> is generally affected by the number of channels), number of recording rules
>> (which may match some of those programs in the listings), and number of
>> shows in recording history.
>>
>> That said, this performance (unless you're running MySQL on an Atom or a
>> PogoPlug or similar--which just isn't enough for a large, cable-sized
>> system, especially if you're using EIT) is bad enough it's almost
>> definitely not your rules/history.  I have 17831 shows in my recorded
>> history, 119 recording rules, but only 35 channels, and my system gives:
>>
>> Scheduled 517 items in 1.3 = 0.00 match + 1.25 place
>>
>> (2 orders of magnitude faster than your system).  Therefore, I'd suggest
>> you should look at other things.  What CPU and how much RAM do you have?
>>   What file system are you using?  What mount options?  Do you have MySQL on
>> the same file system (really bad) or disk (not ideal) as your recordings?
> My machine is an Intel(R) Core(TM)2 Duo CPU     E6750  @ 2.66GHz with 3GB
> RAM (only 2GB used at the moment). My root filesystem and MySQL db is
> running on a separate disk with ext3 filesystem. Storage is all running on
> separate disks with xfs filesystem.
>
> The oldrecorded table contains 12531 entries, and the record table contains
> 189 entries.
>
> I noticed that my root ext3 filesystem was mounted with relatime, remounted
> it with noatime, but this did not seem to help.
>
> Suggestions for further settings to check are appreciated.

You probably are using ext3 with barriers.  Please check:

cat /proc/mounts

and if you don't see "barrier=0", you're using barriers.

I'm also guessing you're using the unsupported InnoDB storage engine.  
InnoDB usage requires very specific configuration to get acceptable 
performance.  You can find out what MySQL storage engine you're using with:

mysql -umythtv -p mythconverg -e 'SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = "mythconverg";'

(you can copy/paste the above into a shell--spacing is fine, even with 
the line feeds).  If any tables other than the MythWeather tables 
(weatherdatalayout, weatherscreens, weathersourcesettings) show InnoDB 
engine, this is likely the root of your problems.

The only supported way to fix a broken schema is to:

a) Create a full database backup ( 
http://www.mythtv.org/wiki/Database_Backup_and_Restore )
b) Do a partial restore of the backup ( 
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Partial_restore_of_a_backup 
).  This involves
     1) dropping the current/broken database
     2) creating a new database (with an empty schema)
     3) starting mythtv-setup on the master backend to let it create a 
brand-new (correct) schema--make sure you use
     4) exiting mythtv-setup as soon as the GUI is displayed
     5) doing the partial restore
     6) starting mythtv-setup and reconfiguring the master backend
     7) reconfiguring the rest of your setup (other backends and 
frontends) as desired

You'll need to do the above with MythTV 0.25-fixes or higher; otherwise, 
you will use your system's default storage engine (which is likely 
InnoDB) rather than the supported MyISAM engine.  Note that partial 
restores cannot be done across different versions, so you will need to 
ensure you've upgraded the current/broken database schema to 0.25-fixes 
or higher before creating the backup you plan to use for the partial 
restore.  See the wiki page description for details.

Mike