Re: Resource management in 7.4

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> > I've been thinking about resource management and postgres. I want to
> > develop a user profile system (a-la oracle) which allows a DBA to
> > restrict/configure access to system resources. This would allow a DBA to
> > configure how much CPU time can be used per query/session for any user,
> > the number blocks that can be read/written by a user per query, and
> > perhaps some other things (see below).
>
> I've got really serious reservations about this whole idea. I don't
> like expending even one CPU cycle on it, and I don't like introducing a
> potential cause of unnecessary query failure, and I don't believe that
> the average DBA would be capable of configuring it intelligently.
[snip]
> Another example is that the cost of verifying transaction completion is
> actually paid by the first transaction to visit a tuple after the
> tuple's authoring transaction completes. Should a transaction be
> penalized if it's foolish enough to do a seqscan shortly after someone
> else does a mass insert or update?

Just want to give my $0.02. I believe the whole resource restriction
idea is only interessting when different people share the same data
e.g. several departments have access to a company-wide database for
analysis purposes. In such a case it could be useful to restrict the
resource usage of some users.

On the other hand, in a shared hosting environment, I don't think
anyone would really like to have resource limits. What does it help
if your online shop stops working because too many people are
ordering stuff? IMHO it would be much more useful to have resource
usage accounting in that case, so that users can be charged for
added database usage. That would probably also be easier to
implement because each user would have their own database and
only pg_xlog shared -- that part seems tricky as you said before.

Best Regards,
Michael Paesold