[Bizgres-general] [ENG] Re: Statement Queuing take II - Resource Scheduling (Running with cost and cursors)

[Jim C. Nasby]

On Thu, Aug 03, 2006 at 01:08:20PM +1200, Mark Kirkwood wrote:
> Luke Lonergan wrote:
> > Mark,
> >
> > On 8/1/06 11:39 PM, "Mark Kirkwood" <mkirkwood at greenplum.com> wrote:
> >
> >   
> >> I guess this is a different way of managing the system load, one way being:
> >>
> >> - put users into queues based on the their expected workload
> >>
> >> Whereas what we have been describing is:
> >>
> >> - put statements into queues based on their estimated workload
> >>
> >> It is certainly interesting...(scratches had), not sure we can do both...
> >>     
> >
> > The latter (#2) is the one that works best to manage workload and throughput
> > for the system IMO.
> >
> > I think admins would have too much trouble trying to force people to change
> > users for different query types as implied by #1, in fact I think it won't
> > work at all.
> >
> > Whether it is cost, memory use, or some other measure of resource
> > consumption, we need to assess an execution plan's resource consumption and
> > place it into the appropriate queue for execution, independent of the user
> > that is connected.
> >   
> 
> I guess we were all thinking along the lines of "users mainly do one 
> type of workload" (which is true in some cases anyway), but clearly in 
> cases where user x does several different types of workload (e.g. on 
> Mondays does data mining and then daily reporting the other days....) 
> what you are describing will work better.

There's probably room for both. IIRC, Oracle's resource management is
based on limiting what individual users can do. I'm not trying to play
'follow the leader' here, but presumably if they put the effort into
doing that there's some demand.

One issue we'd need to be careful of with automatically putting
statements in different queues is that a queue of low-cost statements
doesn't starve out a queue of high-cost statements. In essence, what
we're looking at here is equivalent to a priority-based OS scheduler.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby at pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461