[Bizgres-general] Transactional Statement Queuing

[Jim C. Nasby]

On Mon, Feb 20, 2006 at 09:22:31PM +0000, Simon Riggs wrote:
> Comments? 
> - Is this a good idea? For BI only?
> - Is it important to work on this?

It's certainly a step in the right direction. Here's some (rambling)
thoughts...

One concern is that AFAICT this would be per-database and not
per-cluster. Ideally, this would apply at the machine level, though
trying to cross clusters probably isn't worth the effort.

Ultimately I think a means of assigning priorities to different
connections would be ideal, but even if we had that I think this is
useful on it's own.

I'm also wondering if there would be some way to tie work_mem into
something like this. Though I guess a queue doesn't make too much sense
there; what I'd really like to see for work_mem is a means to prevent it
from running away with all the memory on the box. Maybe at some point
there could be additional criteria for when something is allowed out of
the queue; if we're running low on available memory for work_mem we can
hold off on starting some transactions rather than hammer the hell out
of the server.

Another possibility is bringing query plan cost into the picture. For
example, it seems silly to hold off on a query that we know will be very
fast just because there's a bunch of monster queries ahead of it.

I'm wondering about some form of an IO scheduler. A good example is
vacuum. vaccum_cost_delay is still a hack IMO: why should we force a
delay on vacuum if the machine is otherwise idle? It would be much
better to setup a priority mechanism for IO that allows setting a
minimum percentage. That way you can run a vacuum and set it so it gets
at least 20% of IO bandwidth (so that it doesn't take forever), but
other processes can get first dibs on IO. Yes, I realize that this is
something that would ideally be pushed to the OS, but since there's no
facility for that...

The same could be done for CPU, of course. And work_mem; probably any
resource I guess.

As I said, your proposal is certainly better than nothing, but I expect
we'll want more in the future. Might be worth putting some thought into
it now so that there's a roadmap.
-- 
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