[mythtv-users] mysql consuming 100% CPU - help, please
Stephen Worthington
stephen_agent at jsw.gen.nz
Fri Jul 28 12:05:55 UTC 2017
On Fri, 28 Jul 2017 06:19:52 -0500, you wrote:
>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
I think it would be a good idea to create a ticket for this problem:
https://code.mythtv.org/trac/wiki/TicketHowTo
But for the devs to have much chance of fixing it, they would need to
have a copy of your bad recording, or at least the database for that
recording. A query like this run from root (or with sudo) will dump
all the relevant data for one recording:
mysqldump -uroot -p mythconverg recorded recordedseek recordedrating
recordedprogram recordedmarkup recordedcredits --where="chanid=1024
and starttime='2017-07-18 20:30:00'" --no-create-db --no-create-info >
bad.sql
Change the chanid and starttime values to the correct ones for your
good and bad recordings. Change the name of the resulting .sql file
from bad.sql to <basename of recording>.sql. If you export the data
like that for a good recording and a bad one, the devs should be able
to find the problem by importing that data into a database and running
the query.
It seems that recent versions of mysql have the ability to set a
maximum time for a read-only query:
http://mysqlserverteam.com/server-side-select-statement-timeouts/
I do not know if this has been implemented in Mariadb yet. But it
might be a way for the devs to fix this problem.
More information about the mythtv-users
mailing list