[mythtv-users] Slow MySQL query after delete

[Michael Rice]

hOn Nov 30, 2007 8:56 PM, Michael T. Dean <mtdean at thirdcontact.com> wrote:
> On 11/29/2007 10:39 PM, Michael Rice wrote:
> > It's good to know that it is possible to delete without lock up... but
> > whatever it
> > takes isn't obvious to me.  I am using slow deletes and do not have my database
> > on my recordings disks.  I have a newish Core 2 Duo backend and frontend.  I
> > optimize my database in a cronjob regularly.  I read the list and try to
> > follow the advise you and others give but I've always had this issue.
> >
> > Tonight I turned on "all" logging in the frontend and the backend is running
> > "most" logging right now.  In the front end I deleted a show.. the UI came back
> > immediately.  I then chose another show and selected "Delete".  The
> > delete dialog came up then the frontend hung.  After some seconds
> > it returned and I was able to delete the second show.  Below I've added log
> > snippets...  Can you see the problem?  I am sure those of us that suffer from
> > this would be forever in your debt if you could identify what the
> > misconfiguration
> > is.
> >
> ...
> > FE 2007-11-29 18:05:50.151 MSqlQuery: SELECT icon FROM channel WHERE chanid
> >
> > <frontend blocking>
> >
>
> Verify that your channel icons are actually where the DB says they are
> and that they're readable by the user running the frontend.  Telling
> Myth it can find them where they can't be found/read can "lock up" the
> frontend for 5-30 seconds (i.e. I could see it being 5-10 in Watch
> Recordings but 30 in the EPG).

My icons are on a NFS share accessible by all my frontends.  They show
up perfectly in the EPG and OSD.  So I don't think permissions or
location of the icons are the problem.  The front end query for them
might be though... I don't know.  When one moves from recording to
recording in the watch recordings screens that is the one query that
happens.  If it is blocked waiting for the BUSQ that could be the
issue.

The only time I notice the blocking is when two BUSQs are triggered
close to each other.  So deleting two recordings back to back does it.
 I also see it in the EPG when I set two items to record back to back.

I'll go back to investigate more.