Slide 1

Slide 1 text

Sharding with the Django ORM Mike Clarke

Slide 2

Slide 2 text

Overview • what is sharding • sharding at Disqus • example application • next steps

Slide 3

Slide 3 text

What is sharding?

Slide 4

Slide 4 text

splitting rows across multiple database instances

Slide 5

Slide 5 text

db polls_poll polls_choices

Slide 6

Slide 6 text

db1 db2 db3 db4 polls_choices_1 polls_choices_2 polls_choices_3 polls_choices_4 polls_choices_5 polls_choices_6 polls_choices_7 polls_choices_8 polls_choices_9 polls_choices_10 polls_choices_11 polls_choices_12 polls_choices_13 polls_choices_14 polls_choices_15 polls_choices_16 polls_choices_17 polls_choices_18 polls_choices_19 polls_choices_20

Slide 7

Slide 7 text

Why shard your db?

Slide 8

Slide 8 text

Advantages • smaller databases (disk space) • faster queries • cheaper hardware • easier to manage • horizontal scaling

Slide 9

Slide 9 text

Disadvantages • code complexity • SPOF becomes many-POF • casscading failures • cross-shard JOINs not an option • materialized views can help • DDL changes more difficult

Slide 10

Slide 10 text

When to shard?

Slide 11

Slide 11 text

When to shard? • as late as possible... • vertical scaling (dedicated hardware) • ...but not too late • migrating to shards is harder with time • system metrics are early indicators • disk IO utilization critical metric

Slide 12

Slide 12 text

Database Hardware • 384 GB RAM • Dual procs, octocore CPUs • 36 HDDs • 32 drive RAID10 for data partition • Many read slaves

Slide 13

Slide 13 text

Buying time • Throttling writes via RabbitMQ / celeryd • Shuffling load from master to read slaves • Role-based read slaves to optimize reads • Diminishing returns of read slave strategy • Logical replication delays via slony • Very pricey

Slide 14

Slide 14 text

Connecting to shards • dedicated pgBouncer machines • HAProxy accepts connections • local pgBouncers for • keepalived for failover / clustering • Required due to max connections & NIC limitations on individual machines

Slide 15

Slide 15 text

sidenote: DB tools • pgbadger w/ rsyslog • http://dalibo.github.com/pgbadger/ • pgbarman for backup • http://www.pgbarman.org/ • slony, bucardo for replication • bucardo offers multi-master

Slide 16

Slide 16 text

sidenote: monitoring • Diamond & enhanced postgres collector • http://github.com/disqus/Diamond • collects per-table stats • os and database metrics all in graphite • WIP visualization tool (open sourced soon) • alert with check_graphite.py

Slide 17

Slide 17 text

Use Cases

Slide 18

Slide 18 text

Use Cases • high throughput (insert / update) tables • votes • posts • tweets • large, non-critical data • import metadata • HTTP headers

Slide 19

Slide 19 text

Extending the Django ORM

Slide 20

Slide 20 text

ORM Challenges • configuring database connections • sequences for auto-incremented ids • routing queries • rewriting table names • generating DDL

Slide 21

Slide 21 text

sample sqlshard app https://github.com/disqus/sharding-example

Slide 22

Slide 22 text

database configuration

Slide 23

Slide 23 text

# settings.py DATABASE_CONFIG = { 'root': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', }, 'servers': { 'default': { 'NAME': 'polls', }, 'sharded': { 'NAME': 'sharded_polls', 'HOSTS': { 0: { ‘HOST’: ‘bacon-is-delicious.disqus.com’, ‘PORT’: 5432 }, 1: { ‘HOST’: ‘i-love-bacon.disqus.com’, ‘PORT’: 5432 }, } } } } DATABASES = dict(DatabaseConfigurator( DATABASE_CONFIG['servers'], DATABASE_CONFIG['root'] )) DATABASE_ROUTERS = ['sqlshards.db.shards.routers.ShardedRouter']

Slide 24

Slide 24 text

>>> from django.db import connections >>> connections.databases.keys() ['default', 'sharded.shard0', 'sharded.shard1']

Slide 25

Slide 25 text

sharding sequences

Slide 26

