[ADMIN] Data split -- Creating a copy of database without outage

[Igor Shmain]

Can you please help with advice?

I need to design a solution for a database which will grow and will require
a horizontal split at some moment.

Here is how I am planning to do it: Every record in every table has a shard
number. After the database becomes too large (gets too many requests), the
tables need to be horizontally split. It means that for every table all
records with some specific shard numbers need to be moved to a new database.


My plan is to (1) create a copy of the database on a new server
(backup/restore?), (2) synchronize the databases (how?), and then (3)
forward all requests for the moved shards to the new database. (I will get
rid of the unneeded shards later). An important requirement: there should be
no outage for the users.

What are the ways to achieve it?



Thank you in advance,
-igorS

[Haifeng Liu]

Why not use a partitioned table? You can write a trigger to control which partition the coming data should be inserted.

Regards,
----
Liu Haifeng
Home: http://liuhaifeng.com

Post by Igor Shmain
Can you please help with advice?
I need to design a solution for a database which will grow and will require a horizontal split at some moment.
Here is how I am planning to do it: Every record in every table has a shard number. After the database becomes too large (gets too many requests), the tables need to be horizontally split. It means that for every table all records with some specific shard numbers need to be moved to a new database.
My plan is to (1) create a copy of the database on a new server (backup/restore?), (2) synchronize the databases (how?), and then (3) forward all requests for the moved shards to the new database. (I will get rid of the unneeded shards later). An important requirement: there should be no outage for the users.
What are the ways to achieve it?
Thank you in advance,
-igorS

[Igor Shmain]

Thank you, Liu, for your suggestion.



I might be missing something (I am new to postgres), but it seems that your
suggestion will not help much in my case. Since the number of db requests
will grow with time (more users will come into the system), the current
server will become incapable of serving all the requests quickly enough.



The idea is to increase overall calculation capacity of the system by adding
more servers to it. Throwing more hardware to a single main server is not an
option in my case. Creating multiple replicas (slaves) is also not an good
option - it would be way more efficient to have a group of db servers, each
serving only some subset of users and hosting data for those users only.
Buying new servers in advance is not an option too.



What I am looking for is switching some of the users to another db server
when the capacity of the existing server(s) is not enough. The point is to
do it without interrupting the users' work (so they do not see that horrible
"Sorry! This site is under maintenance.").



If I missed something it would be very kind of you to point this out.



Thank you once again,

-igorS





From: Haifeng Liu [mailto:***@live.com]
Sent: May-29-12 9:13 PM
To: Igor Shmain
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Data split -- Creating a copy of database without
outage



Why not use a partitioned table? You can write a trigger to control which
partition the coming data should be inserted.



Regards,

----

Liu Haifeng

Home: http://liuhaifeng.com









On May 30, 2012, at 4:13 AM, Igor Shmain wrote:





Can you please help with advice?

I need to design a solution for a database which will grow and will require
a horizontal split at some moment.

Here is how I am planning to do it: Every record in every table has a shard
number. After the database becomes too large (gets too many requests), the
tables need to be horizontally split. It means that for every table all
records with some specific shard numbers need to be moved to a new database.

My plan is to (1) create a copy of the database on a new server
(backup/restore?), (2) synchronize the databases (how?), and then (3)
forward all requests for the moved shards to the new database. (I will get
rid of the unneeded shards later). An important requirement: there should be
no outage for the users.

What are the ways to achieve it?



Thank you in advance,
-igorS

[Craig James]

Thank you, Liu, for your suggestion. ****
** **
I might be missing something (I am new to postgres), but it seems that
your suggestion will not help much in my case. Since the number of db
requests will grow with time (more users will come into the system), the
current server will become incapable of serving all the requests quickly
enough.****
** **
The idea is to increase overall calculation capacity of the system by
adding more servers to it. Throwing more hardware to a single main server
is not an option in my case. Creating multiple replicas (slaves) is also
not an good option – it would be way more efficient to have a group of db
servers, each serving only some subset of users and hosting data for those
users only. Buying new servers in advance is not an option too.****
** **
What I am looking for is switching some of the users to another db server
when the capacity of the existing server(s) is not enough. The point is to
do it without interrupting the users’ work (so they do not see that
horrible “Sorry! This site is under maintenance…”).

