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

[T L]

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