[mythtv-users] 0.25 SQL CPU Load issues after optimisation
Warpme
warpme at o2.pl
Thu Jul 19 21:30:17 UTC 2012
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
-------------- 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/20120719/5e29a98d/attachment.vcf>
More information about the mythtv-users
mailing list