Database Replication with MySQL and PostgresSQL

(1)

D t b

R li ti

Fabian Mauchle Fachhochschule Rapperswil, 18.12.2008

Database Replication

with MySQL and PostgresSQL

Schedule

• Theory

– Why Replication

– Replication Layout and Types

– Conflicts

• Usage Scenarios

• Implementations

• Test Setup and Scenario

• Conclusion

• Questions?

• Bibliography

2

(2)

Why Replication?

• Few Data, Few Users:

9 • Lots of Data, Few Users:

9 • Few Data, Lots of Users:

9 • Lots of Data, Lots of Users:

?

4

How Replication?

• Use multiple servers! (with replication)

• Requirements

– Transparency

– Consistency (ACID)

Fabian Mauchle 5 Fachhochschule Rapperswil, 18.12.2008

(3)

Dimensions of

Database Replication

• Number

N

Dimensions of

Database Replication

• Bandwidth

• Delay

d

(4)

Replication Layout and Type

Synchronous

Asynchronous

Master-Slave

Multi-Master

10

Replication Layout and Type

• Master-Slave

read update read

replicate

Replication Layout and Type

• Multi-Master

read update read update

(5)

Replication Layout and Type

• Asynchronous

Update

p

Commit OK

Commit

Replicate

Replication Layout and Type

• Synchronous

Update

p

Commit OK

Commit

Update

Commit

Commit OK

Conflicts

(6)

Conflicts

Update

Commit

Update

Commit

Commit OK

Commit

Replicate

Commit OK

Commit

Replicate

Conflicts

1. Avoid Conflicts!

2. Avoid Conflicts!

3. Use Conflict Handler

4. Write Conflict Handler

5. Resolve Manually

Conflicts

• Update Conflict

Id

Text

1 „A“

update

Id

Text

1 „B“

Id

Text

1 „C“

(7)

Conflicts

• Uniqueness Conflict

Id

Text

1 „A“

insert

Id

Text

1 „A“

2 „B“

Id

Text

1 „A“

2 „C“

Conflicts

• Delete Conflict

Id

Text

1 „A“

delete

update

Id

Text

1 „A“

Id

Text

2 „C“

(8)

Usage Scenarios

• High Performance Cluster

– Synchronous Multi-Master (req. low distance)

– Asynchronous Multi-Master (req. conflict resolution)

read

update

Usage Scenarios

• Distributed Databases (large distance)

– Asynchronous Master-Slave (few updates)

– Asynchronous Multi-Master (req. conflict resolution)

Usage Scenarios

• Backup and Data Warehouse

– Asynchronous Master-Slave

read update read

Public Access

Backup

Data Warehouse

Main Application

(9)

Implementations

Fabian Mauchle 25 / 30 Fachhochschule Rapperswil, 18.12.2008

Implementations

Synchronous

Asynchronous

Synchronous

Asynchronous

Master-Slave

Use Multi-Master

9 Multi-Master

9

8 (see next slide)

• Multi-Master Replication by circular Master-Slave Replication

by (Maxia, 2006)

• No automatic conflict resolution

(10)

• MySQL replication internal

Implementations

Query:

update...

1. insert…

2. update...

Transaction-log

1. insert…

2. update...

Transaction-log

Replicate

Replay

Query

Server 1 (master)

Server 2 (slave)

Implementations

No built-in replication support!

Implementations

Synchronous

Asynchronous

Master-Slave

Use Multi-Master

-Slony-I

-Burcado

• PostgreSQL extensions

Burcado

Multi-Master

-Pgpool

(11)

Implementations

Subscribers Other Subscriber Origin User User sl_log sl_log CREATE CREATE

Fabian Mauchle Fachhochschule Rapperswil, 18.12.2008 Slonik ... CREATE TABLE modifications CREATE TRIGGER fills sl_log TRIGGER CREATE prevents 31 Source: (Momjian, 2008)

Test Setup and Scenario

Fabian Mauchle 32 / 30 Fachhochschule Rapperswil, 18.12.2008

Test Setup and Scenario

• Test against conflict behaviour

• Replication delay not considered

• Performance under high load not considered

VM1

(12)

Test Setup and Scenario

• Database Table

CREATE TABLE test(

INT id PRIMARY KEY AUTO_INCREMENT,

CHAR(10) text NOT NULL UNIQUE,

INT counter NOT NULL);

MySQL Results

• Using Multi-Master layout by (Maxia, 2006)

• Remember: no conflict resolution!

Conflict

Result

Auto Increment

9 Update Conflict

Breaks Consistency

Uniqueness Conflict

Breaks Replication

Delete Conflict

Breaks Consistency

(13)

Conclusion

• Few Data, Few Users:

9 • Lots of Data, Few Users:

9 • Few Data, Lots of Users:

9 • Lots of Data, Lots of Users:

9 but...

37

Conclusion

• Consider at design

• Select layout carefuly

• Try to avoid conflicts

• Closely monitor replication status

(especialy with MySQL Multi-Master Replication)

(14)

Bibliography

• Keating, B. (2001). Challenges Involved in Multimaster Replication. Retrieved 2008,

from

www.dbspecialists.com/files/presentations/mm_replication.html

• Maxia, G. (2006, 20 4). Advanced MySQL Replication. Retrieved 2008, from

www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html

• Momjian, B. (2008, 12). Replication Solutions. Retrieved 2008, from

http://momjian.us/main/writings/pgsql/replication.pdf

• MySQL. (n.d.). Mysql Documentation. Retrieved 11 17, 2008, from

http://dev.mysql.com/doc/

• pgReplicator. (n.d.). PostgreSQL Replicator. Retrieved 2008, from

htt //

li t

f

t

http://pgreplicator.sourceforge.net

• Postgres-R. (n.d.). Terms and Definitons for Database Replication. Retrieved 2008,

from

www.postgres-r.org/documentation/terms

• Wikipedia. (2008, 6 27). Optimistic replication. Retrieved 11 17, 2008, from

http://en.wikipedia.org/wiki/Lazy_replication

• Wikipedia. (2008, 11 7). Replication (computer science). Retrieved 11 17, 2008, from