[mythtv-users] 0.25 SQL CPU Load issues after optimisation
Another Sillyname
anothersname at googlemail.com
Thu Jul 19 03:10:44 UTC 2012
On 18 July 2012 06:03, Thomas Mashos <thomas at mashos.com> wrote:
> On Tue, Jul 17, 2012 at 5:22 PM, Another Sillyname
> <anothersname at googlemail.com> wrote:
>> On 18 July 2012 00:46, Thomas Mashos <thomas at mashos.com> wrote:
>>> On Tue, Jul 17, 2012 at 4:05 PM, Another Sillyname
>>> <anothersname at googlemail.com> wrote:
>>>> On 18 July 2012 00:01, Gary Buhrmaster <gary.buhrmaster at gmail.com> wrote:
>>>>> On Tue, Jul 17, 2012 at 10:48 PM, Another Sillyname
>>>>> <anothersname at googlemail.com> wrote:
>>>>> ....
>>>>>> 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.
>>>>>
>>>>> In my experience, ext4 barriers usually manifest themselves are disk I/O
>>>>> wait, and not high CPU usage (mysql is actually waiting a lot).
>>>>>
>>>>> If you are using innodb tables (which is possible if you rebuilt your system
>>>>> with a newer mysql server) you should review the recommendations at:
>>>>> http://www.mythtv.org/wiki/Tune_MySQL
>>>>> Regardless of whether they help this particular issue, they will
>>>>> help overall performance in the long run.
>>>>> _______________________________________________
>>>>> mythtv-users mailing list
>>>>> mythtv-users at mythtv.org
>>>>> http://www.mythtv.org/mailman/listinfo/mythtv-users
>>>>
>>>> Thanks Gary but I'd already done that, I've been looking at this for 3
>>>> days now and as a number of people seem to have experienced this
>>>> problem without a clearly defined problem/solution I though it would
>>>> be useful to try to get some expert focus on it which means not me.
>>>> _______________________________________________
>>>> mythtv-users mailing list
>>>> mythtv-users at mythtv.org
>>>> http://www.mythtv.org/mailman/listinfo/mythtv-users
>>>
>>> Looks like Linux Mint 13 is based on Ubuntu 12.04, so you did get
>>> MySQL 5.5 from that which has innodb enabled. As previously suggested,
>>> you aren't experiencing the same issue that other people have
>>> commented on. If you were, those tuning parameters would have resolved
>>> it.
>>>
>>> Are you running the MythTV updates from the Mythbuntu repos?
>>>
>>> Thanks,
>>>
>>> Thomas Mashos
>>> _______________________________________________
>>> mythtv-users mailing list
>>> mythtv-users at mythtv.org
>>> http://www.mythtv.org/mailman/listinfo/mythtv-users
>>
>> When you say updates can you be specific possibly.
>>
>> I installed Myth from the Mint Repos that do use the Ubuntu repos and
>> am running 0.25+Fixes 20120410.1f5962a.0ubuntu1
>>
>> Which I assume is coming from the standard Ubuntu repos rather then a
>> specific mint repo.
>> _______________________________________________
>> mythtv-users mailing list
>> mythtv-users at mythtv.org
>> http://www.mythtv.org/mailman/listinfo/mythtv-users
>
> There is newer packages available at the PPA here
> https://launchpad.net/~mythbuntu/+archive/0.25
>
> Thanks,
>
> Thomas Mashos
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users
Thanks for this but didn't help I'm afraid.
Here's four in sequence full processlist dumps with timestamp at end
of each dump, you can see where the problem SQL command ID 162 is
starting to 'hold up' other SQL commands at the end of the fourth
example.
Id User Host db Command Time State Info
1 mythtv HTPC1.local:33280 mythconverg Sleep 315 NULL
153 mythtv localhost mythconverg Sleep 248 NULL
154 mythtv localhost mythconverg Sleep 3 NULL
155 mythtv localhost mythconverg Sleep 266 NULL
156 mythtv localhost mythconverg Sleep 224 NULL
157 mythtv localhost mythconverg Sleep 181 NULL
158 mythtv localhost mythconverg Sleep 137 NULL
159 mythtv localhost mythconverg Sleep 91 NULL
160 mythtv localhost mythconverg Sleep 46 NULL
161 mythtv localhost mythconverg Sleep 1 NULL
162 mythtv localhost mythconverg Execute 30 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 7 NULL
165 mythtv localhost mythconverg Sleep 124 NULL
166 mythtv localhost mythconverg Sleep 3 NULL
167 mythtv localhost mythconverg Sleep 57 NULL
174 mythtv localhost mythconverg Sleep 270 NULL
184 mythtv localhost mythconverg Sleep 249 NULL
185 mythtv localhost mythconverg Sleep 1 NULL
187 mythtv localhost mythconverg Sleep 125 NULL
188 mythtv localhost mythconverg Sleep 2 NULL
189 root localhost:46993 NULL Sleep 6 NULL
190 root localhost:46994 NULL Sleep 3 NULL
191 root localhost:46995 NULL Sleep 216 NULL
192 root localhost:46996 NULL Sleep 216 NULL
193 mythtv localhost mythconverg Sleep 181 NULL
194 mythtv localhost mythconverg Sleep 124 NULL
195 mythtv localhost mythconverg Sleep 1 NULL
196 mythtv localhost mythconverg Sleep 116 NULL
197 mythtv localhost mythconverg Sleep 90 NULL
198 mythtv localhost mythconverg Sleep 90 NULL
206 mythtv localhost mythconverg Sleep 45 NULL
207 mythtv localhost mythconverg Sleep 4 NULL
209 mythtv localhost mythconverg Sleep 1 NULL
210 root localhost NULL Query 0 NULL SHOW full processlist
-e **************
Thu.Jul19.03:52:30
Next Pass
Id User Host db Command Time State Info
1 mythtv HTPC1.local:33280 mythconverg Sleep 315 NULL
153 mythtv localhost mythconverg Sleep 248 NULL
154 mythtv localhost mythconverg Sleep 3 NULL
155 mythtv localhost mythconverg Sleep 266 NULL
156 mythtv localhost mythconverg Sleep 224 NULL
157 mythtv localhost mythconverg Sleep 181 NULL
158 mythtv localhost mythconverg Sleep 137 NULL
159 mythtv localhost mythconverg Sleep 91 NULL
160 mythtv localhost mythconverg Sleep 46 NULL
161 mythtv localhost mythconverg Sleep 0 NULL
162 mythtv localhost mythconverg Execute 30 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 7 NULL
165 mythtv localhost mythconverg Sleep 124 NULL
166 mythtv localhost mythconverg Sleep 3 NULL
167 mythtv localhost mythconverg Sleep 57 NULL
174 mythtv localhost mythconverg Sleep 270 NULL
184 mythtv localhost mythconverg Sleep 249 NULL
185 mythtv localhost mythconverg Sleep 0 NULL
187 mythtv localhost mythconverg Sleep 125 NULL
188 mythtv localhost mythconverg Sleep 2 NULL
189 root localhost:46993 NULL Sleep 6 NULL
190 root localhost:46994 NULL Sleep 3 NULL
191 root localhost:46995 NULL Sleep 216 NULL
192 root localhost:46996 NULL Sleep 216 NULL
193 mythtv localhost mythconverg Sleep 181 NULL
194 mythtv localhost mythconverg Sleep 124 NULL
195 mythtv localhost mythconverg Sleep 1 NULL
196 mythtv localhost mythconverg Sleep 116 NULL
197 mythtv localhost mythconverg Sleep 90 NULL
198 mythtv localhost mythconverg Sleep 90 NULL
206 mythtv localhost mythconverg Sleep 45 NULL
207 mythtv localhost mythconverg Sleep 4 NULL
209 mythtv localhost mythconverg Sleep 0 NULL
211 root localhost NULL Query 0 NULL SHOW full processlist
-e **************
Thu.Jul19.03:52:30
Next Pass
Id User Host db Command Time State Info
1 mythtv HTPC1.local:33280 mythconverg Sleep 316 NULL
153 mythtv localhost mythconverg Sleep 249 NULL
154 mythtv localhost mythconverg Sleep 4 NULL
155 mythtv localhost mythconverg Sleep 267 NULL
156 mythtv localhost mythconverg Sleep 225 NULL
157 mythtv localhost mythconverg Sleep 182 NULL
158 mythtv localhost mythconverg Sleep 138 NULL
159 mythtv localhost mythconverg Sleep 92 NULL
160 mythtv localhost mythconverg Sleep 47 NULL
161 mythtv localhost mythconverg Sleep 1 NULL
162 mythtv localhost mythconverg Execute 31 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 8 NULL
165 mythtv localhost mythconverg Sleep 125 NULL
166 mythtv localhost mythconverg Sleep 4 NULL
167 mythtv localhost mythconverg Sleep 58 NULL
174 mythtv localhost mythconverg Sleep 271 NULL
184 mythtv localhost mythconverg Sleep 250 NULL
185 mythtv localhost mythconverg Sleep 1 NULL
187 mythtv localhost mythconverg Sleep 126 NULL
188 mythtv localhost mythconverg Sleep 3 NULL
189 root localhost:46993 NULL Sleep 1 NULL
190 root localhost:46994 NULL Sleep 4 NULL
191 root localhost:46995 NULL Sleep 217 NULL
192 root localhost:46996 NULL Sleep 217 NULL
193 mythtv localhost mythconverg Sleep 182 NULL
194 mythtv localhost mythconverg Sleep 125 NULL
195 mythtv localhost mythconverg Sleep 1 NULL
196 mythtv localhost mythconverg Sleep 117 NULL
197 mythtv localhost mythconverg Sleep 91 NULL
198 mythtv localhost mythconverg Sleep 91 NULL
206 mythtv localhost mythconverg Sleep 46 NULL
207 mythtv localhost mythconverg Sleep 1 NULL
209 mythtv localhost mythconverg Sleep 1 NULL
212 mythtv localhost mythconverg Sleep 0 NULL
213 root localhost NULL Query 0 NULL SHOW full processlist
-e **************
Thu.Jul19.03:52:31
Next Pass
Id User Host db Command Time State Info
1 mythtv HTPC1.local:33280 mythconverg Sleep 316 NULL
153 mythtv localhost mythconverg Sleep 249 NULL
154 mythtv localhost mythconverg Sleep 4 NULL
155 mythtv localhost mythconverg Sleep 267 NULL
156 mythtv localhost mythconverg Sleep 225 NULL
157 mythtv localhost mythconverg Sleep 182 NULL
158 mythtv localhost mythconverg Sleep 138 NULL
159 mythtv localhost mythconverg Sleep 92 NULL
160 mythtv localhost mythconverg Sleep 47 NULL
161 mythtv localhost mythconverg Sleep 1 NULL
162 mythtv localhost mythconverg Execute 31 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 8 NULL
165 mythtv localhost mythconverg Sleep 125 NULL
166 mythtv localhost mythconverg Sleep 4 NULL
167 mythtv localhost mythconverg Sleep 58 NULL
174 mythtv localhost mythconverg Sleep 271 NULL
184 mythtv localhost mythconverg Sleep 250 NULL
185 mythtv localhost mythconverg Sleep 0 NULL
187 mythtv localhost mythconverg Sleep 126 NULL
188 mythtv localhost mythconverg Sleep 0 NULL
189 root localhost:46993 NULL Sleep 1 NULL
190 root localhost:46994 NULL Sleep 0 NULL
191 root localhost:46995 NULL Sleep 217 NULL
192 root localhost:46996 NULL Sleep 217 NULL
193 mythtv localhost mythconverg Sleep 182 NULL
194 mythtv localhost mythconverg Sleep 125 NULL
195 mythtv localhost mythconverg Sleep 0 NULL
196 mythtv localhost mythconverg Sleep 117 NULL
197 mythtv localhost mythconverg Sleep 91 NULL
198 mythtv localhost mythconverg Sleep 91 NULL
206 mythtv localhost mythconverg Sleep 46 NULL
207 mythtv localhost mythconverg Sleep 1 NULL
209 mythtv localhost mythconverg Execute 0 Waiting for table level
lock REPLACE INTO program ( chanid, title, subtitle,
description, category, category_type, starttime,
endtime, closecaptioned, stereo, hdtv, subtitled,
subtitletypes, audioprop, videoprop, stars,
partnumber, parttotal, syndicatedepisodenumber, airdate,
originalairdate,listingsource, seriesid, programid,
previouslyshown ) VALUES ( 54112, 'Jacob\\'s Cross',
'', 'Epic drama about one man\\'s quest to establish a business
empire in the cut-throat world of the African oil industry.', '',
'', '2012-07-19 04:00:00', '2012-07-19 05:00:00', 0,
1, 0, 0, 0, 1, 0, '0', 0, 0,
'', '0000', NULL, 1, '', '', 0)
212 mythtv localhost mythconverg Sleep 0 NULL
214 root localhost NULL Query 0 NULL SHOW full processlist
-e **************
Thu.Jul19.03:52:31
Any other ideas anyone?
More information about the mythtv-users
mailing list