[mythtv-users] mysql consuming 100% CPU - help, please

[Stephen Worthington]

On Thu, 27 Jul 2017 16:43:35 -0500, you wrote:

>Hi All -
>
>I'm looking for advice / troubleshooting help regarding mariadb/mysqld
>entirely consuming a CPU core and, consequently, causing recordings to
>be missed, aborted, distorted, etc.  Two example queries are at the
>the bottom of this email message, if anyone's familiar with the code
>to be able to spot the problem from that.
>
>I've been using MythTV since around 2003, starting with PVR-250 cards.
>As of last summer, I was using a Core 2 Quad backend running Ubuntu
>12.04 and Myth 0.26.  I decided to try to be a bit more green and move
>to a LIVA X backend and upgrade to the then new Ubuntu 16.04 and Myth
>0.28.  After the upgrade, initially all seemed fine.  Starting a few
>days after the upgrade (and persisting until I gave up on 0.28 some
>weeks later), I'd get periodic lockups, failed recordings, etc.  I
>thought it might be a hardware problem -- maybe not enough cooling.  A
>CPU heatsink and a switch to an Intel NUC convinced me that this
>wasn't the issue.  I also ran MemTest86 on both the Liva's RAM and the
>8GB stick in the NUC and had no errors there.  Finally, I tried
>different boot devices:  built in flash on the Liva, an add-in mSATA
>drive, a hard drive in the NUC and an SSD in the NUC.  No joy.  I
>thought that it was maybe a software issue, as both 16.04 and 0.28
>were pretty new at the time. So, by late September, with the new TV
>season underway, I gave up and fell back to 12.04 and 0.26.
>
>This summer, I've tried again and I'm still hitting the 100% CPU
>problems.  Now, I've put a test CentOS vm with MythTV 0.28 on a
>FreeBSD 11 box, giving it 2 cores and 2GB of RAM under bhyve.  It's
>sitting on a dual core Xeon 56xx series box with 24GB of RAM and ~20
>TB of disk.  When it works, it works flawlessly and only goes over 10%
>CPU briefly, for mythcommflag.  But, starting a few days after
>installation, I hit the same problem.
>
>My recording hardware is a mix of SiliconDust HDHomeRun HDHR, HDHR-2,
>HDTC-2US, and HDTC-2US-M boxes.  With the CentOS install, I started
>with the MPEG-2 recorders and I don't _think_ that I had this problem
>until I added the HDTC boxes into the mix.   I'm recording US OTA
>signals, about 20 miles from the Twin Cities towers, at a spot in
>Minneapolis where I use an AntennaCraft HBU22 which claims a 50 mile+
>range.  The HD Homerun software on a Windows box claims that I"m
>getting 80% raw signal levels and has a solid green line for the
>decoded signal strength meter, so I think that reception isn't the
>root issue.
>
>There's no pattern that I can see to the channel, shows, time of day,
>length of show, etc. that will cause a CPU spike.  After a full
>restart, I might see it on the next recording -- or not for a couple
>of days.  When I do, though, the offending query in MariaDB (on
>CentOS) looks like the ones below (at least according to a show
>fullprocess list;).  Last fall and earlier this summer, I thought that
>it might be this:
>https://code.mythtv.org/trac/ticket/11011
>but, now, I've removed MythWeb and the problem remains.   (FWIW, the
>new built-in web server is nice!  Wish I could get it to default to
>the backend status page, though...)
>
>I'm very open to suggestions as to where to look / what to try to
>diagnose the problem.
>
>Thanks in advance for any help you can provide!
>Thomas
>
>
>
>
>First example query:
>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
>
>Second example query:
>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

Do you run a daily check and repair of your database
(/etc/cron.daily/optimize_mythdb)?  If not, then I would suspect that
your database has crashed or damaged tables and that is causing the
problems.