Обсуждение: PostgreSQL server architecture
We run a small in-house data centre for our various operations. Currently, we are moving applications from dedicated boxes to kvm based CentOS-6.1 virtual machines on a single CentOS-6.1 host. At issue is the question on how to handle the PostgreSQL instances that we currently have running on different machines. As we see it presently we have the option of having one VM host a centralized PostgreSQL server with multiple databases or continue with each application specific VM running its own copy of PostgreSQL with just the dedicated application database. Since whatever we chose we are likely to be using five years from now I am soliciting informed option over which option is considered a better choice for the long term. -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
In response to "James B. Byrne" <byrnejb@harte-lyne.ca>: > We run a small in-house data centre for our various > operations. Currently, we are moving applications from > dedicated boxes to kvm based CentOS-6.1 virtual machines > on a single CentOS-6.1 host. At issue is the question on > how to handle the PostgreSQL instances that we currently > have running on different machines. > > As we see it presently we have the option of having one VM > host a centralized PostgreSQL server with multiple > databases or continue with each application specific VM > running its own copy of PostgreSQL with just the dedicated > application database. > > Since whatever we chose we are likely to be using five > years from now I am soliciting informed option over which > option is considered a better choice for the long term. In my experience, you'll be better off using a single DB for all the databases. With proper roles, database permissions, and pg_hba.conf, you'll have acceptable security. The advantage to doing this is better utilization of hardware, since you don't have all the overhead of multiple VMs using up memory, CPU, and IO load. You also have less instances to monitor. The disadvantage of doing so is a) that it doesn't scale as far, and b) if you have one badly behaved application it can negatively affect other databases. In the case of both A and B, the answer when you hit that problem is to just add another VM or physical machine and move databases off the main server instance an onto their own instance on an as-needed basis. In my experience, what you end up with as time goes on and you learn how things operate are a few database servers housing many database. With things spread out across the multiple instances as seems most logical based on your observation of how they behave. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On 21/12/2011 4:08 AM, Bill Moran wrote: > In response to "James B. Byrne"<byrnejb@harte-lyne.ca>: >> We run a small in-house data centre for our various >> operations. Currently, we are moving applications from >> dedicated boxes to kvm based CentOS-6.1 virtual machines >> on a single CentOS-6.1 host. At issue is the question on >> how to handle the PostgreSQL instances that we currently >> have running on different machines. >> >> As we see it presently we have the option of having one VM >> host a centralized PostgreSQL server with multiple >> databases or continue with each application specific VM >> running its own copy of PostgreSQL with just the dedicated >> application database. >> >> Since whatever we chose we are likely to be using five >> years from now I am soliciting informed option over which >> option is considered a better choice for the long term. > In my experience, you'll be better off using a single DB for all the > databases. With proper roles, database permissions, and pg_hba.conf, > you'll have acceptable security. The advantage to doing this is > better utilization of hardware, since you don't have all the overhead > of multiple VMs using up memory, CPU, and IO load. You also have > less instances to monitor. While I tend to agree with this, there are some important downsides too. Perhaps the most important is that you can't currently use streaming or WAL-shipping replication to replicate only *one* database out of a cluster. You have to replicate all databases in the cluster. If you have some DBs that are small or low traffic but very important, and other DBs that're big or high traffic but less important, this can be a problem. As you noted, it's also harder to isolate performance between DBs and protect more important DBs from response time drops caused by less important but heavily loaded DBs, big reporting queries on other DBs, etc. -- Craig Ringer
On 12/20/11 11:48 AM, James B. Byrne wrote: > we are moving applications from > dedicated boxes to kvm based CentOS-6.1 virtual machines > on a single CentOS-6.1 host Database servers often end up with suboptimal performance on virtual IO hardware. This is especially true if they are sharing storage channels and devices with other virtual machines. The 'big boys' (<cough>IBM</cough>) often give a virtual machine running a database server its own storage channel (typically, the HBA for use with a SAN) to get around this. Ditto, they give VM's their own network adapters Now, if your databases aren't IO performance constrained, this very well may not matter much. -- john r pierce N 37, W 122 santa cruz ca mid-left coast