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

T L tlunde at gmail.com
Fri Jul 28 09:39:35 UTC 2017


Hi Stephen -

Last fall, on "real hardware" everything was local and SATA connected,
so there were no possible network issues related to this database
issue.  Today, the VM I'm using "thinks" that the root drive where the
database is local and that the drive where the recordings are is NFS
mounted.  The root drive is really being offered up by the underlying
bhyve/FreeNAS system as part of the virtualization system.  The
recordings drive is being offered up over NFS, but it's all local to
the same box, so there aren't any physical networking issues.

Both then and now, however, all operations on the box (other than
having a core pegged at ~100% CPU are fine) -- interactive access at
the shell works for all files, I/O benchmarks look fine; iotop shows
minimal / expected activity; etc.  So, I don't think that it's a
problem with the underlying filesystem.  If you have suggestions for
checking filesystem access / networking speed, I'm happy to run
whatever diagnostics you'd like, but all seems well on that end.


Running it by hand seems like a good idea.  Sorry that I hadn't
thought to do so before.

I've taken one of the "bad" queries and put it all on to a single
line.  I had to guess at the "use mythconverg;" bit at the beginning,
but (without it) mysql sensibly complained that it didn't know which
database to use.

First, I substituted in the information for two very recent recordings
(which didn't give the box any problem).

[root at testvm ~]# time mysql -u mythtv -p -e"use mythconverg; SELECT
recordedmarkup.type FROM recordedmarkup WHERE recordedmarkup.chanid =
1024 AND recordedmarkup.starttime = '2017-07-28 05: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;"

Enter password:

+------+
| type |
+------+
|   12 |
+------+

real 0m2.402s
user 0m0.005s
sys 0m0.003s

[root at testvm ~]# time mysql -u mythtv -p -e"use mythconverg; SELECT
recordedmarkup.type FROM recordedmarkup WHERE recordedmarkup.chanid =
1024 AND recordedmarkup.starttime = '2017-07-28 04: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;"

Enter password:

+------+
| type |
+------+
|   12 |
+------+

real 0m5.035s
user 0m0.004s
sys 0m0.003s



It is different when I run a couple of queries that was running at the
time the box was pegged at 100% CPU.  (For each of these, I'd noted
what the box was doing before giving up after it had been at 100% CPU
for hours and the system was not recording properly.  At that point, I
gently rebooted the box to get the system able to record new shows
successfully.). These queries now run quickly, but don't return any
useful output (no type is returned):


[root at testvm ~]# time mysql -u mythtv -p -e"use mythconverg; 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;"

Enter password:

real 0m2.131s
user 0m0.003s
sys 0m0.004s

[root at testvm ~]# time mysql -u mythtv -p -e"use mythconverg; 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;"

Enter password:

real 0m2.293s
user 0m0.004s
sys 0m0.008s

[root at testvm ~]#



Unfortunately, I think that this is because I'd trashed the bad
recordings.  These queries run quickly now because they have nothing
to do.

[root at testvm ~]# ls -alstr
/var/lib/mythtv/Documents/recordings/*1024*2017072702*ts
ls: cannot access
/var/lib/mythtv/Documents/recordings/*1024*2017072702*ts: No such file
or directory
[root at testvm ~]# ls -alstr
/var/lib/mythtv/Documents/recordings/*1021*20170722*ts
ls: cannot access
/var/lib/mythtv/Documents/recordings/*1021*20170722*ts: No such file
or directory
[root at testvm ~]# ls -alstr
/var/lib/mythtv/Documents/recordings/*1024*201707280*ts
1840953 -rw-r--r--. 1 mythtv chrony 1942491952 Jul 27 23:30
/var/lib/mythtv/Documents/recordings/1024_20170728040000.ts
2093021 -rw-r--r--. 1 mythtv chrony 2166321932 Jul 28 00:30
/var/lib/mythtv/Documents/recordings/1024_20170728050000.ts
[root at testvm ~]#

The next time the box gets in this state, I won't trash the recording
so that I can run it by hand and see (a) if it pegs the CPU and (b)
how long it takes to run the query by hand.

If anyone has a suggestion for some utility that could scan a
problematic recording (maybe looking for an undue number of aspect
ratio changes?), that'd be nifty.

Other suggestions?

I'd be happy to force the recordings to be considered 16:9, if I knew
how to do so.  When the problematic query was being run from MythWeb,
I could get to it and change the PHP.  Today, though, I don't know
where the query is being run from, nor how to force the result to be
12 per https://www.mythtv.org/wiki/Recordedmarkup_table.
Thoughts here?

Thanks
Thomas




On Fri, Jul 28, 2017 at 12:58 AM, Stephen Worthington
<stephen_agent at jsw.gen.nz> wrote:
> On Thu, 27 Jul 2017 21:56:02 -0500, you wrote:
>
>>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
>
> No, I could not see anything there that was out of the ordinary.
>
> Have you tried manually running one of the queries that seems to be
> causing trouble?
>
> Also, do you have any partitions mounted over the network on other
> boxes that are used by mythbackend?  If the mount has failed for some
> reason, they can get into a state that makes mythbackend seem like it
> has locked up, while it does long timeouts while trying to access
> them.  Or mounted partitions that mythbackend uses that have failed to
> mount and are in some strange state, such as a JFS partition that
> needs to be fscked before it can mount.  I have had both of those
> cause my mythbackend to fail to record and show high CPU use.
> _______________________________________________
> 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