Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Sharding with the Django ORM

Sharding with the Django ORM

Overview of the Disqus approach to sharding data with the Djagno ORM

E4a87fe047af5a706b99de6d3d81939a?s=128

Mike Clarke

March 13, 2013
Tweet

More Decks by Mike Clarke

Other Decks in Programming

Transcript

  1. Sharding with the Django ORM Mike Clarke

  2. Overview • what is sharding • sharding at Disqus •

    example application • next steps
  3. What is sharding?

  4. splitting rows across multiple database instances

  5. db polls_poll polls_choices

  6. 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
  7. Why shard your db?

  8. Advantages • smaller databases (disk space) • faster queries •

    cheaper hardware • easier to manage • horizontal scaling
  9. Disadvantages • code complexity • SPOF becomes many-POF • casscading

    failures • cross-shard JOINs not an option • materialized views can help • DDL changes more difficult
  10. When to shard?

  11. 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
  12. Database Hardware • 384 GB RAM • Dual procs, octocore

    CPUs • 36 HDDs • 32 drive RAID10 for data partition • Many read slaves
  13. 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
  14. 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
  15. 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
  16. 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
  17. Use Cases

  18. Use Cases • high throughput (insert / update) tables •

    votes • posts • tweets • large, non-critical data • import metadata • HTTP headers
  19. Extending the Django ORM

  20. ORM Challenges • configuring database connections • sequences for auto-incremented

    ids • routing queries • rewriting table names • generating DDL
  21. sample sqlshard app https://github.com/disqus/sharding-example

  22. database configuration

  23. # 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']
  24. >>> from django.db import connections >>> connections.databases.keys() ['default', 'sharded.shard0', 'sharded.shard1']

  25. sharding sequences

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

  27. 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;
  28. 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 );
  29. defining models

  30. # 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()
  31. routing queries

  32. # 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)
  33. running queries

  34. >>> from polls.models import Poll, Choice >>> Choice.objects.create(poll_id=9000, choice_text=‘Absolutely’, votes=100)

    >>> Choice.objects.filter(poll_id=9000) [<Choice_Partition1: Absolutely>] >>> Choice.objects.all() # Raises exception, cannot run
  35. generating DDL

  36. $ 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);
  37. alternatives

  38. • shard by schema (Instagram approach) • public => shard0,

    shard1, ... • shard by database name • route via Django connections • mongodb? • glhf
  39. next steps

  40. • 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
  41. PSA http://postgresweekly.com

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

  43. Thanks! @mikeclarke for questions shout out to @jasonyan and @zeeg

    for their hard work