from mysql to mongodb at sluggy.com

8/9/2019 From MySQL to MongoDB at Sluggy.com

1/69


2/69

Outline

1 IntroductionBasic RundownTechnology History

2 What We Learned

Lessons Learned Over TimeOpen Source Code YieldedWhy MongoDB?

3 Show Me The Code

The Old: MySQL Snippets

4 Final Items

Brendan W. McAdams (Sluggy.com) From MySQL to MongoDB at Sluggy.com Mongo NYC, May 2010 2 / 53
http://find/http://goback/


3/69

Outline


2 What We Learned


3 Show Me The Code


4 Final Items



4/69

Sluggy.com Rundown

Live since August 25, 1997

Updated Every Day (even if its just ller)More Users == More LoadMore Load == More HardwareAdvertising Revenues (e.g. Paying the Bills): I tend to think ofadvertising as a finky spastic mentally retard cat who sometimes wants to jump in my lap

and other times wants to hiss at me and run for the litterbox and often walks in circles

trying to figure out which of the two it wants, followed by dropping dead with a final

thought..."ohhh! food!"

Not Google. Not Trying to be Google. Cant afford to think or scalelike Google.No dedicated staff or operations budget - advertising revenuescover server costs. No ability to cover operations costs, downtime,bugs etc. (all of it means 3am wakeup).



5/69


6/69


7/69

Sluggy.com Rundown









8/69

Sluggy.com Rundown









9/69

Sluggy.com Rundown









10/69


11/69

Sluggy.com Rundown









12/69

Sluggy.com RundownSome Stats

50GB/day (1.5TB of trafc/month on a single virtual box)13 years of daily comics = 6500 image les (just for the comics)Artist is frequently late in updating. System has to handle randomunexpected cache ushes & data updates.LUMP Stack (Lighttpd, Ubuntu, MongoDB & Python)



13/69

Outline


2 What We Learned


3 Show Me The Code


4 Final Items



14/69

Sluggy.com Technology History1997 - 2000

August 25, 1997: Site Started - Static HTML generated viamidnight cron executing Perl. No dynamic content. HTML Editingfor news, navigation, etc.File format requires globs:000217a.gif000217b.gif000217c.gif

All make up the panels for February 17, 2000. Artist likes &understands this format. Code looks for yyMMdd * .(gif|jpg)

via glob and organizes them in order.2000 - Original Developers split off and formed KeenSpot.comusing same code & navigation concepts.



15/69

Sluggy.com Technology History2002

Rewrite Began using MySQL & PHP to allow for No More HTMLEditingSome Features: Dynamic headlines, news, predened templates,dynamic navigation. Members Only Club (behind the scenes

features, sketches, etc)First Folly - Reading MySQL and dynamically generating on eachpage request magnitudes harder than static HTML. I/O DOS.Moved to smarty template caching, generating on-disk cache leupon rst request (expires at midnight)Next 4 years became hellish with Midnight DOS as everyone hitsite and caches regenerated.



16/69


17/69

Sluggy.com Technology History2009

Rewrote the system in Pylons (Python + SQLAlchemy[MySQL])Integrated Beaker caching decorators (templates & code blocks) -simplied adding caching code at need.

Clean ORM model, light & fast with lots of caching.Ran signicantly better than on PHP - innitely more tunable;sensible and sane.memcached continued to become a big, rickety crutch (cascading

failure sucks)


l h l


18/69

Sluggy.com Technology HistoryAug. 3, 2009

Pylons system (v1.0) Live - with MySQLHuge amounts of our code (as much as 80%) was dedicated toconverting UI Objects to and from Database objects. WTF?No more forks - Pylons & Python run threaded. System resourceswere signicantly less taxed by the presentation stack.


Sl T h l Hi


19/69

Sluggy.com Technology HistoryAug. 14, 2009

v1.10 Went Live - MySQL replaced by MongoDB (and MongoKit)Easy Migration - MongoKit was quickly dropped in place andqueries adjusted to new model (Stuck to MySQL schema as muchas possible)Maintained all bug xes on MySQL branch for a few weeks just in

