[mythtv-users] High mysql cpu usage

Tue Jan 8 20:11:35 UTC 2013

What would be wrong with doing a full database dump with
'--create-options', edit the backup file and change all references from
InnoDB to MyISAM, and then drop the database and re-import the backup? I've
done that in the past with success on other databases, and it would be much
easier.

Tom

On Tue, Jan 8, 2013 at 2:35 PM, Michael T. Dean <mtdean at thirdcontact.com>wrote:

> 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<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<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
> ______________________________**_________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/**listinfo/mythtv-users<http://www.mythtv.org/mailman/listinfo/mythtv-users>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20130108/2fd13a00/attachment.html>