[mythtv-users] mysql consuming 100% CPU - help, please
T L
tlunde at gmail.com
Fri Jul 28 13:13:12 UTC 2017
Hi Stephen -
Thanks. I'll look into getting a ticket created. It'd be good to
have some limitations on the query. I can't be the only one with
problematic recordings.
You were right about the database difference between the two:
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
>good.sql
mysqldump -uroot -p mythconverg recorded recordedseek recordedrating
recordedprogram recordedmarkup recordedcredits --where="chanid=1024
and starttime='2017-07-18 21:00:00'" --no-create-db --no-create-info
>bad.sql
[root at testvm tlunde]# cat good.sql | wc -c
85183
[root at testvm tlunde]# cat bad.sql | wc -c
20300732
Look at the size difference!
Thanks
Thomas
On Fri, Jul 28, 2017 at 7:05 AM, Stephen Worthington
<stephen_agent at jsw.gen.nz> wrote:
> 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.
> _______________________________________________
> 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