case (never needed it)Performance vastly improved.Over next few months, built tools to use MongoDB in place ofmemcached for caching ( mongodb_beaker )

LAMP replaced by LUMP (Lighttpd, Ubuntu, MongoDB & Python)A few things left in memcached through a combination of makessense there and indolence (open debt task to migrate much ofthe rest)


Sl T h l Hi


20/69

Sluggy.com Technology HistorySept. 2009

MongoDB completely removed our need for Physical hardware -migrated to Virtual hosting ( slicehost )Average system load is 0.05 on a 2G slice.

MongoDB uses 1% of CPU on average.Switchover took 2 minutes (ran data conversion script, deployednew code tag, bounced webserver / pylons app)No downtime in any way attributable to MongoDB since go live

(Cant say the same for MySQL)


O tli


21/69

Outline


2 What We LearnedLessons Learned Over TimeOpen Source Code YieldedWhy MongoDB?

3 Show Me The Code


4 Final Items


h d i t h


22/69

memcached is a crutch. . .meant to make up for RDBMS shortfalls

memcached can be great for things you can afford to lose.Its not just about what you cant afford to lose. Beware ofcascading failures.Over reliance can cause self-DOS after a crash, reboot,accidental ush (even of just one keyset), etc)See Coders at Work (Siebel) for a great runthrough of what led tomemcacheds creation.


memcached is a crutch


23/69






24/69






25/69




as long as were being hyperbolic


26/69

as long as we re being hyperbolic. . .mongodb is a bionic leg replacement. . .

MongoDBs MMAP system gives you a free MRU cache. Done

right and simple; caching on MongoDB is durable, light and fast.No piles of special code to track between what is and isnt in yourcache and stuff it in there.If it falls out of memory cache, it is persisted to disk.But. . . Dont build your MongoDB system like a MySQL system (itll

work, but youll lose speed and exibility)DBRefs should be used sparingly - favor embedded objects (dontbe afraid to denormalize and duplicate data); autorefs can be evenworse as theres a performance penalty imposed.Flexible schemas are good.Wasting your time mapping data back and forth between yourpresentation layer & RDBMS is not just tedious - its error prone.The more you can put in memory, the less you beat on your disks.Especially important on virtual hosting: Be a Good NeighborMongoDB is very good at automatically memory caching frequentlyused data, reducing the amount of code you need to write .




27/69








28/69








29/69






as long as were being hyperbolic. . .


30/69


