Database Sync - Multi-slave Environment

Database Sync - Multi-Slave Environment

Background

Me: Mark Taylor
- Seven years of Python programming experience
- Ten years of SQL/PostgreSQL experience
The Problem
- Scale a low-transaction (hundereds rows/sec) multi-table SQL database across multiple servers
The Solution needs to be:

Eventually consistent
Scalable
Repeatable
Resilient

Database Sync - Multi-Slave Environment

Background (continued)

Related technologies:
- PostgreSQL Slony-I and PGCluster, MySQL Galera
- TCP proxy servers: HAProxy, nginx, Rackspace Cloud Load Balancer, Amazon Elastic Load Balancer
- Round-robin DNS
Popular solution is to use write-ahead log (WAL) shipping. This is inefficient for this purpose:
- Requires real-time database connection to slaves
- Potentially difficult to tunnel
- Requires db-restore of slave prior to startup
- Synchronizes every transaction on every table

Database Sync - Multi-Slave Environment

Goals

Eventually Consistent
- Slaves quickly catch up with the master
Scalable
- Army of slaves won't kill the master
Repeatable
- Works on many tables and databases
- Simple enough to work on different database types (PostgreSQL, MySQL)
Resilient
- Slaves can catch up after downtime
- Slaves can detect data inconsistency

Database Sync - Multi-Slave Environment

Method

If table receives UPDATEs or DELETEs, then each row needs a unique, indexed, static, non-reusable identifier. If not available, then add one:
ALTER TABLE foo ADD COLUMN id BIGSERIAL UNIQUE
If table never receives UPDATEs, then it needs an indexed monotonically increasing value column, like a TIMESTAMP or SERIAL, possibly populated via a TRIGGER function.
Order tables based on FOREIGN KEY relationships: the FK master table needs to be synced before any table which references it. (DAG: process tables in topological order)

Database Sync - Multi-Slave Environment

Implementation - API

Python: 175 lines, Go: 400 lines
Python: Psycopg2 database API (replacable)
- Provides server-side and dictionary cursors
Python DBSyncTable class:
- init(table_name, unique_id, timestamp, insert_only)
- perform_sync(db_master, db_slave, verbosity=0)
- _get_slave_max(db_slave)
- _gen_master_sql(timestamp_max)
- _gen_insert_sql()
- _gen_update_sql()
- _extract_columns(row_dict, verbosity=0)

Database Sync - Multi-Slave Environment

Implementation - Example: Database

Entropy: Low	Size: Small (thousands) rows.
Updated daily with new gauges, mostly INSERTs.

column

name

country

city

state

lat_lon

data type

TEXT

POINT

modifiers

PRIMARY KEY

Entropy: High	Size: Large
Updated daily via INSERT of thousands of rows.

column

date

value

data type

TEXT

TIMESTAMP

REAL

modifiers

FK to gauges(id)

NOT NULL
INDEXED

Database Sync - Multi-Slave Environment

Implementation - Example: Sync Parameters

gauges

gauges_daily

Unique ID	id
Timestamp Column
Insert Only	False

Unique ID
Timestamp Column	date
Insert Only	True

to master: SELECT * FROM gauges

to replica: SELECT MAX(date) FROM gauges_daily
to master: SELECT * FROM gauges_daily WHERE date > %(max)s

UPDATE gauges
SET name=%(name)s, country=%(country)s, ...
WHERE id = %(id)s

INSERT INTO gauges (id, name, country, ...)
VALUES (%(id)s, %(name)s, %(country)s, ...)

INSERT INTO gauges_daily (id, date, value)
VALUES (%(id)s, %(date)s, %(value)s)

Database Sync - Multi-Slave Environment

Implementation - Example: Python Code

        db_master_conn = psycopg2.connect('dbname=tva user=postgres')

        db_slave_conn  = psycopg2.connect('dbname=tva2 user=postgres')


        table_params = {'table': 'gauges',

                        'unique_id': 'id', 'timestamp': None, 'insert_only': False}

        dbst = DBSyncTable(**table_params)

        ins, upd = dbst.perform_sync(db_master_conn, db_slave_conn)


        table_params = {'table': 'gauges_daily',

                        'unique_id': None, 'timestamp': 'date', 'insert_only': True}

        dbst = DBSyncTable(**table_params)

        ins, upd = dbst.perform_sync(db_master_conn, db_slave_conn)

Database Sync - Multi-Slave Environment

Exercises Left for the Reader

Master
- Create TRIGGER to track deleted rows: TABLE deletions (table TEXT, id TEXT, when TIMESTAMP)
- API instead of slave connecting directly to master database
- Server-side caching of time T₁ -> T₂ query results, until request of T_n where T_n > T₁
Slave
- Sync parameters read from an external resource (file, db table)
- Exception via 'INSTEAD OF UPDATE' and 'INSTEAD OF DELETE'
- Aggregate MD5-CHECK function: run on recently synced timeframes
- Integrate with load balancer: remove itself when unsynced, add when synced
- Handle cyclic table dependencies, via single database transaction
- Add a range of jitter to start-of-sync time, scaled based on number of slaves
- Parallelize table syncs where possible
General
- DDL for slave to create replica database
- Infrastructure knowledge, to allow for slave to waterfall from neighbor
- Automatic creation of database dependency graph

Database Sync - Multi-Slave Environment

Exercise - Read Sync Parameters from Database

Table: sync_info
table	unique_id	timestamp	insert_only	label	order	cron_spec
gauges	id		False	gdaily	1	10 0 * * *
gauges_daily		date	True	gdaily	2	10 0 * * *

Database Sync for Multi-Slave Environments

Background

Background (continued)

Goals

Method

Implementation - API

Implementation - Example: Database

Implementation - Example: Sync Parameters

Implementation - Example: Python Code

Exercises Left for the Reader

Exercise - Read Sync Parameters from Database

Questions?