Re: PostgreSQL users on webhosting - Mailing list pgsql-general

how about to have only one DB with multiple DB shcemas and assign a
DB user per schema?

Will this solution use the multiple CPUs ? - I think it should....
this is my 2cents.

--- Jeff Davis <jdavis-pgsql@empires.org> wrote:

> Benefits of multiple instances:
> (1) Let's say you're using the one-instance method and one of your
> web
> users is a less-than-talented developer, and makes an infinite loop
> that
> fills the database with garbage. Not only will that hurt
> performance,
> but if it fills the disk than no other users can even commit a
> transaction! If you seperate the instances, you can run each as its
> own
> uid and control each with quotas, etc.
> (2) You can do a certain amount of favoritism, i.e. you can
> allocate a
> lot of resources to your best customers, and less to the low-paying
> customers.
>
> Costs:
> (1) The databases can't use eachother's shared memory. That will
> mean
> that the databases with high activity can't cache data in the
> shared
> memeory of a database with low activity.
> (2) The RAM from the extra processes for each user. If a database
> has 0
> connections, it's still using memory for the postmaster.
> (3) Each instance will require about 30MB of disk for the $PGDATA
> directory. In contrast, using the one-instance method it only
> requires
> 5MB for an additional DB (approximate).
>
> Overall, I'd say it would be difficult to run seperate instances if
> you're trying to have hundreds of people on the same server. If you
> have
> a more managable number you could do it quite effectively I think.
> I
> would recommend lowering the per-instance shared memory so that the
> OS
> could buffer more (mitigating cost #1).
>
> If you can't run multiple instances, just consider the risks and
> understand that you should try to limit the users somehow.
>
> Regards,
>     Jeff Davis
>
>
> On Wed, 2005-01-05 at 09:49 -0500, Alan Garrison wrote:
> > Jeff Davis wrote:
> >
> > >However, for truly good seperation, I recommend that you run a
> seperate
> > >instance of postgresql (with a seperate $PGDATA directory) for
> each
> > >user, and run it under the UID of that user. It requires a
> little more
> > >disk space per account, but in a dollar amount it's virtually
> zero with
> > >today's disk prices. You will be able to tie the user into
> filesystem
> > >quotas, etc., much more easily, and also you could tune the DBs
> to the
> > >individual users if needed.
> > >
> > >
> > Out of curiosity, what kind of performance hit (whether CPU,
> memory,
> > disk activity) is incurred with having a lot of postmasters
> running in
> > this kind of a setup versus one postmaster with lots of
> databases?  We
> > typically run one postmaster for a lot of separate web
> applications, but
> > I like the notion of a instance-per-user (for both security and
> > maintenance).  In the case of having several "big" databases on
> one
> > server, would tuning stragegies need to keep in mind the settings
> of
> > other instances, or would you just tune each one as if it were
> the only
> > one on the box and let the OS deal with memory+disk load of
> multiple
> > instances?
> >
> > /hope this question makes sense, waiting for coffee to kick in
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>




__________________________________
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com