Londiste is very good at replicating a database. It's normally used for
real-time backup or to load-balance read-only applications, but it can also
be used for the situation you describe. At some point when you decide it's
necessary to split your database, you would create a new database on a
second server, create an empty duplicate schema (using pg_dump's
schema-only feature) then install Londiste. Londiste would replicate your
database in real time up to the point where you were ready to make the
actual switch.

To switch, you'd simply stop Londiste and simultaneously reconfigure your
load-balancing system so that a subset of your users were directed to the
new database. Then you could uninstall Londiste, and clean out each
database by removing the user data that is for users on the other server.

You might also consider partitioning. If you know in advance that you're
going to be discarding large subsets of the data, it will be much more
efficient if you partition it at the outset. When you split your system
in two, the cleanup process will be nothing more than dropping partitions.
You won't be plagued by bloated indexes and files.

Craig

****
** **
If I missed something it would be very kind of you to point this out.****
** **
Thank you once again,****
-igorS****
** **
** **
*Sent:* May-29-12 9:13 PM
*To:* Igor Shmain
*Subject:* Re: [ADMIN] Data split -- Creating a copy of database without
outage****
** **
Why not use a partitioned table? You can write a trigger to control which
partition the coming data should be inserted.****
** **
Regards,****
----****
Liu Haifeng****
Home: http://liuhaifeng.com****
** **
****
** **
On May 30, 2012, at 4:13 AM, Igor Shmain wrote:****
****
Can you please help with advice?****
I need to design a solution for a database which will grow and will
require a horizontal split at some moment.****
Here is how I am planning to do it: Every record in every table has a
shard number. After the database becomes too large (gets too many
requests), the tables need to be horizontally split. It means that for
every table all records with some specific shard numbers need to be moved
to a new database.****
My plan is to (1) create a copy of the database on a new server
(backup/restore?), (2) synchronize the databases (how?), and then (3)
forward all requests for the moved shards to the new database. (I will get
rid of the unneeded shards later). An important requirement: there should
be no outage for the users.****
What are the ways to achieve it?****
****
Thank you in advance,
-igorS****
****
** **

[Amador Alvarez]

Hi ,

I would start with a single high performance tuned database focusing
mainly on dealing efficiently with concurrent activity and identifying
the real hot spots.
If you check out that you really need to go forward on database power,
consider on adding new databases and relocate some users whenever you
need it.

Take a look on the different options related to replication, tunning and
database balancers, set up some testings and get a deeper understanding
on your real needs and performance.

A.A.

Post by Igor Shmain
Thank you, Liu, for your suggestion.
I might be missing something (I am new to postgres), but it seems that
your suggestion will not help much in my case. Since the number of db
requests will grow with time (more users will come into the system),
the current server will become incapable of serving all the requests
quickly enough.
The idea is to increase overall calculation capacity of the system by
adding more servers to it. Throwing more hardware to a single main
server is not an option in my case. Creating multiple replicas
(slaves) is also not an good option -- it would be way more efficient
to have a group of db servers, each serving only some subset of users
and hosting data for those users only. Buying new servers in advance
is not an option too.
What I am looking for is switching some of the users to another db
server when the capacity of the existing server(s) is not enough. The
point is to do it without interrupting the users' work (so they do not
see that horrible "Sorry! This site is under maintenance...").
If I missed something it would be very kind of you to point this out.
Thank you once again,
-igorS
*Sent:* May-29-12 9:13 PM
*To:* Igor Shmain
*Subject:* Re: [ADMIN] Data split -- Creating a copy of database
without outage
Why not use a partitioned table? You can write a trigger to control
which partition the coming data should be inserted.
Regards,
----
Liu Haifeng
Home: http://liuhaifeng.com
Can you please help with advice?
I need to design a solution for a database which will grow and will
require a horizontal split at some moment.
Here is how I am planning to do it: Every record in every table has a
shard number. After the database becomes too large (gets too many
requests), the tables need to be horizontally split. It means that for
every table all records with some specific shard numbers need to be
moved to a new database.
My plan is to (1) create a copy of the database on a new server
(backup/restore?), (2) synchronize the databases (how?), and then (3)
forward all requests for the moved shards to the new database. (I will
get rid of the unneeded shards later). An important requirement: there
should be no outage for the users.
What are the ways to achieve it?
Thank you in advance,
-igorS

