What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 127 Stack Exchange communities.

I am trying to understand some of the data storage and caching details of Stack Overflow.

  • Are the pages build completely from redis cached data or does it need to make some db calls?

Assuming that database calls are required

  • How many (approximate) database calls are to be made to build a complete page?

  • Does the db queries executed to build page involve joins, GROUP BY kind of operations? If so how is their performance under load?

  • Is the db schema denormalized for performance reasons?

share|improve this question
add comment

2 Answers

up vote 21 down vote accepted

Are the pages build completely from redis cached data or does it need to make some db calls?

Sometimes we serve pages from cache, sometimes we do not see: Does Stack Overflow use caching and if so, how?

How many (approximate) database calls are to be made to build a complete page?

select avg(SqlCount + 0.0) from LogsLastTwoDays
where ResponseCode = 200 and Host = 'stackoverflow.com'

7.724438

Does the db queries executed to build page involve joins, GROUP BY kind of operations? If so how is their performance under load?

Yes we use plenty of SQL. It performs fine under our current load.

select avg(Tr + 0.0) from LogsLastTwoDays
where ResponseCode = 200 and Host = 'stackoverflow.com'

53.315843

Is the db schema denormalized for performance reasons?

Yes in some spots, we use filtered indexes and even indexed views. We duplicate some data for perf.

share|improve this answer
    
Thanks. What is the value 53.315843 ? - What percentage(approximate) of stackoverflow requests are build by making database calls ? –  Anoop Oct 26 '11 at 15:57
1  
53ms on avg for us to do all the work we need to do to generate dynamic page and serve it out. –  waffles Oct 26 '11 at 22:26
    
Just a point of clarification that filtered indexes and indexed views do not necessarily mean that we are now using less than Domain/Key Normal Form (see also en.wikipedia.org/wiki/Domain/key_normal_form). –  Norman H Dec 16 '11 at 17:21
add comment

Additional to Sam's excellent stats - on the subject of redis-cache - it should be noted that we (generally, not always) use redis as our 2nd-level cache; our first point of call is usually more local (using redis pub/sub to handle notification of cache invalidation / update).

When we discussed it, we didn't have any fundamental objection to adding this dual-layer caching code into our bespoke redis client (booksleeve) - the only barrier is the subtlety of teasing apart the "SE inc" parts from the "useful to everyone" parts.

share|improve this answer
add comment

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .