Re: Priorities for users or queries?

On Fri, 2007-02-23 at 12:07 -0800, Ron Mayer wrote:
> Jim Nasby wrote:
> > The problem with using simple OS priority settings is you leave yourself
> > wide open to priority inversion.
>
> Which is why you either
> (a) note that papers studying priority inversion on RDBMS's
> find that it's a non issue on many RDBMS workloads; and
> (except for real-time databases) you tend to still get
> at least partial benefits even in the face of priority
> inversions.
> or
> (b) use a scheduler in your OS that supports priority
> inheritance or other mechanisms to avoid priority
> inversion problems.
> If you want to use priority inheritance to avoid
> the priority inversion settings it appears versions
> of Linux, BSD, Windows, and Solaris at least give
> you the ability to do so.
>
> > There is already work being done on a queuing system; take a look at the
> > bizgres archives.
>
> Which is cool; but not quite the same as priorities.
>
> It seems to me that Bizgres and/or PostgreSQL would not
> want to re-implement OS features like schedulers.

Its now a TODO item, so I thought I'd add a few more notes for later
implementors.

Some feedback from earlier lives: Teradata's scheduling feature was
regularly used, as was the query queuing system. Both seem to be
effective and desirable as distinct features. There were some problems
in early days with priority inversions, but these were mainly caused by
heavily CPU bound queries interacting with heavily I/O bound queries.
Notably this meant that occasional rogue queries would bring the server
to its knees and this took a long time to identify, isolate and bring to
justice. I would hope to learn from lessons like that for PostgreSQL.

We do already have a home-grown priority mechanism in PostgreSQL:
vacuum_delay. Interestingly it handles both I/O and CPU quite well.

The Bizgres queueing feature is specifically designed to allow the
system to utilise large memories effectively without over-subscription.
If you set a query to a lower priority when its taking up lots of RAM,
you'll probably lose much of the benefit.

Simple scheduling seems to work best in practice. Both Teradata and
Microstrategy have provided implementation with just 3 levels of
priority: H, M, L, together with simple rules for when no queries exist
at higher levels.

None of this is patented or patentable, if kept very generic, IMHO.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com