[Igor Shmain]

Thank you Craig and thank you Amador for your great help!



Craig's ideas are very useful - I definitely want to look deeper into
Londiste and to use partitioning.



Amador's approach makes perfect sense to me.





Best wishes,

-igor

[Kevin Grittner]

Post by Igor Shmain
I need to design a solution for a database which will grow and will
require horizontal split at some moment.

Just one more bit of "food for thought" -- we have a database with
3TB processing approximately 50 million database transactions per day
(some with a great many statements or affecting many rows) running
quite comfortably on a single machine (actually sharing that machine
with a 2TB database on a separate drive array), without partitioning.

We have done a lot of tuning.

I'm not sure what your basis is for the assumption that you will need
to split the database across machines; you might be right, but you
might be engaging in "premature optimization".

-Kevin

[Igor Shmain]

Thank you for the "food for thoughts", Kevin :-) 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?

Regarding the original question: It was not a question of potency of
postgres. The architecture I am working on is intended to be used by a web
startup. If the product is successfully, many users can start using the
service in a very short time. If that happens, there will be not time to
re-architect the database and the applications; the database will need to be
scaled almost overnight. Total number of requests per day is not a major
criterion for this system. The response time for multiple hits in a short
period of time is more important. The requirement is to serve thousands of
requests per second.

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.

If you say something like "this is just purely theoretical", "what are the
chances to get all those users", "things like that does not happen
overnight", I would totally agree. But look at it from another angle: If
only a few people use the application, the company will stay with a small
server and will not lose much. But if the service is successful, the company
will deploy a whole bunch of servers in a few hours and will be able to
serve all the users quickly :-)

It is a trade-off. More work now in exchange for having a scalable system
tomorrow (yes, yes, it is also called premature optimization :-) And you
know what, it does not look like too much extra work now :-)

If you see real or potential problems in this logic, or heard about similar
implementations, please mention that. I would appreciate it very much.


Best wishes,
-igor



-----Original Message-----
From: Kevin Grittner [mailto:***@wicourts.gov]
Sent: June-02-12 11:12 AM
To: ***@gmail.com; pgsql-***@postgresql.org
Subject: Re: [ADMIN] Data split -- Creating a copy of database without
outage

Post by Igor Shmain
I need to design a solution for a database which will grow and will
require horizontal split at some moment.

Just one more bit of "food for thought" -- we have a database with 3TB
processing approximately 50 million database transactions per day (some with
a great many statements or affecting many rows) running quite comfortably on
a single machine (actually sharing that machine with a 2TB database on a
separate drive array), without partitioning.

We have done a lot of tuning.

I'm not sure what your basis is for the assumption that you will need to
split the database across machines; you might be right, but you might be
engaging in "premature optimization".

-Kevin

[Kevin Grittner]

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

[Igor Shmain]

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

Best wishes,
-igor


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

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

[Jan Nielsen]

Hi Igor,

Post by Igor Shmain
I need to design a solution for a database which will grow and will
require a horizontal split at some moment.

Ok

Post by Igor Shmain
**
Here is how I am planning to do it: Every record in every table has a
shard number. After the database becomes too large (gets too many
requests), the tables need to be horizontally split. It means that for
every table all records with some specific shard numbers need to be moved
to a new database.

