DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=CB3APXQPvRGbeIy14urSChu2kgC3AuSyiOxfPrcMkS2X9eFYbL6swPMLop29AcIq5nKUfahV6qQTD6FHLHIadQIxaXq0LFsg5PBflsB+VEpOX7FiVh5Qbosg5p0d3vQlZ9fruGg6maG4ZRlYTbwgiuroG0wo7SgRhN3Eum3Z2rY=
Message-ID: <45D18DF6.9030905@enterprisedb.com>
Date: Tue, 13 Feb 2007 10:07:50 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.8 (X11/20061116)
MIME-Version: 1.0
To: ismo.tuononen@solenovo.fi
CC: pgsql-performance@postgresql.org
Subject: Re: many instances or many databases or many users?
References: <Pine.LNX.4.64.0702131016230.7509@ismoli.solenovo.jns>
In-Reply-To: <Pine.LNX.4.64.0702131016230.7509@ismoli.solenovo.jns>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

ismo.tuononen@solenovo.fi wrote:
> Now we have a server reserved only for postgresql, and I'm wondering if it 
> is better to set up:
> - only one instance and many databases or
> - many instances and only one database/instance or
> - one instance, one database and many users

It depends. One instance should give you best overall throughput, 
because the OS can maximize the use of resources across all users.

There shouldn't be any difference between having one instance with many 
databases and one database and many users.

> server will have 8G memory and 2 processors.
> 
> Earlier we have had problems with difficult queries, some query can take 
> 100% cpu and all other processes have slowed down.

How much data do you have? If it all fits in memory, it's not going to 
make much difference if you have one or more instances. If not, you 
might be better off with many instances dividing the memory between 
them, giving some level of fairness in the memory allocation.

Unfortunately there's no way to stop one query from using 100% CPU 
(though on a 2 CPU server, it's only going to saturate 1 CPU). If you 
have difficult queries like that, I'd suggest that you take a look at 
the access plans to check if they could benefit from adding indexes or 
rewritten in a more efficient way.

> is one instance capable to use that 8G of memory? and share it with 
> different databases/users as needed?
> or will one big and difficult query take all memory and slow down whole 
> server?

One instance can use all of the 8G of memory. You should set your 
shared_buffers to maybe 1-2G. People have different opinions on what 
exactly is the best value; I'd suggest that you try with different 
values to see what gives you the best performance in your application.

> if there is 2 instances one query can't take all memory, but downside is 
> that if instance1 is inactive and instance2 is active, there will be much 
> unused memory (reverved for instance1) and that can produce disk io when 
> instance2 reads lots of data and sorts it.

Yep.

> how you have set up your postgresql server?

I'd say it's more a question of isolation and administration than 
performance. For example, do you want to be able to do filesystem-level 
backups and restores one database at a time? Do you need to shut down 
one database while keeping the rest of them running?

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com