ScaleFail(and MPs’ expenses)

Copyright © Steve Bell 2009

How we built it

Crash #1: more Apache children than MySQL connections

unreviewed_count = Page.objects.filter( votes__isnull = True).distinct().count()

SELECT COUNT(DISTINCT `expenses_page`.`id`)FROM `expenses_page` LEFT OUTER JOIN `expenses_vote` ON ( `expenses_page`.`id` = `expenses_vote`.`page_id` ) WHERE `expenses_vote`.`id` IS NULL

unreviewed_count = cache.get('homepage:unreviewed_count')if unreviewed_count is None: unreviewed_count = Page.objects.filter( votes__isnull = True ).distinct().count() cache.set('homepage: unreviewed_count', unreviewed_count, 60)

• With 70,000 pages and a LOT of votes...

• DB takes up 135% of CPU

• Cache the count in memcached...

• DB drops to %35 of CPU

unreviewed_count = Page.objects.filter( votes__isnull = True ).distinct().count()

reviewed_count = Page.objects.filter( votes__isnull = False ).distinct().count()

unreviewed_count = Page.objects.filter( is_reviewed = False ).count()

Migrating to InnoDB on a separate server

ssh mps-live "mysqldump mp_expenses" |sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' |

sed 's/CHARSET=latin1/CHARSET=utf8/g' |ssh mysql-big "mysql -u root mp_expenses"

“next” button

def next_global(request): # Next unreviewed page from the whole site all_unreviewed_pages = Page.objects.filter( is_reviewed = False ).order_by('?') if all_unreviewed_pages: return Redirect( all_unreviewed_pages[0].get_absolute_url() ) else: return HttpResponse( 'All pages have been reviewed!' )

def next_global(request): # Next unreviewed page from the whole site all_unreviewed_pages = Page.objects.filter( is_reviewed = False ).order_by('?') if all_unreviewed_pages: return Redirect( all_unreviewed_pages[0].get_absolute_url() ) else: return HttpResponse( 'All pages have been reviewed!' )

import random

def next_global_from_cache(request): page_ids = cache.get('unreviewed_page_ids') if page_ids: return Redirect( '/page/%s/' % random.choice(page_ids) ) else: return next_global(request)

Next time, I’ll use redis

Read-only mode saved our bacon

Copyright © Martin Rowson 2008