Thread: Multiple data base on same server

From:
"elias ghanem"
Date:

Hi,

I’m using postgresql 8.4

I need to install multiple postgresql dbs on one server but I have some questions:

-Is there any problems (performance wise or other) if I have 10 to 15 DBs on the same server?

-Each DB needs 10 tablespaces, so if I create 10 different tablespaces for each DB I will have 100 to 150 table space on the same server. So can this also cause any problems?

Thanks

From:
Richard Huxton
Date:

On 26/02/10 09:37, elias ghanem wrote:
> Hi,
>
> I'm using postgresql 8.4
>
> I need to install multiple postgresql dbs on one server but I have some
> questions:
>
> -Is there any problems (performance wise or other) if I have 10 to 15 DBs on
> the same server?

Clearly that's going to depend on what they're all doing and how big a
server you have. There's no limitation in PostgreSQL that stops you though.

> -Each DB needs 10 tablespaces, so if I create 10 different tablespaces for
> each DB I will have 100 to 150 table space on the same server. So can this
> also cause any problems?

Do you have 200-300+ disks to put these tablespaces on? If not, I'm not
clear what you are trying to do. Why does each DB need 10 tablespaces?

--
   Richard Huxton
   Archonet Ltd

From:
Richard Huxton
Date:

On 26/02/10 12:45, elias ghanem wrote:
> Hi,
> Thanks for your answer,
> Concerning the second point, each db have different table that are logically
> related (for ex, tables for configuration, tables for business...) plus I'm
> planning to put the indexes on their own tablespaces.
> Concerning the disks I will maybe stored on multiple disks (but surely not
> 200-300). So I'm just wondering If this big number of tablespaces on a same
> db server may cause problems,

If the tablespaces aren't on different disks, I'm not sure what the
point is.

Do you perhaps mean schemas? So you have e.g. a "system" schema with
tables "users", "activity_log" etc? There's no problem with 20-30
schemas per database.

--
   Richard Huxton
   Archonet Ltd

From:
"elias ghanem"
Date:

Hi,
Thanks for your answer,
Concerning the second point, each db have different table that are logically
related (for ex, tables for configuration, tables for business...) plus I'm
planning to put the indexes on their own tablespaces.
Concerning the disks I will maybe stored on multiple disks (but surely not
200-300). So I'm just wondering If this big number of tablespaces on a same
db server may cause problems,
Thanks again.

-----Original Message-----
From: 
[mailto:] On Behalf Of Richard Huxton
Sent: Friday, February 26, 2010 1:44 PM
To: elias ghanem
Cc: 
Subject: Re: [PERFORM] Multiple data base on same server

On 26/02/10 09:37, elias ghanem wrote:
> Hi,
>
> I'm using postgresql 8.4
>
> I need to install multiple postgresql dbs on one server but I have some
> questions:
>
> -Is there any problems (performance wise or other) if I have 10 to 15 DBs
on
> the same server?

Clearly that's going to depend on what they're all doing and how big a
server you have. There's no limitation in PostgreSQL that stops you though.

> -Each DB needs 10 tablespaces, so if I create 10 different tablespaces for
> each DB I will have 100 to 150 table space on the same server. So can this
> also cause any problems?

Do you have 200-300+ disks to put these tablespaces on? If not, I'm not
clear what you are trying to do. Why does each DB need 10 tablespaces?

--
   Richard Huxton
   Archonet Ltd

--
Sent via pgsql-performance mailing list ()
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

From:
Craig James
Date:

Richard Huxton wrote:
> On 26/02/10 12:45, elias ghanem wrote:
>> Hi,
>> Thanks for your answer,
>> Concerning the second point, each db have different table that are
>> logically
>> related (for ex, tables for configuration, tables for business...)
>> plus I'm
>> planning to put the indexes on their own tablespaces.
>> Concerning the disks I will maybe stored on multiple disks (but surely
>> not
>> 200-300). So I'm just wondering If this big number of tablespaces on a
>> same
>> db server may cause problems,
>
> If the tablespaces aren't on different disks, I'm not sure what the
> point is.

Our policy is that *every* database has its own tablespace.  It doesn't cost you anything, and it gives you great
flexibilityif you add new disks.  You can easily move an entire database, or a bunch of databases, by just moving the
datapointing to the new location with symlinks.  Once you put a bunch of databases into a single tablespace, moving
subsetsof them becomes very difficult. 

It also makes it really easy to find who is using resources.

We operate about 450 databases spread across several servers.  Postgres has no trouble at all managing hundreds of
databases.

Craig

From:
"elias ghanem"
Date:

Ok thanks guys for your time

-----Original Message-----
From: Craig James [mailto:]
Sent: Friday, February 26, 2010 4:34 PM
To: Richard Huxton
Cc: elias ghanem; 
Subject: Re: [PERFORM] Multiple data base on same server

Richard Huxton wrote:
> On 26/02/10 12:45, elias ghanem wrote:
>> Hi,
>> Thanks for your answer,
>> Concerning the second point, each db have different table that are
>> logically
>> related (for ex, tables for configuration, tables for business...)
>> plus I'm
>> planning to put the indexes on their own tablespaces.
>> Concerning the disks I will maybe stored on multiple disks (but surely
>> not
>> 200-300). So I'm just wondering If this big number of tablespaces on a
>> same
>> db server may cause problems,
>
> If the tablespaces aren't on different disks, I'm not sure what the
> point is.

Our policy is that *every* database has its own tablespace.  It doesn't cost
you anything, and it gives you great flexibility if you add new disks.  You
can easily move an entire database, or a bunch of databases, by just moving
the data pointing to the new location with symlinks.  Once you put a bunch
of databases into a single tablespace, moving subsets of them becomes very
difficult.

It also makes it really easy to find who is using resources.

We operate about 450 databases spread across several servers.  Postgres has
no trouble at all managing hundreds of databases.

Craig

From:
"Ing. Marcos Ortiz Valmaseda"
Date:

elias ghanem escribió:
>
> Hi,
>
> I’m using postgresql 8.4
>
> I need to install multiple postgresql dbs on one server but I have
> some questions:
>
> -Is there any problems (performance wise or other) if I have 10 to 15
> DBs on the same server?
>
> -Each DB needs 10 tablespaces, so if I create 10 different tablespaces
> for each DB I will have 100 to 150 table space on the same server. So
> can this also cause any problems?
>
> Thanks
>
It´s depends of the features of the server. If is a good server, for
example of 16 GB to 32 of RAM, with 8 a 16 processors, with a good SAN
with RAID -1 for the pg_xlog directory and RAID-10 for the $PG_DATA
using ZFS if you are using Solaris or FreeBSD and xfs or ext3 using
Linux , on a Operating Systems of 64 bits, I think that this load can be
supported.

There are installations of PostgreSQL with more than 400 db, but the
environment is very distribuided on several servers.
About the tablespaces, It´s very necesary to have 10 tablespaces on each
database? Normally, you can separate the table or the tables with more
activity to a rapid disc array (I ´m thinking on a SSD array), other
tablespace for the indexes if you have many, and for example with
pl/proxy you could handle the partitions of your data.
There is not necessaty to have 100 or 150 tablespaces on the same
server. You can separate this on a SAN, you can have two or more main
PostgreSQL servers and several slaves with the data replicated on any
case of data corruption on the main servers.
Rebember look the configuration of the performance of the PostgreSQL
servers: work_mem, shared_buffers, etc
Regards and I hope that comments helps to you.