[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