[mythtv-users] mysql consuming 100% CPU - help, please
T L
tlunde at gmail.com
Fri Jul 28 11:19:52 UTC 2017
Good news -- sort of -- I found a problem recording. Even better, I
have a nice comparison.
As I have small children, I record Daniel Tiger's Neighborhood. For
those who haven't had the pleasure, this is a 30 minute animated show.
Locally, it is broadcast on a PBS station and, with my current
hardware and settings, it generally is captured at ~3/4 G per 30
minute show. On July 18, there were two episodes back-to-back (this
is not uncommon). The first one is fine, but the second is a problem.
For one thing, it's rather larger:
[tlunde at testvm ~]$ ls -als
/var/lib/mythtv/Documents/recordings/1024_20170718203000.ts
843825 -rw-r--r--. 1 mythtv chrony 863542280 Jul 18 16:00
/var/lib/mythtv/Documents/recordings/1024_20170718203000.ts
[tlunde at testvm ~]$ ls -als
/var/lib/mythtv/Documents/recordings/1024_20170718210000.ts
1554737 -rw-r--r--. 1 mythtv chrony 1605045676 Jul 18 16:28
/var/lib/mythtv/Documents/recordings/1024_20170718210000.ts
And the recording is corrupted in a number of ways. For example, it's
missing the closed captioning.
Good:
[tlunde at testvm ~]$ mediainfo
/var/lib/mythtv/Documents/recordings/1024_20170718203000.ts
General
ID : 1605 (0x645)
Complete name :
/var/lib/mythtv/Documents/recordings/1024_20170718203000.ts
Format : MPEG-TS
File size : 824 MiB
Duration : 29 min 59 s
Overall bit rate mode : Variable
Overall bit rate : 3 838 kb/s
Law rating : TV-Y
Video
ID : 65 (0x41)
Menu ID : 1 (0x1)
Format : AVC
Format/Info : Advanced Video Codec
Format profile : High at L4
Format settings, CABAC : Yes
Format settings, ReFrames : 2 frames
Codec ID : 27
Duration : 29 min 59 s
Bit rate mode : Variable
Bit rate : 3 455 kb/s
Maximum bit rate : 12.0 Mb/s
Width : 1 280 pixels
Height : 540 pixels
Display aspect ratio : 16:9
Frame rate : 29.970 (30000/1001) FPS
Standard : Component
Color space : YUV
Chroma subsampling : 4:2:0
Bit depth : 8 bits
Scan type : Progressive
Bits/(Pixel*Frame) : 0.167
Stream size : 741 MiB (90%)
Color range : Limited
Audio
ID : 68 (0x44)
Menu ID : 1 (0x1)
Format : AC-3
Format/Info : Audio Coding 3
Format settings, Endianness : Big
Codec ID : 129
Duration : 29 min 59 s
Bit rate mode : Constant
Bit rate : 192 kb/s
Channel(s) : 2 channels
Channel positions : Front: L R
Sampling rate : 48.0 kHz
Frame rate : 31.250 FPS (1536 spf)
Bit depth : 16 bits
Compression mode : Lossy
Delay relative to video : -536 ms
Stream size : 41.2 MiB (5%)
Language : English
Service kind : Complete Main
Text #1
ID : 65 (0x41)-CC1
Menu ID : 1 (0x1)
Format : EIA-608
Muxing mode : SCTE 128 / DTVCC Transport
Muxing mode, more info : Muxed in Video #1
Duration : 29 min 59 s
Bit rate mode : Constant
Stream size : 0.00 Byte (0%)
CaptionServiceName : CC1
Text #2
ID : 65 (0x41)-1
Menu ID : 1 (0x1)
Format : EIA-708
Muxing mode : SCTE 128 / DTVCC Transport
Muxing mode, more info : Muxed in Video #1
Duration : 29 min 59 s
Bit rate mode : Constant
Stream size : 0.00 Byte (0%)
Bad:
[tlunde at testvm ~]$ mediainfo
/var/lib/mythtv/Documents/recordings/1024_20170718210000.ts
General
ID : 1605 (0x645)
Complete name :
/var/lib/mythtv/Documents/recordings/1024_20170718210000.ts
Format : MPEG-TS
File size : 1.49 GiB
Duration : 28 min 21 s
Overall bit rate mode : Variable
Overall bit rate : 7 546 kb/s
Video
ID : 65 (0x41)
Menu ID : 1 (0x1)
Format : AVC
Format/Info : Advanced Video Codec
Codec ID : 27
Duration : 28 min 21 s
Bit rate : 6 980 kb/s
Stream size : 1.38 GiB (92%)
Audio
ID : 68 (0x44)
Menu ID : 1 (0x1)
Format : AC-3
Format/Info : Audio Coding 3
Format settings, Endianness : Big
Codec ID : 129
Duration : 28 min 21 s
Bit rate mode : Constant
Bit rate : 192 kb/s
Channel(s) : 2 channels
Channel positions : Front: L R
Sampling rate : 48.0 kHz
Frame rate : 31.250 FPS (1536 spf)
Bit depth : 16 bits
Compression mode : Lossy
Delay relative to video : -758 ms
Stream size : 38.9 MiB (3%)
Language : English
Service kind : Complete Main
>From the perspective of this query, the good one is as quick as others
in this thread:
[root at testvm ~]# time mysql -u mythtv -pmythtv -e"use mythconverg;
SELECT recordedmarkup.type FROM recordedmarkup WHERE
recordedmarkup.chanid = 1024 AND recordedmarkup.starttime =
'2017-07-18 20:30:00' AND recordedmarkup.type >= 11 AND
recordedmarkup.type <= 14 GROUP BY recordedmarkup.type ORDER BY SUM((
SELECT IFNULL (rm.mark, (SELECT MAX(rmmax.mark) FROM recordedmarkup AS
rmmax WHERE rmmax.chanid = recordedmarkup.chanid AND
rmmax.starttime=recordedmarkup.starttime)) FROM recordedmarkup AS rm
WHERE rm.chanid = recordedmarkup.chanid AND rm.starttime =
recordedmarkup.starttime AND rm.type >= 11 AND rm.type <= 14 AND
rm.mark > recordedmarkup.mark ORDER BY rm.mark ASC LIMIT 1 ) -
recordedmarkup.mark ) DESC LIMIT 1;"
+------+
| type |
+------+
| 12 |
+------+
real 0m0.007s
user 0m0.004s
sys 0m0.002s
The bad show has the same query running. At present, it is at 20
minutes and counting of wall clock time.
Attempting to stream it to VLC results in the VLC playback window
being fully black but resizing very, very quickly from square to
different sizes of a rectangle over and over and over again. In other
words, it seems that the bad recording looks like a bunch of frames
with different aspect ratios. I suspect that there are so many that
it's making the SELECT MAX take a while to count them up and determine
which ratio is the most frequent.
So, now what I'm looking for is:
a) suggestions on how to keep such bad recordings from happening
(troubleshooting the HDTC-2?)
b) suggestions on how to keep such a bad recording from basically
taking down the entire system while this query runs. (Any way to
cause it to stop after seeing some upper limit of too many aspect
ratio changes in a given show?)
Thanks
Thomas
On Fri, Jul 28, 2017 at 4:39 AM, T L <tlunde at gmail.com> wrote:
> Hi Stephen -
>
> Last fall, on "real hardware" everything was local and SATA connected,
> so there were no possible network issues related to this database
> issue. Today, the VM I'm using "thinks" that the root drive where the
> database is local and that the drive where the recordings are is NFS
> mounted. The root drive is really being offered up by the underlying
> bhyve/FreeNAS system as part of the virtualization system. The
> recordings drive is being offered up over NFS, but it's all local to
> the same box, so there aren't any physical networking issues.
>
> Both then and now, however, all operations on the box (other than
> having a core pegged at ~100% CPU are fine) -- interactive access at
> the shell works for all files, I/O benchmarks look fine; iotop shows
> minimal / expected activity; etc. So, I don't think that it's a
> problem with the underlying filesystem. If you have suggestions for
> checking filesystem access / networking speed, I'm happy to run
> whatever diagnostics you'd like, but all seems well on that end.
>
>
> Running it by hand seems like a good idea. Sorry that I hadn't
> thought to do so before.
>
> I've taken one of the "bad" queries and put it all on to a single
> line. I had to guess at the "use mythconverg;" bit at the beginning,
> but (without it) mysql sensibly complained that it didn't know which
> database to use.
>
> First, I substituted in the information for two very recent recordings
> (which didn't give the box any problem).
>
> [root at testvm ~]# time mysql -u mythtv -p -e"use mythconverg; SELECT
> recordedmarkup.type FROM recordedmarkup WHERE recordedmarkup.chanid =
> 1024 AND recordedmarkup.starttime = '2017-07-28 05:00:00' AND
> recordedmarkup.type >= 11 AND recordedmarkup.type <= 14 GROUP BY
> recordedmarkup.type ORDER BY SUM(( SELECT IFNULL (rm.mark, (SELECT
> MAX(rmmax.mark) FROM recordedmarkup AS rmmax WHERE rmmax.chanid =
> recordedmarkup.chanid AND rmmax.starttime=recordedmarkup.starttime))
> FROM recordedmarkup AS rm WHERE rm.chanid = recordedmarkup.chanid AND
> rm.starttime = recordedmarkup.starttime AND rm.type >= 11 AND rm.type
> <= 14 AND rm.mark > recordedmarkup.mark ORDER BY rm.mark ASC LIMIT 1 )
> - recordedmarkup.mark ) DESC LIMIT 1;"
>
> Enter password:
>
> +------+
> | type |
> +------+
> | 12 |
> +------+
>
> real 0m2.402s
> user 0m0.005s
> sys 0m0.003s
>
> [root at testvm ~]# time mysql -u mythtv -p -e"use mythconverg; SELECT
> recordedmarkup.type FROM recordedmarkup WHERE recordedmarkup.chanid =
> 1024 AND recordedmarkup.starttime = '2017-07-28 04:00:00' AND
> recordedmarkup.type >= 11 AND recordedmarkup.type <= 14 GROUP BY
> recordedmarkup.type ORDER BY SUM(( SELECT IFNULL (rm.mark, (SELECT
> MAX(rmmax.mark) FROM recordedmarkup AS rmmax WHERE rmmax.chanid =
> recordedmarkup.chanid AND rmmax.starttime=recordedmarkup.starttime))
> FROM recordedmarkup AS rm WHERE rm.chanid = recordedmarkup.chanid AND
> rm.starttime = recordedmarkup.starttime AND rm.type >= 11 AND rm.type
> <= 14 AND rm.mark > recordedmarkup.mark ORDER BY rm.mark ASC LIMIT 1 )
> - recordedmarkup.mark ) DESC LIMIT 1;"
>
> Enter password:
>
> +------+
> | type |
> +------+
> | 12 |
> +------+
>
> real 0m5.035s
> user 0m0.004s
> sys 0m0.003s
>
>
>
> It is different when I run a couple of queries that was running at the
> time the box was pegged at 100% CPU. (For each of these, I'd noted
> what the box was doing before giving up after it had been at 100% CPU
> for hours and the system was not recording properly. At that point, I
> gently rebooted the box to get the system able to record new shows
> successfully.). These queries now run quickly, but don't return any
> useful output (no type is returned):
>
>
> [root at testvm ~]# time mysql -u mythtv -p -e"use mythconverg; SELECT
> recordedmarkup.type FROM recordedmarkup WHERE recordedmarkup.chanid =
> 1021 AND recordedmarkup.starttime = '2017-07-22 16:30:00' AND
> recordedmarkup.type >= 11 AND recordedmarkup.type <= 14 GROUP BY
> recordedmarkup.type ORDER BY SUM(( SELECT IFNULL (rm.mark, (SELECT
> MAX(rmmax.mark) FROM recordedmarkup AS rmmax WHERE rmmax.chanid =
> recordedmarkup.chanid AND rmmax.starttime=recordedmarkup.starttime))
> FROM recordedmarkup AS rm WHERE rm.chanid = recordedmarkup.chanid AND
> rm.starttime = recordedmarkup.starttime AND rm.type >= 11 AND rm.type
> <= 14 AND rm.mark > recordedmarkup.mark ORDER BY rm.mark ASC LIMIT 1 )
> - recordedmarkup.mark ) DESC LIMIT 1;"
>
> Enter password:
>
> real 0m2.131s
> user 0m0.003s
> sys 0m0.004s
>
> [root at testvm ~]# time mysql -u mythtv -p -e"use mythconverg; SELECT
> recordedmarkup.type FROM recordedmarkup WHERE recordedmarkup.chanid =
> 1024 AND recordedmarkup.starttime = '2017-07-27 02:00:00' AND
> recordedmarkup.type >= 11 AND recordedmarkup.type <= 14 GROUP BY
> recordedmarkup.type ORDER BY SUM(( SELECT IFNULL (rm.mark, (SELECT
> MAX(rmmax.mark) FROM recordedmarkup AS rmmax WHERE rmmax.chanid =
> recordedmarkup.chanid AND rmmax.starttime=recordedmarkup.starttime))
> FROM recordedmarkup AS rm WHERE rm.chanid = recordedmarkup.chanid AND
> rm.starttime = recordedmarkup.starttime AND rm.type >= 11 AND rm.type
> <= 14 AND rm.mark > recordedmarkup.mark ORDER BY rm.mark ASC LIMIT 1 )
> - recordedmarkup.mark ) DESC LIMIT 1;"
>
> Enter password:
>
> real 0m2.293s
> user 0m0.004s
> sys 0m0.008s
>
> [root at testvm ~]#
>
>
>
> Unfortunately, I think that this is because I'd trashed the bad
> recordings. These queries run quickly now because they have nothing
> to do.
>
> [root at testvm ~]# ls -alstr
> /var/lib/mythtv/Documents/recordings/*1024*2017072702*ts
> ls: cannot access
> /var/lib/mythtv/Documents/recordings/*1024*2017072702*ts: No such file
> or directory
> [root at testvm ~]# ls -alstr
> /var/lib/mythtv/Documents/recordings/*1021*20170722*ts
> ls: cannot access
> /var/lib/mythtv/Documents/recordings/*1021*20170722*ts: No such file
> or directory
> [root at testvm ~]# ls -alstr
> /var/lib/mythtv/Documents/recordings/*1024*201707280*ts
> 1840953 -rw-r--r--. 1 mythtv chrony 1942491952 Jul 27 23:30
> /var/lib/mythtv/Documents/recordings/1024_20170728040000.ts
> 2093021 -rw-r--r--. 1 mythtv chrony 2166321932 Jul 28 00:30
> /var/lib/mythtv/Documents/recordings/1024_20170728050000.ts
> [root at testvm ~]#
>
> The next time the box gets in this state, I won't trash the recording
> so that I can run it by hand and see (a) if it pegs the CPU and (b)
> how long it takes to run the query by hand.
>
> If anyone has a suggestion for some utility that could scan a
> problematic recording (maybe looking for an undue number of aspect
> ratio changes?), that'd be nifty.
>
> Other suggestions?
>
> I'd be happy to force the recordings to be considered 16:9, if I knew
> how to do so. When the problematic query was being run from MythWeb,
> I could get to it and change the PHP. Today, though, I don't know
> where the query is being run from, nor how to force the result to be
> 12 per https://www.mythtv.org/wiki/Recordedmarkup_table.
> Thoughts here?
>
> Thanks
> Thomas
>
>
>
>
> On Fri, Jul 28, 2017 at 12:58 AM, Stephen Worthington
> <stephen_agent at jsw.gen.nz> wrote:
>> On Thu, 27 Jul 2017 21:56:02 -0500, you wrote:
>>
>>>Hi Stephen -
>>>
>>>Thanks for the suggestion. No, I don't have it set to run daily, but
>>>I do run it periodically. Here are the last couple of invocations:
>>>https://pastebin.com/ZJsfT5bQ
>>>
>>>I don't see any differences before or after running it. Also, with
>>>multiple instances of the problem across different distributions
>>>(CentOS and Ubuntu) across different machines (on Liva X hardware, on
>>>NUC hardware, on the current bhyve vm), I'd be surprised if this was
>>>it.
>>>
>>>If you see anything in that log file that I should further
>>>investigate, or additional suggestions, I'm grateful for any pointers.
>>>
>>>Thanks
>>>Thomas
>>
>> No, I could not see anything there that was out of the ordinary.
>>
>> Have you tried manually running one of the queries that seems to be
>> causing trouble?
>>
>> Also, do you have any partitions mounted over the network on other
>> boxes that are used by mythbackend? If the mount has failed for some
>> reason, they can get into a state that makes mythbackend seem like it
>> has locked up, while it does long timeouts while trying to access
>> them. Or mounted partitions that mythbackend uses that have failed to
>> mount and are in some strange state, such as a JFS partition that
>> needs to be fscked before it can mount. I have had both of those
>> cause my mythbackend to fail to record and show high CPU use.
>> _______________________________________________
>> mythtv-users mailing list
>> mythtv-users at mythtv.org
>> http://lists.mythtv.org/mailman/listinfo/mythtv-users
>> http://wiki.mythtv.org/Mailing_List_etiquette
>> MythTV Forums: https://forum.mythtv.org
More information about the mythtv-users
mailing list