Postgresql and resource isolation

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 15 Nov 2007 10:12:51 -0800
"Garber, Mikhail" <mgarber@amazon.com> wrote:

Hello,

We are considering using Postgresql for rather large project and I
have questions about where it stands in respect to the following.

Consider these two features already found in major commercial
products.

A) "resource governor" - the ability to configure how much resources
(IO per second, CPU slices) particular users or sessions are allowed
to use

That is controlled from the operating system.

B) ability to collect statistics about how much IO and CPU particular
users and sessions actually use

That one is a little tougher. The database will provide you what
relations are being used and how much. It will not tell you "which"
user did the work.

But again, if you are looking at controlling the instance itself that
is an Operating system problem not a database problem.

I understand that I should be able to get at least some IO data by
querying pg_stat_* views, but what about getting numbers about CPU
usage? And more general question - what are the plans to support
these and similar features in Postgresql?

I can't speak definitely about your request but I can say I would vote
against much of it. I don't care what user is abusing the system. I
care what process is abusing the system and what that process is doing.

I can get that now between the statistics collection of PostgreSQL and
the standard operating system tools already provided to me.

Sincerely,

Joshua D. Drake

Thanks!

---------------------------(end of
broadcast)--------------------------- TIP 4: Have you searched our
list archives?

http://archives.postgresql.org/

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD4DBQFHPI2nATb/zqfZUUQRAgllAJoCzCH3mbt0j1ZGTeOWI70j15Lu5gCWJd6h
wNIo7HHbQtqkbRpN+i2UXQ==
=m4hI
-----END PGP SIGNATURE-----

On Thu, Nov 15, 2007 at 10:12:51AM -0800, Garber, Mikhail wrote:

A) "resource governor" - the ability to configure how much resources (IO
per second, CPU slices) particular users or sessions are allowed to use

PostgreSQL is not like several other commercial systems, in that it doesn't
"take over" the OS as some others do. So it sort of can't do some of these
things.

Moreover, there is a possible problem with putting in these governors, which
is called priority inversion. Because Postgres has quite advanced
concurrency control, it is possible for a user who has been "throttled" to
restrict the ability of others to do their work, too. A paper discussed not
long ago in one of the mailing lists, however, suggests this problem isn't
as bad as some (including me) historically thought. I can't put my fingers
on it this minute, but a little digging in the archives for "nice settings"
or something similar ought to turn it up.

And more general question - what are the plans to support these and
similar features in Postgresql?

If someone comes along with an implementation that doesn't impose serious
costs on the rest of the system, I'd be surprised if people balked. But I
know of nobody working on it today.

--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

On 11/15/07, Joshua D. Drake <jd@commandprompt.com> wrote:

A) "resource governor" - the ability to configure how much resources
(IO per second, CPU slices) particular users or sessions are allowed
to use

That is controlled from the operating system.

What OSes support this?

Trevor Talbot wrote:

On 11/15/07, Joshua D. Drake <jd@commandprompt.com> wrote:

A) "resource governor" - the ability to configure how much resources
(IO per second, CPU slices) particular users or sessions are allowed
to use

That is controlled from the operating system.

What OSes support this?

Well I know that FreeBSD through Jails and Linux through Xen can give
you fine grained control over how much resources a particular instance
can use.

My understanding is that Solaris Zones also give you quite a bit of
control but someone else would have to comment on that.

Sincerely,

Joshua D. Drake

On 11/15/07, Joshua D. Drake <jd@commandprompt.com> wrote:

Well I know that FreeBSD through Jails and Linux through Xen can give
you fine grained control over how much resources a particular instance
can use.

I hadn't seen the new limits in jail, the CPU limiting in particular
is neat. I'm not familiar with Xen's capabilities, but neither one
will actually work for this scenario, since both can only contain an
entire database cluster. In order to get per-session resource
throttiling, it would at least need to support limits on a per-process
basis. Per-user would require grouping several postgres processes,
although this doesn't seem to be as pressing a need (limit number of
connections instead, etc). There doesn't seem to be an answer to I/O
throttling either.

Even if an OS does supply these capabilities, it would seem to require
cooperation from postgres.

My understanding is that Solaris Zones also give you quite a bit of
control but someone else would have to comment on that.

I'd be interested in hearing about Solaris too, it seems to get a lot
of neat capabilities that don't show up elsewhere.