Re: multiple schema or multiple instance

On Thu, 30 Sep 2004 23:18:52 +0200, see_at_reply-to.invalid (Bruno Jargot) wrote:

>Hello,
>
>I would like to understand when it's better to have multiple schema and
>when it's better to have multiple instance.
>By reading this group, I feel I'm missing something...
>
>Having multiple schema in one instance allow a far better utilization of
>the resource (RAM, disk) of the server. And it's more simple for
>administrative task.

Not necessarily. 2 large applications in one instance may interfer and create e.g. periodic bottlenecks whick can be hard to predict. The simplicity is true regarding backup and other basic stuff, but 2 large applications in 1 instance tend to become less and less simple over time as they grow and/or are further developed on.

>Having multiple instance allow an independance between the applications.
>Independance for the parameters of the database, independance for the
>shutdown of the instance.

Correct.

>
>But the key point is the restore of the database. If a schema require a
>incomplete recovery, all the database will be concerned by this
>incomplete recovery and it would not be acceptable for the other
>application using the other schema.

True. There are workarounds, though.

>
>So, for production database, one instance for one application is almost
>always required. Do you agree or do I miss something ?

I agree, with a few reservations. One minor application may be thrown into an instance containing an enormous application without problems.

Anyway, many third-party applications will claim their own instance.

If you tell your vendor that "his" application behaves strangely and then let him know that you have chosen to let it share an instance with another appl, his immediate response is quite predictable........

• Kenneth Koenraadt