19

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?

Improve this question

2 Answers 2

Reset to default
21

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 Exchange 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.

Improve this answer
3
  • Thanks. What is the value 53.315843 ? - What percentage(approximate) of stackoverflow requests are build by making database calls ?
    – Anoop
    Oct 26, 2011 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, 2011 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, 2011 at 17:21
10

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.

Improve this answer

You must log in to answer this question.

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