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

Thomas Mashos thomas at mashos.com
Fri Jul 20 00:51:22 UTC 2012


On Thu, Jul 19, 2012 at 2:30 PM, 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
>

So your answer is instead of hunting down the issue and fixing it is
to not only try a different version of MySQL, but a completely
different distro. I don't think I've heard a worse recommendation than
that in a long time.

Thanks,

Thomas Mashos


More information about the mythtv-users mailing list