Thread: Resource management in 7.4

Resource management in 7.4

From
Gavin Sherry
Date:
Hi all,

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).

Current, Postgres allows us to trivial calculate the following: CPU time
for a given query (and as such, CPU time for a session), file system
blocks read in/out for a given query (and therefore, for a given session),
and some other stuff. The question is, given access to this information,
how regularly should we check to see if the user has passed the set
limits? My concern is that if we check too regularly, we impact upon
performance. Also, should we be intelligent about it (would hate to error
out of a select when we believe that there is only a small number of
tuples left)?

There are some other resource management features which would be
useful: number of sessions per user, maximum memory usage per user,
maximum disk space for temp files, etc. Oracle supports things like
maximum time in parser, planner and executor, maximum memory blocks
accessed. I think that maximum sessions per user would be useful but the
other ones seem like more trouble than they're worth. Have I missed
any resources here?

Gavin



Re: Resource management in 7.4

From
Tom Lane
Date:
Gavin Sherry <swm@linuxworld.com.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.

To point out just one problem: in the current system design, the backend
that actually issues a write request is not necessarily, or even
probably, the one that dirtied the page.  And you can NOT refuse to
write a dirtied page because of some misbegotten notion about resource
limits; system reliability will go to zero if you do.

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?

In general, I think that per-user resource management policies would
force us to adopt inefficient algorithms that don't share overhead costs
across the whole community.  I'm not eager for that...
        regards, tom lane


Re: Resource management in 7.4

From
Hans-Jürgen Schönig
Date:
Personally I think that configuring things like that is definitely 
beyond the scope of an average administrator.

However, there is one thing which would be useful for many applications: 
It would be nice if there was a way to renice a connection. When it 
comes to reporting it would be nice to have a handle for slowing a 
backend down.

A patch for Linux would be quite easy ("SELECT nice_backend(int)") but I 
don't know how this fits into the Windows port and PostgreSQL on other 
platforms.

I think this would be a straight forward approach fixing most of the 
problems people might have with CPU usage.

Is this the right way to go?
   Regards,       Hans

-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>




Re: Resource management in 7.4

From
"Michael Paesold"
Date:
Tom Lane <tgl@sss.pgh.pa.us> wrote:


> Gavin Sherry <swm@linuxworld.com.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








Re: Resource management in 7.4

From
Tom Lane
Date:
Hans-Jürgen Schönig <postgres@cybertec.at> writes:
> It would be nice if there was a way to renice a connection. When it 
> comes to reporting it would be nice to have a handle for slowing a 
> backend down.

> A patch for Linux would be quite easy ("SELECT nice_backend(int)")

No, it wouldn't.  Or at least, the side effects of just nice'ing a
backend would not be what you'd wish.  See past discussions (try
searching the archives for "priority inversion").
        regards, tom lane


Re: Resource management in 7.4

From
Gavin Sherry
Date:
On Sat, 21 Dec 2002, Sander Steffann wrote:

> Hi,
> 
> > 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.
> 
> I work for an ISP offering shared hosting, and I don't like the idea of
> resource limits at all. Probably two things will happen:
> - Applications/websites will break for no good reason
> - Customers will be complaining about how unreliable PostgreSQL is

The system I would design would resemble that in Oracle. That is, by
default a user has unlimited access to resources. If the admin chooses to
limit resources, then so be it. The reason I've been thinking about it is
some people I've been working with at universities have a problem with
students effectively DoSing shared installations to affect
assessment. This was something they had control over when they used Oracle
:-(.

Gavin



Re: Resource management in 7.4

From
Tom Lane
Date:
Gavin Sherry <swm@linuxworld.com.au> writes:
> ... The reason I've been thinking about it is some people I've been
> working with at universities have a problem with students effectively
> DoSing shared installations to affect assessment. This was something
> they had control over when they used Oracle :-(.

And the students have access to the administrative installation exactly
why?

This is not Oracle: you are not paying a per-installation license.
Put the students on their own box, and you'll have a situation way
more secure than when you used Oracle ;-)
        regards, tom lane


Re: Resource management in 7.4

From
Gavin Sherry
Date:
On Sat, 21 Dec 2002, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > ... The reason I've been thinking about it is some people I've been
> > working with at universities have a problem with students effectively
> > DoSing shared installations to affect assessment. This was something
> > they had control over when they used Oracle :-(.
> 
> And the students have access to the administrative installation exactly
> why?

Should have made this clearer. Students had a shared system to prepare
assessments. Some students were DoSing Postgres so that other students
could not finish assignments. Yes: the students should run their own
installations, they should not need to have 100% uptime. But it didn't
happen like that. It got me thinking...

Gavin