Multiple databases in Django

Slide 1

Slide 1 text

MultiDB

Slide 2

Slide 2 text

MultiDB This

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

No content

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

No content

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

No content

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

No content

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

No content

Slide 15

Slide 15 text

We came to Django for the views ... But stayed for the ORM

Slide 16

Slide 16 text

need

Slide 17

Slide 17 text

No content

Slide 18

Slide 18 text

No content

Slide 19

Slide 19 text

NOT

Slide 20

Slide 20 text

No content

Slide 21

Slide 21 text

No content

Slide 22

Slide 22 text

No content

Slide 23

Slide 23 text

1 year BMDB (before

Slide 24

Slide 24 text

No content

Slide 25

Slide 25 text

READ_FROM_SLAVE = bool(os.environ.get('READ_FROM_SLAVE')) DATABASE_ENGINE = 'mysql' DATABASE_NAME = ('setjam_slave' if READ_FROM_SLAVE else 'setjam') DATABASE_USER = 'setjam'

Slide 26

Slide 26 text

It worked! (for

Slide 27

Slide 27 text

1. A huge amount of data was unnecessarily duplicated. 2. Configuration outside of settings.py was error prone. 3. All of the issues found in the following solutions.

Slide 28

Slide 28 text

And then came MultiDB And all was good

Slide 29

Slide 29 text

DATABASES = { 'default': {'ENGINE': 'django.db.backends.mysql', 'NAME': 'setjam', 'USER': 'setjam'}, 'slave': {'ENGINE': 'django.db.backends.mysql', 'NAME': 'slave', 'USER': 'setjam', 'TEST_MIRROR': 'default'}, }

Slide 30

Slide 30 text

Slide 31

Slide 31 text

./manage.py syncdb --database=default ./manage.py syncdb --database=slave

Slide 32

Slide 32 text

./manage.py syncdb --database=default ./manage.py syncdb --database=slave queryset.using('default') queryset.using('crawlers')

Slide 33

Slide 33 text

./manage.py syncdb --database=default ./manage.py syncdb --database=slave queryset.using('default') queryset.using('crawlers') model.objects.dbmanager('default') model.save(using='crawlers') model.delete(using='crawlers')

Slide 34

Slide 34 text

Routers

Slide 35

Slide 35 text

class AppRouter(object): def db_for_read(self, model, **hints): if model._meta.app_label == 'app': return 'appdb' def db_for_write(self, model, **hints): if model._meta.app_label == 'app': return 'appdb'

Slide 36

Slide 36 text

class AppRouter(object): def db_for_read(self, model, **hints): if model._meta.app_label == 'app': return 'appdb' return None def db_for_write(self, model, **hints): if model._meta.app_label == 'app': return 'appdb' return None

Slide 37

Slide 37 text

class AppRouter(object): def allow_relation(obj1, obj2, **hints): if (obj1._meta.app_label == 'app' and obj2._meta.app_label != 'app'): return False if (obj2._meta.app_label == 'app' and obj2._meta.app_label != 'app'): return False return True def allow_syncdb(db, model): if db == 'appdb': return model._meta.app_label == 'app'

Slide 38

Slide 38 text

DATABASE_ROUTERS = ('multidb.MasterSlaveRouter', 'routers.AppRouter')

Slide 39

Slide 39 text

master-slave logical

Slide 40

Slide 40 text

class HorizontalPartitioningRouter(object): parts = ['p1', 'p2', 'p3'] def _db(self, obj): if obj is None or obj.pk is None: return None else: return self.parts[ instance.pk % len(self.parts)] def db_for_read(self, model, **hints): return self._db(hints.get('instance')) def db_for_write(self, model, **hints): return self._db(hints.get('instance'))

Slide 41

Slide 41 text

Slide 42

Slide 42 text

class AppRouter(object): def db_for_read(self, model, **hints): if model._meta.app_label in ( 'app1', 'app2', 'app3'): return 'appdb' def db_for_write(self, model, **hints): if model._meta.app_label in ( 'app1', 'app2', 'app3'): return 'appdb'

Slide 43

Slide 43 text

Slide 44

Slide 44 text

class AppRouter(object): def _db(self, model): if getattr(model, 'CRAWLER', False): return 'appdb' def db_for_read(self, model, **hints): return self._db(self, model) def db_for_write(self, model, **hints): return self._db(self, model) class TMSCrawler(object): CRAWLER = True # ...Field definitions...

Slide 45

Slide 45 text

class ReadFromSlaveRouter(object): slave_db = 'slave' def db_for_read(self, model, **hints): if (model._meta.app_label not in ('django_cache', 'sessions', 'jobs', 'feedimporter') and not getattr(model, 'CRAWLER'): return self.slave_db def db_for_write(self, model, **hints): return 'default'

Slide 46

Slide 46 text

How does a router know whether the query is for read or write?

Slide 47

Slide 47 text

class GenerateFeedCommand(Command): def post_publish(self): if self.incremental: # Purge all processed diffs from database (FeedDiff.objects .filter(id__lte=self.last_diff_id).raw_delete()) else: # If we have just published full feed, # we can get rid of all the diffs FeedDiff.objects.all().raw_delete()

Slide 48

Slide 48 text

class QuerySet(object): @property def db(self): """Return the database that will be used if this query is executed now""" if self._for_write: return self._db or router.db_for_write(self.model) return self._db or router.db_for_read(self.model)

Slide 49

Slide 49 text

How does a router know whether the query is for read or write? queryset._for_write

Slide 50

Slide 50 text

Slave delay

Slide 51

Slide 51 text

class Fetcher(object): def run(self, pk, generation): show = self.prepare(pk, generation) # ...Magic... self.update_show(show.pk) self.postprocess(show.pk)

Slide 52

Slide 52 text

class Fetcher(object): def run(self, pk, generation): show = self.prepare(pk, generation) # ...Magic... self.update_show(show.pk) self.postprocess(show.pk) def update_show(self, pk): (Show.objects.filter(pk=pk) .update(classified=True)) def postprocess(self, pk): if Show.objects.get(pk=pk).classified: # ...Do something...

Slide 53

Slide 53 text

class Fetcher(object): def run(self, pk, generation): show = self.prepare(pk, generation) # ...Magic... self.update_show(show) self.postprocess(show) show.save() def update_show(self, show): show.classified = True def postprocess(self, show): if show.classified: # ...Do something...

Slide 54

Slide 54 text

class Fetcher(object): def run(self, pk, generation): show = self.prepare(pk, generation) # ...Magic... self.update_show(show.pk) self.postprocess(show.pk) def update_show(self, pk): (Show.objects.filter(pk=pk) .update(classified=True)) def postprocess(self, pk): if (Show.objects.get(pk=pk) .using('default') .classified: # ...Do something...

Slide 55

Slide 55 text

jbalogh / django-multidb-router multidb.PinningMasterSlaveRouter from multidb.pinning import use_master @use_master def func(*args, **kw): # Touches the master database.

Slide 56

Slide 56 text

Transactions

Slide 57

Slide 57 text

class BatchInsertManager(models.Manager): @transaction.commit_manually def batch_insert(self, row_iterator, replace=False): # ...Initialization... for row in row_iterator: sql, vals = self.prepare(row, fields) if query_len + len(sql) + len(vals) > max_query_len: count += self.perform_query( connection, header_sql + ', '.join(query_sql), query_vals) connection.commit() # ...Bookkeeping... else: query_sql.append(sql) query_vals.extend(vals) # ...More bookkeeping...

Slide 58

Slide 58 text

How do transaction decorators know which database to work on?

Slide 59

Slide 59 text

How do transaction decorators know which database to work on? They don’t know. They always use default database.

Slide 60

Slide 60 text

Slide 61

Slide 61 text

class BatchInsertManager(models.Manager): def batch_insert(self, row_iterator, replace=False): return (transaction.commit_manually(self._db) (self._batch_insert)(row_iterator, replace=replace)) def _batch_insert(self, row_iterator, replace=False): # ...Initialization... for row in row_iterator: # ...Loop...

Slide 62

Slide 62 text

Going south

Slide 63

Slide 63 text

./manage.py migrate --database=default ./manage.py migrate --database=slave

Slide 64

Slide 64 text

./manage.py migrate --database=default ./manage.py migrate --database=slave orm.using('default') orm.using('crawlers')

Slide 65

Slide 65 text

ticket #370 extended

Slide 66

Slide 66 text

ticket #370 (3

Slide 67

Slide 67 text

1. Perform all schema migrations on each database. 2. Always use .using in data migrations (code reviews help). 3. Accept that we will have a lot of empty tables :-(

Slide 68

Slide 68 text

Dodging chainsaws Practical

Slide 69

Slide 69 text

class RawDeleteQuerySet(QuerySet): @transaction.commit_on_success def raw_delete(self): qn = connection.ops.quote_name sql = 'DELETE FROM %s' % qn(self.model._meta.db_table) cursor = connection.cursor() cursor.execute(sql) return cursor.rowcount

Slide 70

Slide 70 text

class RawDeleteQuerySet(QuerySet): @transaction.commit_on_success def raw_delete(self): connection = connections[self.db] qn = connection.ops.quote_name sql = 'DELETE FROM %s' % qn(self.model._meta.db_table) cursor = connection.cursor() cursor.execute(sql) return cursor.rowcount

Slide 71

Slide 71 text

No content

Slide 72

Slide 72 text

Slide 73

Slide 73 text

No content

Slide 74

Slide 74 text

class RawDeleteQuerySet(QuerySet): def raw_delete(self): return (transaction.commit_on_success(self.db) (self._raw_delete)()) def _raw_delete(self): connection = connections[self.db] qn = connection.ops.quote_name sql = 'DELETE FROM %s' % qn(self.model._meta.db_table) cursor = connection.cursor() cursor.execute(sql) connection.set_dirty() return cursor.rowcount

Slide 75

Slide 75 text

No content

Slide 76

Slide 76 text

class RawDeleteQuerySet(QuerySet): def raw_delete(self): self._for_write = True return (transaction.commit_on_success(self.db) (self._raw_delete)()) def _raw_delete(self): connection = connections[self.db] qn = connection.ops.quote_name sql = 'DELETE FROM %s' % qn(self.model._meta.db_table) cursor = connection.cursor() cursor.execute(sql) connection.set_dirty() return cursor.rowcount

Slide 77

Slide 77 text

No content

Slide 78

Slide 78 text

Slide 79

Slide 79 text

1. Are you sure that there are no such bugs in your code? 2. Are you sure that there are no such bugs in 3rd party applications you use?

Slide 80

Slide 80 text

Concluding

Slide 81

Slide 81 text

1. We need more (& better) documentation. 2. We need full support for multidb in schema migrations. 3. We need better debugging tools (whiny transaction decorators). 4. Attributes like _for_write should be made more visible. (5. We need better testing tools for master-slave configurations.) MultiDB is awesome, but…

Slide 82

Slide 82 text

Marek Stępniowski @mstepniowski