[mythtv-users] 0.25 SQL CPU Load issues after optimisation
Another Sillyname
anothersname at googlemail.com
Thu Jul 19 22:02:42 UTC 2012
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.
More information about the mythtv-users
mailing list