Re: limiting resources to users

In response to "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>:

> Hi:
>
> Is there a way in Postgres to limit how much cpu, memory, other resources a certain DB gets? A MySQL DB that I'm now replacing with PG has been having problems with run-away users that pound one DB with intense processes running periodically in cron jobs. The effect is that it takes up all the resources and the users of other DBs suffer because the CPU is pegged servicing the first guy..
>
> The PG versio will have one PG instance with many DBs. One set of these DBs are vital for operations and should have the highest priority. Another set of DBs are not vital but their users are the culprits that consume all the resources. I want to give DBs A, B, and C the highest priority, DBs D, E, F the lowest. If a DB F user is taking up all the resources, and a DB B users runs a query, I want to service the DB B guys first and backburner the DB F guy until the DB B guy is serviced first.

You can try various tricks with nice. Have you conclusively determined that
the bottlenecked resource is CPU? In my experience, it's much more likely
to be disk IO, and nice doesn't help with that.

I don't believe there's any way to control this just using PostgreSQL.

One thing to do is to school the users who are hogging resources. Cancel
their queries and send them apologetic emails about how you're sorry but
you have to allow other people to use the system as well. Hopefully, you'll
get them to reconsider their queries and improve the efficiency. Again,
based on my experience, those queries that are hogging the system can
probably be significantly optimized. However, if you don't put pressure
on the users to do so, they're not going to bother.

A better solution would probably be to buy a second database server. Put
the mission-critical DBs on one physical system and the rest on another
system and your problem goes away. Honestly, you might want to consider
this anyway, since it seems like your existing system is nearing overload
if it can't maintain reasonable performance during concurrent loads.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/