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