Re: Data split -- Creating a copy of database without outage - Mailing list pgsql-admin

Thank you, Kevin, for this detailed info. It was very helpful.

Best wishes,
-igor


-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: June-06-12 12:39 PM
To: Igor Shmain; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Data split -- Creating a copy of database without
outage

"Igor Shmain" <igor.shmain@gmail.com> wrote:

> Would it be possible for you to mention what hardware (cpu, ram,
> disks, etc.) and software your system uses to support this db size and
> number of transactions?

We have 4 Intel Xeon  X7350 @ 2.93GHz for 16 cores with 128GB RAM.
We've got a pair of drives in RAID 1 for OS on its own controller, four
drives in RAID 10 for xlog directories on its own controller, and a couple
RAID 5 arrays, each about 40 drives, for our two databases (3TB and 2TB).
I'm not exactly clear on the controller configuration there except that I
understand there are separate paths from two controllers to each drive.  All
controllers are using battery-backed cache configured for write-back.

A machine like that is still capable of handling our current load; but the
load is always increasing so we step up the hardware each time we replace a
machine.  The new server (able to handle about twice the load of the one I
just described for our normal transaction mix) has 4 Intel Xeon X7560 @
2.27GHz for 32 cores with 256GB RAM.

We are replicating to each of the databases on these boxes using a pool of 6
database connections to process data from 72 circuit court databases and on
the 2TB from other sources, like Supreme Court and Court of Appeals, Board
of Bar Examiners, etc.  For the read-only web load we have a pool of 30
database connections.  Checking the monitoring system for the read-only web
application, at the moment we are showing:

Active Requests: 3
Requests Per Second: 148.66
Active Sessions: 9081

This is running through a firewall to an apache web server in our DMZ which
just redirects through another firewall to a an apache web server which just
functions as a load balancer which sends the requests to renderers (well,
currently just one, since on the latest hardware one renderer handles the
load) which runs Tomcat connecting to our custom Java middle tier on the
database server machine which provides the connection pooling and manages
each database transaction.  Requests for "boilerplate" content are served
before it gets to this point where it would show in this monitoring; this is
just requests which require database content.  One "request"
above may run up to about 15 queries, many of which contain a large number
of joins.

While the load I show above would amount to about 13 million web requests if
it went on 24 hours per day, load does drop at night.
Last I heard, we had about 5 million requests per day, but that was a couple
years ago and it seems to grow pretty steadily.

Last I checked, the replication consisted of about two million database
transactions per day, many of which have dozens (or
hundreds) of statements modifying data.  When idle time is detected on a
replication source, it is used to compare source data to target, apply fixes
to the target, and log the fixes for review.
(These are infrequent, but I'm not comfortable running multi-master
replication without such automated review.)

> Buying a "super" computer, hoping that one day it will run at full
> throttle is not for startups. Getting such a powerful computer quickly
> and moving the database there is unrealistic. It makes more sense to
> design the system in a way so it can be easily and quickly distributed
> across many relatively inexpensive servers.
> That is why the sharding is needed.

I understand the scaling need, and certainly don't want to discount that.
Cloud resources might be an alternative to sharding in that fashion, at
least to a point.

Before we moved to PostgreSQL we were using a commercial database which
could not keep up with demand using just one box, so we load balanced
between identical servers.  Since the replication is asynchronous and we
didn't want people potentially jumping around in time, we used session
affinity from the renderers to particular database servers to keep a
consistent timeline for each user session.  This sort of approach is a
viable alternative to sharding in some cases.

I hope that helps.

-Kevin