[mythtv-users] 0.25 SQL CPU Load issues after optimisation
Warpme
warpme at o2.pl
Fri Jul 20 19:30:13 UTC 2012
On 7/20/12 12:02 AM, Another Sillyname wrote:
> On 19 July 2012 22:30, Warpme <warpme at o2.pl> wrote:
>> On 7/18/12 12:48 AM, Another Sillyname wrote:
>>> I recently rebuilt my system from a .23 fixes setup to a .25 fixes
>>> setup using the Linux Mint Mate 13 repo's.
>>>
>>> While setting up and tweaking the system over the last couple of weeks
>>> I started to notice a couple of performance 'glitches' but had other
>>> things I needed to get setup correctly so they got my focus.
>>>
>>> I've now had time to do some analysis and this problem has been
>>> mentioned on the list before but I can't see a clear answer to it.
>>>
>>> My backend is a Quad core Intel Q6700 running at 2.66Ghz and has 4GB
>>> of memory. There are a couple of other services running from that
>>> server but it never runs at anything approaching it's load capability.
>>>
>>> The SQL database is becoming CPU bound on two of the cores in 'bursts'
>>> where two cores will peak at 100% usage for up to 50 seconds at a
>>> time.
>>>
>>> I done all the usual optimisations that are provided by myth and also
>>> used the appropriate mysqlcheck commands to check and optimise the SQL
>>> tables, all to no effect.
>>>
>>> I've also run mysqltuner.pl and it is not showing any tweaking needing
>>> to be done.
>>>
>>> So I wrote a script to detect when the sql server was running above
>>> 75% load and then capture SHOW FULL PROCESSLIST into a dumpfile until
>>> the load fell back below 75%.
>>>
>>> At times this loading is happening for over 60 seconds which frankly
>>> doesn't make much sense.
>>>
>>> In the dumped files to one line that shows up time and time again is
>>> this one.....
>>>
>>> Id User Host db Command Time State Info
>>> 149 mythtv localhost mythconverg Sleep 16
>>> NULL
>>> 150 mythtv localhost mythconverg Sleep 1
>>> NULL
>>> 151 mythtv localhost mythconverg Sleep 2
>>> NULL
>>> 152 mythtv localhost mythconverg Sleep 8
>>> NULL
>>> 153 mythtv localhost mythconverg Sleep 27
>>> NULL
>>> 154 mythtv localhost mythconverg Sleep 1
>>> NULL
>>> 155 mythtv localhost mythconverg Sleep 4
>>> NULL
>>> 159 mythtv localhost mythconverg Sleep 1
>>> NULL
>>> 161 mythtv localhost mythconverg Sleep 1
>>> NULL
>>> 162 mythtv localhost mythconverg Execute 1 Sending
>>> data REPLACE INTO
>>> recordmatch (recordid, chanid, starttime, manualid) SELECT
>>> record.recordid, program.chanid, program.starttime, IF(search = 5,
>>> record.recordid, 0) FROM (record, program INNER JOIN channel ON
>>> channel.chanid = program.chanid) WHERE record.search = 0 AND
>>> program.manualid = 0 AND program.seriesid <> '' AND program.seriesid =
>>> record.seriesid AND channel.visible = 1 AND (((record.filter & 1) =
>>> 0) OR (program.previouslyshown = 0)) AND (((record.filter & 2) = 0) OR
>>> (program.generic = 0)) AND (((record.filter & 4) = 0) OR
>>> (program.first > 0)) AND (((record.filter & 8) = 0) OR
>>> (HOUR(program.starttime) >= 19 AND HOUR(program.starttime) < 23)) AND
>>> (((record.filter & 16) = 0) OR (channel.commmethod = -2)) AND
>>> (((record.filter & 32) = 0) OR (program.hdtv > 0)) AND
>>> (((record.filter & 64) = 0) OR ((record.programid <> '' AND
>>> program.programid = record.programid) OR (record.programid = '' AND
>>> program.subtitle = record.subtitle AND program.description =
>>> record.description))) AND (((record.filter & 128) = 0) OR
>>> ((record.seriesid <> '' AND program.seriesid = record.seriesid))) AND
>>> ((record.type = 4 OR record.type = 6 OR record.type = 9 OR record.type
>>> = 10) OR ((record.station = channel.callsign) AND ((record.type
>>> = 3) OR ((TIME_TO_SEC(record.starttime) =
>>> TIME_TO_SEC(program.starttime)) AND ((record.type = 2) OR
>>> ((DAYOFWEEK(record.startdate) = DAYOFWEEK(program.starttime)
>>> AND ((record.type = 5) OR
>>> ((TO_DAYS(record.startdate) = TO_DAYS(program.starttime)) AND
>>> (record.type <> 0) ) ) ) ) ) ) ) ))
>>> 163 mythtv localhost mythconverg Sleep 0
>>> NULL
>>> 164 mythtv localhost mythconverg Sleep 429
>>> NULL
>>> 166 mythtv localhost mythconverg Sleep 17
>>> NULL
>>> 167 mythtv localhost mythconverg Sleep 180
>>> NULL
>>> 168 mythtv localhost mythconverg Sleep 82
>>> NULL
>>> 170 mythtv localhost mythconverg Sleep 53
>>> NULL
>>> 171 mythtv localhost mythconverg Sleep 61
>>> NULL
>>> 174 root localhost:43880 NULL Sleep 4 NULL
>>> 175 root localhost:43881 NULL Sleep 3 NULL
>>> 176 mythtv localhost mythconverg Sleep 32
>>> NULL
>>> 177 mythtv localhost mythconverg Sleep 158
>>> NULL
>>> 180 mythtv localhost mythconverg Sleep 376
>>> NULL
>>> 181 mythtv localhost mythconverg Sleep 28
>>> NULL
>>> 182 mythtv localhost mythconverg Sleep 349
>>> NULL
>>> 185 mythtv localhost mythconverg Sleep 111
>>> NULL
>>> 194 mythtv localhost mythconverg Sleep 1
>>> NULL
>>> 201 mythtv localhost mythconverg Sleep 91
>>> NULL
>>> 209 mythtv localhost mythconverg Sleep 3
>>> NULL
>>> 210 mythtv localhost mythconverg Sleep 15
>>> NULL
>>> 229 mythtv localhost mythconverg Sleep 3
>>> NULL
>>> 230 mythtv localhost mythconverg Sleep 229
>>> NULL
>>> 248 mythtv localhost mythconverg Sleep 14
>>> NULL
>>> 252 mythtv localhost mythconverg Sleep 1
>>> NULL
>>> 272 mythtv localhost mythconverg Sleep 2
>>> NULL
>>> 302 mythtv localhost mythconverg Sleep 1
>>> NULL
>>> 307 mythtv localhost mythconverg Sleep 0
>>> NULL
>>> 308 root localhost NULL Query 0 NULL SHOW full
>>> processlist
>>> -e **************
>>>
>>>
>>> Tue.Jul17.16:25:19
>>>
>>> Often it will force other queries to wait for table level lock before
>>> executing but time and time again in the dump files this line appears
>>> on it's own in the FULL PROCESSLIST while the SQL server has gone 100%
>>> on two cores.
>>>
>>> Now while I can hack around on SQL I'm far from being a DB expert and
>>> it needs someone who knows what they're doing to please explain to me
>>> how a single query can in effect tie up the SQL server for 60 seconds
>>> when the databases appear optimized and clean.
>>>
>>> To put it in perspective this 'lock up' happens about every minute or
>>> so and can vary in time from about 30 seconds to over a minute, it has
>>> a direct impact on the usability as it stops recording deletions
>>> happening until it's cleared and also stops mythweb responding
>>> properly.
>>>
>>> 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.
>>>
>>> HELP!!
>>> _______________________________________________
>>> mythtv-users mailing list
>>> mythtv-users at mythtv.org
>>> http://www.mythtv.org/mailman/listinfo/mythtv-users
>>>
>> Another,
>>
>> Maybe I'm hyper-pragmatic here, but even if You will find exact root cause
>> for issue - fixing it is probably beyond Your scope (write access to
>> sources, willingness source devs to qualify this as root cause, speed of
>> patch propagating across approvals/quality control, etc).
>> My experience is that Linux average quality grows in time so gains by
>> probability that given issue is resolved in new OS/component version are
>> higher than costs of effort going with new version.
>> I simply suggest to try new mysql version or even other OS distro.
>> Time spent to compile or migrate probably is already [much]shorter than time
>> You spent on hunting root cause....
>> Sure - there is no learning aspect, but is learning on bugs worth at all ?
>> (I'm not mix bugs with design mistakes).
>>
>> -br
>>
>>
>> _______________________________________________
>> mythtv-users mailing list
>> mythtv-users at mythtv.org
>> http://www.mythtv.org/mailman/listinfo/mythtv-users
>>
> Sorry I don't agree with your premis.
>
> It's resolving difficult issues that make us learn the most, time
> invested in learning is always good.
>
> Waiting for a possible external factor, i.e. SQL or updated myth code
> to fix the issue is not managing the problem.
>
> I recognise I'm outside my comfort zone but I'll keep pumping away
> till I either get help or fix it myself.
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users
>
Ah I see: we both want to learn but probably different things.
Your issue specifics compared with rather lack of other reports with
similar symptoms suggest me that it is rather software component or
distro packaging bug.
In this context learnings we would get will be probably mainly about
somebody coding or packaging mistakes.
As (I guess) we are not coders of any component in question or distro
You use - such learning is somehow useless IMHO.
That's why I proposed highly pragmatic/opportunistic approach.
If however issue will be related to
design/implementation/integration/configuration - such learning will be
highly beneficial I think.
I simply doubt it is a case here.
You have different needs than me (and I have full respect for this) so
for sure my proposal seems to little missed :-(
-br
-------------- next part --------------
A non-text attachment was scrubbed...
Name: warpme.vcf
Type: text/x-vcard
Size: 83 bytes
Desc: not available
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20120720/16110b9f/attachment-0001.vcf>
More information about the mythtv-users
mailing list