Thread: Multiple data base on same server
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
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
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
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: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Richard Huxton Sent: Friday, February 26, 2010 1:44 PM To: elias ghanem Cc: pgsql-performance@postgresql.org 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 (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
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
Ok thanks guys for your time -----Original Message----- From: Craig James [mailto:craig_james@emolecules.com] Sent: Friday, February 26, 2010 4:34 PM To: Richard Huxton Cc: elias ghanem; pgsql-performance@postgresql.org 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
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.