Cache-able Queries
We just released the first version of Johnny Cache. It's very exciting to see it done and available to the public. Jason wrote a great write up about the path Johnny has taken recently, so I won't re-hash what he said. Instead, I thought I would give an overview of query cache-ability using the framework.
Just to give a brief overview of how Johnny Cache works, it takes all reads from the database and caches them based on a group of generational keys per table. When a table is written to, the generational key is changed "invalidating" that table from reads. Johnny Cache builds a hash of the tables, sql, and parameters to cache the query.
There are a few ways to optimize the query caching of Johnny.
1. Avoid Dynamic Queries
Dynamic queries are queries that have a dynamic component, typically a date/time stamp. Johnny will not be able to cache queries that are randomly seeded or stamped with the current time since the date/time is inserted into the sql before it is cached. An example:
Post.objects.filter(created_at__gte=\ (datetime.datetime.now() - datetime.timedelta(60))
will yield (in a simplified form):
SELECT * FROM "blog_post" WHERE "blog_post"."created_at" >= "2009-12-28 21:48:14.253433"
As you can see, the date is injected into the sql query, so every time the query is run it will change the cached query. A simple way of make this query cache-able is by changing the date to some solid number. An example:
Post.objects.filter(created_at__gte=\ (datetime.datetime.now().date() - datetime.timedelta(60))
Adding the date() will insert the current date instead of datetime allowing the query to be cached for a full day (or until blog_post is written to)
2. Avoid Joins
Joins across multiple tables will force johnny to use the aggregate generation keys from each table. If one table is changed, all joined queries are invalidated. Though you do want to minimize queries, de-normilization in a web environment is typically a "good" thing (remind me again why we have relational databases on the web?), and will allow for scalability sometimes outside of Johnny. But it also causes johnny to only query each table individually, causing less cache misses.
Ultimately in this case, you need to understand your query and understand that Johnny caches on a per table basis. Stats about your cache hits/misses are great for understanding each table and each page. I recently made a very basic stat analyzer that uses mongodb...not suggested for production, but the link is http://github.com/finder/cache_plot. It will hopefully get better in the near future.
3. Use Transactions
If your database supports transactions, you're in luck...so does Johnny! Transactions delay the actual committing of a query until a defined point in time, whether it be at the end of a request, or manually entered. Johnny caches all local transaction requests to the LocalStore instead of the global cache store (read memcached) until the point of the transaction being committed. This includes table invalidation...so while you're in the middle of a long request changing multiple tables, other users loading in other threads will still be able to read the cached tables until you commit.
You can peruse Django's transaction documentation here: http://docs.djangoproject.com/en/dev/topics/db/transactions/
There are of course other things you can do to avoid cache misses, such as sharding data, but they are well beyond the scope of this article. The most useful tool you can have is an understanding of what is going on at the caching and database layer to avoid cache misses, or perhaps cut down on the number of queries you are doing in the first place.
Comments
Click here to post a comment.This is the job description <a href=" http://www.netvibes.com/ulaulubyg#Non_nude_panties_lolita ">young naked lolitas pics</a> 723849 <a href=" http://www.netvibes.com/kuqyfukip#Lolita_tiny_nude_xxx ">russian lolita model video</a> >:-)) <a href=" http://www.netvibes.com/feloityce#Shy_lolita_bbs_toplist ">shy young top lolita</a> 3060 <a href=" http://www.netvibes.com/osijalunok#Little_lolita_bald_pussy ">best free lolita site</a> caie <a href=" http://www.netvibes.com/oragunufeg#Young_lolita_kidz_images ">young lolita kidz images</a> 688 <a href=" http://www.netvibes.com/qabymekaj#Loli_illegal_blogs_bbs ">lolitas nonude pictures gallery</a> fsnej <a href=" http://www.netvibes.com/igenyposur#Russian_pedo_lolita_nymphet ">teen lolita or 17</a> 2757 <a href=" http://www.netvibes.com/tokunycajy#Japanese_non_nude_lolis ">nude preteen pussy loli</a> =PPP <a href=" http://www.netvibes.com/imefeyfy#Lolita_preteen_nude_arina ">lolita preteen nude arina</a> ynv <a href=" http://www.netvibes.com/orabioa#Nude_loli_pay_sites ">lolita messageboard ls magazine</a> gorvy
I'm a housewife <a href=" http://www.netvibes.com/timuycuso#Teen_models_freeones ">alena model naked</a> 1206 <a href=" http://www.netvibes.com/uutanune#Biker_bikini_model ">16 models nonude</a> 936 <a href=" http://www.netvibes.com/golycuab#Nude_college_model ">teen japanese modeling</a> 2008 <a href=" http://www.netvibes.com/acetimypi#Poser_model_kid ">young model puberty</a> 2871 <a href=" http://www.netvibes.com/lofynagic#Bbs_vicky_model ">naked babe model</a> ossnqr <a href=" http://www.netvibes.com/iliidene#Teen_forums_models ">model eva teen</a> phfyze <a href=" http://www.netvibes.com/urureluby#Janiece_teen_model ">dutch bondage models</a> emunx <a href=" http://www.netvibes.com/yjooomi#Mania's_teen_model ">model river section</a> nbcazk <a href=" http://www.netvibes.com/byitekebi#Erotic_children_models ">model naked woman </a> :-]]] <a href=" http://www.netvibes.com/ryacoled#Nude_teen_models.net ">alina model bbs</a> 226
I'd like to send this parcel to <a href=" http://www.netvibes.com/bidedyrej#Jobs_for_preteens ">preteen nn info</a> %-DDD <a href=" http://www.netvibes.com/halytiliq#Preteen_jailbait_costumes ">blue links preteen</a> 715 <a href=" http://www.netvibes.com/abapajeb#Pic_preteen_tgp ">preteen undewear model</a> 81275 <a href=" http://www.netvibes.com/oarilebop#Exclusive_preteens_nude ">x preteen galleries</a> euw <a href=" http://www.netvibes.com/yioinu#Cp_lover_preteen ">preteens nudist artistic</a> %-PP <a href=" http://www.netvibes.com/amyytua#Lane_model_preteens ">enchanting preteen model</a> 7329 <a href=" http://www.netvibes.com/yryseryc#Tight_preteen_booties ">preteen gay world</a> 6070 <a href=" http://www.netvibes.com/aqyhohudej#Preteen_dark_redhouse ">nn bakini preteen</a> 196 <a href=" http://www.netvibes.com/rameuo#Family_naturism_preteens ">florida erotic preteens</a> 93741 <a href=" http://www.netvibes.com/unytomemek#Preteen_cartoon_gallery ">preteen lesbian nudes</a> scndf
I'm in a band <a href=" http://www.netvibes.com/sehamuka#Preteen_nymphets_lolita_underage ">preteen russian lolitas nude</a> 932 <a href=" http://www.netvibes.com/lirebyko#Home_lolita_comchild_models ">lolis sex stories incest</a> 303 <a href=" http://www.netvibes.com/abolaiga#Baby_lolita_girl_nude ">little lollita sex gallery</a> 7842 <a href=" http://www.netvibes.com/esukofetyf#12_year_lolita_foto ">lolitas free top model</a> =-D <a href=" http://www.netvibes.com/byygabi#Hardcore_lolita_asian_porn ">loli bbs preteen tits</a> nxknjd <a href=" http://www.netvibes.com/ycyhakiy#Hungarian_nude_loli_link ">little russians premium lolitas</a> 862288 <a href=" http://www.netvibes.com/afeaynep#Preteen_lolita_incest_pic ">extreme preteen lolita rape</a> ztc <a href=" http://www.netvibes.com/uquregemu#Lolita_bbs_naked_preteen ">candid young nn lolita</a> :-( <a href=" http://www.netvibes.com/edacyohe#Top_lolita_cp_site ">nn lolita panties models</a> =-[[[ <a href=" http://www.netvibes.com/jagoihuo#Dark_lolli_bbs_preteen ">top sites lolita bbs</a> 1989
Sorry, you must have the wrong number <a href=" http://www.netvibes.com/ulanumadyn#Russian_preteen_action ">plump sexy preteens</a> djtfyx <a href=" http://www.netvibes.com/unutyalaq#Exotic_nonnude_preteen ">preteen sex topsites</a> jia <a href=" http://www.netvibes.com/ofujasuki#Sandra_preteen_pussy ">preteen bikini wet</a> ipys <a href=" http://www.netvibes.com/hagudimob#Preteen_modeling_photo ">preteen angel tgp</a> 8PPP <a href=" http://www.netvibes.com/ajopusury#Preteen_gorgeous_model ">juicy cute preteen</a> 920958 <a href=" http://www.netvibes.com/itekisyto#Naturalist_preteens_pics ">free preteen cameltoes</a> dnn <a href=" http://www.netvibes.com/utyytudi#Model_preteen_petite ">x preteens model</a> nyu <a href=" http://www.netvibes.com/eboritaom#Nonude_girls_preteen ">preteen models undies </a> 958256 <a href=" http://www.netvibes.com/qomacyjil#Dolli_preteen_nude ">preteen used panties</a> 62809 <a href=" http://www.netvibes.com/mimiomene#Preteen_beauty_forum ">horny wet preteen</a> viwi