Re: Architectural question

On 2/11/16 12:06 PM, Moreno Andreo wrote:
> Now, the actual question, is:
> Having a VM that can be upgraded with a click on a panel and a reboot,
> and that the server fault is only related to a OS failure, should I keep
> a single-server solution (but I fear that I/O throughput will become
> even more inadequate) or is it convenient to migrate in a 2-server
> system? And, in case of 2-server configuration, what would you recommend?

Much of that depends on your disaster recovery strategy.

> Scenario 1:
> Given 350 databases, I split them in 2, 175 on server 1 and 175 on
> server 2, having pgBouncer to resolve the connections and each server
> has its own workload
>
> Scenario 2:
> Server 1 -> Master, Server 2 -> Slave (Replicated with Slony or...?),
> Server 1 for writes, Server 2 for reads

Personally I'd do kind of a hybrid at this point.

First, I'd split the masters across both servers, with a way to easily
fail over if one of the servers dies.

Next, I'd get streaming replication setup so that the half with masters
on A have replicas on B and vice-versa. That way you can easily recover
from one server or the other failing.

Depending on your needs, could could use synchronous replication as part
of that setup. You can even do that at a per-transaction level, so maybe
you use sync rep most of the time, and just turn it off when inserting
or updating BLOBS.

> Last thing: should blobs (or the whole database directory itself) go in
> a different partition, to optimize performance, or in VM environment
> this is not a concern anymore?

First: IMO concerns about blobs in the database are almost always
overblown. 30GB of blobs on modern hardware really isn't a big deal, and
there's a *lot* to be said for not having to write the extra code to
manage all that by hand.

When it comes to your disk layout, the first things I'd look at would be:

- Move the temporary statistics directory to a RAM disk
- Move pg_xlog to it's own partition

Those don't always help, but frequently they do. And when they do, it
usually makes a big difference.

Beyond that, there might be some advantage to putting blobs on their own
tablespace. Hard to say without trying it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com