[mythtv-users] 0.25 SQL CPU Load issues after optimisation

[Another Sillyname]

On 18 July 2012 10:58, Mike Perkins <mikep at randomtraveller.org.uk> wrote:
> On 18/07/12 06:03, Thomas Mashos wrote:
>>
>> On Tue, Jul 17, 2012 at 5:22 PM, Another Sillyname
>> <anothersname at googlemail.com> wrote:
>>>
>>> On 18 July 2012 00:46, Thomas Mashos <thomas at mashos.com> wrote:
>>>>
>>>> On Tue, Jul 17, 2012 at 4:05 PM, Another Sillyname
>>>> <anothersname at googlemail.com> wrote:
>>>>>
>>>>> On 18 July 2012 00:01, Gary Buhrmaster <gary.buhrmaster at gmail.com>
>>>>> wrote:
>>>>>>
>>>>>> On Tue, Jul 17, 2012 at 10:48 PM, Another Sillyname
>>>>>> <anothersname at googlemail.com> wrote:
>>>>>> ....
>>>>>>>
>>>>>>> I am running ext4 and noticed from a previous comment on this
>>>>>>> problems
>>>>>>> that ext4 barriers could be an issue but couldn't find anything
>>>>>>> explanatory in the mailing list archives to explain this.
>>>>>>
>>>>>>
>>>>>> In my experience, ext4 barriers usually manifest themselves are disk
>>>>>> I/O
>>>>>> wait, and not high CPU usage (mysql is actually waiting a lot).
>>>>>>
>>>>>> If you are using innodb tables (which is possible if you rebuilt your
>>>>>> system
>>>>>> with a newer mysql server) you should review the recommendations at:
>>>>>>     http://www.mythtv.org/wiki/Tune_MySQL
>>>>>> Regardless of whether they help this particular issue, they will
>>>>>> help overall performance in the long run.
>>>>>
>>>>>
>>>>> Thanks Gary but I'd already done that, I've been looking at this for 3
>>>>> days now and as a number of people seem to have experienced this
>>>>> problem without a clearly defined problem/solution I though it would
>>>>> be useful to try to get some expert focus on it which means not me.
>>>>
>>>>
>>>> Looks like Linux Mint 13 is based on Ubuntu 12.04, so you did get
>>>> MySQL 5.5 from that which has innodb enabled. As previously suggested,
>>>> you aren't experiencing the same issue that other people have
>>>> commented on. If you were, those tuning parameters would have resolved
>>>> it.
>>>>
>>>> Are you running the MythTV updates from the Mythbuntu repos?
>>>
>>>
>>> When you say updates can you be specific possibly.
>>>
>>> I installed Myth from the Mint Repos that do use the Ubuntu repos and
>>> am running 0.25+Fixes 20120410.1f5962a.0ubuntu1
>>>
>>> Which I assume is coming from the standard Ubuntu repos rather then a
>>> specific mint repo.
>>
>>
>> There is newer packages available at the PPA here
>> https://launchpad.net/~mythbuntu/+archive/0.25
>>
> Somebody recently had mysql problems which turned out to be temp file space.
> That's a fairly gnarly looking sql statement and probably results in various
> temporary tables being created. Have you enough space in /tmp or wherever
> your my.cnf places temp stuff?
>
> --
>
> Mike Perkins
>
>
>
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users

Mike

164GB of space on the drive with the tmp directory and no permissions
issues so I think we're OK there but thanks for the idea.

I think it's the nature of the SQL statement, I've read elsewhere it's
not a good idea to combine a replace with a conditional query in the
same statement but it's outside the scope of my experience.