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

[Mike Holden]

On 29 July 2017 at 03:19, T L <tlunde at gmail.com> wrote:

> Oh, it does complete.
>
> When I first encountered this last year, I had multiple (of whatever
> it is) going on a little Live X and, after _days_ at 100% CPU, I gave
> up and just dropped the box.  Now, with one core of a Xeon 56xx
> processor, it takes many hours, but it does eventually complete.  The
> only problem is that it keeps the database busy enough or tables
> locked or something that it screws up all of the recordings that try
> to start within that long period of time.
>
> On Fri, Jul 28, 2017 at 9:21 AM, Stephen Worthington
> <stephen_agent at jsw.gen.nz> wrote:
> > On Fri, 28 Jul 2017 08:13:12 -0500, you wrote:
> >
> >>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
> >
> > Ouch!  That is a huge difference.  But even with that, the query
> > should complete eventually, unless something is thrashing, so the
> > query is probably too big to be executed in the available RAM.
>

Might also be worth running an EXPLAIN on the queries. Run the queries as
before, but put the word "EXPLAIN" before the SELECT command, so it starts
with "EXPLAIN SELECT ...".

This will show how mysql will execute the query, but doesn't actually run
it, so execution should be quick, even for the problem query.

Run this for both query versions, and see if there is any difference
between the 2.

This will highlight things like order of joins of tables, which indexes are
used etc.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20170730/3ac351c2/attachment.html>