Thread: Re: Is postgresql ca do the job for software deployed in ASP ou SaaS mode?
Re: Is postgresql ca do the job for software deployed in ASP ou SaaS mode?
From
"Mikael Carneholm"
Date:
Do you really need to create one *DB* per client - that is, is one
schema (in the same DB) per client out of the question? If not, I would
look into moving all reference tables (read-only data, constants and
such) into a common schema (with read permission granted to each
client/role), that way reducing the amount of objects needed to be
created/maintained and at the same time reducing the memory requirements
(lots of shared objects == lots of reused shared buffers). Set the
default_tablespace variable per client (login role) also so that the I/O
load can be balanced. A system based on Opterons such as the HP DL385 or
DL585 with two CPUs (or four if you go for the 585), 8-16Gb of RAM and a
decent storage system with 14-28 disks could be worth evaluating.
/Mikael
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of David
Gagnon
Sent: den 3 juli 2006 13:42
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Is postgresql ca do the job for software deployed in
ASP ou SaaS mode?
Hi all,
I've been working on my personal project for 3.5 years now. I
developed an ERP system in web/java. Now the people I will work with
suggest to offers it in Saas mode. Which means my customer will connect
to my website and found they ERP software and data there. It's not the
deployment I planned initially so if you can just validate some
technicals points to be sure it's not crazy using Postgresl here and not
a big $$$ db to do the job.
Typically I will have 1db per client and around 150 tables per db. So
since I hope I didn`t work all those year for nothing .. I expect to
have bunch of clients witch means the same amount of db since I have 1
db/client.
Can I hope having several hundred of db on 1 db server? Like 250 dbs =
250 client = 360 000 tables !!!
So is there a limit for the number of db in the db server ?(this spec is
not on the website) What about the performance? Can I expect to have the
same performance?
Since I put everything on the web I do needs an High Availability
infrastructure. I looked into SlonyI and Mammoth to replicate the db
but since SlonyI use triggers can I expect it to do the job? Is Mammoth
is the only available solution?
Last question and not the least I'm reading this performance list for
several years now and know suggestion about hardware to run postgresl is
discussed. Since I wrote software there severals points about hardware
that I don`t understand. Do you have any suggestion of platform to run
into my Saas configuration? I do need the WISE one! I'm pretty sure
that if I was a big company I would be able throw bunch of $$$$ but it's
not my case. I'm pretty sure it exists out there some piece of Hardware
that would do the job perfectly with a fair price.
So far I did understand that Postgresql loves Opteron and I have looked
into the dl145 series of HP. I did understand that Dell Hardware it`s
not reliable. But it's still not clear what should be my requirement
for memory, disk, nb cpu, cpu power, etc.
I'm pretty sure it`s better to have more slower CPUs that having the
latest Opteron available on the market, or more slower servers that
having the fastest one... am I right? But agains what it`s the optimal
choice?
Thanks you to share your knowledge on those point. I do consider using
Postgresql is the Smart choice in my project since the beginning but
before putting all the money (That I don`t have ..:-)) to buy some
hardware I just want to be sure I'm not crazy!
Thanks for your help I really appreciate it!!
Best Regards
/David
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Hi All, First thanks for your help everyone! Mikael Carneholm wrote: > Do you really need to create one *DB* per client - that is, is one > schema (in the same DB) per client out of the question? If not, I would > look into moving all reference tables (read-only data, constants and > such) into a common schema (with read permission granted to each > client/role), that way reducing the amount of objects needed to be > created/maintained and at the same time reducing the memory requirements > (lots of shared objects == lots of reused shared buffers). For my application there is very little info I can share. Maybe less than 10 on 100 actually so I not sure it worth it ... > Set the > default_tablespace variable per client (login role) also so that the I/O > load can be balanced. A system based on Opterons such as the HP DL385 or > DL585 with two CPUs (or four if you go for the 585), 8-16Gb of RAM and a > decent storage system with 14-28 disks could be worth evaluating. > > /Mikael > I look into the HP DL385 and DL585 on HP site and they are price between 3000 and 15000$$ (base price). Thats quite a difference? So is the HP DL385 with 2 cpus will do the job ? http://h71016.www7.hp.com/dstore/ctoBases.asp?jumpid=re_NSS_dl585storageserver&oi=E9CED&BEID=19701&SBLID=&ProductLineId=450&FamilyId=2230&LowBaseId=&LowPrice=&familyviewgroup=405&viewtype=Matrix http://h71016.www7.hp.com/dstore/ctoBases.asp?ProductLineId=431&FamilyId=2048&jumpid=re_hphqiss/Ovw_Buy/DL385 I will look more deeply into them in detail trying to understand something ... Thanks for your help! Best Regards /David > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of David > Gagnon > Sent: den 3 juli 2006 13:42 > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Is postgresql ca do the job for software deployed in > ASP ou SaaS mode? > > Hi all, > > I've been working on my personal project for 3.5 years now. I > developed an ERP system in web/java. Now the people I will work with > suggest to offers it in Saas mode. Which means my customer will connect > to my website and found they ERP software and data there. It's not the > deployment I planned initially so if you can just validate some > technicals points to be sure it's not crazy using Postgresl here and not > a big $$$ db to do the job. > > Typically I will have 1db per client and around 150 tables per db. So > since I hope I didn`t work all those year for nothing .. I expect to > have bunch of clients witch means the same amount of db since I have 1 > db/client. > > Can I hope having several hundred of db on 1 db server? Like 250 dbs = > 250 client = 360 000 tables !!! > So is there a limit for the number of db in the db server ?(this spec is > not on the website) What about the performance? Can I expect to have the > same performance? > > Since I put everything on the web I do needs an High Availability > infrastructure. I looked into SlonyI and Mammoth to replicate the db > but since SlonyI use triggers can I expect it to do the job? Is Mammoth > is the only available solution? > > Last question and not the least I'm reading this performance list for > several years now and know suggestion about hardware to run postgresl is > discussed. Since I wrote software there severals points about hardware > that I don`t understand. Do you have any suggestion of platform to run > into my Saas configuration? I do need the WISE one! I'm pretty sure > that if I was a big company I would be able throw bunch of $$$$ but it's > not my case. I'm pretty sure it exists out there some piece of Hardware > that would do the job perfectly with a fair price. > > So far I did understand that Postgresql loves Opteron and I have looked > into the dl145 series of HP. I did understand that Dell Hardware it`s > not reliable. But it's still not clear what should be my requirement > for memory, disk, nb cpu, cpu power, etc. > > I'm pretty sure it`s better to have more slower CPUs that having the > latest Opteron available on the market, or more slower servers that > having the fastest one... am I right? But agains what it`s the optimal > choice? > > Thanks you to share your knowledge on those point. I do consider using > Postgresql is the Smart choice in my project since the beginning but > before putting all the money (That I don`t have ..:-)) to buy some > hardware I just want to be sure I'm not crazy! > > Thanks for your help I really appreciate it!! > > Best Regards > /David > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > > > > >
Re: Is postgresql ca do the job for software deployed in ASP ou SaaS mode?
From
"Mikael Carneholm"
Date:
> For my application there is very little info I can share. Maybe less than 10 on 100 actually so I not sure it worth it ... Ok, so 90% of the tables are being written to - this either means that your application uses very little constants, or that it has access to constans that are stored somewhere else (eg, a JMX Mbean that's initialized from property files on application startup). Would it be too much work to redesign the DB model to support more than one client? >I look into the HP DL385 and DL585 on HP site and they are price between >3000 and 15000$$ (base price). Thats quite a difference? So is the HP >DL385 with 2 cpus will do the job ? Yeah, there's quite a difference on the price tags between those two. I'd vote for the DL385 since the sockets for the two extra CPU's won't give you linear scalability per $ in the end. A single machine may be cheaper to administrate, but if administration costs are irrelevant/negligible I'd go for several 2-socket machines instead of one 4-socket machine. /Mikael > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of David > Gagnon > Sent: den 3 juli 2006 13:42 > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Is postgresql ca do the job for software deployed in > ASP ou SaaS mode? > > Hi all, > > I've been working on my personal project for 3.5 years now. I > developed an ERP system in web/java. Now the people I will work with > suggest to offers it in Saas mode. Which means my customer will connect > to my website and found they ERP software and data there. It's not the > deployment I planned initially so if you can just validate some > technicals points to be sure it's not crazy using Postgresl here and not > a big $$$ db to do the job. > > Typically I will have 1db per client and around 150 tables per db. So > since I hope I didn`t work all those year for nothing .. I expect to > have bunch of clients witch means the same amount of db since I have 1 > db/client. > > Can I hope having several hundred of db on 1 db server? Like 250 dbs = > 250 client = 360 000 tables !!! > So is there a limit for the number of db in the db server ?(this spec is > not on the website) What about the performance? Can I expect to have the > same performance? > > Since I put everything on the web I do needs an High Availability > infrastructure. I looked into SlonyI and Mammoth to replicate the db > but since SlonyI use triggers can I expect it to do the job? Is Mammoth > is the only available solution? > > Last question and not the least I'm reading this performance list for > several years now and know suggestion about hardware to run postgresl is > discussed. Since I wrote software there severals points about hardware > that I don`t understand. Do you have any suggestion of platform to run > into my Saas configuration? I do need the WISE one! I'm pretty sure > that if I was a big company I would be able throw bunch of $$$$ but it's > not my case. I'm pretty sure it exists out there some piece of Hardware > that would do the job perfectly with a fair price. > > So far I did understand that Postgresql loves Opteron and I have looked > into the dl145 series of HP. I did understand that Dell Hardware it`s > not reliable. But it's still not clear what should be my requirement > for memory, disk, nb cpu, cpu power, etc. > > I'm pretty sure it`s better to have more slower CPUs that having the > latest Opteron available on the market, or more slower servers that > having the fastest one... am I right? But agains what it`s the optimal > choice? > > Thanks you to share your knowledge on those point. I do consider using > Postgresql is the Smart choice in my project since the beginning but > before putting all the money (That I don`t have ..:-)) to buy some > hardware I just want to be sure I'm not crazy! > > Thanks for your help I really appreciate it!! > > Best Regards > /David > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > > > > >
Mikael Carneholm wrote: >> For my application there is very little info I can share. Maybe less >> > than 10 on 100 actually so I not sure it worth it ... > > Ok, so 90% of the tables are being written to - this either means that > your application uses very little constants, or that it has access to > constans that are stored somewhere else (eg, a JMX Mbean that's > initialized from property files on application startup). Would it be too > much work to redesign the DB model to support more than one client? > Yes configuration are in property files or somewhere else. I will keep this solution in mind but for now I really think that would really complicated for what it will give in return... > >> I look into the HP DL385 and DL585 on HP site and they are price >> > between > >> 3000 and 15000$$ (base price). Thats quite a difference? So is the HP >> > > >> DL385 with 2 cpus will do the job ? >> > > Yeah, there's quite a difference on the price tags between those two. > I'd vote for the DL385 since the sockets for the two extra CPU's won't > give you linear scalability per $ in the end. A single machine may be > cheaper to administrate, but if administration costs are > irrelevant/negligible I'd go for several 2-socket machines instead of > one 4-socket machine. > I do need 2 machines since I need an HA solution. So on top of those question I try to figure out if Slony-I can do the job in my scenario or do I need the Mammoth solution. I'm searching the list right now and there is not a lot of info... :-( Any Idea? So thanks for the info about the DL385 I will look deeply into it ! Best Regards /David > /Mikael > > >> -----Original Message----- >> From: pgsql-performance-owner@postgresql.org >> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of David >> Gagnon >> Sent: den 3 juli 2006 13:42 >> To: pgsql-performance@postgresql.org >> Subject: [PERFORM] Is postgresql ca do the job for software deployed >> > in > >> ASP ou SaaS mode? >> >> Hi all, >> >> I've been working on my personal project for 3.5 years now. I >> developed an ERP system in web/java. Now the people I will work with >> suggest to offers it in Saas mode. Which means my customer will >> > connect > >> to my website and found they ERP software and data there. It's not >> > the > >> deployment I planned initially so if you can just validate some >> technicals points to be sure it's not crazy using Postgresl here and >> > not > >> a big $$$ db to do the job. >> >> Typically I will have 1db per client and around 150 tables per db. So >> since I hope I didn`t work all those year for nothing .. I expect to >> have bunch of clients witch means the same amount of db since I have 1 >> db/client. >> >> Can I hope having several hundred of db on 1 db server? Like 250 dbs >> > = > >> 250 client = 360 000 tables !!! >> So is there a limit for the number of db in the db server ?(this spec >> > is > >> not on the website) What about the performance? Can I expect to have >> > the > >> same performance? >> >> Since I put everything on the web I do needs an High Availability >> infrastructure. I looked into SlonyI and Mammoth to replicate the db >> but since SlonyI use triggers can I expect it to do the job? Is >> > Mammoth > >> is the only available solution? >> >> Last question and not the least I'm reading this performance list for >> several years now and know suggestion about hardware to run postgresl >> > is > >> discussed. Since I wrote software there severals points about >> > hardware > >> that I don`t understand. Do you have any suggestion of platform to >> > run > >> into my Saas configuration? I do need the WISE one! I'm pretty sure >> that if I was a big company I would be able throw bunch of $$$$ but >> > it's > >> not my case. I'm pretty sure it exists out there some piece of >> > Hardware > >> that would do the job perfectly with a fair price. >> >> So far I did understand that Postgresql loves Opteron and I have >> > looked > >> into the dl145 series of HP. I did understand that Dell Hardware it`s >> not reliable. But it's still not clear what should be my requirement >> for memory, disk, nb cpu, cpu power, etc. >> >> I'm pretty sure it`s better to have more slower CPUs that having the >> latest Opteron available on the market, or more slower servers that >> having the fastest one... am I right? But agains what it`s the >> > optimal > >> choice? >> >> Thanks you to share your knowledge on those point. I do consider >> > using > >> Postgresql is the Smart choice in my project since the beginning but >> before putting all the money (That I don`t have ..:-)) to buy some >> hardware I just want to be sure I'm not crazy! >> >> Thanks for your help I really appreciate it!! >> >> Best Regards >> /David >> >> >> >> >> >> >> >> ---------------------------(end of >> > broadcast)--------------------------- > >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> >> >> >> >> >> > > > > > > >
Hi, Mikael, Just my 2 cents: Mikael Carneholm wrote: > Do you really need to create one *DB* per client - that is, is one > schema (in the same DB) per client out of the question? Sometimes, schemas would work _technically_, but not politically, as a postgresql user cannot be prevented from listing all schemas (or even all databases in the same user), regardless whether he/she has access rights. But it is not always acceptable that a customer knows which other customers one has. This forces the use of the "one cluster per customer" paradigm. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
A sodden late night idea ... schemas don't need to have names that are meaningful to outsiders.
Still, the point about "political" aspects is an important one. OTH, schemas provide an elegant way of segregating
data.
My $0.02 (not worth what it was)
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: pgsql-performance-owner@postgresql.org on behalf of Markus Schaber
Sent: Wed 7/5/2006 3:38 AM
To: pgsql-performance@postgresql.org
Cc:
Subject: Re: [PERFORM] Is postgresql ca do the job for software deployed in
Hi, Mikael,
Just my 2 cents:
Mikael Carneholm wrote:
> Do you really need to create one *DB* per client - that is, is one
> schema (in the same DB) per client out of the question?
Sometimes, schemas would work _technically_, but not politically, as a
postgresql user cannot be prevented from listing all schemas (or even
all databases in the same user), regardless whether he/she has access
rights.
But it is not always acceptable that a customer knows which other
customers one has.
This forces the use of the "one cluster per customer" paradigm.
Thanks,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
!DSPAM:44ab96fb98231804284693!
Hi, Gregory, Gregory S. Williamson wrote: > A sodden late night idea ... schemas don't need to have names that > are meaningful to outsiders. Yes, but having schema names like A34FZ37 not only qualifies for thedailywtf.com, but also tends to produce maintainance nightmares. And it still allows the customer to estimate the amount of customers. > Still, the point about "political" aspects is an important one. OTH, > schemas provide an elegant way of segregating data. Yes, they do, and in the ASP case (where we have control over all software that connects to PostgreSQL) we use the one schema per customer paradigm quite successfully. > My $0.02 (not worth what it was) Oh, I think the're at least $0.03 cents worth. :-) Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org