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

Fri Jul 28 02:56:02 UTC 2017

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

On Thu, Jul 27, 2017 at 6:45 PM, Stephen Worthington
<stephen_agent at jsw.gen.nz> wrote:
> 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.
> _______________________________________________
> 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