Thread: Advice on running two database clusters on one server machine

Advice on running two database clusters on one server machine

From
"Andreas Philipp"
Date:
Hi all,

We are implementing a hospital information system and a human
resources/payroll processing system on two identical dedicated servers with
two Xeon Quad Core processors and 32 GB RAM each, both servers being attached
via FC to a SAN, and both applications running on PostgreSQL 8.3 / CentOS 51.

We are wondering about the advisability to distribute the databases between
the two server machines, both machines acting as active production systems
for one application each, and as warm standby servers for the other, using
WAL shipping to a second database cluster running on another port on each of
the two server machines.

What would be the performance cost of doing so, rather than running all
databases on one database cluster on one machine, and using the second
machine as a warm standby server for all databases of the two applications?

What other considerations should we take into account? We have no prior
experience with PostgeSQL administration, having run our previous systems on
Windows Servers and MS SQL Server.

Thanks to all for your input!
--
Andreas Philipp
Clínica Universitaria Teletón
Chía, Colombia

Re: Advice on running two database clusters on one server machine

From
"Scott Marlowe"
Date:
On Sun, Jun 15, 2008 at 11:11 AM, Andreas Philipp
<andreas.philipp@clinicauniversitariateleton.edu.co> wrote:
> Hi all,
>
> We are implementing a hospital information system and a human
> resources/payroll processing system on two identical dedicated servers with
> two Xeon Quad Core processors and 32 GB RAM each, both servers being attached
> via FC to a SAN, and both applications running on PostgreSQL 8.3 / CentOS 51.
>
> We are wondering about the advisability to distribute the databases between
> the two server machines, both machines acting as active production systems
> for one application each, and as warm standby servers for the other, using
> WAL shipping to a second database cluster running on another port on each of
> the two server machines.
>
> What would be the performance cost of doing so, rather than running all
> databases on one database cluster on one machine, and using the second
> machine as a warm standby server for all databases of the two applications?

Well, when both machines are working, your performance would be better
on two machines than on one.  But after a failover, the warm standby
will be running two instances of postgresql, and that's sub-optimal.

> What other considerations should we take into account? We have no prior
> experience with PostgeSQL administration, having run our previous systems on
> Windows Servers and MS SQL Server.

Also, worry about the possibility of switching the wrong databases
when things are going wrong, etc with this type of setup.

However, the biggest concern is your FC-SAN setup.  Some are very
fast.  Some are quite pokey, and once you buy them, the manufacturer
will just laugh and point their fingers at linux, postgresql, or the
phase of the moon as the cause of your performance problem and do
nothing more.

If you haven't tested your FC-SAN setup with both servers running
comlex benchmarks (multiple bonnie++ instances, lots of dds, etc...)
and proven that it's fast, don't expect it to be.

Re: Advice on running two database clusters on one server machine

From
"Kevin Grittner"
Date:
>>> "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
> Andreas Philipp <andreas.philipp@clinicauniversitariateleton.edu.co>
wrote:

>> We are wondering about the advisability to distribute the databases
between
>> the two server machines, both machines acting as active production
systems
>> for one application each, and as warm standby servers for the other,
using
>> WAL shipping to a second database cluster running on another port on
each of
>> the two server machines.
>>
>> What would be the performance cost of doing so, rather than running
all
>> databases on one database cluster on one machine, and using the
second
>> machine as a warm standby server for all databases of the two
applications?
>
> Well, when both machines are working, your performance would be
better
> on two machines than on one.  But after a failover, the warm standby
> will be running two instances of postgresql, and that's sub-optimal.

We have many machines here running multiple PostgreSQL clusters,
including one running two clusters, each with hundreds of GB of data,
which run tens of millions of queries per day without performance
problems.  (It's also running several Java processes through which all
this traffic passes.)  It is a matter of having adequate hardware,
properly tuned databases, efficient software design, and well written
queries.

-Kevin

Re: Advice on running two database clusters on one server machine

From
"Peter Koczan"
Date:
On Sun, Jun 15, 2008 at 12:11 PM, Andreas Philipp
<andreas.philipp@clinicauniversitariateleton.edu.co> wrote:
> Hi all,
>
> We are implementing a hospital information system and a human
> resources/payroll processing system on two identical dedicated servers with
> two Xeon Quad Core processors and 32 GB RAM each, both servers being attached
> via FC to a SAN, and both applications running on PostgreSQL 8.3 / CentOS 51.
>
> We are wondering about the advisability to distribute the databases between
> the two server machines, both machines acting as active production systems
> for one application each, and as warm standby servers for the other, using
> WAL shipping to a second database cluster running on another port on each of
> the two server machines.
>
> What would be the performance cost of doing so, rather than running all
> databases on one database cluster on one machine, and using the second
> machine as a warm standby server for all databases of the two applications?
>
> What other considerations should we take into account? We have no prior
> experience with PostgeSQL administration, having run our previous systems on
> Windows Servers and MS SQL Server.
>
> Thanks to all for your input!

I've experimented a bit with this. Probably the biggest thing to keep
in mind is that different clusters don't play nice with resources,
especially shared memory. You're ostensibly cutting your available
memory in half by running two clusters on one machine.

Other things to keep in mind...they can't use the same user and group
data (so roles and passwords may be different and it's a bit of work
to keep them sync'd up if you would want that). You also have to
maintain two different sets of configs, have data located at two
different places, and listen to data on two different ports. It's
about double the basic administration.

I would recommend one cluster per machine for production machines, if
it works well for you, that is.

Peter