Database Replication: Models & Benefits
Translated by
ScribdTranslationsDatabase Replication: Models & Benefits
Translated by
ScribdTranslations07/11/2016
Replication
Definitions
Reply
Copy of a reference dataset.
Horizontal or vertical fragment of a stored table
in a database that is copied and transferred
to another database
The original is called the primary copy (or master or
reference) and the copies are called secondary copies
(either slave or target)
Propagation (diffusion): relaying the update
from a reference data to a target data.
1
07/11/2016
The advantages of replication
Performance improvement
reading the closest copy
avoid the single server bottleneck
Improvement of availability
In the event of a server failure, one can fall back on
the other
Availability = 1 - failure_probabilityN
Failure probability = 5% and 2 copies => availability = 99.75%
Better fault tolerance
possibility of detecting diffuse failures
Access to remote data
Without replication:
All applications access the same DBMS
Database management system overload
Degradation of response time
Important transfers
Low tolerance to failures
Simplicity
Transaction locale
2
07/11/2016
Access to replicated data
Access to multiple copies in an environment
distributed
Parallelism
Load balancing
Best response time
Reduces data transfers
Availability of a replica
even when the reference data is not
available
Lower probability of failure
The problems of replication
Convergence
copies must be kept up to date
At a given moment, they can be different.
- but they must converge towards the same state
coherent where all updates are executed
everywhere in the same order
Transparency: the DBMS must ensure
the dissemination and reconciliation of updates
the resistance to failures
3
07/11/2016
Replication model
Copies
Primary Copy (or Master or Source)
receives updates
Secondary Copy (or Slave or Target)
in consultation "only"
may be designated as Primary in case of copying stoppage
primary
Replication mode
Asymmetric
•a primary copy / N secondary copies
Symmetric
N primary copies
Replication mode: Single-master replication
Designate a copy as primary ("publisher"); the
transactions only update this copy,
the updates to the primary copy are sent
subsequently to secondary copies ("subscribers")
in the order they were applied (asymmetric r);
T1: Start
… Write(x1) ...
x2
Commit
T2 x1
xm
Tn Secondary copies
4
07/11/2016
Mode de réplication:Réplication multi-maîtres
Replication with conflict resolution: A
priority rule allows to solve the
conflicts (symmetric)
Multiple master systems
Propagation of updates: harder to serialize than
mono-master
Propagation of Updates
from the Source to the Target
Synchronous
Global updates in a single transaction
Strong coherence
Slow down the transaction and the flow
Asynchronous
Updates in deferred transactions
A little late
Manual merging of divergent copies
5
07/11/2016
Synchronous diffusion (propagation)
The application receives a response after the
propagation
– 1)transaction locale 2) propagation 3) validation
4) answer
Propagation: number of messages exchanged between
sites
Immediately after each operation (L,E)
1 message per operation (L/E): linear interaction
Deferred just before the end of the transaction
1 message per transaction: constant interaction
Validation: decision made
-through several sites (vote, e.g. 2PC)
– by each site separately (without voting)
A transaction updates
all copies of all
the data that she modifies.
real-time update of
data
too expensive for most
applications
no control of the moment
of update
6
07/11/2016
Asynchronous diffusion (propagation)
The application receives a response before the
propagation
["1) transaction locale","2) validation locale","3)"]
réponse 4) propagation 5) validation
Pessimist:
Updates and distributions made in an order
predefined serializable for transactions
Optimist:
Order between transactions calculated in progress
execution based on the operations
commutative, conflicting. Possibility of abandonment
7
07/11/2016
Asynchronous - asymmetric diffusion
Collect updates on the primary copy
via :
triggers (Oracle, SQL Server, DB2, …)
The image journal after ('log sniffing') (SQL
Server, DB2, ...
Asynchronous - asymmetric diffusion (2)
-Another technique: to broadcast a request rather than the
updated data (e.g., stored procedure call)
Problem: ensuring the proper scheduling of requests
Requests can be broadcasted synchronously.
to all copies but the distribution is approved even if
an update on a copy has failed
necessity of a recovery procedure in this case
8
07/11/2016
Gestion des défaillances de site
Failure of a secondary copy - nothing to
to do
After recovery, apply the missed updates.
during the outage (determined from the
journal)
If the outage is too long, it is better to obtain
a new copy
Failure of a primary copy - same in
the products
Failure of communications
Secondary copies cannot distinguish
1 communication failure from a site failure
If the secondaries elect a new primary and
the former primary is still alive, there will be a
reconciliation issue ...
One solution is that a network partition knows
which is the only one able to function, but
she cannot communicate with others
partitions for knowledge.
static decision: the partition that has the primary
win
-dynamic solution : majority consensus
9
07/11/2016
The Writing Rule of Thomas
We consider three interconnected sites by
a computer network (S1, S2, S3).
The identifiers of each site are totally
ordered.
Each site replicates a string of characters
of initial value 'AB'.
Two users generate two operations
concurrent op1 and op2. They execute them.
locally and spread them to their neighbor.
We consider three sites
interconnected by a network
computer science (S1, S2, S3).
Each site replicates a
string of characters
initial value "AB".
Two users generate
two operations
concurrent op1 and op2. They
execute them locally and
les propagent à leur voisin.
10
07/11/2016
To ensure that the state of the copies converges:
The Thomas rule associates with each reply on a site i a
stampTi (h, n); h is a time and n is a site number.
To modify its reply, a site generates a modification operation.
updated containing the new value and a stamp.
This stamp contains the current time of the site and the number.
of the site.
This operation is executed immediately on the site then
propagated to neighboring sites.
For example, site 1 can generate the operation at any time.
op1 = set(s, " AXB", (13h, 1)), s is a string,
"AXB" the new value,(1 PM, 1) is the stamp.
All products use a variation of this rule.
When an operation is received
on a site, the site compares
the stamp of the operation to that one
from his reply.
if the operation stamp is
more recent then he executes
the operation and updates
the stamp,
Otherwise, he ignores the operation.
A stamp T1(h1, n1) is
newer than a stamp
T2(h2,n2) if:
(h1> h2)ou((h1= h2)et(n1 > n2))
11
07/11/2016
Optimistic replication thus leaves the copies
diverge, provided that they end up
to converge.
However, one must be able to do
converge copies that have diverged
However, in a collaborative context, the
the result obtained is not satisfactory.
12