[mythtv] Scheduler needs table keys?

[usleepless at gmail.com]

Chris, List,

On 1/29/07, Chris Pinkham <cpinkham at bc2va.org> wrote:
> * On Sun Jan 28, 2007 at 11:12:09PM -0800, Bruce Markey wrote:
> > Chris Pinkham wrote:
> > > matter if it takes 10-20 seconds to run at low priority.  I do think
> > > we need to do something about it running repetitively like when you're
> > > going through Watch Recordings and cleaning up stuff.  It would be nice
>
> > Requests are already queued while the scheduler is running and
> > it is optimized so that it throws out recid 0 if there are any
> > recid > 0 and a recid -1 (full matching) will blow off every-
> > thing else in the queue and cut to the chase.
>
> Sorry, I was mixing up two different (but related) issues.  The pauses
> I'm talking about are related to the RECORDING_LIST_CHANGE event that gets
> sent when a recording is deleted.  This triggers a PlaybackBox::FillList()
> which makes the GUI a bit unresponsive when you're trying to do things
> like delete multiple recordings.  Sometimes I forget and think that
> this is related to a resched, but it is not.  I think that this refill
> could be fixed in a few ways now that we have the recordings list cached.
> We could extend the RECORDING_LIST_CHANGE event or make a new event that
> would just say "the recording for CHANID @ STARTTIME has been deleted."
> PlaybackBox could handle this by removing the item from the recordings
> list cache and redrawing the screen instead of doing a full FillList()
> again which can be a bit slow.
>
> I think I just figured out why this is happening also.  On my MySQL
> v3.23.x version, any subsequent reads are blocked by an attempted update
> on a table that is being blocked by another select.  How's that for
> confusing.... :)
>
> When the scheduler runs, it ends up causing the recorded table to
> be blocked for updates.  This is why the recorders can't update the
> recorded.filesize field.  Something that I just discovered and tested
> a few times is that while the update of the filesize field is blocked,
> any subsequent selects on the table are blocked as well until the update
> finishes.  The select is blocked whether it is a select on a specific
> row or a select on all rows.
>
> This means that if you have something recording and you delete an existing
> recording, that the PlaybackBox::FillList() call could end up being
> blocked waiting on the ProgramInfo::SetFilesize() call to complete which
> is in turn waiting on the big SQL query in Scheduler::AddNewRecords()
> to complete.
>
> Since most of the time I have something recording in prime-time while
> I'm also watching and deleting shows, this means that I end up waiting
> on the sheduler query in multiple places (PlaybackBox and the various
> *recorders).
>
> If you want to replicate the test to see if the same thing happens on your
> system or MySQL version, here's what I did:
>
> 1) In one window run mythbackend -v schedule to see when the schedule runs.
> 2) In another window, log into MySQL and type "select * from recorded" but
>    don't hit ENTER yet.
> 3) In another window, log into MySQL and type "update recorded set filesize
>    = 0 where chanid = CHANID and starttime = STARTTIME", substituting in a
>    valid chand and starttime combination but don't hit ENTER yet.
> 4) In a fourth window, run "mythbackend --resched".
> 5) When the scheduler's SQL query fires off, hit ENTER in the "update"
>    SQL window.  That will block on the scheduler's select.
> 6) Immediately hit ENTER in the "select" SQL window and you should see this
>    query block on the update query.
>
> I think this is further reason to not have the scheduler hitting the
> recorded table to check for duplicates.

the scheduler and hickups have been discussed endlessly on this list.
this is just another part of the sega(?).

how about making mythtv-sql sql99 compliant and supporting other
databases for which qt-drivers exist? in particular postgresql. having
it support multiple dbs could make trouble-shooting easier as well.

1. the scheduler query is big fat MF: mysql is not good at big
queries. postgresql is.
2. you have locking issues: postgresql doesn't have this problem:
readers can read and writers can write.
3. you need utils to fix tables: postgresql doesn't corrupt tables.
end result? less user problems.

you can try to bandaid this by introducing threads and
thread-priorities, but in the end the result will be sub-optimal by
definition.

i am running a mythtv-0.18-fixes on postgresql. works like a charm.
and in a concerted effort, it wouldn't be too much work either. give
users choice!

and your scheduler query could use some optimization too. i already
posted about this on this list earlier.

regards,

usleep

>
> --
> Chris
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev
>