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

[Jim C. Nasby]

On Mon, Aug 07, 2006 at 01:12:48AM -0400, Luke Lonergan wrote:
> In a recent example, some ad-hoc queries (joins against fact tables with
> GROUP BY / ORDER BY clauses) were run together alongside ELT queries
> (INSERT ... SELECT) while reporting queries were running.  The ELT
> workload and the reporting queries run without negative interference
> (ELT does not push each instance to it's write rate limit on MPP), but
> the ad-hoc queries were slowing down by 2-3 times greater than they
> would if there were simple CPU sharing.  As a consequence, other queries
> also slowed down non-linearly, basically the whole system ran 3 times
> less throughput as a consequence of the 3 ad-hoc queries running
> simultaneously.
> 
> >From the user's perspective - the reporting queries would take 90
> seconds instead of 30, the ad-hoc queries would take 60 minutes instead
> of 20, etc.
> 
> If the ad-hoc queries were in a single threaded or time sliced queue,
> the whole system would run 3 times faster and would be predictable.

Please keep that test scenario around; it will be extremely useful when
it comes time to show -hackers why queues are needed.
-- 
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