Since PG does not support the notion of a shard at the database layer, you
will have to create this yourself. The key to scaling shards is ensuring
there there are no cross-shard joins (or any other shared data); this is an
application-design question so unless you "own" the application-design,
this will be difficult to achieve. If you do own the design, the
application's entity-tree will have to be carefully constructed to avoid
any sharing of resources across shards. RDBMS programmers are taught just
the opposite so this will require diligence from your team with a very
clear, likely simple, application-entity design amenable to shard-ing.

Post by Igor Shmain
****
My plan is to (1) create a copy of the database on a new server
(backup/restore?), (2) synchronize the databases (how?), and then (3)
forward all requests for the moved shards to the new database. (I will get
rid of the unneeded shards later). An important requirement: there should
be no outage for the users.

As I mentioned above, it is unlikely that you will be able to shard an
RDBMS at this level; you need to look at the application entity-design.

****

Post by Igor Shmain
What are the ways to achieve it?

If you mean to dictate a shard solution, very few will be able to help. On
the other hand, if you mean to ask for help in finding a "low-cost
scale-able PG DB solution for a typical RDBMS application with the
potential for rapid growth", then there are a number of options which have
been employed with success. For predominately read applications, use a
single master database replication strategy:


http://www.postgresql.org/docs/current/static/different-replication-solutions.html

For predominately write applications, you will likely need a multi-master
solution (unless you can dictate the application entity-design) which is
more complex territory from a scale-ability and application design
standpoint but you might look at Postgres-XS and Londiste for ideas.


Cheers,

Jan

[Igor Shmain]

Hi Jan,



Thank you so much for your input and sorry about the delay.



I understand most of the limitations, compromises, and sacrifices that come
with distributed databases (sharding). But definitely not all of them. After
working with databases for about 25 years, I have some basic knowledge about
how they work (mssql, oracle, sybase). But I am new to postgres and new to
distributed db approach, so any feedback in those areas is extremely welcome
:-)



If anybody is aware of any successful implementations of distributed
approach with postgres and any information around this area, please let me
know. If I cannot find a suitable prototype and has to "invent the wheel", I
hope to be able to contribute back to the community with my story one day.





Thank you once again,

-igor





From: Jan Nielsen [mailto:***@gmail.com]
Sent: June-02-12 11:00 PM
To: Igor Shmain
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Data split -- Creating a copy of database without
outage



Hi Igor,

On Tue, May 29, 2012 at 2:13 PM, Igor Shmain <***@gmail.com> wrote:

I need to design a solution for a database which will grow and will require
a horizontal split at some moment.



Ok



Here is how I am planning to do it: Every record in every table has a shard
number. After the database becomes too large (gets too many requests), the
tables need to be horizontally split. It means that for every table all
records with some specific shard numbers need to be moved to a new database.



Since PG does not support the notion of a shard at the database layer, you
will have to create this yourself. The key to scaling shards is ensuring
there there are no cross-shard joins (or any other shared data); this is an
application-design question so unless you "own" the application-design, this
will be difficult to achieve. If you do own the design, the application's
entity-tree will have to be carefully constructed to avoid any sharing of
resources across shards. RDBMS programmers are taught just the opposite so
this will require diligence from your team with a very clear, likely simple,
application-entity design amenable to shard-ing.



My plan is to (1) create a copy of the database on a new server
(backup/restore?), (2) synchronize the databases (how?), and then (3)
forward all requests for the moved shards to the new database. (I will get
rid of the unneeded shards later). An important requirement: there should be
no outage for the users.



As I mentioned above, it is unlikely that you will be able to shard an RDBMS
at this level; you need to look at the application entity-design.



What are the ways to achieve it?



If you mean to dictate a shard solution, very few will be able to help. On
the other hand, if you mean to ask for help in finding a "low-cost
scale-able PG DB solution for a typical RDBMS application with the potential
for rapid growth", then there are a number of options which have been
employed with success. For predominately read applications, use a single
master database replication strategy:




http://www.postgresql.org/docs/current/static/different-replication-solution
s.html



For predominately write applications, you will likely need a multi-master
solution (unless you can dictate the application entity-design) which is
more complex territory from a scale-ability and application design
standpoint but you might look at Postgres-XS and Londiste for ideas.





Cheers,



Jan