MongoDBs MMAP system gives you a free MRU cache. Doneright and simple; caching on MongoDB is durable, light and fast.No piles of special code to track between what is and isnt in yourcache and stuff it in there.If it falls out of memory cache, it is persisted to disk.But. . . Dont build your MongoDB system like a MySQL system (itll





31/69







32/69

as o g as we e be g ype bo c. . .mongodb is a bionic leg replacement. . .






33/69

g g ypmongodb is a bionic leg replacement. . .




Caveats


34/69

While there is a lot wrong with our rst pass implementation,MongoDB has been consistent and most importantly: forgiving .

Someone has to enforce a consistent schema - if its not yourdatastore (like a RDBMS does) then your code or ops people (orboth) have to.


Outline


35/69

1 Introduction

Basic RundownTechnology History


3 Show Me The Code

The Old: MySQL Snippets4 Final Items


Open Source Code


36/69

pmongodb_beaker: Beaker Caching for MongoDB

Open Source caching plugin for the Python Beaker stack.Uses distutils plugin entry points.Switching from memcached to Beaker + MongoDB required a 2line cong le change:

1 beaker . ses sio n . typ e = libmemcached2 beaker . sess ion . u r l = 127 .0 .0 .1 :112113 + beaker . ses sio n . typ e = mongodb4 + beaker . sess ion . ur l = mongodb : / / l oc a lh o s t :270 17/ emergencyPants#

sess ions

Lots of useful options in MongoDB BeakerA few limitations on the beaker side which need changes inBeaker (manipulable cache data)


Open Source Code


37/69

mongodb_beaker: Beaker Caching for MongoDB



sess ions



Open Source Code


38/69

mongodb_beaker: Beaker Caching for MongoDB



sess ions



Open Source Codel l h f h


39/69

MongoKit-Pylons: Pylons patches for Python MongORM

Added support to MongoKit to run within a Pylons environment

(threadlocal setup of connection pool)Adding a globally available thread safe connection pool to Pylonwas simple. Add 2 lines to cong/environment.py:

1 from mongokit . ext . pylons_env impor t MongoPylonsEnv2 MongoPylonsEnv. init_mongo ()

Added a few other features to simplify SQLAlchemy migrationsetattr / getattr support to allow mongoDoc. eld instead of thedict interface ( mongoDoc[ eld ])DB AuthenticationA few missing corners such as additional datatypes, enhancedindex denitions on-document, group statement shortcuts, etc.Integrated support for autoreferences (which was/are a bad idea)Changes merged into MongoKit Trunk (GREAT unit test coverage)


Open Source CodeM Ki P l P l h f P h M ORM


40/69







Open Source CodeM Kit P l P l t h f P th M ORM


41/69







Open Source CodeMongoKit Pylons: Pylons patches for Python MongORM


42/69







Open Source CodeMongoKit Pylons: Pylons patches for Python MongORM


43/69








44/69


45/69

Open Source CodeMongoKit-Pylons: Pylons patches for Python MongORM


46/69

MongoKit Pylons: Pylons patches for Python MongORM

Added support to MongoKit to run within a Pylons environment(threadlocal setup of connection pool)Adding a globally available thread safe connection pool to Pylonwas simple. Add 2 lines to cong/environment.py:


Added a few other features to simplify SQLAlchemy migrationsetattr / getattr support to allow mongoDoc. eld instead of thedict interface ( mongoDoc[ eld ])DB Authentication

A few missing corners such as additional datatypes, enhancedindex denitions on-document, group statement shortcuts, etc.Integrated support for autoreferences (which was/are a bad idea)Changes merged into MongoKit Trunk (GREAT unit test coverage)


Outline


47/69

1 Introduction



3 Show Me The CodeThe Old: MySQL Snippets

4 Final Items


Why MongoDB?


48/69

Dynamic Querying

FlexibilityCouchDBs approach appeared obtused and rather unPythonicTools like MongoKit allowed for easy replacement of existingMySQL ORM code with something almost identical

FASTGreat Support & Community AvailableMongoDB Mailing ListIRC: freenode.net #mongodb

Easy access - talking to developers NOT support staff. Oneofcial development company behind MongoDB.Shiny


Why MongoDB?


49/69

Dynamic Querying





Why MongoDB?


50/69

Dynamic Querying





Why MongoDB?


51/69

Dynamic Querying





Why MongoDB?


52/69

Dynamic Querying





Why MongoDB?


53/69

Dynamic Querying





Outline


54/69

1 Introduction



3 Show Me The CodeThe Old: MySQL Snippets

4 Final Items



55/69

MySQL Schema SnippetsThe News Table...


56/69

1 CREATE TABLE news (2 id i n t (11 ) unsigned NOT NULL auto_increment ,3 author _id i n t (11 ) unsigned NOT NULL,4 st ar t_ da te date NOT NULL,5 end_date da te de fa u l t NULL,6 headli ne varc har (25 5) NOT NULL,7 s to ry te x t NOT NULL,8 a rc hi ve t i n y i n t ( 1 ) d e f a ul t 0 ,9 PRIMARY KEY ( id ) ,0 KEY news_author ( author _id ) ,1 CONSTRAINT new s_author FOREIGN KEY ( au tho r_ id ) REFERENCES

admin_users ( id )2 ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET= l a t i n 1 ;


SQLAlchemy ModelThe Admin Object


57/69

1 c l a s s AdminUser ( ORMObject ) :2 pass34 t_admin_users = Table ( admin_users , meta . metadata ,5 Column( id , I n t ege r , p r imary_key=True ) ,6 Column( username , Unicode (45 ) , nu l l ab l e =Fa lse , unique=True ) ,7 Column( password , Unicode (32 ) , nu l l ab l e =Fal se ) ,

8 Column( display_name , Unicode (64) ) , # Should be un ique?9 Column( ema il , Unicode (255) , nu l l ab l e =Fa lse , unique=True ) ,0 Column( l a s t _ i p , IPV4Address , nu l l ab le =True ) ,1 Column( l a s t _ l o g i n _ d a t e , MSTimeStamp , n u l l a b l e =False ) ,2 Column( ava tar , Unicode (255) , nu l l ab l e =True ) ,3 Column( d i sab l ed , Boolean , de fa u l t=False ) ,4 mysql_engine= InnoDB 5 )67 mapper( AdminUser , t_admin _users )


SQLAlchemy ModelThe News Object


58/69

1 c l a s s NewsStory ( ORMObject ) :2 pass34 t_news = Table ( news , meta . metadata ,5 Column( id , I n t ege r , p r imary_key=True ) ,6 Column( au thor _ id , I n t ege r , ForeignKey ( admin_users . id ) , n u l l a bl e

=Fals e ) ,7 Column( s t a r t _ d a t e , Date , nu l l a b l e=Fa lse ) ,8 Column( end_date , Date ) ,9 Column( headl ine , Unicode (255) , nu l l ab l e =Fa l se ) ,0 Column( s t o ry , Unicode , nu l l ab l e =Fal se ) ,1 Column( a rch ive , Boolean , de fa u l t=False ) ,2 mysql_engine= InnoDB 3 )45 mapper ( NewsStory , t_news , pr op er ti es ={6 auth or : r e l a t i on (AdminUser , back re f= news_stor ies )7 } )



59/69

MongoKit ModelAdmin


60/69

1 c l a s s AdminUser ( MongoPylonsDocument ) :2 use_auto ref s = True3 use_do t_no ta t i on = True4 co l l ec ti on_name = admin_users 5 s t r u c t u r e = {6 username : uni code , # unique7 password : unicode ,8 display_name : unicode ,9 ema il : unicode ,0 l a s t _ i p : unicode ,1 l a s t _ l o g i n _ d a t e : date t ime . date t ime ,2 avat ar : unicode ,3 d is abl ed : b oo l ,4 }56 r e q u i r e d _ f i e l d s = [ username , password , ema il ]7 d e fa u lt _ va l ue s = { d i sab l ed : Fal se , l a s t _ l o g i n _ d a t e : da t et ime .

date time .now ( ) }89 i nd ex es = [0 {1 f i e l d s : [ username , password ] ,2 t t l : 86400

3 } ,Brendan W. McAdams (Sluggy.com) From MySQL to MongoDB at Sluggy.com Mongo NYC, May 2010 37 / 53

MongoKit ModelAdmin


61/69

1 c l a s s NewsStory ( MongoPylonsDocument ) :2 use_do t_no ta t i on = True3 use_auto ref s = True4 co l l ec ti on_name = news 5 s t r u c t u r e = {6 auth or : AdminUser ,7 head l ine : unicode ,8 s to ry : unicode ,9 s t a r t _ d a t e : da te t ime . da tet ime ,0 end_date : date t ime . date t ime ,1 arch ived : bool # d e f au l t f a l s e2 }34 r e q u i r e d _ f i e l d s = [ auth or , head l ine , s t o ry , s t a r t _ d a t e ]5 d e fa u lt _ va l ue s = {6 s t a r t _ d a t e : TODAY,7 arch ived : Fal se ,8 }90 i nd ex es = [1 {2 f i e l d s : [ s t a r t _ d a t e , end_date ] ,3 t t l : 86400 ,

4 } ,Brendan W. McAdams (Sluggy.com) From MySQL to MongoDB at Sluggy.com Mongo NYC, May 2010 39 / 53

MongoKit ModelPayPal: OH THE. . . thats not so bad.


62/69


MongoKit ModelPayPal: Instead


63/69

1 ipn_db = None2 t r y :3 dbh = MongoPylonsEnv . mongo_conn ( ) \ 4 [ MongoPylonsEnv . ge t_d efa ul t_d b ( ) ] [ defenders_paypal_ip n ]5 ip n_ id = dbh . i n s e r t ( d i c t ( request .POST. i tems ( ) ) , safe=True )6

ipn_db = dbh . f ind_one ( { _id : i p n_ id } )7 i f n ot ipn_db :8 r a i s e Exce ption ( cou ld no t l ookup , pos t i n s e r t )9 except Except ion , e :0 lo g . exc ept ion ( " P ay pal IPN E r r o r : Unable t o commit IPN d at a t o "1 " Database : %s " % rep r ( e ) )2 r a i s e DefendersIPNExcept ion ( repr ( e) )


MySQL -> Mongo MigrationAdmin Migration


64/69

1 db = mongoModel . AdminUser . _ge t_c onn ect ion ( )2 conn = db . connec t ion ( )3 conn . drop_database ( emergencyPants )45 admins = {}6 f o r user i n meta . Session . query ( AdminUser ) . a l l ( ) :7 _admin = mongoModel . AdminUser ( doc={8 username : user . username ,9 password : user . password ,0 avat ar : user . avatar ,1 d is abl ed : user . d i sab led ,2 display_name : user . display_name ,3 ema il : user . emai l ,4 l a s t _ i p : uni code ( u ser . l a s t _ i p ) ,5 l a s t _ l o g i n _ d a t e : u se r . l a s t _ l o g i n _ d a t e ,6 } ) . save ( )

7 admins [ user . id ] = _admin89 mongoModel . AdminUser . ge t_ c o l l e c t i o n ( ) . ensure_index ( password ,0 d i r e c t i o n =ASCENDING, unique=True )1 mongoModel . AdminUser . ge t_ c o l l e c t i o n ( ) . ensure_index (2 [ ( username , ASCENDING) ,3 ( password , ASCENDING) ] )



65/69

Looking for newsIn Mongo. . .


66/69

1 def _get_news ( date ) :2 news = NewsStory . a l l ( {3 arch ived : Fal se ,4 s t a r t _ d a t e : { $ l t e : c . _ to da y }5 } ) .where( th i s . end_date == nu l l | | th i s . end_date >= new Date ( ) ) . \ 6 so r t ( s t a r t _ d a t e , 1)78 r e t u r n news


Looking for newsThe old MySQL/SQLAlchemy way. . .


67/69

1 def _get_news ( date ) :2 news = meta . Session . query ( NewsStory ) . \ 3 f i l t e r ( and_ ( NewsStory . arch iv e==False , NewsStory . sta rt _d at e d ate ) ) ) \ 5 . order_by ( NewsStory . s t ar t_ da te . desc ( ) ) . a l l ( )6 r e t u r n news


MongoKitUpcoming Features


68/69

update validation. This feature would allow to validate the updatequery in order to match the structure. I have to think carefullyabout it to not miss something.migrate to the new gridfs implementation (wont break the API).

locked eld support : eld that cannot be changed once set. Thisis usefull for slug elds or _id.xtures support : ability to generate on the y documents whichvalues match the structure.

rdf support : like the to_json method but generate a valid rdfdocument.


Questions?Contact Info


69/69

Contact Me

twitter: @ritemail: [email protected]: http://hg.evilmonkeylabs.net

Pressing Questions?IRC - freenode.net #mongodb

MongoDB Users List -http://groups.google.com/group/mongodb-user

Mongo Python Language Center -http://api.mongodb.org/python/1.6%2B/index.html(Links to Java driver docs, and many of the third party libraries. Pythonic!)

MongoKit -http://bitbucket.org/namlook/mongokit/wiki/Home

Pylons - http://pylonshq.com

http://hg.evilmonkeylabs.net/http://groups.google.com/group/mongodb-userhttp://api.mongodb.org/python/1.6%2B/index.htmlhttp://bitbucket.org/namlook/mongokit/wiki/Homehttp://pylonshq.com/http://pylonshq.com/http://bitbucket.org/namlook/mongokit/wiki/Homehttp://api.mongodb.org/python/1.6%2B/index.htmlhttp://groups.google.com/group/mongodb-userhttp://hg.evilmonkeylabs.net/http://find/http://goback/

from mysql to mongodb at sluggy.com

Documents