Thread: many instances or many databases or many users?

Hi,

I have used postgresql some years now, but only small databases and only
one database per instance and one user per database.

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

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.

I have used oracle many years and in oracle it's better have one
instance and many users, but oracle can handle many difficult queries in
same time. no process (=query) can slow other queries as much as in postgesql.

there is no need think safety, maintenance, ... only pure performance!

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?

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.

how you have set up your postgresql server?

Ismo

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