[wdmmg-dev] openspending.org back up

[Stefan Wehrmeyer]

Hi,

thanks a lot guys for fixing it and thanks Carsten for this post mortem.

On 19.03.2011, at 00:30 , Carsten Senger wrote:
> Today openspending.org was most of the time down. The maschine was idle
> most of the time, but almost all connection stalled shortly after every
> apache restart.
> Stefan struggled to find the cause of it. Later Rufus found out
> that the cause where enormous mongodb query response times for some of
> the queries.

Sorry for bailing out early, I'm moving to a new apartment tomorrow and was busy packing stuff.

> A brief review of the mongodb logs showed that there where several
> queries that took an hour or more.
> We suspected that it's the amount of data in mongodb. From the mongo stats
> we 1.4 million objects in the db, had 8 gig of storaged data and 12 gigs of
> allocated disk space. So we decided to drop data and see what happens.
> 
> We droped eu, us, departments and fts (and barnet, but don't do that ;).
> Now we have 330.000 objects, 1 gig of data and 4 gig of db file on
> disc(mongo preallocates big files). The site works well again so far.

So it sounds like we need a scaling plan, because in the end we want the data to be available.
I think we need to figure out exactly what kind of queries we want to support, have these indizes ready and forbid other queries.
It would be a good idea to abstract all read queries into class methods and stop calling .find arbitrarily.

Other ideas? Maybe a master/slave setup might help at a later stage.
Also see: http://trac.openspending.org/ticket/49

> What we also found is that we have many requests from search engine bots.
> When they start to crawl the site they download alot of pages and use the
> /api so we constantly have to compute new aggregates and do big queries on
> mongodb.

 A proper robots.txt file that disallows /api pages would help, I created a ticket for that:
http://trac.openspending.org/ticket/50

Cheers
Stefan