Slide 26 text

PL/pgSQL function a la Instagram http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram

Slide 27

Slide 27 text

CREATE OR REPLACE FUNCTION next_sharded_id(varchar, int, OUT result bigint) AS $$ DECLARE sequence_name ALIAS FOR $1; shard_id ALIAS FOR $2; seq_id bigint; now_millis bigint; BEGIN SELECT nextval(sequence_name::regclass) % 1024 INTO seq_id; SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis; result := (now_millis - 1351746000000) << 23; result := result | (shard_id << 10); result := result | (seq_id); END; $$ LANGUAGE PLPGSQL;

Slide 28

Slide 28 text

CREATE SEQUENCE polls_choice_0_id_seq; CREATE TABLE "polls_choice_0" ( "id" bigint DEFAULT next_sharded_id('polls_choice_0_id_seq', 0) NOT NULL PRIMARY KEY, "poll_id" integer CHECK ("poll_id" >= 0) NOT NULL, "choice_text" varchar(200) NOT NULL, "votes" integer NOT NULL );

Slide 29

Slide 29 text

defining models

Slide 30

Slide 30 text

# models.py from sqlshards.db.shards.fields import ShardedAutoField from sqlshards.db.shards.models import PartitionModel class PollPartitionBase(PartitionModel): id = ShardedAutoField(primary_key=True, auto_created=True) poll_id = models.PositiveIntegerField(db_index=True) class Meta: abstract = True class Shards: key = 'poll_id' num_shards = 2 cluster = 'sharded' class Choice(PollPartitionBase): choice_text = models.CharField(max_length=200) votes = models.IntegerField()

Slide 31

Slide 31 text

routing queries

Slide 32

Slide 32 text

# sqlshard/db/shards/routers.py class ShardedRouter(object): def db_for_read(self, model, **hints): shard_info = getattr(model, '_shards', None) if shard_info: if not shard_info.is_child: raise ValueError(...) return shard_info.get_database() return None def db_for_write(self, model, **hints): hints['is_write'] = True return self.db_for_read(model, **hints)

Slide 33

Slide 33 text

running queries

Slide 34

Slide 34 text

>>> from polls.models import Poll, Choice >>> Choice.objects.create(poll_id=9000, choice_text=‘Absolutely’, votes=100) >>> Choice.objects.filter(poll_id=9000) [] >>> Choice.objects.all() # Raises exception, cannot run

Slide 35

Slide 35 text

generating DDL

Slide 36

Slide 36 text

$ manage.py sqlpartition polls.choice CREATE OR REPLACE FUNCTION next_sharded_id(varchar, int, OUT result bigint) ... CREATE SEQUENCE polls_choice_0_id_seq; ... CREATE TABLE "polls_choice_0" ( "id" bigint DEFAULT next_sharded_id('polls_choice_0_id_seq', 0) NOT NULL PRIMARY KEY, "poll_id" integer CHECK ("poll_id" >= 0) NOT NULL, "choice_text" varchar(200) NOT NULL, "votes" integer NOT NULL ); ... ALTER TABLE "polls_choice_0" ADD CONSTRAINT "polls_choice_0_poll_id_check_modulo" CHECK (("poll_id") % 2 = 0); ALTER TABLE "polls_choice_1" ADD CONSTRAINT "polls_choice_1_poll_id_check_modulo" CHECK (("poll_id") % 2 = 1);

Slide 37

Slide 37 text

alternatives

Slide 38

Slide 38 text

• shard by schema (Instagram approach) • public => shard0, shard1, ... • shard by database name • route via Django connections • mongodb? • glhf

Slide 39

Slide 39 text

next steps

Slide 40

Slide 40 text

• open source as standalone app • needs better tests • actual documentation • integrate into nydus • parallel query execution • https://github.com/disqus/nydus • beyond python • no tool exists currently that meets our needs • postgres query routing proxy

Slide 41

Slide 41 text

PSA http://postgresweekly.com

Slide 42

Slide 42 text

PSA #2 we’re hirring! disqus.com/jobs

Slide 43

Slide 43 text

Thanks! @mikeclarke for questions shout out to @jasonyan and @zeeg for their hard work